Parameterized Queries - Asp.Net

Feb 20, 2019 BY xtreme

A parameterized query includes placeholders for the actual data to be passed in

Using Sql Command
string strQuery = "select Id,Name from Table where Name = @name";
SqlCommand cmd = new SqlCommand(strQuery, con);
cmd.Parameters.AddWithValue("@name", txt.Text.Trim());
DataTable dt = GetData(cmd);



USE Parameters Explicitly
string strQuery = "select Id,Name from Table where Name = @name";
SqlCommand cmd = new SqlCommand(sql, con);
SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserID", txtUSerID.Text);
param[1] = new SqlParameter("@pwd", txtPwd.Text);
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);
con.Open();
object result = cmd.ExecuteScalar();
con.Close();



Using Hash Table
Hashtable parameters = new Hashtable();
parameters.Clear();
parameters.Add("@ID", 12);
SqlCommand cmd = new SqlCommand("SprocName", con);
try{
   DictionaryEntry Item = default(DictionaryEntry);
   cmd.CommandType = CommandType.StoredProcedure;
   foreach (DictionaryEntry param in parameters){
      Item = param;
      cmd.Parameters.AddWithValue(Item.Key.ToString(), Item.Value);
   }
   cmd.Connection.Open();
   cmd.ExecuteNonQuery();

}

The main advantage of a parameterized query is that the query does not need to be prepared each time it is run. When a parameterized query is used, SQL Server can maintain just one execution plan in its plan cache and use it over and over again for different values supplied to this statement, just like a stored procedure. From a performance and resource utilization perspective, this approach is much more economical.

Related