Working With SqlDataAdapter Class in ADO.NET

Introduction

The SqlDataAdapter class is found in the System.Data.SqlClient namespace. It is a very important class in the .NET framework. It works as a bridge between DataSet and Database. It opens the database connection, executes the SQL statements and closes the connection at last. There is no need to open and close the connection. It has two more important methods Fill() and Update(). The Fill() method is used to fill the DataSet or DataTable and Update() is used for saving the changes to the Database in same order as the DataSet, which is made with DataSet.

Now we will work with SqlDataAdapter in our application. I have a Database student and a Database table as "student_detail" which has one record. We take a window form application in Visual Studio 2010 > Take 2 button and 1 DataGridView control and write the following code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
 
namespace adapter
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlDataAdapter adapter;
        DataSet ds;
        DataTable dt;
        string connstring = "database=student;server=.;user=sa;password=wintellect";
        private void Form1_Load(object sender, EventArgs e)
        {
          
        }
        private void btndataset_Click(object sender, EventArgs e)
        {
            // Using DataSet
 
            adapter = new SqlDataAdapter("select * from student_detail", connstring);
            ds = new DataSet();
            adapter.Fill(ds);
            dGVshowrecord.DataSource = ds.Tables[0];
 
            //********
            // Same operation can be performed by below code
            //********
 
            //adapter = new SqlDataAdapter();
            //SqlConnection con = new SqlConnection(connstring);
            //adapter.SelectCommand = new SqlCommand("select * from student_detail", con);
            //ds = new DataSet();
            //adapter.Fill(ds);
            //dGVshowrecord.DataSource = ds.Tables[0];
        }
        private void btndatatable_Click(object sender, EventArgs e)
        {
            // Using DataTable
 
            adapter = new SqlDataAdapter("select * from student_detail", connstring);
            dt = new DataTable();
            adapter.Fill(dt);
            dGVshowrecord.DataSource = dt;
 
            //********
            // Same operation can be performed by below code
            //********
 
             //adapter = new SqlDataAdapter();
             //SqlConnection con = new SqlConnection(connstring);
             // adapter.SelectCommand = new SqlCommand("select * from student_detail", con);
             //dt = new DataTable();
             //adapter.Fill(dt);
             //dGVshowrecord.DataSource = dt;
        }
    }
}
 
Run the application.

Output



Click on both buttons to show records from "student_detail" table.



Now we insert, delete and update records using the SqlDataAdapter class. We will use the update() method of SqlDataAdapter and also we have to use the SqlCommandBuilder class for performing such an operation. Add 2 buttons in your application and set their text property to "Clear" and "Update". Replace the preceding code with the following code. 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
 
namespace adapter
{
    public partial class Form1 : Form
    {
       public Form1()
        {
           InitializeComponent();
        }
        SqlDataAdapter adapter;
        DataSet ds;
        DataTable dt;
        string connstring = "database=student;server=.;user=sa;password=wintellect";
        private void Form1_Load(object sender, EventArgs e)
        {        

        }
        private void btndataset_Click(object sender, EventArgs e)
        {
            // Using DataSet
            adapter = new SqlDataAdapter("select * from student_detail", connstring);
            ds = new DataSet();
            adapter.Fill(ds);// fill the DataSet
            dGVshowrecord.DataSource = ds.Tables[0];// Binding DataGridView with DataSet
         }
        private void btnupdate_Click(object sender, EventArgs e)
        {
            SqlCommandBuilder cmb = new SqlCommandBuilder(adapter);// Creating instance of SqlCommandBuilder
            adapter.Update(ds);// updating changes
        }
        private void btnclear_Click(object sender, EventArgs e)
        {
            ds.Clear();// Clear the DataSet
        }
    }
}

Run the application

Output



Click the "Using Dataset" button. It will show record from Database.



Make some changes in records of DataGridView. I have made some changes by adding some new row and updating course of first row. Click the "Update" button. It will save the current record of DataSet into Database.



Now click the Clear button to clear the DataSet.



Now click the "Using Dataset" button. It will show the record from the Database.

Here are some related resources

• Working with the SqlTransaction Class in ADO.NET

• Working with DataTable and its Methods in ADO.NET

• Update Database using DataSet

• Dynamic Database Creation - 2

• Data Transfer from SQL Server to Excel