Optimized Insert Process for a database in asp.net c#

There are many ways to insert data into a database. Particularly in asp.net and c#, we use SQLClient class to include database related functionalities in our programming part. He are the three ways i generally use, presented before you to check the process:

Using Data Adapter with Dataset:

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

Data Adapter using Command:

SqlDataAdapter AdapterMessage = new SqlDataAdapter();
AdapterMessage.InsertCommand = new SqlCommand();
AdapterMessage.InsertCommand.Connection = con;
AdapterMessage.InsertCommand.CommandText = "insert into Table1(col1,col2,col3) values ('" + txtBox1.Text + "','" + txtBox2.Text + "','" + User.Identity.Name.ToString(); + "')";
AdapterMessage.InsertCommand.ExecuteNonQuery();
AdapterMessage.Dispose();

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

These are the one among many methods to use while inserting a new row into the database table. The first example uses data adapter and data set, calling the update method of adapter to perform the insert. The second method method uses the sql command object of data adapter to perform the insert operation. The third operation uses   the SQL command object only, for performing the insert operation.

This should be noted that the second method is highly prone to SQL injection attacks :X The website is thus prone to hacking if the query is written as described in the second method. The simple way to avoid SQL injection attacks is to use parameters in the query as done in the third case.

Now it may seem that the third case will have the faster performance time but it should also be noted that even data adapter also uses the same executeNonQuery methods and the connection opening/closing by itself. As the underlying functions are same, there isn't much difference in performance. Now what's an eye candy is that, updating a data source is much easier using DataAdapters. It's easier to make changes since we only need to modify the dataset and call an Update.

The SQL command is still handy when we have perform a simple single insert operation. That said, it now depends upon you to choose which suits you during your development. However what if there are bulk of data to be inserted into the database? :X :P

Well relax!!! There is a special SqlBulkCopy class residing in System.Data.SqlClient.SqlBulkCopy namespace which aids us in bulk insert situation. A sample example of using the same can be:

DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(); SqlBulkCopy sbc = new SqlBulkCopy(con);
sbc.DestinationTableName = "dbo.SomeTable";
sbc.WriteToServer(dt);
sbc.Close();

But its should be kept in mind that the schema of the database table must be same as that of the table in which the insert operation is performed. Hope it helped some of you :)