What is a SqlCommandBuilder in ADO.NET

SqlCommandBuilder class in ADO.NET provides the feature of reflecting the changes made to a DataSet or an instance of the SQL server data. When an instance of the SqlCommandBuilder class is created, it automatically generates Transact-SQL statements for the single table updates that occur. The object of the SqlCommandBuilder acts as a listener for RowUpdating events, whenever the DataAdapter property is set.

The SqlCommandBuilder object automatically generates the values contained within the SqlDataAdapter's InsertCommand, UpdateCommand and DeleteCommand properties based on the initial SelectCommand. The advantage here is that you will not need to write SqlCommand & SqlParameter Types explicitly.

Basically the command builder object builds these objects on the fly. The command builder object actually reads the metadata of the method called. After the builder object reads the underlying schema of the adapter's method, it autogenerates an underlying insert, update & delete command object. See code example below...
//Code below in C#...
DataSet ds = new DataSet();
SqlConnection cn = new SqlConnection("strSomeConnectionString");
//Autogenerate Insert, Update & Delete commands
SqlDataAdapter da = new SqlDataAdapter("Select from t_Something", cn);
SqlCommandBuilder scb = new SqlCommand(da);


.......................................................................................................................................................
CommandBuilder generates insert/update/delete commands for Data adapter based on select command. Automatic creation of insert/update/delete commands hinders performance. In case one knows the contents of insert/update/delete, should create those explicitly. Better to create explicit stored procedures for insert/update/delete and assign those.

The CommandBuilder uses SelectComand property of DataAdapter to determine values for other commands. If there is change in SelectCommand of DataAdapter, remember to call ReferhScheme to update the command properties.

CommandBuilder only generates a command for data adapter’s Command property if command property is null. By default command properties are null for data adapter. If you explicitly set a command property, the CommandBuilder does not overwrite it. You need to set the command property to null to allow CommandBuilder to generate a command for Command property.



// Create a new Connection and SqlDataAdapter

SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Customers", myConnection);
DataSet myDataSet = new DataSet();
DataRow myDataRow;

// Create command builder. This line automatically generates the update commands for you, so you don't
// have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

// Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

mySqlDataAdapter.Fill(myDataSet, "Customers");

myDataRow = myDataSet.Tables["Customers"].NewRow();
myDataRow["CustomerId"] = "NewID";
myDataRow["ContactName"] = "New Name";
myDataRow["CompanyName"] = "New Company Name";

myDataSet.Tables["Customers"].Rows.Add(myDataRow);

myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach";

DataRow myDataRow1 = myDataSet.Tables["Customers"].Rows.Find("ALFKI");
myDataRow1["ContactName"]="Peach";

myDataSet.Tables["Customers"].Rows[0].Delete();

/* Before submitting the update back to the database, you need to setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database. For limited scenarios you can use the SqlCommandBuilder to automatically generate those for you, as is shown in the following example: */

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

mySqlDataAdapter.Update(myDataSet, "Customers");



41 comments:

Bay Max said...

Thanks for sharing this wonderful information. Keep blogging article like this. I have bookmarked this page for future reference as well.
Regards,
DOT NET Training in chennai | DOT NET Course in chennai

apttree said...

very useful information
Bank exam questions and answers

SBI Po exam questions and answers
Vao exam questions and answers

Unknown said...

lenovo laptop service center chennai
lenovo thinkpad service center chennai
lenovo ideapad service center chennai
lenovo laptop service center in vadapalani

IT Tutorials said...

Basically the command builder object builds these objects on the fly. The command builder object actually reads the metadata of the method called. Perfect explanation. Dot Net Training in chennai | Ddot net training center in chennai

IT Tutorials said...

Basically the command builder object builds these objects on the fly. The command builder object actually reads the metadata of the method called. Perfect explanation. Dot Net Training in chennai | Ddot net training center in chennai

Unknown said...

This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharng this information,this is useful to me...
Android Training in Chennai
Ios Training in Chennai

vignesjoseph said...

I am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article.very interesting to read..please sharing like this information.
Hadoop Training in Chennai
Hadoop Training Institute in Chennai

Suresh said...
This comment has been removed by the author.
kevingeorge said...

I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
Best Java Training Institute Chennai

Java Training Institute Bangalore

shalinipriya said...

The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.

Devops training in Chennai

Devops training in Bangalore

Devops training in Pune

Devops training in Online

Devops training in Pune

Devops training in Bangalore

Devops training in tambaram

Devops training in Sollonganallur

DeepikaOrange said...

I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.

Cassandra Database Training in Chennai
Cassandra Training in Chennai

Mounika said...

I think you have a long story to share and i am glad after long time finally you cam and shared your experience.
python online training
python training in OMR
python training course in chennai

sunshineprofe said...

I’m planning to start my blog soon, but I’m a little lost on everything. Would you suggest starting with a free platform like Word Press or go for a paid option?
fire and safety course in chennai

Unknown said...

Really very nice blog information for this one and more technical skills are improve,i like that kind of post.

angularjs Training in electronic-city

angularjs online Training

angularjs Training in marathahalli

angularjs interview questions and answers

angularjs Training in bangalore

angularjs Training in bangalore

Unknown said...

Excellant post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
Data Science training in Chennai | Data science training in bangalore

Data science training in pune | Data science online training

Data Science Interview questions and answers


Unknown said...

A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.
Java training in Chennai | Java training in Annanagar | Java training in Chennai

Java training in Chennai | Java training in Bangalore | Java training in Electronic city

Unknown said...

I’ve desired to post about something similar to this on one of my blogs and this has given me an idea. Cool Mat.
Data Science Training in Indira nagar | Data Science Training in btm layout

Python Training in Kalyan nagar | Data Science training in Indira nagar

Data Science Training in Marathahalli | Data Science training in Bangalore | Data Science Training in BTM Layout | Data Science training in Bangalore

sai said...

This is very good content you share on this blog. it's very informative and provide me future related information.
python course institute in bangalore | python Course institute in bangalore| python course institute in bangalore

Anonymous said...

Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
excel advanced excel training in bangalore | Devops Training in Chennai

Mounika said...

Really great post, Thank you for sharing This knowledge.Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!
Python training in bangalore | Python course in pune | Python training in bangalore

Unknown said...

I think you have a long story to share and i am glad after long time finally you cam and shared your experience.
Java training in Chennai | Java training in Velachery

Java training in Chennai | Java training in Omr

Oracle training in Chennai

Java training in Chennai | Java training in Annanagar

Unknown said...

I am definitely enjoying your website. You definitely have some great insight and great stories. 
Data Science training in Chennai | Data Science Training Institute in Chennai
Data science training in Bangalore | Data Science Training institute in Bangalore
Data science training in pune | Data Science training institute in Pune
Data science online training | online Data Science certification Training-Gangboard
Data Science Interview questions and answers

genga g said...

You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us

aws Training in indira nagar

selenium Training in indira nagar

python Training in indira nagar

datascience Training in indira nagar

devops Training in indira nagar

priya rajesh said...

One of the great article, I have seen yet. Waiting for more updates.
UiPath Training in Chennai
UiPath Courses in Chennai
Robotics Process Automation Training in Chennai
AWS Training in Chennai
Angularjs Training in Chennai
Blue Prism Training in Chennai

Ram Ramky said...

Inspiring article, all your points are worth to learn. Cheers and thanks for the clear path.


Selenium Training in Chennai
Selenium Course in Chennai
iOS Course in Chennai
Digital Marketing Training in Chennai
J2EE Training in Chennai
DOT NET Course in Chennai
DOT NET Training Institute in Chennai
Big Data Training in Chennai

Sadhana Rathore said...

Thanks for sharing this information admin, it helps me to learn new things. Continue sharing more like this.
ccna course in Chennai
ccna Training in Chennai
AWS course in Chennai
Robotics Process Automation Training in Chennai
DevOps course in Chennai
Angularjs Training in Chennai

rohini said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
iphone service center | ipad service center | imac service center

Infocampus said...

Some very valid points! I appreciate you penning this article and also the rest of the site is really good.
advanced java training in Bangalore Marathahalli
Best Advanced Java training In Bangalore Marathahalli Marathahalli
Advanced Java Courses In Bangalore Marathahalli
Advanced Java institute In Bangalore Marathahalli
Advanced Java Training Center In Bangalore
Advanced Java Classes Bangalore

Praylin S said...

This is really informative! Glad that I found your post.
Placement Training in Chennai
Placement Training institutes in Chennai
Best Placement Training institutes in Chennai
Training and Job Placement in Chennai
Spark Course in Chennai
C++ Programming Course

Anbarasan14 said...

I believe that your blog gave valuable information to the readers. kindly keep sharing this kind of useful information.

English Speaking Classes in Mulund
English Speaking Classes in Mulund West
English Speaking Course in Mulund
English Speaking Course in Mulund East
English Speaking Course in Mulund West
Best English Speaking Classes in Mulund West
English Speaking Classes Mulund

jefrin said...

Best article from author
CCNA training class in chennai

Anjali Siva said...

Great collection and thanks for sharing this info with us. Waiting for more like this.
Data Science Course in Chennai
Data Science Training in Chennai
Data Analytics Courses in Chennai
Big Data Analytics Courses in Chennai

user123 said...

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
Data Science Training in Chennai
Robotic Process Automation Training in Chennai
Cloud Computing Training in Chennai
Data Warehousing Training in Chennai
Dev Ops Training in Chennai

Vicky Ram said...

A good blog for the people who really needs information about this. Good work keep it up.

Article submission sites
Technology

ramya said...

Great post very useful info thanks for this post ....
RPA training in chennai

Riyas Fathin said...

Your very own commitment to getting the message throughout came to be rather powerful and have consistently enabled employees just like me to arrive at their desired goals.

Angularjs Training in Chennai
Java Training in Chennai
Bigdata Hadoop Training in Chennai
SAS Training in Chennai

saran said...

"Thank you for your information.
.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

"

lavanya said...

Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog.
Java training in Chennai

Java Online training in Chennai

Java Course in Chennai

Best JAVA Training Institutes in Chennai

Java training in Bangalore

Java training in Hyderabad

Java Training in Coimbatore

Java Training

Java Online Training

shiva said...

Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
Cyber Security Training Course in Chennai | Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course |
CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course

INFYCLE TECHNOLOGIES said...

Big Data training in Chennai is nowadays not easy for many students. They don’t know how to start this?? Here, is the secured and Certified placewhich is INFYCLE who were all offering good courses with training and with top salary package placement. HURRY UP!!!

iteducationcentre said...

Very impressive blog. I really appreciate your efforts.
SQL Classes in Pune

Post a Comment