Click here to Skip to main content
11,634,568 members (66,912 online)
Click here to Skip to main content

SQLDataAdapter without using SQLCommandBuilder

, 28 Jan 2004 173K 48
Rate this:
Please Sign up or sign in to vote.
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.

//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

You may also be interested in...

Comments and Discussions

 
QuestionUpdate database Pin
saeedazam7866-Jan-13 21:56
membersaeedazam7866-Jan-13 21:56 
Hi
Your article is very good.i am new to database.i want to ask a question that in update your are changing the values but how can we Subtract the values in update.for example a stock column contain 20,we give input as 5,then how can we subtract 5 from stock column and then stock column updated to 15.
Thanks in advance
GeneralMy vote of 4 Pin
Member 96405622-Dec-12 18:03
memberMember 96405622-Dec-12 18:03 
GeneralMy vote of 1 Pin
Saed Laghaee6-Apr-12 22:40
memberSaed Laghaee6-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
HasanVaez1-Feb-08 4:30
memberHasanVaez1-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
HasanVaez1-Feb-08 4:37
memberHasanVaez1-Feb-08 4:37 
QuestionWhat am I missing ? Pin
BarneaGal28-Jan-04 21:04
sussBarneaGal28-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    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150728.1 | Last Updated 29 Jan 2004
Article Copyright 2004 by vinoth1979
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid