Vs Sql Command Data Adapter

Question

Which one would be better to execute the insert statement for the ms-sql database:

Sql DataAdapter or SQL Command Object?

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

A simple code usage example:

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"); 

Answer 1

Updating a data source is much easier with DataAdapters. It’s easier to make changes since you just need to change the DataSet and call Update.

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

Answer 2

I would use LinqToSql with a DataSet for single insertion and most CRUD database queries. It is type safe, relatively fast for uncompiled queries like the ones 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 enter it into the stored procedure and merge it into the destination

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

Answer 3

I suggest you have some kind of control over your message to the database. This means abstracting some code, and for this, CommandBuilder automatically generates CUD instructions for you.

What's even better if you use this technique with a typed dataset. then you have intellisense and compile time check in all columns