Download Files from a specific folder

If you want to allow the user to download files from a specific folder in the server the following snippet of code lets you accomplish the task. Initially display all the files the user want to download in a Gridview. Put a download link to let the user download files.

HTML Markup

<div>
        <asp:GridView ID="gvFiles" runat="server" AutoGenerateColumns="False" CellPadding="2"
            ForeColor="#333333" GridLines="None" AllowPaging="True"
            onpageindexchanging="gvFiles_PageIndexChanging"
            onrowcommand="gvFiles_RowCommand">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:LinkButton ID="lbDownload" runat="server" Text="Download" CommandName="Download"
                            CommandArgument='<%#Eval("FullName")  %>'></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Name" HeaderText="File Name" />
                <asp:BoundField DataField="Length" HeaderText="Size (Bytes)" />
            </Columns>
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#999999" />
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        </asp:GridView>
    </div>

C#

using System.IO;


protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            BindDownloadFiles();
        }
    }
    private void BindDownloadFiles()
    {
        string DataDirectory= "~/DownloadFiles";

        DirectoryInfo dirInfo = new DirectoryInfo(Server.MapPath(DataDirectory));
        FileInfo[] files = dirInfo.GetFiles();
        gvFiles.DataSource = files;
        gvFiles.DataBind();
    }
    private void DownloadFile(string fileName, string fullFilePath)
    {
        Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
        Response.TransmitFile(fullFilePath);
        Response.End();
    }
    protected void gvFiles_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Download")
        {
            Uri uri = new Uri(e.CommandArgument.ToString());
            string[] fileInfo = uri.LocalPath.Split(';');
            string fullPath = fileInfo[0];
            string fileName = fileInfo[1];
            DownloadFile(fileName, fullPath);
        }
    }
    protected void gvFiles_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvFiles.PageIndex = e.NewPageIndex;
        BindDownloadFiles();
    }

Check/Uncheck all checkbox in gridview

The following snippet will demostrate how to check/uncheck all checkboxes in a Gridview. Using javascript will take few lines of code to accomplish the task. But using Jquery minimizes the number of lines to code to implement.

Add reference to the jquery library on the page,

<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.min.js" type="text/javascript"></script>

The script to check/uncheck the gridview checkboxes and the html code would be,

<script type="text/javascript">

function ToggleProductSelect(ctrl, chk) {
            $('#<%=gvProducts.ClientID %> :checkbox[id$=' + chk + ']').attr('checked', ctrl.checked);
        }
</script>


<div style="clear:both;">
                <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False"
                    DataKeyNames="ProductId" HeaderStyle-CssClass="header-column">
                    <Columns>
                        <asp:TemplateField>
                            <HeaderTemplate>
                                <input id="cbSelectAll" runat="server" type="checkbox" onclick="javascript:ToggleProductSelect(this,'cbSelect');" />
                            </HeaderTemplate>
                            <ItemTemplate>
                                <asp:CheckBox ID="cbSelect" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
                        <asp:BoundField DataField="Category" HeaderText="Category" />
                        <asp:BoundField DataField="Price" HeaderText="Price" />
                        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" />
                        <asp:BoundField DataField="Supplier" HeaderText="Supplier" />
                        <asp:CommandField SelectText="View" ShowSelectButton="True" />
                    </Columns>
                    <EmptyDataTemplate>
                        <div>No Products found.</div>
                    </EmptyDataTemplate>
                    <HeaderStyle CssClass="header-column" />
                </asp:GridView>
            </div>

C#

protected void Page_Load(object sender, EventArgs e)
{
        gvProducts.DataSource = GetProducts();
        gvProducts.DataBind();
}


public DataTable GetProducts()
        {
            DataTable dtProducts = new DataTable();
            SqlDataAdapter adpProducts = new SqlDataAdapter("GetProducts", sqlcon);
            adpProducts.SelectCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                adpProducts.Fill(dtProducts);
                return dtProducts;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }

Make ajax calls using jquery to bind dropdownlist

Introduction

This article describes how to make AJAX calls using Jquery to bind databound control like dropdownlist with data from database. To Bind the data to any databound control, return data as json format.

Default.aspx

<script type="text/javascript">
        $(document).ready(function () {
            //Retrieve data and bind to dropdownlist
            $.ajax({
                url: "../Services/BookService.asmx/GetBooks",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                type: "POST",
                data: {},
                error: function (err) {
                    alert("Error:" + err.toString());
                },
                success: function (data) {
                    $("#booksList").html(data);
                    $.each(data.d, function (key, value) {
                        $("#<%=ddlBooks.ClientID %>").append($("<option></option>").val(value.ID).html(value.BookName));
                    });
                }
            });
        });
    </script>

<div>
        <asp:DropDownList ID="ddlBooks" runat="server">
        <asp:ListItem>Choose...</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="btnGetBooks" runat="server" Text="Get Books" onclick=" btnGetBooks_Click" />
    </div> 

It is important to register the client script for event validation, since we bind the dropdownlist dynamically using client script it should be registered for event validation. Otherwise, the page will raise an error on postback. Though there is a possibility to bypass this scenario by setting either,

<%@ Page EnableEventValidation="false">

or Globally in the Web.config

<pages enableEventValidation="false"></pages>

The setting above may allow hackers to inject sql commands making the application vulnerable. To overcome this problem during the override the Render method as shown below.

Default.aspx.cs

protected override void Render(HtmlTextWriter writer)
    {
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID,"1");
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "2");
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "3");
        base.Render(writer);
    }

App_Code/Books.cs

public class Books
{
public Books()
{

}
    public string ID { get; set; }
    public string BookName { get; set; }
    public string Author{ get; set; }
}

Don't forget to uncomment the line shown below in the .asmx file if it does not exists add one.This enables us to make asynchronous calls to the function.

[System.Web.Script.Services.ScriptService]

App_Code/BookService.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// SQL Server Connection String
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

[WebMethod]
    public List<Books> GetBooks()
    {
        DataTable dtBooks = new DataTable();
        List<Books> books = new List<Books>();
        SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
        adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
        //adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
        try
        {
            adpBooks.Fill(dtBooks);
            Books book;
            foreach (DataRow dr in dtBooks.Rows)
            {
                book = new Books();
                book.ID = dr["ID"].ToString();
                book.BookName = dr["Name"].ToString();
                book.Author = dr["Author"].ToString();
                books.Add(book);
            }
            return books;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Call server side functions using PageMethods in AJAX

This post illustrates how to call server side function using PageMethod. Enabling PageMethods property in the ScriptManager allows us to call functions directly from the server side. Therefore significant performance improvement of the page is noticed.

Create a stored procedure to get Books from the database.


CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
      ,[BookName]
  FROM [Books]
END
GO

We will start by adding ScriptManager on the page and setting the EnablePageMethods property to true.


<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" EnablePageMethods="true">
</ajaxToolkit:ToolkitScriptManager>

Iterate through the list of books returned by the function using loops and bind the data to controls.



Default.aspx

<script type="text/javascript">
    function getBooks() {
            var data = { ID: "1", Name: "Csharp" };
            PageMethods.getBooks(data, function (returnValue) {
                for (var i in returnValue) {
                    var pList = document.getElementById("booksList");
                    var li = document.createElement("li");
                    li.appendChild(document.createTextNode(returnValue[i].ID + " - " + returnValue[i].BookName));
                    pList.appendChild(li);
                }
            });
    }
    </script>

<div>
<h3>Using Page Methods to call server side function without web service</h3>
<input type="button" onclick="getBooks()" value="Get Books" />
<ul id="booksList">
</ul>
</div>

Add a class in the code behind or in a seperate file and write the function to query the SQL server to get books. Create a WebMethod to which will return list of Books.

Default.aspx.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;


protected void Page_Load(object sender, EventArgs e)
    {

    }

[WebMethod]
    public static List<Books> getBooks(Books b)
    {
        DataTable dtBooks = new DataTable();
        dtBooks = b.GetBooks();
        List<Books> books = new List<Books>();

        Books book;
        foreach (DataRow dr in dtBooks.Rows)
        {
            book = new Books();
            book.ID = dr["ID"].ToString();
            book.BookName = dr["BookName"].ToString();
            books.Add(book);
        }
        return books;
    }


public class Books
    {
        // SQL Server Connection String
        SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

        public string ID { get; set; }
        public string BookName { get; set; }

        public DataTable GetBooks()
        {
            DataTable dtBooks = new DataTable();
            SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
            adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
            //adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
            try
            {
                adpBooks.Fill(dtBooks);
                return dtBooks;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }
    }    

Export data from SQL Server to Excel in asp.net

This sample introduces how to export data from SQL Server to an Excel spreadsheet and in the next article we shall see how to generate an Excel spreadsheet with data from SQL Server.

Default page retrieves data from SQL Server using a DataTable and then exports the DataTable to an Excel 2003/2007 spreadsheet on server disk.

In the previous post we have seen how to import data from excel to sql server. Please refer the table used in the previous post for this sample. First, create a procedure to select books from the database. Then create a function in the code-behind page to return Books in a DataTable. Use StringWriter and HtmlTextWriter to iterate through the data in the DataTable. Create an object for DataGrid and Bind the DataTable to it.

Stored procedure to get Books

CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
      ,[Name]
      ,[Author]
  FROM [Books]
END
GO

Default.aspx

<div>
<asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" />
            <asp:BoundField DataField="Name" HeaderText="Book Name" />
           <asp:BoundField DataField="Author" HeaderText="Author" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="btnExport" runat="server" Text="Export"
        onclick="btnExport_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</div>

Default.aspx.cs

using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


// SQL Server Connection String
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        gvBooks.DataSource = GetBooks();
        gvBooks.DataBind();
    }

protected void btnExport_Click(object sender, EventArgs e)
    {
        ExportToExcel(GetBooks(), "DownloadBooksToExcel.xls");
    }

public void ExportToExcel(DataTable dt, string fileName)
    {
        if (dt.Rows.Count > 0)
        {
            StringWriter sw = new StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);
            DataGrid dg = new DataGrid();
            dg.DataSource = dt;
            dg.DataBind();

            dg.RenderControl( htmlWriter);
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            this.EnableViewState = false;
            Response.Write(sw.ToString());
            Response.End();
        }
    }
    public DataTable GetBooks()
    {
        DataTable dtBooks = new DataTable();
        SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
        adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpBooks.Fill(dtBooks);
            return dtBooks;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Import data from Excel to SQL Server using C#.NET

Introduction

In this example  I am going to explain how to import data from Microsoft Excel to SQL Server. And, in the next blog post we will see how to export data from SQL Server back to Microsoft Excel. First, launch Visual studio and create a new project. Give it a name (ie, ImportExcel2SQL).

Create a database in the SQL Server, ExcelDataDB and create a table with the columns that matches to the columns in the Excel file.


CREATE TABLE [dbo].[Books](
[ID] [int] NOT NULL,
[BookName] [varchar](200) NOT NULL,
        [Author] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


Drop in a couple of controls from the Toolbox on to the page, FileUpload and a Button. Re-name the Button Text and say "Import".

Default.aspx

<h1>Import data from Excel to SQL server</h1>
<asp:FileUpload ID="FileUpload1" runat="server" />
&nbsp;
<asp:Button ID="Import" runat="server" Text="Import" onclick="Import_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>

Create a temporary folder(eg: TempExcelFiles) in the root of the directory to save the excel file before being imported to the SQL Server. Delete the Excel file in the temporary folder after the data has been imported.

For Microsoft office Excel 2007, please use the following connection string,

string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

and for other versions of Excel use,

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

Default.aspx.cs

using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;


SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Import_Click(object sender, EventArgs e)
    {
        string excelContentType = "application/vnd.ms-excel";
        string excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string contentType = FileUpload1.PostedFile.ContentType;
        if (FileUpload1.HasFile)
        {
            if (contentType == excelContentType || contentType == excel2010ContentType)
            {
                try
                {
                    string path = string.Concat(Server.MapPath("~/TempExcelFiles/"), FileUpload1.FileName);
                    FileUpload1.SaveAs(path);
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    using (OleDbConnection connection =
                                 new OleDbConnection(excelConnectionString))
                    {
                        OleDbCommand command = new OleDbCommand
                                ("Select * FROM [Sheet1$]", connection);

                        connection.Open();
                        using (DbDataReader dr = command.ExecuteReader())
                        {
                            using (SqlBulkCopy bulkCopy =
                                       new SqlBulkCopy(sqlcon))
                            {
                                bulkCopy.DestinationTableName = "Books";
                                bulkCopy.WriteToServer(dr);
                                Label1.Text = "The data has been exported successfully from Excel to SQL";
                            }
                        }
                    }
                }

                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }
    }

Calendar Basics in asp.net

For all the examples shown below the System.Globalization namespace should be used. It is a class library containing calender related functions.

Get the DayNames in C#.NET

public string[] GetDayNamesList()

{

        DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;

        string[] _months = dfi.MonthNames;

        return _months;

}

Output:

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Get Week Number of the year.

public static int GetWeekNumber(DateTime dtDate)

    {

        CultureInfo ciCurr = CultureInfo.CurrentCulture;

        int weekNum = ciCurr.Calendar.GetWeekOfYear(dtDate, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

        return weekNum;

    }

Output:

49

Get weeks spanned by

private static int getWeeksSpannedBy(DateTime startDate, DateTime endDate)

    {

        var calendar = CultureInfo.CurrentCulture.Calendar;

        var weekRule = CultureInfo.CurrentCulture.DateTimeFormat.CalendarWeekRule;

        var firstDayOfWeek = DayOfWeek.Sunday;

        int lastWeek = calendar.GetWeekOfYear(endDate, weekRule, firstDayOfWeek);

        int firstWeek = calendar.GetWeekOfYear(startDate, weekRule, firstDayOfWeek);

        int weekDiff = lastWeek - firstWeek + 1;

        return weekDiff;

    }

Output:

4

Retrieve images from SQL Server database and bind to Gridview

In the previous blog post I have explained how to upload images to SQL Server Database. In this post I am going to show you how you can retrieve images stored in SQL Server Database and bind it to gridview. I am going to handler to bind image in the gridview. Start by adding a handler at the root directory of your project and call it as ShowPhoto.ashx. We are going to use the same table that we used to store images in the database in the previous post. Additionally, a couple of stored procedures are needed to bind images to gridview. Refer previous post for tables

Stored procedure to get picture by Id.

CREATE PROCEDURE [dbo].[sprocGetPictureById]
@ImageId int
AS
BEGIN
SELECT [Picture]
  FROM [Pictures] WHERE ImageId = @ImageId
END

Stored procedure to get all pictures.

CREATE PROCEDURE [dbo].[sprocGetPictures]

AS
BEGIN
SELECT [ImageId]
      ,[ImageName]
      ,[ImageSize]
  FROM [Pictures]
END

ShowPhoto.ashx

<%@ WebHandler Language="C#" Class="ShowPhoto" %>

using System;
using System.Web;
using System.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class ShowPhoto : IHttpHandler
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    object img;
    public void ProcessRequest(HttpContext context)
    {
        int imageId, newWidth = 100, newHeight = 100;
        double scale = 0;
        if (context.Request.QueryString["id"] != null)
            imageId = Convert.ToInt32(context.Request.QueryString["id"]);
        else
            throw new ArgumentException("No parameter specified");

        //Code to Read Thumbnail Image from the database
        System.Drawing.Image image = System.Drawing.Image.FromStream(ShowPicture(imageId));
        if (image.Height < image.Width)
        {
            scale = ((double)newHeight) / image.Width;
        }
        else
        {
            scale = ((double)newWidth) / image.Height;
        }

        // Set new width and height.
        newWidth = (int)(scale * image.Width);
        newHeight = (int)(scale * image.Height);

        // create a new image from original
        System.Drawing.Image thumbnail = new Bitmap(image, newWidth, newHeight);
        System.Drawing.Graphics graphic = System.Drawing.Graphics.FromImage(thumbnail);

        graphic.InterpolationMode = InterpolationMode.HighQualityBicubic;
        graphic.SmoothingMode = SmoothingMode.HighQuality;
        graphic.PixelOffsetMode = PixelOffsetMode.HighQuality;
        graphic.CompositingQuality = CompositingQuality.HighQuality;

        graphic.DrawImage(image, 0, 0, newWidth, newHeight);

        ImageCodecInfo[] info;
        info = ImageCodecInfo.GetImageEncoders();
        EncoderParameters encoderParameters;
        encoderParameters = new EncoderParameters(1);
        encoderParameters.Param[0] = new EncoderParameter(Encoder.Quality, 100L);
        context.Response.ContentType = "image/jpeg";
        thumbnail.Save(context.Response.OutputStream, info[1], encoderParameters);
    }

    public Stream ShowPicture(int imageId)
    {
        SqlCommand cmd = new SqlCommand("sprocGetPictureById", sqlcon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ImageId", imageId);
        sqlcon.Open();
        try
        {
            img = cmd.ExecuteScalar();
            return new MemoryStream((byte[])img);
        }
        catch
        {
            return null;
        }
        finally
        {
            sqlcon.Close();
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}

Next, right-click the root of your project and click Add new item -> Web Form and give it a name (in our case RetrieveImage.aspx)

RetrieveImage.aspx

<div>
    <asp:GridView ID="gvPictures" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ImageId" HeaderText="ID" />
            <asp:BoundField DataField="ImageName" HeaderText="Image Name" />
            <asp:BoundField DataField="ImageSize" HeaderText="Image Size(Kb)" />
            <asp:TemplateField HeaderText="Picture">
                <ItemTemplate>
                    <img id="Img1" alt='<%#Eval("ImageName") %>' runat="server" src='<%# "~/ShowPhoto.ashx?id=" + Eval("ImageId") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</div>

RetrieveImage.aspx.cs

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            gvPictures.DataSource = BindPictures();
            gvPictures.DataBind();
        }
    }


private DataTable BindPictures()
    {
        DataTable dtPictures = new DataTable();
        SqlDataAdapter adpPictures = new SqlDataAdapter("sprocGetPictures", sqlcon);
        adpPictures.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpPictures.Fill(dtPictures);
            return dtPictures;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Upload images to SQL Server Database using asp.net

There are several ways to upload images from an asp.net application. Uploading images or pictures to the directory is quite simple and easy in asp.net but another approach is to use SQL server database. To save an image file in the database, first create a table with columns to hold the ImageName, ImageSize, Picture. The datatype for the Picture column should be image and for other columns  use nvarchar. The image is saved in the database in binary format. In order to save an image file in the database it should be first converted to byte.

Create a Pictures table in the database Album.

CREATE TABLE [dbo].[Pictures](
[ImageId] [int] IDENTITY(1,1) NOT NULL,
[ImageName] [nvarchar](100) NOT NULL,
[ImageSize] [int] NULL,
[Picture] [image] NULL,
 CONSTRAINT [PK_Pictures] PRIMARY KEY CLUSTERED
(
[ImageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Stored Procedure to save image to the database table Pictures.

CREATE PROCEDURE sprocSaveImage
@ImageName nvarchar(100),
@ImageSize int,
@Picture image
AS
BEGIN
INSERT INTO [Album].[dbo].[Pictures]
           (ImageName
           ,ImageSize
           ,Picture)
     VALUES
           (@ImageName
           ,@ImageSize
           ,@Picture)
END
GO

Default.aspx

<div>
    Image:&nbsp;
    <asp:FileUpload ID="fileImage" runat="server" />
    &nbsp;
    <asp:Button ID="btnUpload" runat="server" Text="Upload"
        onclick="btnUpload_Click" />
</div>

Default.aspx.cs

using System.Data;
using System.Data.SqlClient;

SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Album.MDF;Integrated Security=True;User Instance=True");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
protected void btnUpload_Click(object sender, EventArgs e)
    {
        HttpPostedFile _pfImage = fileImage.PostedFile;
        string imgName = _pfImage.FileName;
        int imgSize = _pfImage.ContentLength;
        byte[] imageOriginal = new byte[imgSize];
        _pfImage.InputStream.Read(imageOriginal, 0, imgSize);

        SaveImageToDatabase(imgName, imgSize, imageOriginal);
    }
    private void SaveImageToDatabase(string imgName, int imgSize, byte[] imageOriginal)
    {
        SqlCommand sqlcmd = new SqlCommand("sprocSaveImage", sqlcon);
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.Parameters.AddWithValue("ImageName", imgName);
        sqlcmd.Parameters.AddWithValue("ImageSize", imgSize);
        sqlcmd.Parameters.AddWithValue("Picture", imageOriginal);
        if (sqlcon.State == ConnectionState.Closed)
        {
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
        }
        if (sqlcon.State == ConnectionState.Open)
        {
            sqlcon.Close();
        }
    }

In the next post I will explain how to retrieve images from SQL server database and bind to gridview.

Add/Remove Gridview rows dynamically in asp.net

In this article i am going to explain how to add or remove rows from Gridview dynamically. First, a default row in the gridview is added with controls bound to it when the page loads. Then, the user can add or remove any number of rows to the gridview. Every time a new row is added the gridview is loaded from the ViewState. It is important to preserve the state of the DropDownList while adding another row therefore the selected value of the DropDownList should be bound with the DataField.

Default.aspx

<div>
 <asp:GridView ID="gvBooks" runat="server" ShowFooter="True"
                                AutoGenerateColumns="False" HeaderStyle-CssClass="header-column" onrowcommand="gvBooks_RowCommand"
                                onrowdatabound="gvBooks_RowDataBound" BorderStyle="None" GridLines="None">
                                <Columns>
                                    <asp:TemplateField HeaderText="Book Name">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtBookName" runat="server">
                                            </asp:TextBox>
                                        </ItemTemplate>
                                        <FooterTemplate>
                                            <asp:LinkButton ID="lbNewBook" runat="server" CausesValidation="false" CommandName="NewBook">New</asp:LinkButton>
                                        </FooterTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Author">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtAuthor" runat="server" Text='<%#Bind("Author") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Description">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtDescription" runat="server" Text='<%#Bind("Description") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Genre">
                                        <ItemTemplate>
                                            <asp:DropDownList ID="ddlGenre" runat="server" AppendDataBoundItems="true">
                                            <asp:ListItem>Choose...</asp:ListItem>
                                            <asp:ListItem>Computer</asp:ListItem>
                                            <asp:ListItem>Fantasy</asp:ListItem>
                                           <asp:ListItem>Horror</asp:ListItem>
                                           <asp:ListItem>Comedy</asp:ListItem>
                                            </asp:DropDownList>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Price">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtPercentage" runat="server" Width="100px" Text='<%#Bind("Price") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Publish Date">
                                        <ItemTemplate>
                                            <asp:TextBox ID="txtPublishDate" runat="server" Width="100px" Text='<%#Bind("PublishDate") %>'></asp:TextBox>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField>
                                        <ItemTemplate>
                                            <asp:ImageButton ID="imgbtnDeleteBook" runat="server" CausesValidation="false" ImageUrl="~/App_Themes/Red/Images/cross.png" CommandName="DeleteBook" />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </div>

Default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
                 AddBookDefault();
        }
}
private void AddBookDefault()
    {
        DataTable dt = new DataTable();
        DataRow dr = null;
        dt.Columns.Add(new DataColumn("BookName", typeof(string)));
        dt.Columns.Add(new DataColumn("Author", typeof(string)));
        dt.Columns.Add(new DataColumn("Description", typeof(string)));
        dt.Columns.Add(new DataColumn("Genre", typeof(string)));
        dt.Columns.Add(new DataColumn("Price", typeof(string)));
        dt.Columns.Add(new DataColumn("PublishDate", typeof(string)));

        dr = dt.NewRow();

        dr["BookName"] = string.Empty;
        dr["Author"] = string.Empty;
        dr["Description"] = string.Empty;
        dr["Genre"] = string.Empty;
        dr["Price"] = string.Empty;
        dr["PublishDate"] = string.Empty;

        dt.Rows.Add(dr);
        //dr = dt.NewRow();
        //Store the DataTable in ViewState

        ViewState["CurrentTable"] = dt;

        gvBooks.DataSource = dt;
        gvBooks.DataBind();
    }
protected void gvBooks_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        int rowIndex = 0;
        DataTable dt = (DataTable)ViewState["CurrentTable"];

        if (e.CommandName == "NewBook")
        {
            DataRow rw = dt.NewRow();
            dt.Rows.Add(rw);

            //Set Previous Data
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count - 1; i++)
                {
                    TextBox txtBookName = (TextBox)gvBooks.Rows[rowIndex].Cells[0].FindControl("txtBookName");
                    TextBox txtAuthor = (TextBox)gvBooks.Rows[rowIndex].Cells[1].FindControl("txtAuthor");
                    TextBox txtDescription = (TextBox)gvBooks.Rows[rowIndex].Cells[2].FindControl("txtDescription");
                    DropDownList ddlGenre = (DropDownList)gvBooks.Rows[rowIndex].Cells[3].FindControl("ddlGenre");
                    TextBox txtPrice = (TextBox)gvBooks.Rows[rowIndex].Cells[4].FindControl("txtPrice");
                    TextBox txtPublishDate = (TextBox)gvBooks.Rows[rowIndex].Cells[5].FindControl("txtPublishDate");

                    dt.Rows[i]["BookName"] = txtBookName.Text;
                    dt.Rows[i]["Author"] = txtAuthor.Text;
                    dt.Rows[i]["Description"] = txtDescription.Text;
                    dt.Rows[i]["Genre"] = ddlGenre.SelectedItem.Text;
                    dt.Rows[i]["Price"] = txtPrice.Text;
                    dt.Rows[i]["PublishDate"] = txtPublishDate.Text;

                    rowIndex++;
                }
            }
        }
        else if (e.CommandName == "DeleteBook")
        {
            GridViewRow gvr = (GridViewRow)((ImageButton)e.CommandSource).NamingContainer;
            dt.Rows.RemoveAt(gvr.RowIndex);
        }

        gvBooks.DataSource = dt;
        gvBooks.DataBind();
    }

protected void gvBooks_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DropDownList ddlGenre = (DropDownList)e.Row.Cells[3].FindControl("ddlGenre");
         
            ddlGenre.SelectedValue = DataBinder.Eval(e.Row.DataItem, "Genre").ToString();
        }
    }

Dynamic Gridview with TemplateColumn

There are situations when you want to create the columns of the Gridview dynamically because the number of columns and rows are unknown until runtime. Thankfully, ASP.Net has a nice feature to overcome this challenge. Using a seperate class which inherits the ITemplate interface lets you create the controls and the data to be bound to the Gridview at runtime.

Create a class called GridviewTemplate which also inherits the ITemplate interface, in the App_Code folder of your project.

GridviewTemplate.cs

public class GridViewTemplate : ITemplate
{
    ListItemType _templateType;
    string _columnName;

    //Constructor where we define the template type and column name.
    public GridViewTemplate(ListItemType type, string colname)
    {
        _templateType = type;
        _columnName = colname;
    }

    void ITemplate.InstantiateIn(System.Web.UI.Control container)
    {
        switch (_templateType)
        {
            case ListItemType.Header:
                Label lbl = new Label();
                lbl.Text = _columnName;
                container.Controls.Add(lbl);
                break;

            case ListItemType.Item:
                TextBox tb1 = new TextBox();
                tb1.DataBinding += new EventHandler(tb1_DataBinding);
                tb1.Columns = 8;
                container.Controls.Add(tb1);
                break;

            //EditItem is optional
            case ListItemType.EditItem:
                DropDownList ddl = new DropDownList();
                ddl.ID = "ddl" + _columnName;
                container.Controls.Add(ddl);
                break;

            case ListItemType.Footer:
                CheckBox chkColumn = new CheckBox();
                chkColumn.ID = "Chk" + _columnName;
                container.Controls.Add(chkColumn);
                break;
        }
    }

    /// <summary>
    /// This is the event, which will be raised when the binding happens.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void tb1_DataBinding(object sender, EventArgs e)
    {
        TextBox txtdata = (TextBox)sender;
        GridViewRow container = (GridViewRow)txtdata.NamingContainer;
        object dataValue = DataBinder.Eval(container.DataItem, _columnName);
        if (dataValue != DBNull.Value)
        {
            txtdata.Text = dataValue.ToString();
        }
    }
}

Default.aspx

<div>
<asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False">
</asp:GridView>
</div>

Default.aspx.cs

//Global declaration of variables on the page

const string PRODUCTNAME = "PRODUCT NAME";
const string ID = "ID";
const string PRICE = "PRICE";

protected void Page_Load(object sender, EventArgs e)
    {
        BindProducts();
    }

    private void BindProducts()
    {
        DataTable dt = new DataTable();
     
        DataColumn dc = new DataColumn(ID, typeof(System.Int32));
        dc.AutoIncrement = true;
        dt.Columns.Add(dc);

        dc = new DataColumn(PRODUCTNAME, typeof(System.String));
        dt.Columns.Add(dc);

        dc = new DataColumn(PRICE, typeof(System.Double));
        dt.Columns.Add(dc);

        Random r = new Random();

        for (int idx = 0; idx < 10; idx++)
        {
            DataRow dr = dt.NewRow();

            dr[PRODUCTNAME] = "Product " + Convert.ToString((idx + 1));
            dr[PRICE] = Convert.ToDouble(Math.Round(r.NextDouble() * 100 + 25, 2));

            dt.Rows.Add(dr);
        }

        foreach (DataColumn col in dt.Columns)
        {
            TemplateField tf= new TemplateField();
            tf.HeaderTemplate = new GridViewTemplate(ListItemType.Header, col.ColumnName);
            tf.ItemTemplate = new GridViewTemplate(ListItemType.Item, col.ColumnName);
            gvProducts.Columns.Add(tf);
        }

        gvProducts.DataSource = dt;
        gvProducts.DataBind();
    }

Grouping Gridview cells in ASP.NET

Grouping cells in a Gridview is important to merge related items. But, merging related items has not been easy from the code-behind. Thankfully, using Jquery merging of gridview cells can be done effortlessly.


Add reference to the jquery library from the Microsoft Content Delivery Network or Google Libraries API,

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
(or alternatively)
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js"></script>


And in the body place two Gridviews, one for listing the products with no grouping and the other for listing the products with grouping.

Default.aspx

<script type="text/javascript">

function GroupGridViewCells(gridviewId) {

            var rowNum = $(gridviewId + " tr").length;

            var colNum = $(gridviewId + " tr:eq(0)>th").length;

            var cell = null;

            var previouscell = null;

            // Begin to loop from the second row to the end
            for (var col = 0; col < colNum; ++col) {

                for (var row = 1; row < rowNum; ++row) {

                    cell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();

                    if (row == 1) {
                        previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")").first();
                        previouscell.attr("rowspan", "1");
                    }
                    else {

                        if (cell.html() == previouscell.html()) {

                            previouscell.attr("rowspan", parseInt(previouscell.attr("rowspan") + 1));
                            cell.css("display", "none");
                        }
                        else {

                            previouscell = $(gridviewId + " tr:eq(" + row + ")>td:eq(" + col + ")");
                            previouscell.attr("rowspan", "1");
                            previouscontent = previouscell.html();
                        }
                    }
                }
            }
        }

$(document).ready(function () {
        GroupGridViewCells("#groupedGridView");
}
</script>

<div>
        <table>
            <tr>
                <td style="padding: 20px">
                    <h3>
                        GridView(without grouping)</h3>
                    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
                        OnPageIndexChanged="GridView1_PageIndexChanged"
                        OnPageIndexChanging="GridView1_PageIndexChanging">
                        <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                        <RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                    </asp:GridView>
                </td>
                <td style="padding: 20px">
                    <h3>
                        GridView(with grouping)</h3>
                    <asp:GridView ID="groupedGridView" runat="server" AllowPaging="True"
                        OnPageIndexChanged="groupedGridView_PageIndexChanged"
                        OnPageIndexChanging="groupedGridView_PageIndexChanging">
                        <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                        <RowStyle HorizontalAlign="Center" VerticalAlign="Middle" />
                    </asp:GridView>
                </td>
            </tr>
        </table>
    </div>

Default.aspx.cs

using System.Data;

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindSortedProducts(GridView1);
                BindSortedProducts(groupedGridView);
            }
        }

private void BindSortedProducts(GridView gridView)
        {
            const string ProductsDataViewStateId = "ProductsData";
            DataTable dt = ViewState[ProductsDataViewStateId] as DataTable;

            if (dt == null)
            {
                dt = new DataTable();
                dt.Columns.Add("Product Name", typeof(string));
                dt.Columns.Add("Category", typeof(int));
                dt.Columns.Add("Price", typeof(double));
                Random r = new Random();

                for (int i = 1; i <= 100; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["Product Name"] = "Product" + r.Next(1, 5);
                    dr["Category"] = Convert.ToInt32(r.Next(1, 5));
                    dr["Price"] = Convert.ToDouble(Math.Round(r.NextDouble() * 100 + 25, 2));
                    dt.Rows.Add(dr);
                }

                ViewState[ProductsDataViewStateId] = dt;
            }

            // Sort by ProductName and Category
            dt.DefaultView.Sort = "Product Name,Category";
         
            gridView.DataSource = dt;
            gridView.DataBind();
        }


        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            generalGridView.PageIndex = e.NewPageIndex;
        }

        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            BindSortedProducts(GridView1);
        }

        protected void groupedGridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            groupedGridView.PageIndex = e.NewPageIndex;
        }

        protected void groupedGridView_PageIndexChanged(object sender, EventArgs e)
        {
            BindSortedProducts(groupedGridView);
        }

Serialize JSON string using asp.net and Jquery

As web applications are highly responsive, with asynchronous request to the sever and back to the client. In order to achieve flicker-free effect on web pages the request made to the server is asynchronous. Therefore the data sent to the browser should be in certain format (in our case JSON). Serialize the JSON string before being sent to the web browser.

App_Data/Persons.xml

<?xml version="1.0" encoding="utf-8" ?>
<persons>
    <person id="p101">
        <name>Aisha, Kumari</name>
        <age>28</age>
        <sex>female</sex>
        <nationality>India</nationality>
        <date_of_birth>2000-10-01</date_of_birth>
        <description> Sales manager </description>
    </person>
    <person id="p102">
        <name>Jonathan, Trott</name>
        <age>41</age>
        <sex>male</sex>
        <nationality>United states</nationality>
        <date_of_birth>2000-12-16</date_of_birth>
        <description> A former architect. </description>
    </person>
    <person id="p103">
        <name>Angelina, Jolie</name>
        <age>32</age>
        <sex>female</sex>
        <nationality>United states</nationality>
        <date_of_birth>2000-11-17</date_of_birth>
        <description> Technical operator. </description>
    </person>
    <person id="p104">
        <name>Jagatheesh, Pandian</name>
        <age>35</age>
        <sex>male</sex>
        <nationality>India</nationality>
        <date_of_birth>2001-03-10</date_of_birth>
        <description> Software Engineer. </description>
    </person>
    <person id="p105">
        <name>Jacob,John</name>
        <age>25</age>
        <sex>male</sex>
        <nationality>England</nationality>
        <date_of_birth>2001-09-10</date_of_birth>
        <description> Cheif Executive. </description>
    </person>
    <person id="p106">
        <name>Cynthia, Myriam</name>
        <age>29</age>
        <sex>female</sex>
        <nationality>China</nationality>
        <date_of_birth>2000-09-02</date_of_birth>
        <description> Operations manager. </description>
    </person>
    <person id="p107">
        <name>Nixon, Paul</name>
        <age>45</age>
        <sex>male</sex>
        <nationality>England</nationality>
        <date_of_birth>2000-11-02</date_of_birth>
        <description> A deep sea diver. </description>
    </person>
    <person id="p108">
        <name>Knorr, Stefan</name>
        <age>54</age>
        <sex>male</sex>
        <nationality>United states</nationality>
        <date_of_birth>2000-12-06</date_of_birth>
        <description> Technical Mentor. </description>
    </person>
    <person id="p109">
        <name>Kress, Peter</name>
        <age>43</age>
        <sex>male</sex>
        <nationality>India</nationality>
        <date_of_birth>2000-11-02</date_of_birth>
        <description> Artist. </description>
    </person>

    <person id="p110">
        <name>O'Brien, Tim</name>
        <age>33</age>
        <sex>male</sex>
        <nationality>Ireland</nationality>
        <date_of_birth>2000-12-09</date_of_birth>
        <description> Programmer. </description>
    </person>
</persons>

App_Code/Person.cs

public class Book
{

    /// <summary>
    /// do not change or remove "id", "value" and the "label" variables as atuocomplete need them to be sent    ///back
    /// </summary>
    public string id { get; set; }
    public string label { get; set; }
    public string value { get; set; }
 
    public string Name{get;set;}
    public string Age{ get; set; }
    public string Sex{ get; set; }
    public string Nationality{ get; set; }
    public string Date_of_birth{ get; set; }
    public string Description { get; set; }
}

PersonAutocomplete.ashx

<%@ WebHandler Language="C#" Class="AutoComplete" %>
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Web;
using System.Web.Script.Serialization;

public class AutoComplete : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        //  Query string 'term' is for autocomplete. By default, it sends the variable
        //  "term" with the search word to the backend page.
        string searchText = context.Request.QueryString["term"];

        Collection<Person> persons= new Collection<Person>();

        DataSet ds = new DataSet();
        ds.ReadXml(HttpContext.Current.Server.MapPath("App_Data/Persons.xml"));
        DataView dv = ds.Tables[0].DefaultView;
        dv.RowFilter = String.Format("title like '{0}*'", searchText.Replace("'", "''"));

        Person person;
        foreach (DataRowView myDataRow in dv)
        {
            person= new Person();
            person.id = myDataRow["id"].ToString();
            person.value = person.label = person.Name = myDataRow["name"].ToString();
            person.Age= myDataRow["age"].ToString();
            person.Sex= myDataRow["sex"].ToString();
            person.Nationality= myDataRow["nationality"].ToString();
            person.Date_of_birth = myDataRow["date_of_birth"].ToString();
            person.Description = myDataRow["description"].ToString();
            persons.Add(person);
        }

        JavaScriptSerializer serializer = new JavaScriptSerializer();

        string jsonString = serializer.Serialize(persons);

        context.Response.Write(jsonString);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Initially, reference the required Jquery library, Jquery UI library and the CSS in the head section as shown below.

Default.aspx

<link rel="stylesheet" href="Styles/jquery-ui.css" type="text/css" media="all" />
<link rel="stylesheet" href="Styles/site.css" type="text/css" />
<script type="text/javascript" src="Scripts/jquery.min.js"></script>
<script type="text/javascript" src="Scripts/jquery-ui.min.js"></script>
   
<script type="text/javascript">
        $(function () {
            $('#<%= tbBookName.ClientID %>').autocomplete({
                source: "PersonAutoComplete.ashx",
                select: function (event, ui) {
                    $(".name").text(ui.item.Name);
                    $(".age").text(ui.item.Age);
                    $(".sex").text(ui.item.Sex);
                    $(".date_of_birth").text(ui.item.Date_of_birth);
                    $(".description").text(ui.item.Description);
                }
            });
        });
    </script>

And, put the markup below in the body,

<form id="form1" runat="server">
    <h3>
        AutoComplete Example -- serialize JSON string and return search result
    </h3>
    <div class="search_bar">
        select book:
        <asp:TextBox ID="tbPersonName" runat="server" />
        (try a few examples like: 'Cynthia, Myriam', ' Kress, Peter')</div>
    <div class="search_response">
        <p>
            name: <span class="name"></span>
        </p>
        <p>
            age: <span class="age"></span>
        </p>
        <p>
            sex: <span class="sex"></span>
        </p>
        <p>
            date of birth: <span class="date_of_birth"></span>
        </p>
        <p>
            description: <span class="description"></span>
        </p>
    </div>
    </form>