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.
con = new SqlConnection(
"Data Source=mysource;Initial Catalog=mydbname;uid=sa");
SqlDataAdapter sqlDa = new SqlDataAdapter("select * from emp", con);
DataSet dSet = new DataSet();
sqlDa.Fill(dSet, "emp");
dataGrid1.DataSource=dSet.Tables["emp"];
SqlCommand selCmd = new SqlCommand("select * from emp",con);
sqlDa.SelectCommand=selCmd;
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;
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;
SqlCommand delCmd = new SqlCommand(
"delete from emp where No=@No",con);
delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.DeleteCommand = delCmd;
sqlDa.Update(dSet,"emp");
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.