Click here to Skip to main content
15,395,065 members
Articles / Programming Languages / C#
Article
Posted 28 Jan 2004

Stats

201K views
49 bookmarked

SQLDataAdapter without using SQLCommandBuilder

Rate me:
Please Sign up or sign in to vote.
2.61/5 (35 votes)
28 Jan 2004
This article speaks about how to use SQLDataAdapter and its update method 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.

C#
//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");

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

vinoth1979
Web Developer
India India
No Biography provided

Comments and Discussions

 
QuestionUpdate database Pin
saeedazam7866-Jan-13 21:56
Membersaeedazam7866-Jan-13 21:56 
GeneralMy vote of 4 Pin
Member 96405622-Dec-12 18:03
MemberMember 96405622-Dec-12 18:03 
GeneralMy vote of 1 Pin
Saed Leghaee6-Apr-12 22:40
MemberSaed Leghaee6-Apr-12 22:40 
GeneralMy vote of 5 Pin
MohammadAli SJ1-Oct-10 4:38
MemberMohammadAli SJ1-Oct-10 4:38 
Generala problem with SqlDataAdapter and Update Pin
MohammadAli SJ30-Sep-10 21:51
MemberMohammadAli SJ30-Sep-10 21:51 
GeneralMy vote of 1 Pin
Member 16396733-Sep-09 5:10
MemberMember 16396733-Sep-09 5:10 
GeneralUpdate Failed............... Pin
rparbat18-Mar-09 1:04
Memberrparbat18-Mar-09 1:04 
QuestionSQl command builder Pin
Member 387282010-Oct-08 23:36
MemberMember 387282010-Oct-08 23:36 
GeneralOleDbCommandBuilder (Please help me) Pin
Hasan Vaez1-Feb-08 4:30
MemberHasan Vaez1-Feb-08 4:30 
AnswerRe: OleDbCommandBuilder (Please help me) Pin
Member 387282010-Oct-08 23:34
MemberMember 387282010-Oct-08 23:34 
GeneralTo those who say : boring & nothing new Pin
platus200015-Jan-06 0:56
Memberplatus200015-Jan-06 0:56 
GeneralRe: To those who say : boring & nothing new Pin
harryjo9-Feb-06 2:25
Memberharryjo9-Feb-06 2:25 
GeneralCan't Update The DataBase Pin
boaz25823-Jun-05 2:09
Memberboaz25823-Jun-05 2:09 
GeneralRe: Can't Update The DataBase Pin
Hasan Vaez1-Feb-08 4:37
MemberHasan Vaez1-Feb-08 4:37 
QuestionWhat am I missing ? Pin
BarneaGal28-Jan-04 21:04
MemberBarneaGal28-Jan-04 21:04 
AnswerRe: What am I missing ? Pin
vinoth197928-Jan-04 21:27
Membervinoth197928-Jan-04 21:27 
GeneralRe: What am I missing ? Pin
Member 35932238-Jun-08 20:23
MemberMember 35932238-Jun-08 20:23 
Answermsdn article on this topic Pin
vinoth197928-Jan-04 23:23
Membervinoth197928-Jan-04 23:23 
GeneralBoring! Pin
dog_spawn29-Jan-04 8:42
Memberdog_spawn29-Jan-04 8:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.