Sunday, 11 September 2016

Using sql data adapter for reading data in ADO.NET

10:08    No comments

Sql Data Adapter
It is used to read  the data and bind it to the DataSet. It is used for disconnected data access.
fill method does the part of opening and closing connection as soon as the data is loaded.

Creating instance of Sql data adapter

  1.  SqlDataAdapter adapter = new SqlDataAdapter();
  2.  SqlDataAdapter adapter = new SqlDataAdapter(sqlcommand);
  3.  SqlDataAdapter adapter = new SqlDataAdapter(string, sqlconnection);
  4.  SqlDataAdapter adapter = new SqlDataAdapter(string, string);

Let's see with an example:
select * from EmployeeTest



Creating Procedure for accessing data
Create proc A_GetAllEmployeeDetails
As
select * from EmployeeTest


and 

Create proc A_GetEmployeeDetails
@empName varchar(200)
As
select * from EmployeeTest where EmpName like @empName +'%'

In the aspx file put the following code


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            Employee Name :
                <asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
            <asp:Button ID="EmpSearch" runat="server" OnClick="btnClick_Search" Text="Search" />
            <br />
            <br />
            <asp:GridView ID="gdvEmlpoyee" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>

In code behind add the following code

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ADONET
{
    public partial class UsingSqlDataAdapter : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
                using (SqlConnection sqlcon = new SqlConnection(CS))
                {
                    SqlCommand sqlcmd = new SqlCommand("A_GetAllEmployeeDetails", sqlcon);
                    sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    gdvEmlpoyee.DataSource = ds;
                    gdvEmlpoyee.DataBind();

                }
            }
        }
        protected void btnClick_Search(object sender, EventArgs e)
        {
            String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
            using (SqlConnection sqlcon = new SqlConnection(CS))
            {
                SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeDetails", sqlcon);
                sqlcmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlcmd.Parameters.AddWithValue("@empName", txtEmployeeName.Text);
                SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                gdvEmlpoyee.DataSource = ds;
                gdvEmlpoyee.DataBind();
            }
        }
    }

}

Lets Execute our application we will get the following output




0 comments:

Post a Comment