SQLDataAdapter without using SQLCommandBuilder

Introduction

This article talks about how to use SQLDataAdapter and its update method without using SQLCommandBuilder.

The code

The following code creates a database connection and then the data adapter for the connection; and then filling the data set using the adapter and binding the data grid with the data set. Now creating four command objects namely SelectCommand, InsertCommand, UpdateCommand, DeleteCommand for the data adapter.

There comes the important thing, closely look at the parameter added with the every command object. The add method of SQLParameterCollection accepts 4 parameter values. They are,

Parameter name, db type, size, and column name. so for every change in row state of data set, the update command is going to use the row to build the corresponding query and then updates the data source.

//Connecting database

con = new SqlConnection(
  "Data Source=mysource;Initial Catalog=mydbname;uid=sa");
//create sql adapter for the "emp" table

SqlDataAdapter sqlDa = new SqlDataAdapter("select * from emp", con);
//create dataset instance

DataSet    dSet = new DataSet();
//fill the dataset

sqlDa.Fill(dSet, "emp");
//bind the data grid with the data set

dataGrid1.DataSource=dSet.Tables["emp"];

//build select command

SqlCommand selCmd = new SqlCommand("select * from emp",con);
sqlDa.SelectCommand=selCmd;

//build insert command

SqlCommand insCmd = new SqlCommand(
  "insert into emp (Name, Age) values(@Name, @Age)",con);
insCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
insCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
sqlDa.InsertCommand = insCmd;

//build update command

SqlCommand upCmd = new SqlCommand(
  "update emp set Name=@Name, Age=@Age where No=@No",con);
upCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
upCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
upCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.UpdateCommand = upCmd;

//build delete command

SqlCommand delCmd = new SqlCommand(
  "delete from emp where No=@No",con);
delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.DeleteCommand = delCmd;

//now update the data adapter with dataset.

sqlDa.Update(dSet,"emp");