why we going for sqlcommandbuilder?

Question

Option 1: to insert data in a db

SqlCommand cmd = new SqlCommand("INSERT INTO table_name(eid,eName,Dept) values('"+ textBox1.Text +"','"+ textBox2.Text +"','"+ Textbox3.Text +"'", con);

cmd.ExecuteNonQuery();

Option 2:

SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * FROM table_name",con);

SqlCommandBuilder sqlcomb = new SqlCommandBuilder(sqlda);

DataSet dset = new DataSet("table1");

sqlda.Fill(dset,"table1");

DataRow drow = dset.Tables["table1"].NewRow();
drow["eid"] = textBox1.Text.ToString();
drow["eName"] = textBox2.Text.ToString();
drow["Dept"] = textBox3.Text.ToString();

dset.Tables["table1"].Rows.Add(drow);

sqlda.Update(dset, "table1");

My question is, I feel like option1 is best method to insert.. why we using SqlCommandBuilder to insert data? What is the use of SqlCommandBuilder? Any advantages in inserting the data using SqlCommandBuilder? your suggestion plz

Answer 1

I would always go for option 3: parametrized queries !!

DO NOT CONCATENATE TOGETHER YOUR SQL STATEMENTS - N E V E R

If you do - see what will happen eventually.....

They are both safe against SQL injection attacks, and more efficient (since a single query plan can be reused if you call this with several parameter values):

string insertStmt = "INSERT INTO dbo.table_name(eid, eName, Dept) " + 
                    "VALUES(@eid, @eName, @dept)";

SqlCommand cmd = new SqlCommand(insertStmt, con);

cmd.Parameters.Add("@eid", SqlDbType.Int).Value = .......;
cmd.Parameters.Add("@ename", SqlDbType.VarChar, 50).Value = .......;
cmd.Parameters.Add("@dept", SqlDbType.VarChar, 100).Value = .......;

cmd.ExecuteNonQuery();

Or then option 4 : use an ORM (object-relational mapper) like Dapper-Dot-Net or Entity Framework to get your relational data into nice objects for use in .NET (and finally forget about messing around with DataRow and DataColumn and such stuff) ??