Dot Net Thoughts

In the current project we are using Entity Framework for database operations. Entity Framework comes with Visual Studio SP1, which helps you to map tables / views / procedures as entities in C# / VB Code. You can find more details about EF from here : http://msdn.microsoft.com/en-us/library/bb399572.aspx. In this post I am explaining how to use Stored Procedures in Entity Framework.

1. Add the Stored Procedure to the Entity Model Designer using Update Model From Database Option.


Add Procedure

2. If you are added successfully, you can get the procedure in Model Browser.


Model Browser

3. Right click on the Procedure name and select Create Function Import.


Create Function Import

4. It will popups a Windows with Stored Procedure Name, Function Import Name and Return Type. If the procedure returns nothing, you can choose none. If the procedure is returns single value, like UserId, Number Of Rows etc, then you can choose scalar option, where you need to specify the return type. And if the procedure is returns Table or Number of Rows, you need to choose the last option Entities, which will allow to select entities created in the Model as the Output. Sometimes we need to create a View in the DB and need to import it in the Model, so that we can use the View as the return type entity. Select the appropriate return type and click Ok. You can use this in code. In this code I am using a View to return the selected users.

using (SampleEntities context = new SampleEntities())
{
/*
* Thanks to Barry Soetoro.
* I was not calling the GetAllUsers function.
List<Users> Users = null;
Users = (from user in context.Users
             select user).ToList();
this.dataGridView1.DataSource = Users;
*/
//Updated Version.
IEnumerable<UsersView> userview = context.GetAllUsers();
this.dataGridView1.DataSource = userview;
}

This will display list of Users in the DataGridView. Happy Coding

Normally for implementing Paging in Grid View, we enable the Paging property in GridView and will write code in the PageIndex Changed event. But the problem with this approach is it will fetch all the data from Database for Paging, will result wastage of network bandwidth and resources, also affect in the Page Performance. In SQL Server 2005 Microsoft introduced a new concepts called Row_Number() and Derived Table with the help these two we can move the Paging logic to Database instead of ASP.Net web forms. This concept also works with custom paging implementations for DataList or Repeater controls.

CREATE PROCEDURE usp_GetContacts
(@Page INT, @RecsPerPage INT)
AS
SELECT tblContacts.[Name], tblContacts.[IsParent] FROM
(SELECT ROW_NUMBER() OVER(ORDER BY [ID]) AS RowNumber,
[Name], [IsParent] FROM [TreeView].[dbo].[Contacts]) tblContacts
WHERE RowNumber > @RecsPerPage*(@Page) AND RowNumber <= @RecsPerPage*(@Page+1)

In this code we are creating a Column called “RowNumber”. And we are comparing the input parameters with the derived table tblContacts.

Thanks to Aneesh / Prasanth for their valueable suggestions and comments.

One of the project I am worked, I had to enumerate SQL Server instances of the network for creating dynamic connection string. Here is a code snippet which will retrieve all the SQL Server instance in a network using C# and ADO.Net.

using System.Data;
using System.Data.Sql;

SqlDataSourceEnumerator sqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance;
DataTable sqlServerInstances = sqlDataSourceEnumerator.GetDataSources();

The Data Table contains 4 columns, and columns are

1. ServerName - Name of the Server.
2. InstanceName - Name of the server instance. Blank if the server is running as the default instance.
3. IsClustered - Indicates whether the server is part of a cluster.
4. Version - Version of the server (8.00.x for SQL Server 2000, 9.00.x for SQL Server 2005, and 10.0.x for SQL Server 2008).

Another way is using SQL Server Management Objects (SMO). For this you need to add reference of Microsoft.SqlServer.Smo assembly.

DataTable sqlServerInstances = SmoApplication.EnumAvailableSqlServers();

It will also return a Data Table, with 6 columns and the columns are

1. Name- Name of the Server.
2. Server - The name of the server on which the instance of SQL Server is installed.
3. Instance-The instance of SQL Server.
4. IsClustered -A Boolean value that is True if the instance is participating in failover clustering, or False if it is not.
5. Version -Version of the SQL Server (8.00.x for SQL Server 2000, 9.00.x for SQL Server 2005, and 10.0.x for SQL Server 2008).
6. IsLocal-A Boolean value that is True if the instance is local, or False if the instance is remote.

Normally DataRepeater doesn’t have a paging feature; and you can implement it using LINQ, if you are using .Net 3.5. Here is an implementation which is using SQL Server 2005 feature ROW_NUMBER(). You can get more information about ROW_NUMBER() from MSDN.

Stored Procedure – xsp_GetPersons

CREATE PROCEDURE xsp_GetPersons
@StartIndex INT, @Count INT
AS
BEGIN
DECLARE @EndIndex INT
SET @StartIndex = @StartIndex * @Count
SET @EndIndex = @StartIndex + @Count
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY UserId DESC) AS SlNo, UserId, FirstName, LastName, Email, ISNULL(DOB, GETDATE()) AS DOB FROM U_MyRegistration)Registration WHERE Registration.SlNo BETWEEN @StartIndex AND @EndIndex
END

And I added a DataRepeater control, with Next and Previous buttons in the Footer.

<tr>
    <td colspan="3" align="left">
        <asp:LinkButton CommandName="Navigation" CommandArgument="Prev" runat="server" ID="linkPrevious"
            Text="<< Previous" />
    </td>
    <td colspan="3" align="right">
        <asp:LinkButton CommandName="Navigation" CommandArgument="Next" runat="server" ID="linkNext" Text="Next >>" />
    </td>
</tr>

Code behind. As it is a sample code, I am not added any validations.

int count = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindData(); //Binding Data.
    }
}

private void BindData()
{
    int startIndex = Convert.ToInt32(this.hidCurrentIndex.Value);
    Persons p = new Persons();
    this.Repeater1.DataSource = null;
    this.Repeater1.DataSource = p.GetPersons(startIndex, count);
    this.Repeater1.DataBind();
}

protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
{
    if (e.CommandName.Equals("Navigation",
StringComparison.CurrentCultureIgnoreCase))
    {
        if (e.CommandArgument.ToString().Equals("Prev",
StringComparison.CurrentCultureIgnoreCase))
        {
            this.hidCurrentIndex.Value = (Convert.ToInt32
(this.hidCurrentIndex.Value) - 1).ToString(); //Decrementing the count
            this.BindData();
        }
        else
        {
            this.hidCurrentIndex.Value = (Convert.ToInt32
(this.hidCurrentIndex.Value) + 1).ToString(); //Incrementing the count
            this.BindData();
        }
    }
}

And the implementation of Persons class from App_Code

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

public class Persons : List<Persons.Person>
{
    public class Person
    {
        public int UserId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public DateTime DOB { get; set; }
    }

    public Persons GetPersons(int startIndex, int count)
    {
        Persons persons = new Persons();
        using (SqlConnection connection = new SqlConnection ("Server=.\SQLEXPRESS;User Id=sa;Password=sapwd;Database=myDatabase"))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("xsp_GetPersons",

connection))
            {
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@StartIndex", startIndex);
                command.Parameters.AddWithValue("@Count", count);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    persons.Add(new Person()
                    {
                        DOB = Convert.ToDateTime(reader["DOB"].ToString()),
                        Email = reader["Email"].ToString(),
                        FirstName = reader["FirstName"].ToString(),
                        LastName = reader["LastName"].ToString(),
                        UserId = Convert.ToInt32(reader["UserId"].ToString()),
                    });
                }
            }
        }
        return persons;
    }
}

The core thing is stored procedure, which will return the results based on the given index. Thanks to Aneesh for the details of ROW_NUMBER() function.

If you want to display hierarchical data in a Treeview normally we are using recursion. I was looking for code which helps to avoid recursion using a Single query. But that code was using VB.Net and it was using a class called “Collection”, which is not available in C#. So I was looking for a compatable code in C# for long time and today I got the chance to re-write it using C#, but I am using Lamda expressions for this.

Here is the Table structure I want to display in Treeview

CREATE TABLE [dbo].[tblEmployees](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](50) NOT NULL,
[Parent] [int] NOT NULL)

And I inserted following Data in it.

And the Stored Procedure for getting employees using Common Table Expressions.

-- usp_GetEmployees
CREATE PROCEDURE [dbo].[usp_GetEmployees]
AS
BEGIN
	WITH SimpleRecursive AS
	(SELECT EmployeeName, EmployeeId, Parent, 0 AS Depth FROM dbo.tblEmployees
	WHERE (EmployeeId IN(SELECT EmployeeId FROM dbo.tblEmployees AS Recursion1
	WHERE (Parent = 0)))
	UNION ALL
	SELECT P.EmployeeName, P.EmployeeId,P.Parent, A.Depth + 1 AS Depth
	FROM dbo.tblEmployees AS P INNER JOIN SimpleRecursive AS A ON A.EmployeeId = P.Parent)
	SELECT EmployeeName, EmployeeId, CONVERT(INT, Parent) AS Parent, Depth
	FROM SimpleRecursive AS SimpleRecursive_1
	ORDER BY Depth
END

And the code in C# which adding nodes to Treeview.

private void PopulateTreeview()
{
    this.tvEmployees.Nodes.Clear();
    Employees employees = new Employees();
    using (SqlConnection connection = new SqlConnection(@"Server=.\SQLEXPRESS; User Id=SQLUser;Password=SQLPassword;Database=Database"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("usp_GetEmployees", connection))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            while (reader.Read())
            {
                employees.Add(new Employees.Employee()
                {
                    Depth = int.Parse(reader["depth"].ToString()),
                    EmployeeId = int.Parse(reader["EmployeeId"].ToString()),
                    Parent = int.Parse(reader["Parent"].ToString()),
                    EmployeeName = reader["EmployeeName"].ToString(),
                });
            }
        }
    }

    foreach (Employees.Employee employee in employees)
    {
        Employees.Employee parentEmp = employees.Find(o => o.EmployeeId == employee.Parent);
        if (parentEmp != null)
        {
            this.tvEmployees.Nodes.Find(parentEmp.EmployeeId.ToString(), true)[0].Nodes.Add(employee.EmployeeId.ToString(), employee.EmployeeName);
        }
        else
        {
            this.tvEmployees.Nodes.Add(employee.EmployeeId.ToString(), employee.EmployeeName);
        }
    }
    this.tvEmployees.ExpandAll();
}

And the employees class

public class Employees : List<Employees.Employee>
{
    public class Employee
    {
        public int EmployeeId
        {
            get;
            set;
        }
        public string EmployeeName
        {
            get;
            set;
        }
        public int Parent
        {
            get;
            set;
        }
        public int Depth
        {
            get;
            set;
        }
    }
}

And here is the screenshot

Thanks to Aneesh and Anas for their valuable comments.
Happy Programming

The forum I joined recently got lot of queries like How to Save Images in the Database, How to save files in SQL Server, How read files from Database etc. So I thought of writing a post regarding this. Even though I am part of a Web project, I am doing some Windows applications for the client. So I thought it will nice to brush-up the ASP.Net skills.

Here is the code. I am using SQL Server 2008, but I am not using FileStream for the current project, I already wrote a post to how to
manage files with FileStream feature. In this post I am using nvarchar(MAX) datatype for storing the file content.

Table Design

CREATE TABLE [dbo].[tblFiles](
	[FileId] [uniqueidentifier] NOT NULL,
	[Filename] [nvarchar](255) NOT NULL,
	[FileContent] [varbinary](max) NULL
)

And the I set FileId default to newId() and FileContent default to NULL

ALTER TABLE [dbo].[tblFiles] ADD  CONSTRAINT [DF_tblFiles_FileId]  DEFAULT (newid()) FOR [FileId]
GO
ALTER TABLE [dbo].[tblFiles] ADD  CONSTRAINT [DF_tblFiles_FileContent]  DEFAULT (NULL) FOR [FileContent]
GO

I wrote the code in C#. I am having a Asp FileUpload control and a button to upload the file, and a Repeater control with two controls, a label for displaying the filename and hyper link control for downloading the file.

<body>
<form runat="server">
<asp:FileUpload runat="server" ID="fileUploadImage" />
<asp:Button runat="server" ID="cmdUpload" Text="Upload File" OnClick="cmdUpload_Click" />
<asp:Repeater runat="server" ID="rptrFiles">
    <HeaderTemplate>
        <table>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <asp:Label runat="server" ID="lblFilename" Text='<%# Eval("FileName")%>' />
            </td>
            <td>
              <asp:HyperLink runat="server" Target="_blank" ID="lbtDownload" Text="Download" NavigateUrl='<%# "Download.aspx?File="  + Eval("FileId").ToString() %>' />
            </td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>
</form>
</body>

Uploading the File to the Database
Code behind

protected void cmdUpload_Click(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(this.fileUploadImage.FileName);
    byte[] fileContent = this.fileUploadImage.FileBytes;
    using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("INSERT INTO tblFiles(Filename, FileContent) VALUES(@Filename, @FileContent)", connection))
        {
            SqlParameter fileNameParameter = new SqlParameter("@Filename", System.Data.SqlDbType.NVarChar, 255);
            fileNameParameter.Value = fileName;
            SqlParameter fileContentParameter = new SqlParameter("@FileContent", System.Data.SqlDbType.VarBinary);
            fileContentParameter.Value = fileContent;
            command.Parameters.AddRange(new SqlParameter[] { fileNameParameter, fileContentParameter });
            command.ExecuteNonQuery();
        }
    }
}

And here is code to bind the repeater from the Database

DataTable dtFiles = new DataTable("Files");
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT FileId, FileName FROM tblFiles", "Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
{
    adapter.Fill(dtFiles);
}
this.rptrFiles.DataSource = dtFiles;
this.rptrFiles.DataBind();

Download / Read the file from Database
And to download / read the file from Database, I am passing the File unique id to another page(download.aspx).I this page I am checking for the File querysting and based on that reading filecontent from Sql and writing it to Asp.net output stream. You can get more information about how to download files from IIS in this post.

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["File"] != null)
    {
        string fileId = Request.QueryString["File"];
        using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=Sample"))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("SELECT Filename, FileContent FROM tblFiles WHERE FileId = @FileId", connection))
            {
                command.Parameters.AddWithValue("@FileId", fileId);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.HasRows)
                {
                    reader.Read();
                    byte[] content = reader["FileContent"] as byte[];
                    string filename = reader["FileName"].ToString();
                    Response.Clear();
                    Response.ClearContent();
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
                    Response.AddHeader("Content-Length", content.Length.ToString());
                    Response.OutputStream.Write(content, 0, content.Length);
                    Response.End();
                }
            }
        }
    }
}

Please write to me if I missed something. Happy Programming .