The SqlHelper Class
The SqlHelper class simplifies the task of writing data access by wrapping up the data access logic for you. You just have to call a single method with a few parameters, and voila! you have an instant DataSet, DataReader etc. This class is very well thought out as it contains methods for returning DataSets, DataReaders, XmlReaders, scalar values, and the number of rows affected for non-query commands. It also has specialized methods for performing database updates from a DataSet, and a method for populating a pre-defined DataSet such as a typed DataSet. The methods fall into two major categories: those that accept parameters from a DataRow object, and those that don't. The two exceptions to the categories are the CreateCommand
method and UpdateDataset
method. In addition, the FillDataSet
method that populates the DataSet that is passed in does not have a DataRow parameter implementation. You can easily tell which methods use the DataRow object for passing in parameters by the "TypedParams" at the end of the method name. Table 2 has a list of SqlHelper class methods.
Table 2: SqlHelper class method definitions.
Executes the SqlDataAdapter's Fill method to return an untyped DataSet.
Executes the SqlCommand ExecuteReader method to return a SqlDataReader.
Executes the SqlCommand ExecuteScalar method to return a single object.
Executes the SqlCommand ExecuteXmlReader method to return an XmlReader.
Executes the SqlDataAdapter's Fill method to fill the DataSet specified in the parameter list of the method.
Executes the SqlDataAdapter's Update method to update the table specified in the parameter list.
Returns a SqlCommand Object to be used for the UpdateDataSet method.
All methods except for UpdateDataset
require some type of connection or transaction to be specified by passing in a SqlConnection object, SqlTransaction object, or connection string. Note that the CreateCommand
method supports only a SqlConnection object. Also, any method returning an XmlReader object cannot support a connection string parameter because the .NET implementation of the ExecuteXmlReader
method of the SqlCommand class does not support a CommandBehavior parameter. All methods also require a command string (again except for the UpdateDataset
method). In addition, depending on the overload or method you use, you may also be required to specify a CommandType parameter of StoredProcedure
. (You cannot use a TableDirect CommandType because that's supported only by the .NET Framework Provider for OLE DB).
All methods except for CreateCommand
, and DataRow
("TypedParams") support overloads for running a stored procedure or in-line text without specifying parameters. However, if you want to specify parameters for a SqlCommand you have three options:
- You can specify an array of SqlParameter objects
- You can specify an array of objects
- If you're using one the TypedParams methods you can specify a DataRow object.
One important thing to be aware of when you use the DataRow parameter method or the Object array parameter overload is that they both utilize the SqlHelperParameterCache class to retrieve and cache the SqlParameter array for future use. Your first use of these methods will take a slight performance hit while the SqlHelperParameterCache retrieves the SqlParameter from the stored procedure. Subsequent calls then simply pull the SqlParameter array from the cache. You should note a few other important things about using the Object array parameter overloads.
- Only use the Object array to pass in parameters if you don't plan to use any output parameters.
- The Object array parameter overload only works with stored procedures.
- You must put the Object array parameters in the same order as they are defined in the stored procedure.
Also, one important thing to note about using the DataRow parameter methods is that the column name in the DataRow must match the corresponding SQL parameter name without the "@". This means that if you had a DataRow column named "AccountId," to pass it as a parameter to your SQL command you must have a input parameter defined with the name "@AccountId" and its data type must be the same as the DataRow column.
When you specify the SqlParameter arrays or object arrays as parameters you do not have to pass them to the SqlHelper
method as an array. Instead you can specify a variable list of arguments that will convert to an array method parameter. Look at the example below.
public DataSet GetMyData(string myConnection,
string myCommand, string myParam1,
myCommand, myParam1, mParam2);
The example shows a command string, a connection string, and two parameters of different types being passed into the ExecuteDataSet
method of the SqlHelper class. I could have specified any number of parameters, all of which could be of different data types, because this particular overload accepts the array of parameters as object types. This comes in very handy for writing quick and dirty data access code.