How to use SqlDataAdapter Class in ASP.NET

Introduction

Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database(according to msdn library). You can say that retrieved table load into DataSet.

Represents a set of data commands and a database connection that are used to fill the DataSet

SqlDataAdapter class contains different parameter or non parameter constructor such as 

SqlDataAdapter( ) // you can initiate a new instance of SqlDataAdapter class.
SqlDataAdapter(SqlCommand instance) //you can specify SqlCommand instance into the SqlDataAdapter.

Lets take a simple example to take a SqlCommand instance for retrieving data from the database and these object pass to SqlDataAdapter object.

Algorithm

Step-1 : Create a SqlConnection class object  for connecting database server.

SqlConnection con = new SqlConnection();
        con.ConnectionString =ConfigurationManager.ConnectionStrings["ConnectionString"].ToString ();

        con.Open ();


Step-2 : Create a SqlCommand Object for retrieving data columns and data rows.

SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from deltable";

        
Step-3 : Connect SqlCommand to SqlConnection.

cmd.Connection = con;

Step-4 : Create SqlDataAdapter Object.
Step-5 : Pass SqlCommand instance to SqlDataAdapter object.

SqlDataAdapter da = new SqlDataAdapter(cmd);

Step-6 : Create DataSet Object.

DataSet ds = new DataSet();

Step-7 : Fill DataSet by SqlDataAdapter using fill( ) method.

da.Fill(ds, "deltable");

Step-8 : Bind GridView or any other datacontrol from DataSet.

  GridView1.DataSource = ds;

        GridView1.DataBind();

Complete code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="binding.aspx.cs" Inherits="binding" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="GetData"
        Width="123px" />
    <br />
    <div>
   
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
   
    </div>
    </form>
</body>

</html>
Codebehind

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.Configuration;
using System.Data;

public partial class binding : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString =ConfigurationManager.ConnectionStrings ["ConnectionString"].ToString ();
        con.Open ();

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from deltable";
        cmd.Connection = con;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "deltable");
        GridView1.DataSource = ds;
        GridView1.DataBind();



    }
}
Output
How to use SqlDataAdapter Class in ASP.NET

How to use SqlDataAdapter Class in ASP.NET How to use SqlDataAdapter Class in ASP.NET Reviewed by Jacob Lefore on October 18, 2013 Rating: 5

No comments