CODEWITHSUNDEEP
c#sql-server
ron_87
ron_87

Reputation: 17

Why we passing values using parameters?

option 1: in this values are inserted using parameters

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

SqlCommand cmd = new SqlCommand(insertstr, con);

cmd.Parameters.Add("@eid", SqlDbType.Int).Value = Convert.ToInt32(textBox1.Text);
cmd.Parameters.Add("@ename", SqlDbType.VarChar, 50).Value = textBox2.Text.ToString();
cmd.Parameters.Add("@dept", SqlDbType.VarChar, 100).Value = textBox3.Text.ToString();

Option 2:

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

Most of the projects i had seen the 1st option... what is the use of passing values using parameters?? any advantages by passing values using parameters?? your suggestion plz..

Upvotes: 1

Views: 133

Answers (3)

Otiel
Otiel

Reputation: 18743

Several reasons:

  • You don't have to worry to escape special characters (like ') to avoid SQL errors or SQL injections (as explained by H-Man2).
  • You don't have to worry to transform C# types to SQL types. For instance:
    • If your NumberDecimalSeparator is equal to the comma , and you want to insert a Double value in your database, you normally have to replace the comma by a point. No need to worry about that with parameters.
    • No need to worry about DateTime formats neither.
    • ...
  • I find also the code more readable and easier to maintain with parameters when your SQL queries start to have a great length.

Upvotes: 1

Curtis
Curtis

Reputation: 103348

You should NEVER use option 2.

This is very bad practise, and very open to SQL Injection.

Always stick with Option 1. This is the best option by far.

Read here for more information on SQL Injection: http://en.wikipedia.org/wiki/SQL_injection

Upvotes: 0

H-Man2
H-Man2

Reputation: 3189

You don't have to care about quoting special characters. If you forget to quote a SQL-Injection attack is possible.

If you use Option 2 and one enters the following into Textbox3

'); DELETE * FROM table_name; --

the following SQL statements are excecuted:

INSERT INTO table_name(eid,eName,Dept) values ('value1','value2','');
DELETE * FROM table_name; -- ')

The second statement deletes every row from table_name. Instead of a delete statement any possible statement could be inserted there.

Upvotes: 8

Related Questions