.Net Goodies: 2009

Wednesday, November 18, 2009

Download all images from a Webpage using C#

In this article I am going to explain how we can use the power of .Net framework class library for downloading the images imposed on a web page. There is a class called WebBrowser. Check here for msdn documentation.

WebBrowser browser = new WebBrowser();
browser.DocumentCompleted += new WebBrowserDocumentCompletedEventHandler(browser_DocumentCompleted);
browser.Navigate(“http://www.msn.com”);

In the above code snippet, I am creating a new WebBrowser object. After that registering DocumentCompleted event. Now the most important statement, WebBrowser.Navigate, this method will accept a url and will load the webpage. Once the entire webpage is loaded, DocumentCompleted event will get fired. In this method we will do as follow

void browser_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
{
    WebBrowser browser = sender as WebBrowser;
    HtmlElementCollection imgCollection = browser.Document.GetElementsByTagName("img");
    WebClient webClient = new WebClient();

    foreach (HtmlElement img in imgCollection)
    {
        string url = img.FirstChild.GetAttribute("src");
        webClient.DownloadFile(url, url.Substring(url.LastIndexOf('/')));
    }
}

First we will convert the sender object to a WebBrowser. Then we will get all the img tag’s in the loaded webpage. For each img tag we will use the WebClient object to download the image. We will be downloading the file to the current working directory.

Sunday, November 15, 2009

Image slide show using JQuery

In this article I am going to explain, how we can use JQuery for preparing a slid show using various images. You can download JQuery from the following link.

Download JQuery

Below is the aspx page content

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Image slide show</title>
    <script type='text/javascript'
        src="JQuery/jquery-1.3.2.min.js">
    </script>
  
    <script type="text/javascript">
        $(function() {
            var img = $("img.imgclass");           
            $(img).hide().eq(0).show();
            var cnt = img.length;

            setInterval(imgRotate, 5000);

            function imgRotate() {
               $(img).eq((img.length++) % cnt).fadeOut("slow", function() {
                    $(img).eq((img.length) % cnt)
                    .fadeIn("slow");
                });
            }
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
            <img src="Images/1.jpg" class="imgclass" alt="Image1"/>
            <img src="Images/10.jpg" class="imgclass" alt="Image2"/>
            <img src="Images/11.jpg" class="imgclass" alt="Image3"/>
    </form>
</body>
</html>

In the code what I am doing is straight forward. Get all img tags with class “imgclass”. Hide them initially. Every 5 second, call the method “imgRotate”. This method will fadeout the previous image and fade in the current image. Inside the form tag, you can add as many images you needed.

Hope this was helpful thumbs_up

Friday, November 13, 2009

Enabling IIS and ASP.Net 32 bit on 64 bit Windows OS

Assuming .Net framework is not installed. In that case follow the below step to enable IIS to run 32 bit ASP.Net applications.

Navigate to %WINDIR%\Inetpub\AdminScripts directory and run the adsutil.vbs script using the following command line

cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 "true"

If you have .Net framework pre installed. Then after following the above step. Run the following command on the command line from the directory %WINDIR%\Mircosoft.Net\Framework\v2.0.50727

aspnet_regiis -i

Now you can install 32 bit ASP.Net applications.

The above solution is for IIS 6.0 on Windows Server 2003 64 Bit.

Now I am going to explain how to achieve the same on IIS 7.0

As compared to all Microsoft products IIS 7.0 is also backward compatible. This is achieved through IIS 6.0 metabase compatibility and IIS 6.0 WMI compatibility. Installing this will make IIS 6.0 Default Application Pool on IIS 7.0. For installing these options, if IIS 7.0 is already installed, configure it using the Add Roles option under Server Manager.

From Server Manager, Select Web Server(IIS). Now from left pane click on "Add Rol Services". In the new windows, scroll down, to check "IIS 6 Metabase Compatibility" and "IIS 6 WMI Compatibility".

Now open IIS Manger. From left pane select "Set Application Pool Defaults...". Now under General, select "Enable 32-Bit Applications" as true.

Paging and Sorting Optimized with ASP.Net GridView

Lets start by creating an Employee table

CREATE TABLE [dbo].[Employee](
   [EmpID] [int] NOT NULL,
   [Name] [varchar](50) NULL,
   [Department] [varchar](50) NULL,
   [Salary] [decimal](18, 0) NULL
) ON [PRIMARY]

GO

For optimization, we need a stored procedure at database level, which will return one page of data. The following stored procedure is the bottom line of optimization

Create PROCEDURE spGetAllEmployee
(
   @startIndex     int,
   @pageSize       int,
   @sortBy     nvarchar(30),
   @totalEmployees int OUTPUT    
)
AS
   SET NOCOUNT ON
   DECLARE @sqlStatement nvarchar(max)
   DECLARE @upperBound int
   IF @startIndex  < 1
       SET @startIndex = 1
   IF @pageSize < 1
       SET @pageSize = 1
   SET @upperBound = @startIndex + @pageSize
   Select @totalEmployees=Count(*) From Employee
   SET @sqlStatement =
   'SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
       FROM (
       SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ')
           AS rowNumber, * FROM    Employee
       ) AS E
       WHERE  rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
              rowNumber <  ' + CONVERT(varchar(9), @upperBound)

   exec (@sqlStatement)

The stored procedure accepts page index, page size and sort expression. It returns the sorted data for that page index. The procedure contains two SELECT statements. The first one will find out the total employee count while the second statement is dynamic, which will return the sorted records of one page according to the provided page index and page size. If you look into the second SELECT statement you can see the use of ROW_NUMBER() function, which is an addition to SQL Server 2005 and above. What it will do is, it will add an additional column to the record set and places a record number for each row. In the outer query we will filter the result rows by using lower bound and upper bound indexes so that we return only the rows between lower and upper bounds.

Now take a look at the aspx page

<asp:GridView ID="GridView1" DataKeyNames="EmpID" runat="server" 
AllowPaging="True" AutoGenerateColumns="False"
CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" 
GridLines="None" AllowSorting="True" PageSize="5" >
       <RowStyle BackColor="#EFF3FB" />
       <Columns>
           <asp:BoundField DataField="EmpID" HeaderText="EmpID" 
ReadOnly="true" SortExpression="EmpID" />           
           <asp:BoundField DataField="Name" HeaderText="Name" 
SortExpression="Name" />
           <asp:BoundField DataField="Department" HeaderText="Department" 
SortExpression="Department" />
           <asp:BoundField DataField="Salary" HeaderText="Salary"
SortExpression="Salary" />
       </Columns>
       <FooterStyle BackColor="#507CD1" Font-Bold="True" 
ForeColor="White" />
       <PagerStyle BackColor="#2461BF" ForeColor="White" 
HorizontalAlign="Center" />
       <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" 
ForeColor="#333333" />
       <HeaderStyle BackColor="#507CD1" Font-Bold="True" 
ForeColor="White" />
       <EditRowStyle BackColor="#2461BF" />
       <AlternatingRowStyle BackColor="White" />
   </asp:GridView>
  
   <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"  
SelectMethod="GetAllEmployees" EnablePaging="true"
   TypeName="WebApplication1.EmployeeData" 
StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize"
   SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >   
   </asp:ObjectDataSource>

We will be using the ObjectDataSource, which allow Virtual Paging. For achieving this we will be using the following property of ObjectDataSource

StartRowIndexParameterName - Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page index. For example, if Grid View page size is 10 and the user clicks page number 3 to view the 3rd page, then the ObjectDataSource will calculate the value of StartRowIndexParameter by using the (page Size * page Index) formula. In this case it will be 10 * 3= 30. Default value of this property is startRowIndex, which means that if we don't mention any value for this property then the Select method should have the startRowIndex parameter.

MaximumRowsParameterName - Specifies the parameter name of the Select method of ObjectDataSurce’s bounded Type. ObjectDataSource will pass the value to this parameter when the user changes the page Index. ObjectDataSource source gets its value from GridView's PageSize property. Default value of this property is maximumRow, which means that if we don't set any value for this property then the Select method should have the maximumRow parameter.

SelectCountMethod - Specifies the method name of ObjectDataSource’s Type. This method will be called by ObjectDataSource to get the total number of records in the database. This count will help ObjectDataSource to create virtual paging in the Grid. For example, if GridView page size is 10, and SelectCountMethod returns 100 as total number of employees in the database then ObjectDataSource will display 10 page indexes in the GridView's pager area, even if we didn't get all the 100 records from the database.

SortParameterName - Specifies the parameter name of the Select method of ObjectDataSource’s bounded Type. ObjectDataSource will pass the value to this parameter when the user clicks on any column header to sort the data according to that column. ObjectDataSource fetches the value from the SortExpression property of the particular GridView column.

public class EmployeeData
   {
       private static int employeesCount;

       public EmployeeData()
       {
           //
           // TODO: Add constructor logic here
           //
       }

       [DataObjectMethod(DataObjectMethodType.Select)]
       public static List<Employee> GetAllEmployees(int startIndex,
int pageSize, string sortBy)
       {
           List<Employee> result;
           int totalEmployees = 0;
           if (string.IsNullOrEmpty(sortBy))
               sortBy = "EmpID";
           result = GetAllEmployee(startIndex, pageSize, sortBy, 
ref totalEmployees);
           employeesCount = totalEmployees;
           return result;
       }

       public static int GetTotalEmployeesCount()
       {
           return employeesCount;
       }

       public static List<Employee> GetAllEmployee(int startIndex, 
int pageSize, string sortBy, ref int totalEmployees)
       {
           System.Data.SqlClient.SqlConnection connection;
           System.Data.SqlClient.SqlCommand selectCommand = null;
           List<Employee> employeeList = new List<Employee>();

           try
           {
               using (connection = new
System.Data.SqlClient.SqlConnection(
"Data Source=.;Initial Catalog=deptStore;Integrated Security=SSPI;"))
               {
                   if (connection.State != ConnectionState.Open)
                       connection.Open();

                   using (selectCommand = new System.Data.SqlClient.SqlCommand())
                   {
                       selectCommand.CommandText = "dbo.spGetAllEmployee";
                       selectCommand.Connection = connection;
                       selectCommand.CommandType = CommandType.StoredProcedure;

                       selectCommand.Parameters.Add(new 
SqlParameter("@startIndex", SqlDbType.Int));
                       selectCommand.Parameters[0].Value = startIndex;
                       selectCommand.Parameters.Add(new 
SqlParameter("@pageSize", SqlDbType.Int));
                       selectCommand.Parameters[1].Value = pageSize;
                       selectCommand.Parameters.Add(new 
SqlParameter("@sortBy", SqlDbType.VarChar, 30));
                       selectCommand.Parameters[2].Value = sortBy;
                       selectCommand.Parameters.Add(new
SqlParameter("@totalEmployees", SqlDbType.Int));
                       selectCommand.Parameters[3].Value = totalEmployees;
                       ((System.Data.SqlClient.SqlParameter)
selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;
                       SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);
                       DataSet empDS = new DataSet();
                       adapter.Fill(empDS);
                       totalEmployees = Convert.ToInt32(((
System.Data.SqlClient.SqlParameter)
selectCommand.Parameters["@totalEmployees"]).Value);

                       for (int index = 0; index < empDS.Tables[0].Rows.Count;
index++)
                       {
                           Employee emp = new Employee()
                           {
                               EmpID = Convert.ToInt32(
empDS.Tables[0].Rows[index][0].ToString()),
                               Name = empDS.Tables[0].Rows[index][1].ToString(),
                               Department =
empDS.Tables[0].Rows[index][2].ToString(),
                               Salary = Convert.ToInt32(
empDS.Tables[0].Rows[index][3].ToString())
                           };

                           employeeList.Add(emp);
                       }
                   }
               }
           }
           catch (System.Data.SqlClient.SqlException ex)
           {
               throw ex;
           }
           return employeeList;
       }
   }

   public class Employee
   {
       public int EmpID { get; set; }
       public string Name { get; set; }
       public string Department { get; set; }
       public int Salary { get; set; }
   }

Thursday, November 12, 2009

ASP.Net to Check whether request is coming from mobile device

For checking this use the following code

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.Browser.IsMobileDevice == true ||
        Request.UserAgent.ToLower().Contains("iphone"))
    {
        Response.Redirect("DefaultIPhone.aspx");
    }
}

The UserAgent property contains the data which tells from which device the request is coming. On problem with this approach is that there are lot of mobile devices and browsers which runs on mobile. One solution I found for this problem is by using a library called 51degrees.mobi.

Basic usage of LINQ

We can use LINQ in any scenario where we want to iterate through a list of items. For example how about listing all types in the current application domain?

var asm = from a in AppDomain.CurrentDomain.GetAssemblies()
         from type in a.GetExportedTypes()
         select type;

foreach (var val in asm)
{
   Console.WriteLine(val.Name);
}

How about listing all 3.5 assemblies order by length

var alist = asm.Where(x => x.Assembly.FullName.Contains("3.5.0.0")).
   OrderByDescending(x => x.Name.Length);

foreach (var val in alist)
{
   Console.WriteLine(val.Name);
}

Lets find the total count of types for each version

var vers = asm.Select(
       x => x.Assembly.FullName.Split(",".ToCharArray())[1])
.GroupBy(y => y)
.Select(z => new { VerName = z.Key, Count = z.Count() });

foreach (var ver in vers)
{
   Console.WriteLine(".NET {0} has {1} types\n", ver.VerName, ver.Count);
}

Interesting right?

Highest value in each group using LINQ

First of all, let me thank Suportim for explaining how to achieve this. For demonstrating I have created an employee class as shown below.

public class Employee
   {
       public string Name { get; set; }
       public string Department { get; set; }
       public int Salary { get; set; }
   }

Our aim is to find those employee from each department who earns the most. Here I am manually creating some employee as shown below

List<Employee> employeeList = new List<Employee>();
           employeeList.Add(new Employee() { Name = "John", Department = "Web", Salary = 1000 });
           employeeList.Add(new Employee() { Name = "Frank", Department = "Web", Salary = 2000 });
           employeeList.Add(new Employee() { Name = "Loyd", Department = "Web", Salary = 3000 });
           employeeList.Add(new Employee() { Name = "Peter", Department = "IT", Salary = 1500 });
           employeeList.Add(new Employee() { Name = "Tevez", Department = "IT", Salary = 2500 });
           employeeList.Add(new Employee() { Name = "James", Department = "IT", Salary = 3500 });
           employeeList.Add(new Employee() { Name = "Peter", Department = "Finance", Salary = 500 });
           employeeList.Add(new Employee() { Name = "Tevez", Department = "Finance", Salary = 1500 });
           employeeList.Add(new Employee() { Name = "Cameron", Department = "Finance", Salary = 3250 });

Now check the LINQ statement for finding the most earning employees for each department.

var employees = from e in employeeList
   group e by e.Department into egrp
   let max = egrp.Max(sal => sal.Salary)
   select new
   {
       Department = egrp.Key,
       Name = egrp.First(val=>val.Salary == max).Name,
       Salary =  egrp.First(val=>val.Salary == max).Salary
   };

For demonstrating lets print the values to screen

foreach (var emp in employees)
{
   Console.WriteLine("In Department {0}, Employee {1} has the highest salary {2}",
   emp.Department, emp.Name, emp.Salary);
}

Hope this helps.

Wednesday, November 11, 2009

ASP.Net Dropdown List and Tooltip

In some cases the dropdown list will be having large text. And the size of dropdown list is small. In such cases the item won’t be visible to the end user. One option is to expand the dropdown list. But if the size of text is large then it won’t look good in UI. One workaround is to place the item as tooltip. For achieving this you can use the following code.

DropDownList1.Items.Add("1");
DropDownList1.Items[0].Attributes.Add("Title", "Some1");
DropDownList1.Items.Add("2");
DropDownList1.Items[1].Attributes.Add("Title", "Some2");
DropDownList1.Items.Add("3");
DropDownList1.Items[2].Attributes.Add("Title", "Some3");

The title attribute will just add the provided value as the tooltip.
Hope this helps.

Monday, November 9, 2009

Sharepoint List Webservice, access data from folders

Recently, for my project I was trying to do the same thing, but unfortunately there was no material available in the net. The only help I got was from MSDN Sharepoint Development forum. So I thought of documenting this, so if anyone who is looking for this can be find it useful.

First of all, if you want to get data recursively, then use the following code.

System.Xml.XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");

queryOptions.InnerXml = "<ViewAttributes Scope=\"Recursive\" />";

Now for getting data from a particular folder inside a list, use the following code

string url = "http://server/sites/site/list/folder";
queryOptions.InnerXml = "<Folder>" + url + "</Folder><ViewAttributes Scope=\"Recursive\" />";

The remaining portion of accessing the webservice is already documented in the following post.

Sharepoint List Webservice

Friday, November 6, 2009

SQL Server 2008 Creating FILESTREAM Enabled Database

In this post I am going to explain how you can create a database with the new FILESTREAM feature enabled. If you want to know, how to enable FILESTREAM in instance level, please refer to my previous post "SQL Server 2008 FILESTREAM Feature".

Lets start by creating a database name TestFileStream using the below scripts.

CREATE DATABASE [TestFileStream] ON PRIMARY
( NAME = N'TestFileStream', FILENAME = N'C:\DB\TestFileStream.mdf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED, FILEGROWTH = 12% )
LOG ON
( NAME = N'TestFileStream_log', FILENAME = N'C:\DB\TestFileStream_log.ldf' ,
SIZE = 25MB , MAXSIZE = UNLIMITED , FILEGROWTH = 12%)
GO

Now the database is ready. Lets go ahead and add the filegroups.

ALTER DATABASE [TestFileStream]
ADD FILEGROUP [TestFileStreamGroup] CONTAINS FILESTREAM
GO

ALTER DATABASE [TestFileStream]
ADD FILE (NAME = N'TestFileStream_FSData', FILENAME = N'D:\DB\TestFileStream')
TO FILEGROUP TestFileStreamGroup
GO

One important fact is the usage of

CONTAINS FILESTREAM

clause. Atleast for one filegroup we must specify this clause. Open the properties window of the newly created database, and look into the Files section. There you can see that for the file “TestFileStream_FSData”, the file type is “File Stream Data”. Now open the folder “C:\DB”. There will be folder named “TestFileStreamData”. All the FILESTREAM related data gets stored in TestFileStreamData folder which is also known as FILESTREAM Data Container. Inside this folder you can see the following files

Among this, the file “filestream.hdr” is the most important one. As the name suggests it hold the file stream information.

Let go ahead and create a table. Before creating keep a note of the following points.

Must have a column of type VARBINARY(MAX) along with the FILESTREAM attribute.

Table must have a UNIQUEIDENTIFIER column along with the ROWGUIDCOL attribute.

Try this query

Use TestFileStream
GO
CREATE TABLE [FileStreamTable]
(
[ID] [INT] IDENTITY(1,1) NOT NULL,
[Data] VARBINARY(MAX) FILESTREAM NULL,
[DataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DateTime] DATETIME DEFAULT GETDATE()
)
ON [PRIMARY]
FILESTREAM_ON TestFileStreamGroup
GO

Lets insert some data

Use TestFileStream
GO
INSERT INTO [FileStreamTable] (Data)
SELECT * FROM
OPENROWSET(BULK N'C:\DSCN5021_large.jpg' ,SINGLE_BLOB) AS Document
GO

This will create a folder under “C:\DB\TestFileStream”, if you can travel inside the subfolder and one file will be there. Open it in any image viewer and you can see the image you have inserted.

To retrieve the data, use the following query

USE TestFileStream
GO
SELECT ID
, CAST([Data] AS VARCHAR) as [FileStreamData]
, DataGUID
, [DateTime]
FROM [FileStreamTable]
GO

For updating, use the following query

USE TestFileStream
GO
UPDATE [FileStreamTable]
SET [Data] = (SELECT *
FROM OPENROWSET(
BULK 'C:\DSCN5022_large.JPG',
SINGLE_BLOB) AS Document)
WHERE ID = 1
GO

For deletion, use the following query

USE TestFileStream
GO
DELETE [FileStreamTable]
WHERE ID = 1
GO

On updating/deleting, the table will be updated/deleted immediately. But the FileStream container data will be removed once the Garbage Collector Process runs.

That’s all for this post. In my next post I am planning to explain optimizing FileStream objects.

SQL Server 2008 FILESTREAM Feature

One of the new feature addition to the SQL Server 2008 is FILESTREAM. This enables the storage of BLOBs in File Sytem instead of database file.

We can enable FILESTREAM feature as follows:

Open SQL Server Configuration Manager
(Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager)
From SQL Configuration Manager select "SQL Server Services as shown in below figure.

Now from the right pane right click SQL Server and select properties. A new window will be bought up as shown below.

Now go to the FILESTREAM tab, and Select the option “Enable FILESTREAM for Transact-SQL access.

If you want to enable reading/writing FILESTREAM date from windows, then select the option “Enable FILESTREAM for file I/O streaming access” and provide the window share name.

If you want to enable remote client access for FILESTREAM data, then enable the 3rd option and hit “Apply”.

Another way to enable the option is through T-SQL query. Open SQL Server Management Studio. Open new query window and execute the following query.

USE master
Go
EXEC sp_configure 'show advanced options'
GO
--0 means FILESTREAM is disabled
--1 means FILESTREAM is enabled for this instance
--2 means FILESTREAM is enabled with window streaming
EXEC sp_configure filestream_access_level, 1
GO
RECONFIGURE WITH OVERRIDE
GO

Another way is using SQL Server Management Studio. Open SQL Server Management Studio. From the object explorer, select the server and right click.

Click on the properties menu item

There from the Advanced property, you can see FileStream Access Level. Set the level you want and click OK.

Another way to enable this feature is during the installation of SQL Server 2008. If you have referred to my post related to SQL Server 2008 installation over here. You can find how to do that.

Now we are done with enabling FILESTREAM on our database server.

On my next post I will describe, how to create a FILESTREAM database and tables fields.

Hope you have nice reading. For more queries and information ping me.

Tuesday, November 3, 2009

Using XPath in C#

In this blog I am going to give an overview of how we can easily parse and get the data from an XML file. For demonstrating, I have created a sample XML file listed below.

<?xml version="1.0" encoding="utf-8" ?>
<Employees>
  <Employee>
    <Name>Amal</Name>
    <Age>26</Age>
    <Address>333 Indiana Street</Address>
  </Employee>
  <Employee>
    <Name>Munna</Name>
    <Age>20</Age>
    <Address>222 West Region</Address>
  </Employee>
</Employees>

Add the following namespace in your application

using System.Xml.XPath;

Now we can open the XPathDocument object as follows

XPathDocument Doc = new XPathDocument("emp.xml");

Once this is done, we need a XPathNavigator

XPathNavigator navigator = Doc.CreateNavigator();

Using this navigator object, we can traverse through the document. We can provide XPath expressions as shown below

XPathNodeIterator iterator = navigator.Select("/Employees");
while (iterator.MoveNext())
{
     Console.WriteLine(iterator.Current.Name);
     Console.WriteLine(iterator.Current.Value);
}

Try changing the XPath expression as demonstrated below.

iterator = navigator.Select("/Employees/Employee");

iterator = navigator.Select("/Employees/Employee[Age>22]");

iterator = navigator.Select("/Employees/Employee[Age>22]/Name");

Hope this helps :-)

SQL Server 2008 Installation

Start the SQL Server 2008 installation by clicking the following icon

This will bring up the following screen

From right pane, click on “Installation link”. You will get the following screen.

From the left pane, click on the link “New SQL Server stand-alone installation or add features to an existing installation”. The 1st action performed by the installer is checking the setup support rules.

You can get a detailed report, by following the link “View detailed report”. Click OK.

Now we need to specify, which version we are going to install. If you have purchased SQL Server 2008 then enter the product key. Click Next.

Accept the agreement and Click Next.

Hit Install.

For detailed report, follow the link “View detailed Report”. Click Next.

Select the features you want to install and Click Next.

Select the instance name and Path. Click Next.

From the screen we can understand that the minimum disk space we need in C drive is 2040. Click Next.

In this screen we can specify on which account each of the SQL services should run. Click Next.

Specify the Authentication Mode. If you want to add the Current User in the SQL Administrators list, then Click Add Current User. You can enable the FILESTREAM feature by opening the FILESTREAM tab.

Once you are done, Click Next.

Provision the accounts that require have admin privileges. Click Next.

In this screen, we must specify how the Reporting Server must be configures. The available options are “Native Mode”, “Sharepoint Integrated Mode”. If you want to access the data from sharepoint lists, then select Sharpoint integrated mode. Else Native mode will be fine. Click Next.

Click Next.

Click Install.

Installation Completed. Click Next.

Monday, November 2, 2009

Sharepoint and Silverlight - 1

As part of setting up the environment we must be careful to do the following points

Point #1

Changing the MIME type in IIS to support Silverlight application. This can be done as follows

1. Open run window from Start Menu

2. Enter inetmgr and click OK

3. This will bring up the Internet Information Services Manager. From the folder structure on the left side, select the site which is your Sharepoint application.

4. Now from the right pane select "Mime Types" and "Open Feature"

5. In Mime Type View, Click Add, add the file extension, and type .xap as the file extension and "application/x-silverlight-2" as the Mime type.

Point #2

In the web.config of your Sharepoint application, add the required configurations.

Point #3

Make sure the System.Web.Silverlight.dll is in the GAC

Installing Sharepoint Extensions for VS 2008 in XP/Vista

The extensions installer can be downloaded from the following location

Download

Once you have downloaded. You need to modify the registry, for doing that follow the below steps

1. Open run window from Start Menu

2. Enter Regedit and Click OK

3. Open the following location

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\

Now create a new Key named “12.0”

Under 12.0 create a new String “Sharepoint” and give the value “Installed”

4. Now run the installer you have downloaded from the link above.

Sharepoint Top-Level Object Model

Building and Deploying a Web Part

In this article am going to explain how we can develop a simple web part and deploy it in Sharepoint.

There are two way we can build a web part.

1. Using Sharepoint extensions for Visual studio.
2. Developing a Class library and manually deploying it in Sharepoint.

Sharepoint extensions for visual studio can be downloaded from the following location

Download

Once you have installed the extension, from Visual Studio->File->New Project->Share Point->Web Part

This will provide a template with all setting ready for building a web part. Once you have done with the web part, you can deploy it directly from Visual Studio. For doing that open the properties dialog by right click the project from solution explorer, go to the debug tab and enter the sharepoint web application URL and save the settings. You can use the same right click context menu for deploying the web part directly to sharepoint server by clicking the deploy menu item.

Manual development and deployment steps

From visual studio File->New Project->C#->Class Library

Right click and Add Reference of System.Web

Rename Class.cs to NewWebPart.cs

Open NewWebPart.cs and add the following code

Right click project->Properties->Signing, and sign the assembly with a key

Now build the project

Open visual studio command prompt and use the following command

Open your sharepoint web application's web.config file and add the following entry

Open Site Setting from Site Actions menu

Click on Web Parts

Click New

SQL Server 2008 Installation Pre-Requisites

In this blog series, I am going to explain in details about how we can install SQL Server 2008 dev edition on a windows XP box.

My current configuration is

Windows XP SP3
Visual Studio 2008 (No service pack installed)
.Net Framework 3.5 SP1

Once I started installing SQL Server 2008 on my dev box, I started complaining that it need the KB(KB942288-v2) to be installed.

This KB installer is a hot fix for Windows installer. I clicked Ok and an installer window comes up, and installed that hot fix. Once this hot fix is installed, we needs to reboot our machine.

Once the system reboots, execute the SQL 2008 installer. This will bring up the following screen.

In this screen if you notice, there is an option called “System Configuration Checker”. Click on that link, which will bring up a utility that can scan and show the components which has passed/failed as shown below.

Now, we are ready to start our SQL Server 2008 installation :-)

Saturday, October 31, 2009

Using Sharepoint List Webservice

Sharepoint exposes some really useful webservices. One among the most useful is the List Webservices.

We can take the webreference from the following location

http://mossserver/_vti_bin/lists.asmx

Once we have added the webservice, we can use following generic method to call webservice

XmlNode CallWebService(bool isRecursive, ListsService listService,
string listName, string queryObj, string viewID, string viewFieldsInnerXml)
{
  try
  {
      NetworkCredential credential = new NetworkCredential();
      credential.UserName = "UserName";
      credential.Password = "Password";
      credential.Domain = "Domain";
      listService.Credentials = credential;

      // Instantiate an XmlDocument object
      System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
   
      string viewName = string.Empty;
      if (string.Empty != viewID)
      {
          viewName = viewID;
      }
      string rowLimit = "150";

      System.Xml.XmlElement query = xmlDoc.CreateElement("Query");
      System.Xml.XmlElement viewFields = xmlDoc.CreateElement("ViewFields");
      System.Xml.XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");

      if (isRecursive)
          queryOptions.InnerXml = "<ViewAttributes Scope=\"Recursive\" />";
      else
          queryOptions.InnerXml = "";

      if (string.Empty == queryObj)
      {
          query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +
                              "<Value Type=\"Counter\">0</Value></Gt></Where>";
      }
      else
      {
          query.InnerXml = queryObj;
      }

      // Assign View fields
      if (string.Empty != viewFieldsInnerXml)
      {
          viewFields.InnerXml = viewFieldsInnerXml;
      }
      else
      {
          viewFields.InnerXml = "<FieldRef Name=\"Title\" />";
      }
      System.Xml.XmlNode nodeListItems =
          listService.GetListItems(listName, viewName, query,
viewFields, rowLimit, queryOptions, null);

      return nodeListItems;
  }
  catch (Exception ex)
  {
      throw ex;
  }
}

You can use this generic method for getting the list items.

Pass true to “IsRecursive” if you want to get items inside folder.

Example:

string fields = "<FieldRef Name=\"FieldName1\" />
<FieldRef Name=\"FieldName2\" />";
XmlNode nodeListItems = CallWebService(true, l, "Answer Choices",
string.Empty, string.Empty, fields);

foreach (System.Xml.XmlNode listItem in nodeListItems)
{
   if (listItem.ChildNodes.Count > 0)
   {
       foreach (XmlNode node in listItem.ChildNodes)
       {
           string choice = node.Attributes["ows_Title"]
.Value;               
       }
   }
}

Thursday, October 29, 2009

SSRS report and Webservice as datasource

One good feature i found in SSRS report is that, we can provide webservice as the datasource. We can create dataset using webservice methods.

For creating a dataset using webservice, do the following steps.

Create a new Datasource

From solution explorer, right click "Shared Data Sources".
Click on "Add new Datasource".
In the new window, provide a name for the new datasource.
Select type as "Xml".
In connection string, provide the path of wsdl. Ex: http://localhost/Service1.asmx?wsdl
Hit Ok.

Create a new Dataset

Select the Data tab.
From the Dataset dropdown, select "New Dataset".
In the Dataset window, Select the dataset we have created.
Select Text as command.
Use the following query

<Query>
<Method Namespace="http://tempuri.org/" Name="MethodName">
<Parameters>
<Parameter Name="parameter1">
<DefaultValue>1 </DefaultValue>
</Parameter>
</Parameters>
</Method>
<SoapAction>http://tempuri.org/MethodName </SoapAction>
</Query>

MethodName is the method you want to invoke in the webservice.
parameter1 is the name of the parameter the webservice method is expecting. You need to create a Report Parameter. And in the "Create New Datset" window, open the Parameter tab and add that parameter.

Now in the query window, execute the query by clicking the "Red Exclamation Icon".

Done!!!

For queries and suggestions, please feel free to ping me.

Report Viewer control and Local Reports

There will be cases in which we need to load a local report on ReportViewer control. The below code will help to do the same.

//Declare ReportViewer object

public Microsoft.Reporting.WebForms.ReportViewer reportViewer1;

reportViewer1.ProcessingMode = ProcessingMode.Local;
reportViewer1.LocalReport.ReportPath = path;
reportViewer1.LocalReport.
ExecuteReportInCurrentAppDomain(
System.Reflection.Assembly.GetExecutingAssembly().
Evidence);

// The application is responsible for collecting
// parameters
ReportParameterInfoCollection parameterInfo =
reportViewer1.LocalReport.GetParameters();
if (parameterInfo.Count > 0)
{
List<ReportParameter> param = new List<ReportParameter>();
ReportParameter yearParam = new ReportParameter("Year", “2009”);
param.Add(yearParam);
// Add report data sources

ReportDataSource rds = new ReportDataSource("ds", dataSourceObject);
reportViewer1.LocalReport.DataSources.Add(rds);
// Add report parameters
reportViewer1.LocalReport.SetParameters(param);
}

reportViewer1.LocalReport.Refresh();

Hope this helps :)

If you have any query, please feel free to ping me.

Wednesday, October 28, 2009

Sharepoint Calculated Field - Week Number

The following formula will help us to find the week number for a date value.

=INT(([Date]-DATE(YEAR([Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Date]),1,1)),"d")))/7)+1

Hope this helps :)

Saturday, October 24, 2009

Integrating SSRS + Sharepoint + Report Viewer

Scenario:

We need to display a report based on the data spread across multiple list. In such a case we won't be able to use either List View or Data View. One easier way to solve this problem is to create a custom webservice which will basically call the List Web Services provided by Sharepoint, create the dataset and return it back to the SSRS report.

One problem which we will face is while the deployment in production server. What we can do is in the report viewer control, lets take the rdl file from local instead of server. We can keep the rdl file in sharepoint list only. Thus the rdl file will be secure and can be replaced only by administrators. One drawback of the approach is that, the rdl file will not be available in the Sql Reporting Server. But compared to the ease of deployment, I think we can live with this :)

Wednesday, October 14, 2009

Sharepoint List View - Calculated Columns

In some scenarios, while creating views we might need to filter based on complex queries. In List View, filters are available but they are not powerful. For example, if we want to group the condition based on And and or, that feature is not available.

One workaround for this problem is to add a calculated column in the list and the result of the calculation will say whether that row should be placed in the view or not.

Example query will be as follows:

IF(AND(OR([Col1]="Some Value", [Col2]="Some Value"), AND([Col3]="Some Value", [Col4]="Some Value")), "Show Row", "Dont Show")

Friday, October 9, 2009

SSRS - Creating reports with specific rows per page

In layout tab, select the table and open the properties windows by right clicking and selecting properties. From the properties windows move to the Groups tab. Click Add and enter the following expression

=Ceiling(RowNumber(Nothing)/20)

Replace 20 with the required number of rows per page. Select "Page Break at Start" and uncheck "Page break at Start". If you want the header and footer to be appear on each page then select those options.

Hope this helps :-)

Tuesday, September 15, 2009

Login to FTP Server

In this post I am going to put a piece of code I have written for my friend. The requirement was to validate the given username and password are valid for the specified FTP server.

static bool CheckFTPLogin(String ftpServer, uint ftpPort, String userName, String password)
{
if (String.IsNullOrEmpty(ftpServer) ||
String.IsNullOrEmpty(userName) ||
String.IsNullOrEmpty(password))
return false;

using (TcpClient client = new TcpClient())
{
try
{
client.Connect(ftpServer, Convert.ToInt32(ftpPort));
NetworkStream nStream = client.GetStream();
StreamReader myReader = new StreamReader(nStream);
StreamWriter myWriter = new StreamWriter(nStream);

string retValue = myReader.ReadLine();
Console.WriteLine(retValue);
if (retValue.EndsWith("ready.") && retValue.StartsWith("220"))
{
myWriter.WriteLine(string.Format("USER {0}", userName));
myWriter.Flush();
retValue = myReader.ReadLine();
Console.WriteLine(retValue);
if (retValue.StartsWith("331"))
{
myWriter.WriteLine(string.Format("PASS {0}", password));
myWriter.Flush();
retValue = myReader.ReadLine();
Console.WriteLine(retValue);
if (retValue.StartsWith("230"))
{
///Login Success
myReader.Close();
myWriter.Close();
return true;
}
}
else
{
///Login Error.
myReader.Close();
myWriter.Close();
return false;
}
}
else
{
///FTP Connection error.
myReader.Close();
myWriter.Close();
return false;
}
return false;
}
catch
{
///FTP Connection Error
return false;
}
}
}

CheckFTPLogin is a generic method which will return a boolean value true if and only if the username and password are valid credentials in the given ftpServer.

Thursday, August 27, 2009

Check a URL exist or not using C#

static bool CheckURL(String url)
{
if(String.IsNullOrEmpty(url))
return false;

WebRequest request = WebRequest.Create(url);

try
{
HttpWebResponse res = request.GetResponse() as HttpWebResponse;

if (res.StatusDescription == "OK")
return true;
}
catch
{
}

return false;
}

Creating a zip file using C#

Using the System.IO.Compression namespace in Codeplex a classlibrary project is going on name DotNetZip

The class library has a ZipFile class. Using this, you can create zip files using the below code

ZipFile zip= new ZipFile("MyNewZip.zip");

zip.AddDirectory("My Pictures", true); // AddDirectory recurses subdirectories

zip.Save();

Tuesday, August 25, 2009

Encyption/Decryption in C# and Java

C# Code

string Decrypt(string textToDecrypt, string key)
{
RijndaelManaged rijndaelCipher = new RijndaelManaged();
rijndaelCipher.Mode = CipherMode.CBC;
rijndaelCipher.Padding = PaddingMode.PKCS7;

rijndaelCipher.KeySize = 0x80;
rijndaelCipher.BlockSize = 0x80;
byte[] encryptedData = Convert.FromBase64String(textToDecrypt);
byte[] pwdBytes = Encoding.UTF8.GetBytes(key);
byte[] keyBytes = new byte[0x10];
int len = pwdBytes.Length;
if (len > keyBytes.Length)
{
len = keyBytes.Length;
}
Array.Copy(pwdBytes, keyBytes, len);
rijndaelCipher.Key = keyBytes;
rijndaelCipher.IV = keyBytes;
byte[] plainText = rijndaelCipher.CreateDecryptor().TransformFinalBlock(encryptedData, 0, encryptedData.Length);
return Encoding.UTF8.GetString(plainText);
}

string Encrypt(string textToEncrypt, string key)
{
RijndaelManaged rijndaelCipher = new RijndaelManaged();
rijndaelCipher.Mode = CipherMode.CBC;
rijndaelCipher.Padding = PaddingMode.PKCS7;

rijndaelCipher.KeySize = 0x80;
rijndaelCipher.BlockSize = 0x80;
byte[] pwdBytes = Encoding.UTF8.GetBytes(key);
byte[] keyBytes = new byte[0x10];
int len = pwdBytes.Length;
if (len > keyBytes.Length)
{
len = keyBytes.Length;
}
Array.Copy(pwdBytes, keyBytes, len);
rijndaelCipher.Key = keyBytes;
rijndaelCipher.IV = keyBytes;
ICryptoTransform transform = rijndaelCipher.CreateEncryptor();
byte[] plainText = Encoding.UTF8.GetBytes(textToEncrypt);
return Convert.ToBase64String(transform.TransformFinalBlock(plainText, 0, plainText.Length));
}

Java Code

String Decrypt(String text, String key) throws Exception{
Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
byte[] keyBytes= new byte[16];
byte[] b= key.getBytes("UTF-8");
int len= b.length;
if (len > keyBytes.length) len = keyBytes.length;
System.arraycopy(b, 0, keyBytes, 0, len);
SecretKeySpec keySpec = new SecretKeySpec(keyBytes, "AES");
IvParameterSpec ivSpec = new IvParameterSpec(keyBytes);
cipher.init(Cipher.DECRYPT_MODE,keySpec,ivSpec);

BASE64Decoder decoder = new BASE64Decoder();
byte [] results = cipher.doFinal(decoder.decodeBuffer(text));
return new String(results,"UTF-8");
}

String Encrypt(String text, String key)
throws Exception {
Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5Padding");
byte[] keyBytes= new byte[16];
byte[] b= key.getBytes("UTF-8");
int len= b.length;
if (len > keyBytes.length) len = keyBytes.length;
System.arraycopy(b, 0, keyBytes, 0, len);
SecretKeySpec keySpec = new SecretKeySpec(keyBytes, "AES");
IvParameterSpec ivSpec = new IvParameterSpec(keyBytes);
cipher.init(Cipher.ENCRYPT_MODE,keySpec,ivSpec);

byte[] results = cipher.doFinal(text.getBytes("UTF-8"));
BASE64Encoder encoder = new BASE64Encoder();
return encoder.encode(results);
}

Monday, June 29, 2009

Convert time from one timezone to another in .Net

DateTime dlocal = DateTime.Now;

DateTime dNonLocal = TimeZoneInfo.ConvertTime(dlocal, TimeZoneInfo.FindSystemTimeZoneById("US Mountain Standard Time"));

Thursday, June 25, 2009

Comma delimted String from List of String using C#

When you have a list of strings like IList of IEnumerable and you want to convert it to for example a comma separated list you can easily do that with the String.Join method.

List<string> list = new List<string>() { "A" , "B", "C", "D", "E" };

string commaSeparated = String.Join(",", list.ToArray());

The output will be like this:

A,B,C,D,E

This is an easy and clean way to create a delimiter separeated list of strings.

Getting calling method name in C# using Reflection

The name of the calling method can be extracted from the stack. Since the method on top of the stack is the method that is currently being executed, the calling method will be right below it. Thus, by instantiating StackTrace and getting the frame with index 1 will result in getting a StackFrame that corresponds to the call from the calling method. Finally, reflection can be used to get method name.

using System.Diagnostics;
 
void Log(string eventMessage)
{
   Console.WriteLine("Event logged by " + (new StackTrace()).GetFrame(1).GetMethod().Name);
   Console.WriteLine("Event: " + eventMessage);

}