Click here to Skip to main content
Click here to Skip to main content

SQLDataAdapter without using SQLCommandBuilder

By , 28 Jan 2004
Rate this:
Please Sign up or sign in to vote.

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

About the Author

vinoth1979
Web Developer
India India
No Biography provided

Comments and Discussions

 
QuestionUpdate database Pinmembersaeedazam7866-Jan-13 21:56 
GeneralMy vote of 4 PinmemberMember 96405622-Dec-12 18:03 
GeneralMy vote of 1 PinmemberSaed Laghaee6-Apr-12 22:40 
GeneralMy vote of 5 PinmemberMohammadAli SJ1-Oct-10 4:38 
Generala problem with SqlDataAdapter and Update PinmemberMohammadAli SJ30-Sep-10 21:51 
GeneralMy vote of 1 PinmemberMember 16396733-Sep-09 5:10 
GeneralUpdate Failed............... Pinmemberrparbat18-Mar-09 1:04 
QuestionSQl command builder PinmemberMember 387282010-Oct-08 23:36 
GeneralOleDbCommandBuilder (Please help me) PinmemberHasanVaez1-Feb-08 4:30 
AnswerRe: OleDbCommandBuilder (Please help me) PinmemberMember 387282010-Oct-08 23:34 
GeneralTo those who say : boring & nothing new Pinmemberplatus200015-Jan-06 0:56 
GeneralRe: To those who say : boring & nothing new Pinmemberharryjo9-Feb-06 2:25 
GeneralCan't Update The DataBase Pinmemberboaz25823-Jun-05 2:09 
GeneralRe: Can't Update The DataBase PinmemberHasanVaez1-Feb-08 4:37 
QuestionWhat am I missing ? PinsussBarneaGal28-Jan-04 21:04 
AnswerRe: What am I missing ? Pinmembervinoth197928-Jan-04 21:27 
GeneralRe: What am I missing ? PinmemberMember 35932238-Jun-08 20:23 
Answermsdn article on this topic Pinmembervinoth197928-Jan-04 23:23 
GeneralBoring! Pinmemberdog_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 | Mobile
Web04 | 2.8.140415.2 | Last Updated 29 Jan 2004
Article Copyright 2004 by vinoth1979
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid