Use Parameterized Query/ Stored Procedures:
To make query condition based, we need to define parameters, whose value can be changed at run time in programming world. It is recommended to use SqlParamter object to pass the parameters to query or stored procedure.
e.g.
“Select * from Customers where City = ‘” + txtCity.Text + “’”
Don’t ever build query based on the above example rather use parameters to pass to City value shown below.
Steps to create parameter
- Create command object with parameterized query (stored procedure name which takes parameters).
- Create parameter object
- Attach parameter object to parameters collection of the command object.
- Assign the value of the parameter.
e.g. Use of above query using the parameters
try
{
SqlConnection conn = new SqlConnection("connection string");
conn.Open();
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter(“@City”, SqlDbType.VarChar);
// 3. add new parameter to command object
cmd.Parameters.Add(param);
//4. assign the value
param.Value = inputCity;
// get data stream
reader = cmd.ExecuteReader();
// write each record
while(reader.Read())
{
Console.WriteLine("{0}, {1}",
reader["CompanyName"],
reader["ContactName"]);
}
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}
// close connection
if (conn != null)
{
conn.Close();
}
}
How to Call Stored procedure passing parameters:
SqlCommand cmd = new SqlCommand("sp name", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50);
cmd.Parameters["@param1"].Value = value of parameter to pass to stored procedure;
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
Advantages:
1. Type safe. Each parameter has the Sql type, so user cannot set values of different type to the parameter.
2. User cannot attach other sub query to parameters, as it was the risk in simple string concatenations.
3. No need to give attention to null values. Just set the parameter value null.
4. No need to format the string such as putting the single quote around the value of the string.
Summary:
It is secure to use parameters. It only contains 4 steps to use parameter.