Building a Data Access Component
To better demonstrate what you have learned so far about ADO.NET, in this section you are going to build a data access component. This component is designed to abstract the processing of stored procedures. The component you build is targeted at SQL Server, and it is assumed that all data access to the database will be through stored procedures. The idea of only using stored procedures to access data in a database has a number of advantages, such as scalability, performance, flexibility, and security. The only disadvantage is that you have to use stored procedures, and not SQL strings. Through the process of building this component, you will see how stored procedures are implemented in ADO.NET. You will also be building on the knowledge that you have gained from the previous chapters.
This component’s main job is to abstract stored procedure calls to SQL Server, and one of the ways you do this is by passing in all of your stored procedure parameter metadata as XML (covered later in this section). The other job of the component is to demonstrate the use of some of the new objects in ADO.NET.
| Tip | The code for this project is quite extensive and you will only examine the key parts of it in this chapter. The full source is available in the code download. |
Let’s start with the beginning of the component. The first thing you do is declare your class and the private members of the class:
Option Explicit On Option Strict On Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Xml Imports System.Collections Imports System.Diagnostics ''' <summary> ''' This class wraps stored procedure calls to SQL Server. It requires that all ''' stored procedures and their parameters be defined in an XML document before ''' calling any of its methods. The XML can be passed in as an XmlDocument ''' instance or as a string of XML. The only exceptions to this rule are ''' stored procedures that do not have parameters. This class also caches ''' SqlCommand objects. Each time a stored procedure is executed, a SqlCommand ''' object is built and cached into memory so that the next time the stored ''' procedure is called the SqlCommand object can be retrieved from memory. ''' </summary> Public NotInheritable Class StoredProcedureHelper Private _connectionString As String = "" Private _spParamXml As String = "" Private _spParamXmlDoc As XmlDocument = Nothing Private _spParamXmlNode As XmlNode = Nothing Private _commandParametersHashTable As New Hashtable() Private Const ExceptionMsg As String = "There was an error in the method. " _ & "Please see the Windows Event Viewer Application log for details" You begin with your Option statements. Note that you are using the Option Strict statement. This helps prevent logic errors and data loss that can occur when you work between variables of different types. Next, you import the namespaces that you need for your component. In this case, most of your dependencies are on System.Data.SqlClient. You call your class StoredProcedureHelper to indicate that it wraps calling stored procedures to SQL Server. Next, you declare your private data members. You use the ExceptionMsg constant to indicate a generic error message for any exceptions that you throw.
Constructors
Now you get to declare your constructors for the StoredProcedureHelper class. This is where you can really take advantage of method overloading, and it gives you a way to pass data to your class upon instantiation. First, you declare a default constructor:
''' <summary> ''' Default constructor. ''' </summary> Public Sub New() End Sub The default constructor is provided in case users want to pass data to your class through public properties instead of through constructor arguments.
The next constructor you create allows for a database connection string to be passed in. By abstracting the database connection string out of this component, you give users of your component more flexibility in how they decide to store and retrieve their database connection strings. Here is the code for the constructor:
''' <summary> ''' Overloaded constructor. ''' </summary> ''' <param name="connectionString">The connection string to the ''' SQL Server database.</param> Public Sub New(ByVal connectionString As String) Me._connectionString = connectionString End Sub The only difference between this constructor and the default constructor is that you are passing in a database connection string.
In the next constructor, you pass in both a database connection string and a string of XML representing the stored procedure parameters for the stored procedures you want to call. Here is the code for the constructor:
''' <summary> ''' Overloaded constructor. ''' </summary> ''' <param name="connectionString">The connection string to the ''' SQL Server database.</param> ''' <param name="spParamXml">A valid XML string which conforms to ''' the correct schema for stored procedure(s) and their ''' associated parameter(s).</param> Public Sub New(ByVal connectionString As String, ByVal spParamXml As String) Me.New(connectionString) Me._spParamXml = spParamXml Me._spParamXmlDoc = New XmlDocument Try Me._spParamXmlDoc.LoadXml(spParamXml) Me._spParamXmlNode = Me._spParamXmlDoc.DocumentElement Catch e As XmlException LogError(e) Throw New Exception(ExceptionMsg, e) End Try End Sub This constructor sets the database connection string by calling the first overloaded constructor. This handy technique enables you to avoid writing duplicate code in your constructors. The constructor then loads the stored procedure parameter configuration into a private XmlDocument instance variable as well as a private XmlNode instance variable.
The remaining constructors enable you to pass in combinations of database connection strings as well as either a valid XmlDocument instance representing the stored procedure parameters or a valid XmlNode instance that represents the stored procedure parameters.
Properties
Now let’s look at the properties of your class. Your object contains the following properties: ConnectionString, SpParamXml, and SpParamXmlDoc. All of the properties are provided as a courtesy in case the user of your object did not want to supply them via a constructor call. The ConnectionString property provides the same functionality as the first overloaded constructor you looked at. The SpParamXml property enables the user of the object to pass in a valid XML string representing the stored procedures parameter metadata. All of the properties are read-write. The SpParamXmlDoc property enables the user to pass in an XmlDocument instance representing the stored procedures’ parameter metadata.
Here is the code for the SpParamXml property:
''' <summary> ''' A valid XML string which conforms to the correct schema for ''' stored procedure(s) and their associated parameter(s). ''' </summary> Public Property SpParamXml() As String Get Return Me._spParamXml End Get Set(ByVal Value As String) Me._spParamXml = Value ' Set the XmlDocument instance to null, since ' an XML string is being passed in. Me._spParamXmlDoc = Nothing Try Me._spParamXmlDoc.LoadXml(Me._spParamXml) Me._spParamXmlNode = Me._spParamXmlDoc.DocumentElement Catch e As XmlException LogError(e) Throw New Exception(ExceptionMsg) End Try End Set End Property Note that this property resets the XmlDocument instance to Nothing before trying to load the document. This is done in case it was already set in one of the overloaded constructors, or from a previous call to this property. It also sets the XmlNode instance to the DocumentElement property of the XmlDocument instance, thus keeping them both in sync.
Stored Procedure XML Structure
In this case, rather than have the user of this class be responsible for populating the Parameters collection of a Command object, you will abstract it out into an XML structure. The structure is very simple; it basically enables you to store the metadata for one or more stored procedures at a time. This has a huge advantage because you can change all of the parameters on a stored procedure without having to recompile the project. The following is what the XML structure for the metadata looks like:
<StoredProcedures> <StoredProcedure name> <Parameters> <Parameter name size datatype direction isNullable sourceColumn /> </Parameters> </StoredProcedure> </StoredProcedures>
Here is what some sample data for the XML structure looks like:
<?xml version="1.0"?> <StoredProcedures> <StoredProcedure name="usp_Get_Authors_By_States"> <Parameters> <Parameter name="@states" size="100" datatype="VarChar" direction="Input" isNullable="True" /> <Parameter name="@state_delimiter" size="1" datatype="Char" direction="Input" isNullable="True" /> </Parameters> </StoredProcedure> </StoredProcedures> The valid values for the direction attribute are Input, Output, ReturnValue, and InputOutput. These values map directly to the System.Data.Parameter enumeration values. The valid values for the datatype attribute are BigInt, Binary, Bit, Char, DateTime, Decimal, Float, Image, Int, Money, NChar, NText, NVarChar, Real, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp, TinyInt, UniqueIdentifier, VarBinary, VarChar, and Variant. These values map directly to the System.Data.SqlDbType enumeration values.
Methods
That completes our look at the stored procedure XML structure the class expects, as well as the public properties and public constructors for the class. Now let’s turn our attention to the public methods of your class.
ExecSpReturnDataSet
This public function executes a stored procedure and returns a DataSet object. It takes a stored procedure name (String), an optional DataSet name (String), and an optional list of parameter names and values (IDictionary). Here is the code for ExecSpReturnDataSet:
''' <summary> ''' Executes a stored procedure with or without parameters and returns a ''' populated DataSet object. ''' </summary> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <param name="dataSetName">An optional name for the DataSet instance.</param> ''' <param name="paramValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> ''' <returns>A populated DataSet object.</returns> Public Function ExecSpReturnDataSet(ByVal spName As String, _ ByVal dataSetName As String, _ ByVal paramValues As IDictionary) As DataSet Dim command As SqlCommand = Nothing Try ' Get the initialized SqlCommand instance. command = GetSqlCommand(spName) ' Set the parameter values for the SqlCommand. SetParameterValues(command, paramValues) ' Initialize the SqlDataAdapter with the SqlCommand object. Dim sqlDA As New SqlDataAdapter(command) ' Initialize the DataSet. Dim ds As New DataSet() If Not (dataSetName Is Nothing) Then If dataSetName.Length > 0 Then ds.DataSetName = dataSetName End If End If ' Fill the DataSet. sqlDA.Fill(ds) ' Return the DataSet. Return ds Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e) Finally ' Close and release resources. DisposeCommand(command) End Try End Function This function uses three main objects to accomplish its mission: the SqlCommand, SqlDataAdapter, and the DataSet objects. You first wrap everything in a Try-Catch-Finally block to ensure that you trap any exceptions that are thrown and to properly close and release the SqlCommand and SqlConnection resources. You first call a helper method, GetSqlCommand, in order to get a fully initialized SqlCommand instance, to include any SqlParameter objects the SqlCommand may have based on your object’s internal XmlDocument. Here is the code for GetSqlCommand and its overload:
''' <summary> ''' Initializes a SqlCommand object based on a stored procedure name ''' and a SqlTransaction instance. Verifies that the stored procedure ''' name is valid, and then tries to get the SqlCommand object from ''' cache. If it is not already in cache, then the SqlCommand object ''' is initialized and placed into cache. ''' </summary> ''' <param name="transaction">The transaction that the stored ''' procedure will be executed under.</param> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <returns>An initialized SqlCommand object.</returns> Public Function GetSqlCommand(ByVal transaction As SqlTransaction, _ ByVal spName As String) As SqlCommand Dim command As SqlCommand = Nothing ' Get the name of the stored procedure. If spName.Length < 1 Or spName.Length > 127 Then Throw New ArgumentOutOfRangeException("spName", _ "Stored procedure name must be from 1 - 128 characters.") End If ' See if the command object is already in memory. Dim hashKey As String = Me._connectionString & ":" & spName command = CType(_commandParametersHashTable(hashKey), SqlCommand) If command Is Nothing Then ' It was not in memory. ' Initialize the SqlCommand. command = New SqlCommand(spName, GetSqlConnection(transaction)) ' Tell the SqlCommand that you are using a stored procedure. command.CommandType = CommandType.StoredProcedure ' Build the parameters, if there are any. BuildParameters(command) ' Put the SqlCommand instance into memory. Me._commandParametersHashTable(hashKey) = command Else ' It was in memory, but you still need to set the ' connection property. command.Connection = GetSqlConnection(transaction) End If ' Return the initialized SqlCommand instance. Return command End Function
''' <summary> ''' Overload. Initializes a SqlCommand object based on a stored ''' procedure name, with no SqlTransaction instance. ''' Verifies that the stored procedure name is valid, and then tries ''' to get the SqlCommand object from cache. If it is not already in ''' cache, then the SqlCommand object is initialized and placed into cache. ''' </summary> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <returns>An initialized SqlCommand object.</returns> Public Function GetSqlCommand(ByVal spName As String) As SqlCommand ' Return the initialized SqlCommand instance. Return GetSqlCommand(Nothing, spName) End Function The difference between this method and its overload is that the first method takes in a SqlTransaction instance argument, and the overload does not require the SqlTransaction instance to be passed in. The overload simply calls the first method and passes in a value of Nothing for the SqlTransaction argument.
This method first performs a check to ensure that the stored procedure name is between 1 and 128 characters long, in accordance with SQL Server’s object naming conventions. If it is not, then you throw an exception. The next step this method performs is to try to get an already initialized SqlCommand object from your object’s private Hashtable variable, commandParametersHashTable, using your object’s database connection string and the name of the stored procedure as the key. If the SqlCommand is not found, then you go ahead and build the SqlCommand object by calling its constructor and passing in the stored procedure name and a SqlConnection instance returned from the GetSqlConnection helper method. The code then sets the SqlCommand’s CommandType property. You ensure that you pass in the CommandType.StoredProcedure enumeration value, as you are executing a stored procedure.
Once the SqlCommand object is properly initialized, you pass it to the BuildParameters method. You will take a look at this method in more detail later. After this step, the SqlCommand is fully initialized, and you then place it into your object’s internal cache (the commandParametersHashTable Hashtable variable). Finally, the SqlCommand is returned to the calling code.
Getting back to the ExecSpReturnDataSet method, now that the SqlCommand object has been properly initialized, you need to set the values of the parameters. This is done via another helper method called SetParameterValues. SetParameterValues takes two arguments: a reference to a SqlCommand object and an IDictionary interface. You are using an IDictionary interface instead of a class such as a Hashtable (which implements the IDictionary interface) in order to make your code more flexible. This is a good design practice and works quite well - for example, in the cases where the user of your class has built his or her own custom dictionary object that implements the IDictionary interface. It then loops through the SqlCommand’s Parameters collection and sets each SqlParameter’s value based on the corresponding name-value pair in the IDictionary object as long as the parameter’s direction is not Output. Following is the code for the SetParameterValues method:
''' <summary> ''' Traverses the SqlCommand's SqlParameters collection and sets the values ''' for all of the SqlParameter(s) objects whose direction is not Output and ''' whose name matches the name in the dictValues IDictionary that was ''' passed in. ''' </summary> ''' <param name="command">An initialized SqlCommand object.</param> ''' <param name="dictValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> Public Sub SetParameterValues(ByVal command As SqlCommand, _ ByVal dictValues As IDictionary) If command Is Nothing Then Throw New ArgumentNullException("command", _ "The command argument cannot be null.") End If ' Traverse the SqlCommand's SqlParameters collection. Dim parameter As SqlParameter For Each parameter In command.Parameters ' Do not set Output parameters. If parameter.Direction <> ParameterDirection.Output Then ' Set the initial value to DBNull. parameter.Value = TypeCode.DBNull ' If there is a match, then update the parameter value. If dictValues.Contains(parameter.ParameterName) Then parameter.Value = dictValues(parameter.ParameterName) Else ' There was not a match. ' If the parameter value cannot be null, throw an exception. If Not parameter.IsNullable Then Throw New ArgumentNullException(parameter.ParameterName, _ "Error getting the value for the " _ & parameter.ParameterName & " parameter.") End If End If End If Next parameter End Sub When traversing the SqlCommand’s Parameters collection, if a SqlParameter’s value cannot be found in the IDictionary instance, then a check is made to see whether the SqlParameter’s value is allowed to be null or not. If it is allowed, then the value is set to DBNull; otherwise, an exception is thrown.
After setting the values of the parameters, the next step is to pass the SqlCommand object to the SqlDataAdapter's constructor:
' Initialize the SqlDataAdapter with the SqlCommand object. Dim sqlDA As New SqlDataAdapter(command)
Then try to set the name of the DataSet using the dataSetName method argument:
' Try to set the name of the DataSet. If Not (dataSetName Is Nothing) Then If dataSetName.Length > 0 Then ds.DataSetName = dataSetName End If End If
After doing this, you call the Fill method of the SqlDataAdapter to fill your DataSet object:
' Fill the DataSet. sqlDA.Fill(ds)
You then return the DataSet object back to the caller:
' Return the DataSet. Return ds
If an exception was caught, then you log the exception data to the Windows Application Log via the LogError private method, and then throw a new exception with your generic exception message. Nest the original exception inside of the new exception via the innerException constructor parameter:
Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e)
In the Finally block, you close and release the SqlCommand object’s resources via the DisposeCommand helper method:
Finally ' Close and release resources DisposeCommand(command)
The DisposeCommand helper function closes the SqlCommand's SqlConnection property and disposes of the SqlCommand object:
''' <summary> ''' Disposes a SqlCommand and its underlying SqlConnection. ''' </summary> ''' <param name="command"></param> Private Sub DisposeCommand(ByVal command As SqlCommand) If Not (command Is Nothing) Then If Not (command.Connection Is Nothing) Then command.Connection.Close() command.Connection.Dispose() End If command.Dispose() End If End Sub
BuildParameters
This private method is the heart of this object and does the most work. It is responsible for parsing the stored procedure parameter XML and mapping all of the SqlParameter objects into the Parameters property of the SqlCommand object. Here is the signature of the method:
''' <summary> ''' Finds the parameter information for the stored procedure from the ''' stored procedures XML document and then uses that information to ''' build and append the parameter(s) for the SqlCommand's ''' SqlParameters collection. ''' </summary> ''' <param name="command">An initialized SqlCommand object.</param> Private Sub BuildParameters(ByVal command As SqlCommand) The first thing you do in this method is determine whether any XML is being passed in or not. Here is the code that checks for the XML:
' See if there is an XmlNode of parameter(s) for the stored procedure. If Me._spParamXmlNode Is Nothing Then ' No parameters to add, so exit. Return End If The last bit of code simply checks whether there is an XmlNode instance of parameter information. If the XmlNode has not been initialized, then you exit the method. It is entirely possible that users of this object may have stored procedures with no parameters at all. You choose an XmlNode object to parse the XML because loading all of the stored procedure XML into memory will not hurt performance; it is a small amount of data. As an alternative, you could use an XmlReader object to load into memory only what you need at runtime.
The next step is to clear the SqlCommand object’s Parameters collection:
' Clear the parameters collection for the SqlCommand command.Parameters.Clear() You then use the name of the stored procedure as the key in your XPath query of the XML, and execute the following XPath query to get the list of parameters for the stored procedure:
' Get the node list of <Parameter>'s for the stored procedure. Dim xpathQuery As String = "//StoredProcedures/StoredProcedure[@name='" _ & command.CommandText & "']/Parameters/Parameter" Dim parameterNodes As XmlNodeList = Me._spParamXmlNode.SelectNodes(xpathQuery) This query is executed off the XmlDocument object and returns an XmlNodeList object. You start the loop through the Parameter elements in the XML and retrieve all of the mandatory Parameter attributes:
Dim parameterNode As XmlElement For Each parameterNode In parameterNodes ' Get the attribute values for the <Parameter> element. ' Get the attribute values for the <Parameter> element. ' name Dim parameterName As String = parameterNode.GetAttribute("name") If parameterName.Length = 0 Then Throw New ArgumentNullException("name", "Error getting the 'name' " _ & "attribute for the <Parameter> element.") End If ' size Dim parameterSize As Integer = 0 If parameterNode.GetAttribute("size").Length = 0 Then Throw New ArgumentNullException("size", "Error getting the 'size' " _ & "attribute for the <Parameter> element.") Else parameterSize = Convert.ToInt32(parameterNode.GetAttribute("size")) End If ' datatype Dim sqlDataType As SqlDbType If parameterNode.GetAttribute("datatype").Length = 0 Then Throw New ArgumentNullException("datatype", "Error getting the " _ & "'datatype' attribute for the <Parameter> element.") Else sqlDataType = CType([Enum].Parse(GetType(SqlDbType), _ parameterNode.GetAttribute("datatype"), True), SqlDbType) End If ' direction Dim parameterDirection As ParameterDirection = parameterDirection.Input If parameterNode.GetAttribute("direction").Length > 0 Then parameterDirection = CType([Enum].Parse(GetType(ParameterDirection), _ parameterNode.GetAttribute("direction"), True), ParameterDirection) End If End If Because these attributes are mandatory, if any of them are missing, then you throw an exception. The interesting part of this code is using the Enum.Parse static method to convert the string value from the XML into the correct .NET enumeration datatype for the sqlDataType and parameterDirection variables. This is possible because the probable values in your XML for these attributes map directly to the names of their respective enumeration datatypes in .NET. Next, you get the optional attributes:
' Get the optional attribute values for the <Parameter> element. ' isNullable Dim isNullable As Boolean = False Try If parameterNode.GetAttribute("isNullable").Length > 0 Then isNullable = Boolean.Parse(parameterNode.GetAttribute("isNullable")) End If Catch End Try ' sourceColumn This must map to the name of a column in a DataSet. Dim sourceColumn As String = "" Try If parameterNode.GetAttribute("sourceColumn").Length > 0 Then sourceColumn = parameterNode.GetAttribute("sourceColumn") End If Catch End Try These attributes are optional mainly because of their datatypes. Because isNullable is Boolean, you go ahead and convert it to False if it is missing; and if sourceColumn is missing, then you just ignore it entirely.
Now you are ready to create the SqlParameter object and set its Direction property:
' Create the parameter object. Pass in the name, datatype, ' and size to the constructor. Dim sqlParameter As SqlParameter = New SqlParameter(parameterName, _ sqlDataType, parameterSize) 'Set the direction of the parameter. sqlParameter.Direction = parameterDirection You then set the optional property values of the SqlParameter object:
' If the optional attributes have values, then set them. ' IsNullable If isNullable Then sqlParameter.IsNullable = isNullable End If ' SourceColumn sqlParameter.SourceColumn = sourceColumn Finally, you add the SqlParameter object to the SqlCommand object’s Parameters collection, complete your loop, and finish the method:
' Add the parameter to the SqlCommand's parameter collection. command.Parameters.Add(sqlParameter) Next parameterNode End Sub Now it’s time to look at ExecSpReturnDataReader. This function is almost identical to ExecSpReturnDataSet except that it returns a SqlDataReader object instead of a DataSet object.
ExecSpReturnDataReader
This public function executes a stored procedure and returns a SqlDataReader object. Similar to the ExecSpReturnDataSet method, it takes a stored procedure name (String) and an optional list of parameter names and values (IDictionary). Here is the code for ExecSpReturnDataReader:
''' <summary> ''' Executes a stored procedure with or without parameters and returns a ''' SqlDataReader instance with a live connection to the database. It is ''' very important to call the Close method of the SqlDataReader as soon ''' as possible after using it. ''' </summary> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <param name="paramValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> ''' <returns>A SqlDataReader object.</returns> Public Function ExecSpReturnDataReader(ByVal spName As String, _ ByVal paramValues As IDictionary) As SqlDataReader Dim command As SqlCommand = Nothing Try ' Get the initialized SqlCommand instance. command = GetSqlCommand(spName) ' Set the parameter values for the SqlCommand. SetParameterValues(command, paramValues) ' Open the connection. command.Connection.Open() ' Execute the sp and return the SqlDataReader. Return command.ExecuteReader(CommandBehavior.CloseConnection) Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e) End Try End Function
This function uses two objects to accomplish its mission: the SqlCommand and SqlDataReader objects. The only part where this function differs from ExecSpReturnDataSet is right after you call the SetParameterValues private method. In this case, you have to make sure that the SqlCommand object’s SqlConnection is opened because the SqlDataReader requires an open connection. You then call the ExecuteReader method of the SqlCommand object to get your SqlDataReader object, passing in the CommandBehavior.CloseConnection value for the method’s behavior argument.
Because this method returns a SqlDataReader object, which requires an open database connection, you do not close the connection in this method. It is up to the caller to close the SqlDataReader and the connection when finished. Since you used the CommandBehavior.CloseConnection value for the behavior argument, the user of the method only has to remember to call the SqlDataReader’s Close method in order to close the underlying SqlConnection object.
The next function you are going to look at, ExecSpReturnXmlReader, is almost identical to the last two functions, except that it returns an XmlReader instead of a DataSet or a SqlDataReader.
ExecSpReturnXmlReader
This public function executes a stored procedure and returns an XmlReader instance. The function requires the stored procedure to contain a FOR XML clause in its SQL statement. Once again, it takes a stored procedure name (String) and an optional list of parameter names and values (IDictionary). Here is the code for ExecSpReturnXmlReader:
''' <summary> ''' Executes a stored procedure with or without parameters and returns an ''' XmlReader instance with a live connection to the database. It is ''' very important to call the Close method of the XmlReader as soon ''' as possible after using it. Only use this method when calling stored ''' procedures that return XML results (FOR XML ...). ''' </summary> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <param name="paramValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> ''' <returns>An XmlReader object.</returns> Public Function ExecSpReturnXmlReader(ByVal spName As String, _ ByVal paramValues As IDictionary) As XmlReader Dim command As SqlCommand = Nothing Try ' Get the initialized SqlCommand instance. command = GetSqlCommand(spName) ' Set the parameter values for the SqlCommand. SetParameterValues(command, paramValues) ' Open the connection. command.Connection.Open() ' Execute the sp and return the XmlReader. Return command.ExecuteXmlReader() Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e) End Try End Function
The only difference between this method and ExecSpReturnDataReader is that you call the ExecuteXmlReader method of the SqlCommand object instead of the ExecuteReader method. Like the ExecSpReturnDataReader method, users of this method need to close the returned XmlReader after using it in order to properly release resources.
| Tip | This method works only with SQL Server 2000 and later. |
Next up is the ExecSp method, which needs only the SqlCommand object to get its work done. Its job is to execute stored procedures that do not return result sets.
ExecSp
This public method executes a stored procedure and does not return a value. It takes a stored procedure name (String) and an optional list of parameter names and values (IDictionary) for its arguments. Here is the code for ExecSp:
''' <summary> ''' Executes a stored procedure with or without parameters that ''' does not return output values or a resultset. ''' </summary> ''' <param name="transaction">The transaction that the stored procedure ''' will be executed under.</param> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <param name="paramValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> Public Sub ExecSp(ByVal spName As String, ByVal paramValues As IDictionary) Dim command As SqlCommand = Nothing Try ' Get the initialized SqlCommand instance. command = GetSqlCommand(transaction, spName) ' Set the parameter values for the SqlCommand. SetParameterValues(command, paramValues) ' Run the stored procedure. RunSp(command) Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e) Finally ' Close and release resources. DisposeCommand(command) End Try End Sub
It is almost identical to the other Exec* functions, except when it executes the stored procedure. The code inside of the private RunSp method opens the SqlCommand’s SqlConnection object and then calls the SqlCommand object’s ExecuteNonQuery method. This ensures that the SqlCommand does not return any type of DataReader object to read the results. This method is used mostly to execute INSERT, UPDATE, and DELETE stored procedures that do not return any results. It also has an overload that does not include the SqlTransaction argument.
Following is the code for RunSp:
''' <summary> ''' Opens the SqlCommand object's underlying SqlConnection and calls ''' the SqlCommand's ExecuteNonQuery method. ''' </summary> ''' <param name="command">An initialized SqlCommand object.</param> Private Sub RunSp(ByRef command As SqlCommand) ' Open the connection. command.Connection.Open() ' Execute the stored procedure. command.ExecuteNonQuery() End Sub Finally, the last public function you are going to create is ExecSpOutputValues.
ExecSpOutputValues
This last public function in your component executes a stored procedure and returns an IDictionary object that contains output parameter name-value pairs. It is not meant for stored procedures that return result sets. As with the previous examples, this function takes a stored procedure name (String) and an optional list of parameter names and values (IDictionary) for its arguments. Here is the code for ExecSpOutputValues:
''' <summary> ''' Executes a stored procedure with or without parameters and returns an ''' IDictionary instance with the stored procedure's output parameter ''' name(s) and value(s). ''' </summary> ''' <param name="transaction">The transaction that the stored procedure ''' will be executed under.</param> ''' <param name="spName">The name of the stored procedure to execute.</param> ''' <param name="paramValues">A name-value pair of stored procedure parameter ''' name(s) and value(s).</param> ''' <returns>An IDictionary object.</returns> Public Function ExecSpOutputValues(ByVal transaction As SqlTransaction, _ ByVal spName As String, _ ByVal paramValues As IDictionary) As IDictionary Dim command As SqlCommand = Nothing Try ' Get the initialized SqlCommand instance. command = GetSqlCommand(transaction, spName) ' Set the parameter values for the SqlCommand. SetParameterValues(command, paramValues) ' Run the stored procedure. RunSp(command) ' Get the output values. Dim outputParams As New Hashtable() Dim param As SqlParameter For Each param In command.Parameters If param.Direction = ParameterDirection.Output _ Or param.Direction = ParameterDirection.InputOutput Then outputParams.Add(param.ParameterName, param.Value) End If Next param Return outputParams Catch e As Exception LogError(e) Throw New Exception(ExceptionMsg, e) Finally ' Close and release resources. DisposeCommand(command) End Try End Function
This function is almost identical to ExecSp except that after the SqlCommand.ExecuteNonQuery method is called you iterate through the SqlCommand object’s Parameters collection and look for all of the parameters that are output parameters. Next, you take the values of the output parameters and add the name-value pair to the IDictionary instance that you return. This method also has an overload that does not include the SqlTransaction argument.
Using DataSet Objects to Bind to DataGrids
Now that you have built your data access component, it is time to test it. A nice way to do that is to call the ExecSpReturnDataSet method, take the DataSet object that was created, and then bind the DataSet to a DataGrid. (You can find more about data binding in Chapter 14.) You also get to see how easily the DataSet and the DataGrid control integrate together. This exercise uses a Windows Application project called SqlServerWrapperTestHarness, added to the Examples solution. It contains references to System, System.Data, System.Drawing, System.Windows.Forms, and System.Xml, as well as a project reference to the SqlServerWrapper project. Added to the project is a form named TestForm.vb with two buttons, one for testing the ExecSpReturnDataSet method and one for testing the ExecSpReturnSqlRecord method. In this example, you will be looking only at the code for testing the ExecSpReturnDataSet method. Figure 10-4 shows what the test form looks like.
Figure 10-4
Figure 10-5 shows what your references should look like.
Figure 10-5
Here is the code for the declarations and private members of the form:
Option Explicit On Option Strict On Imports SqlServerWrapper Imports System.Data.SqlClient Imports System.Xml Imports System.Configuration Public Class TestForm Inherits System.Windows.Forms.Form Private _helper As StoredProcedureHelper = Nothing These declarations should look pretty familiar by now. Note that you are declaring a private variable (_helper) for the StoredProcedureHelper class that you are using so you can get to the class from other parts of the form instead of just a Button Click event handler. Next, you initialize the _helper variable in the form’s Load event handler:
Private Sub TestForm_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Set the SQL connection string Dim connectionString As String = ConfigurationSettings.AppSettings("dbConnectionString") ' Call the SqlServer wrapper constructor and ' pass the DB connection string and the stored procedures config. helper = New StoredProcedureHelper(connectionString, _ CType(ConfigurationSettings.GetConfig("StoredProcedureSettings"), _ XmlNode)) End Sub As in the earlier examples, this code begins by retrieving a connection string to the pubs database from the app.config file. You then create a new instance of the StoredProcedureHelper and assign it to the _helper class variable. During the constructor call to the StoredProcedureHelper class, you first pass in your connection string, and then you pass in an XmlNode of the stored procedure metadata for the StoredProcedureHelper class to consume. Note that you are passing the stored procedure metadata in to your class via the GetConfig method of the ConfigurationSettings class. This is because you have created a section inside of your app.config file called StoredProcedureSettings, and you have configured a SectionHandler to let the .NET Framework application configuration functionality consume your XML and give it back to you as an XmlNode. Here is what this section looks like inside of the app.config file:
<configSections> <section name="StoredProcedureSettings" type="SqlServerWrapper.StoredProcedureSectionHandler, SqlServerWrapper" /> </configSections> <StoredProcedureSettings> <StoredProcedures> <StoredProcedure name="usp_Get_Authors_By_States"> <Parameters> <Parameter name="@states" datatype="VarChar" direction="Input" isNullable="false" size="100" /> <Parameter name="@state_delimiter" datatype="Char" direction="Input" isNullable="false" size="1" /> </Parameters> </StoredProcedure> <StoredProcedure name="usp_Get_Author_By_ID"> <Parameters> <Parameter name="@au_id" datatype="VarChar" direction="Input" isNullable="false" size="11" /> </Parameters> </StoredProcedure> </StoredProcedures> </StoredProcedureSettings> This is nice because you do not need to include a separate XML file for your project; you just integrate seamlessly into the app.config file. Note how you are defining what class in what assembly will handle consuming your <StoredProcedureSettings> section in the <section> element. The requirement for this to work is that the class defined must implement the System.Configuration .IConfigurationSectionHandler interface. Here is the code for your section handler:
Option Explicit On Option Strict On Imports System Imports System.Configuration Imports System.Xml Imports System.Xml.Serialization Imports System.Xml.XPath Public Class StoredProcedureSectionHandler Implements IConfigurationSectionHandler Public Function Create(ByVal parent As Object, _ ByVal configContext As Object, _ ByVal section As System.Xml.XmlNode) As Object _ Implements IConfigurationSectionHandler.Create Return section("StoredProcedures") End Function End Class This code is pretty simple; you just return the XML node named StoredProcedures to the caller of your handler.
Back to your Button’s Click event handler, once you have the StoredProcedureHelper class instance fully initialized, you then create the parameter values for the stored procedure you want to execute and pass these arguments to the ExecSpReturnDataSet method:
' Add the two parameter name-values. Dim params As New Hashtable params.Add("@states", "CA") params.Add("@state_delimiter", "^") ' Execute the sp, and get the DataSet object back. Dim ds As DataSet = _helper.ExecSpReturnDataSet("usp_Get_Authors_By_States", _ "", params) The last step is to actually bind the data to the form’s grid:
' Bind the DataGrid to the DataSet object. dgdAuthors.SetDataBinding(ds.Tables(0), Nothing) The results should look like the dialog shown in Figure 10-6.
Figure 10-6
EAN: 2147483647
Pages: 267