Data Adapter Vs Sql Command

Which one would be better in executing an insert statement for ms-sql database:

Sql DataAdapter or SQL Command
Object?

Which of them would be better, while inserting only one row and while inserting multiple rows?

A simple example of code usage:

SQL Command

string query = "insert into Table1(col1,col2,col3) values (@value1,@value2,@value3)";
int i;

SqlCommand cmd = new SqlCommand(query, connection);
// add parameters...
cmd.Parameters.Add("@value1",SqlDbType.VarChar).Value=txtBox1.Text;
cmd.Parameters.Add("@value2",SqlDbType.VarChar).Value=txtBox2.Text;
cmd.Parameters.Add("@value3",SqlDbType.VarChar).Value=txtBox3.Text;
cmd.con.open();
i = cmd.ExecuteNonQuery();
cmd.con.close();

SQL Data Adapter
DataRow dr = dsTab.Tables["Table1"].NewRow();
DataSet dsTab = new DataSet("Table1");
SqlDataAdapter adp = new SqlDataAdapter("Select * from Table1", connection);
adp.Fill(dsTab, "Table1");
dr["col1"] = txtBox1.Text;
dr["col2"] = txtBox5.Text;    
dr["col3"] = "text";

dsTab.Tables["Table1"].Rows.Add(dr);

SqlCommandBuilder projectBuilder = new SqlCommandBuilder(adp);
DataSet newSet = dsTab.GetChanges(DataRowState.Added);
adp.Update(newSet, "Table1");

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Updating a data source is much easier using DataAdapters. It’s easier to make changes since you just have to modify the DataSet and call Update.

There is probably no (or very little) difference in the performance between using DataAdapters vs Commands. DataAdapters internally use Connection and Command objects and execute the Commands to perform the actions (such as Fill and Update) that you tell them to do, so it’s pretty much the same as using only Command objects.

Method 2

I would use LinqToSql with a DataSet for single insert and most Database CRUD requests. It is type safe, relatively fast for non compilcated queries such as the one above.

If you have many rows to insert (1000+) and you are using SQL Server 2008 I would use SqlBulkCopy. You can use your DataSet and input into a stored procedure and merge into your destination

For complicated queries I recommend using dapper in conjunction with stored procedures.

Method 3

I suggest you would have some kind of control on your communication with the database. That means abstracting some code, and for that the CommandBuilder automatically generates CUD statements for you.

What would be even better is if you use that technique together with a typed Dataset. then you have intellisense and compile time check on all your columns


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x