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