Tuesday, 21 February 2012

Calling database using jQuery AJAX and ASP.NET

AJAX offers users a seamless way to work with your interface, no waiting for whole pages to load. jQuery has a set of tools to make it super simple to implement. We will walk you through how to use jQuery to communicate databse using jquery’s ajax method in asp.net.
Each example will feature the same requirement, and that is to obtain and display the Customer details relating to a specific CustomerID selected from a DropDownList on a page called Customer.aspx.


And we use following Database table:
1
2
3
4
5
CREATE TABLE [dbo].[tblCustomer](
    [customerID] [int] NOT NULL,
    [firstName] [varchar](50) NULL,
    [surname] [varchar](50) NULL,
    [Company] [varchar](50) NULL,)
Once table created, need to insert some data into tblCustomer.
Here is Customer.aspx code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customer.aspx.cs" Inherits="Customer" %>
"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
"http://www.w3.org/1999/xhtml">
"server">
    Customer Page
    
    
"form1" runat="server">
    
"SelectCustomers">
        Select Customer:
        "Customers" runat="server">
        
    
    
"CustomerDetails">
    
    
Code behind file customer.cs code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
public partial class Customer : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       string st= ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
        SqlConnection con = new SqlConnection(st);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from tblCustomer",con);
        Customers.DataSource=cmd.ExecuteReader();
        Customers.DataValueField = "customerID";;
       Customers.DataTextField = "firstName";
        Customers.DataBind();
    }
}
and webconfig file code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
xml version="1.0"?>
<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0"/>
    system.web>
    <connectionStrings>
        <add name="aaa" connectionString="Data Source=ASHISHSQLEXPRESS;Initial Catalog=tblCustomers;Integrated Security=True;Pooling=False"/>
    connectionStrings>
configuration>
So far our project VS solution explorer look like:

Method 1
Now, we first call ASPX File by jquery AJAX to get customer data into customer.aspx page.
create new page called fetchcustomer.aspx. put below code in fatchcustomer.cs file .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
public partial class FetchCustomer : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string st= ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
        string id = Request.QueryString["customerID"];
        if (id != null )
        {
            using (SqlConnection con = new SqlConnection(st))
            {
            using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
                {
                    cmd.Parameters.AddWithValue("cid", Request.QueryString["customerID"]);
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            Response.Write("");
                            Response.Write(" CustomerID:" + rdr["CustomerID"].ToString() + "
");
                            Response.Write(" First Name:" + rdr["Firstname"].ToString() + "
");
                            Response.Write(" Surname:" + rdr["surname"].ToString() + "
");
                            Response.Write(" Company" + rdr["company"].ToString() + "
");
                            Response.Write("
");
 
                        }
                    }
                }
            }
        }
        else
        {
            Response.Write("No customer selected
");
        }
        Response.End();
    }
}
Now we use jQuery Ajax function to call fetchcustomer page. here is code of customer.aspx page
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Customer.aspx.cs" Inherits="Customer" %>
 
"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
"http://www.w3.org/1999/xhtml">
"server">
    
    
 
   <%-- with other asp page--%>
   
 
    
"form1" runat="server">
    
"SelectCustomers">
        Select Customer:
        "Customers" runat="server">
        
    
    
"CustomerDetails">
    
    
Method 2
Now, we call Web service to get customer data into customer.aspx page.
create new web sevice called fetchcustomer.asmx. put below code in fetcustomer.cs file (web service file mostly in App_code folder) .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Script.Services;
///
/// Summary description for FetchCustomer
///
[WebService(Namespace = "http://ashishblog.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
 [System.Web.Script.Services.ScriptService]
public class FetchCustomer : System.Web.Services.WebService {
    public FetchCustomer () {
       //Uncomment the following line if using designed components
        //InitializeComponent();
    }
    [WebMethod]
    public string GetCustomer(string CustomerID)
    {
        string res = "No customer selected
";
        string st = ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
       // Company com = new Company();
        if (CustomerID  != null)
        {
            StringBuilder sb = new StringBuilder();
            using (SqlConnection con = new SqlConnection(st))
            {
                using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
                {
                    cmd.Parameters.AddWithValue("cid",CustomerID );
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                             //com.CompanyID = rdr["company"].ToString();
                            //com.CompanyName = rdr["surname"].ToString();
                            sb.Append("");
                            sb.Append("" + rdr["customerid"].ToString() + "
");
                            sb.Append("" + rdr["firstname"].ToString() + "
");
                            sb.Append("" + rdr["company"].ToString() + "
");
                            sb.Append( rdr["surname"].ToString() + "
");
                            res = sb.ToString();
                        }
                    }
                }
            }
        }
        return res;
    }
}
Now we use jQuery Ajax function to call fetchcustomer web service. here is code (put into customer.aspx page)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<%--using web service with string output--%>
An alternative to above example, instead of string we can pass customer object from web service, here is code of webservice code file fetchcustomer.cs:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Web.Script.Services;
///
/// Summary description for FetchCustomer
///
[WebService(Namespace = "http://ashishblog.com/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
 [System.Web.Script.Services.ScriptService]
public class FetchCustomer : System.Web.Services.WebService {
    public FetchCustomer () {
        //Uncomment the following line if using designed components
        //InitializeComponent();
    }
    [WebMethod]
    public Customer GetCustomer(string CustomerID)
    {
        //string res = "No customer selected
";
        string st = ConfigurationManager.ConnectionStrings["aaa"].ConnectionString;
       Customer  com = new Customer ();
 
        if (CustomerID  != null)
        {
            StringBuilder sb = new StringBuilder();
            using (SqlConnection con = new SqlConnection(st))
            {
                using (SqlCommand cmd = new SqlCommand("select * from tblCustomer where customerID = @cid", con))
                {
 
                    cmd.Parameters.AddWithValue("cid",CustomerID );
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            com.CustomerID = rdr["customerid"].ToString();
                            com.FirstName = rdr["firstname"].ToString();
                            com.Company = rdr["company"].ToString();
                            com.surname  = rdr["surname"].ToString();
 
                            //sb.Append("");
                            //sb.Append("" + rdr["customerid"].ToString() + "
");
                            //sb.Append("" + rdr["firstname"].ToString() + "
");
 
                            //sb.Append("" + rdr["company"].ToString() + "
");
                            //sb.Append( rdr["surname"].ToString() + "
");
                            //res = sb.ToString();
                        }
                    }
                }
            }
        }
        return com;
    }
    public class Customer
    {
        public string CustomerID { get; set; }
        public string FirstName { get; set; }
        public string surname { get; set; }
        public string Company { get; set; }
 
    }
}
Now we use jQuery Ajax fuction to call fetchcustomer web service (customer object). here is code (put into customer.aspx page)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<%-- using web service with object or class output--%>
   
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)