Search and Populate GridView from database using Jquery Ajax

Introduction:

In this article I am going to explain how to search and Populate GridView from database using Jquery Ajax.

Prerequisites:

You need to add the necessary JQuery files to your page prior to use this plugin. In this example I have used the online JQuery CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery files to your solution. I have explained the steps here in detail.

I have used Address table from AdventureWorks Database. You can download it from here.

Implementation:

Here we are make use of Jquery Ajax post method in Jquery to check if record exists in database and get the details on the blur event of textbox.

HTML

 <html xmlns="http://www.w3.org/1999/xhtml">
 <head runat="server">
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
     <title runat="server"></title>
     <script src="http://code.jquery.com/jquery-1.10.2.min.js"></script>
     <script type='text/javascript'>
         $(document).ready(function () {
             //Attach on change event to textbox here
             $("#TextBox1").change(function () {
                 //Remove all previously added rows in gridview
                 $("[id*=sampleGV] tr").not($("[id*=sampleGV] tr:first-child")).remove();
                 $.ajax({
                     type: "POST",
                     //Call the method to get data from database 
                     url: "WebForm4.aspx/GetOrderDetails",
                     //Pass the value from textbox to method
                     data: '{"addressID":' + $("#TextBox1").val() + '}',
                     contentType: "application/json; charset=utf-8",
                     dataType: "json",
                     success: function (result) {
                         //Add the resul to gridview
                         $("#sampleGV").append("<tr><td>" + result.d[0][0] + "</td><td>" + result.d[0][1] + "</td><td>" + result.d[0][2] + "</td></tr>");
                     },
                     failure: function (response) {
                         alert(response.d);
                     },
                     error: function (response) {
                         alert(response.d);
                     }
                 });
 
             });
         });
     </script>
 </head>
 <body class="myCss">
     <div>
         <form id="form1" runat="server" class="hidden">
             <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
             <asp:GridView ID="sampleGV" runat="server" Width="1000px" ShowHeaderWhenEmpty="true"
                 CellPadding="0" CellSpacing="0">
             </asp:GridView>
         </form>
     </div>
 </body>
 </html>
 

C#:

First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side

 protected void Page_Load(object sender, System.EventArgs e)
 {
         //Load and empty row to gridview
         if (!IsPostBack) {
                DataTable dt = new DataTable();
                dt.Columns.Add("AddressID");
                dt.Columns.Add("AddressLine1");
                dt.Columns.Add("City");
                sampleGV.DataSource = dt;
                sampleGV.DataBind();
         }
 }
 

Next is the GetOrderDetails method which will read the parameter from client side and the search the database and then pass the result set back. Note the [Services.WebMethod()] attribute I added to the GetOrderDetails method,this is important  otherwise the serverside method will not be called from client side jQuery AJAX call.

[Services.WebMethod()]
public static Array GetOrderDetails(string addressID)
{
	//Create sql connection object here
	SqlConnection conn = null;
	//Create sql DataTable object here
	DataTable dt = new DataTable();
	//Create sql SqlDataAdapter object here
	SqlDataAdapter da = default(SqlDataAdapter);
	//Create sql ArrayList object here
	ArrayList rows = new ArrayList();
	//Get the connectionstring from webconfig file
	string connection = ConfigurationManager.ConnectionStrings("Adventure WorksConnectionStringPersonal").ConnectionString;
	//Create sql SqlConnection object here and assign the conenctionstring
	conn = new SqlConnection(connection);
	//Create sql query here
	string sql = "SELECT AddressID,AddressLine1,City FROM Person.Address a WHERE a.AddressID = @AddressID";
	//Create sql SqlCommand and assign connection and command here
	SqlCommand cmd = new SqlCommand(sql, conn);
	//Pass your parameter here
	cmd.Parameters.AddWithValue("@AddressID", addressID);
	//Open the SQLConnection here
	conn.Open();
	//Create SqlDataAdapter object here
	da = new SqlDataAdapter(cmd);
	using (DataSet ds = new DataSet()) {
		//Load the DataTable
		da.Fill(dt);
		foreach (DataRow dataRow in dt.Rows) {
			rows.Add(dataRow.ItemArray.Select(item => item.ToString()));
		}
		//Create an array and then return the values
		return rows.ToArray();
	}
	conn.Close();
}

Demo

Advertisements

, , , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: