Practices for Executing Queries

Executing  Single – Value Queries

To execute a query that returns a single value you need to create a Command Object that specifies the query text, connection, and command type, invoke the ExecuteScalar method on the command object, use the single value returned by the ExecuteScalar method.

string TC = " select avg(ListPrice) from TutorialsCode";

SqlCommand CMD = new SqlCommand(TC, connection);

CMD.CommandType = CommandType.Text;

decimal PriceAverage = Convert.ToDecimal(CMD.ExecuteScalar());

It`s important that you close various data provider objects such as the connection object and the data reader object. If the command returns a single (or no) value then it is easy to forget to close objects like the data reader. The SqlCommand class has methods that wrap the ExecuteReader method so that it hides the SqlDataReader object and ensures that this object is closet before the wrapper method returns.

• Creating and Executing Parameterized Commands

To create and execute a parameterized command you need to create a Command object that requires parameters and create a Parameter object for each parameter and add it to the Parameres collection on the Command object.

string query = "select Name, ListPrice from TutorialsCode"

               + "where ListPrice between @min and @max";

SqlCommand CMD = new SqlCommand(query, connection);

CMD.CommandType = CommandType.Text;

CMD.Parameters.AddWithValue("@min", 10);

CMD.Parameters.AddWithValue("@max", 100);

SqlDataReader dr = CMD.ExecuteReader();

We used parameterized commands for two reasons.

• If you  call a parameterized command several times, data sources can recognize these as being the same command and optimize its execution.
• Parameterized commands help to prevent SQL injection attacks. 

If you need any help feel free to leave a comment (or) contact me.