Introduction
Let’s be honest: accessing a database in .Net can be a pain. But with a dedicated toolbox and a few basic objects, it can become much less of a headache. Why try to maintain 20 instances of this:
Dim Sql As New StringBuilder
Dim Conn As New SqlConnection
Dim Reader As SqlDataReader = Nothing
Dim Param As SqlParameter = Nothing
Sql.Append("SELECT * FROM Inventory ")
Sql.Append("WHERE Author=@Author AND Title=@Title ")
Sql.Append("ORDER BY Author")
Try
Conn.ConnectionString = ConnStr
Dim Cmd As New SqlCommand(Sql.ToString, Conn)
Cmd.CommandType = CommandType.Text
Param = New SqlParameter("@Author", SqlDbType.VarChar)
Param.Value = "Rowling, J. K."
Cmd.Parameters.Add(Param)
Param = New SqlParameter("@Title", SqlDbType.VarChar)
Param.Value = "Harry Potter and the Philosopher's Stone"
Cmd.Parameters.Add(Param)
Conn.Open()
Reader = Cmd.ExecuteReader
If Reader.HasRows Then
Do While Reader.Read
Loop
Else
End If
Reader.Close()
Catch ex As Exception
Finally
Conn.Close()
End Try
when you can instead have 20 instances of this?
Dim Sql As New StringBuilder
Dim Params As New SqlParameterList
Dim RS As SqlRecordset = Nothing
Sql.Append("SELECT * FROM Inventory ")
Sql.Append("WHERE Author=@Author AND Title=@Title ")
Sql.Append("ORDER BY Author")
Params.Add("@Author", SqlDbType.VarChar, "Rowling, J. K.")
Params.Add("@Title", SqlDbType.VarChar, "Harry Potter and the Philosopher's Stone")
RS = SqlLib.OpenRecordset(Sql.ToString, Params, ConnStr)
For Each DR As DataRow In RS.Rows
Next
Background
I wrote these methods and objects about six years ago for use on my company’s website, and they have proven to be quite robust. The SqlTransaction object has not been as heavily used, but I’ve had no problems with it so far. Your mileage, of course, may vary.
Those of you who want to port this code to C# will note the use of some VB specific methods like the CInt function to convert an object into an Integer. These methods are easily callable from any .Net language: all you need to do is reference the Microsoft.VisualBasic assembly. This may be easier than using the language neutral versions such as Convert.ToInt32.
This code was originally written with Visual Studio 2005 and was later moved to VS 2008, where it has received the most use. The basic concepts should still be useful in later versions of the .Net Framework.
General organization
The DLL generated by the source code is called DbToolbox. I use the term “toolbox” to mean an uninheritable class containing only constants and shared (static, in C#) methods.
Within the DbToolbox namespace is the DbLib toolbox, which contains a number of useful methods, mainly conversions from database values (with possible DbNull values) into value types.
There is also a SqlClient namespace, which holds the objects and toolboxes specific to accessing a SQL Server. If you want to expand the DLL to include OLE, ODBC or other specific technologies, you may want to collect them into their own namespaces.
SqlParameterList
Let’s start with the SqlParameterList object. As you might be able to tell from the name, it is a list of the Framework’s SqlParameter object, with additional methods that let the user reference member objects by name. Here is the complete code:
Public Class SqlParameterList
Inherits List(Of SqlParameter)
Public Function ContainsParamName(ByVal ParameterName As String) As Boolean
If Me.Count = 0 Then Return False
Dim RetVal As Boolean = False
For Each SQP As SqlParameter In Me
If String.Equals(SQP.ParameterName, ParameterName, _
StringComparison.CurrentCultureIgnoreCase) Then
RetVal = True
Exit For
End If
Next
Return RetVal
End Function
Public Function Copy() As SqlParameterList
Dim PL As New SqlParameterList
For Each SP As SqlParameter In Me
PL.Add(CType(SP, ICloneable).Clone)
Next
Return PL
End Function
Public Shadows Function Find(ByVal ParameterName As String) As SqlParameter
If Me.Count = 0 Then Return Nothing
Dim Result As SqlParameter = Nothing
For Each P As SqlParameter In Me
If String.Equals(P.ParameterName, ParameterName, _
StringComparison.CurrentCultureIgnoreCase) = 0 Then
Result = P
Exit For
End If
Next
Return Result
End Function
Default Overloads Property Item(ByVal ParameterName As String) As SqlParameter
Get
If Find(ParameterName) Is Nothing Then
Throw New ArgumentException( _
String.Format("Collection does not contain an element '{0}'.", ParameterName))
End If
Dim I As Integer = 0
For I = 0 To Me.Count - 1
If String.Compare(Me.Item(I).ParameterName, ParameterName, ignoreCase:=True) = 0 Then
Exit For
End If
Next
Return Me(I)
End Get
Set(ByVal value As SqlParameter)
Throw New InvalidOperationException("List items cannot be set using this property overload.")
End Set
End Property
Public Overloads Function Add(ByVal Parameter As SqlParameter) As SqlParameter
Remove(Parameter.ParameterName)
MyBase.Add(Parameter)
Return Parameter
End Function
Public Overloads Function Add(ByVal ParameterName As String, ByVal ParameterType As SqlDbType, _
ByVal Value As Object) As SqlParameter
Dim Param As New SqlParameter(ParameterName, ParameterType)
If Value Is Nothing Then
Param.Value = DBNull.Value
Else
Param.Value = Value
End If
Return Me.Add(Param)
End Function
Public Overloads Sub Remove(ByVal ParameterName As String)
If Me.Count = 0 Then Exit Sub
Dim ThisParam As SqlParameter = Nothing
For Each Param As SqlParameter In Me
If String.Equals(Param.ParameterName, ParameterName, _
StringComparison.CurrentCultureIgnoreCase) Then
ThisParam = Param
Exit For
End If
Next
If ThisParam IsNot Nothing Then MyBase.Remove(ThisParam)
End Sub
Public Overrides Function ToString() As String
Dim Retval As New StringBuilder
If Me.Count = 0 Then
Retval.Append("[Parameter list is empty]")
Else
For Each Param As SqlParameter In Me
Retval.AppendFormat("Parameter {0}: {1} ({2}, {3})" + vbCrLf, _
Param.ParameterName, Param.Value, Param.SqlDbType.ToString, Param.Size)
Next
End If
Return Retval.ToString
End Function
Public Function ToStringHtml() As String
Dim Retval As New StringBuilder
If Me.Count = 0 Then
Retval.Append("[Parameter list is empty]")
Else
If Me.Count > 0 Then
Retval.Append("<ul>")
For Each Param As SqlParameter In Me
Retval.AppendFormat("<li>Parameter {0}: {1} ({2}, {3})</li>" + vbCrLf, _
Param.ParameterName, Param.Value, Param.SqlDbType.ToString, Param.Size)
Next
Retval.AppendLine("</ul>")
End If
End If
Return Retval.ToString
End Function
End ClassThe overload of the default property Item allows you to reference parameters by their name:
Params("@Author").Value = "George R. R. Martin"The base Add method is overloaded to first remove a parameter of the same name, guaranteeing that the names in the list are unique. An additional Add method makes it easier add new parameters; none of my code requires directions and I have never had any problem with not indicating the size of the fields, but if these are concerns for you, it would be very easy to make additional overloads.
The ToString method is overridden to present a nice, orderly presentation of all member parameters. Because this was originally written for use on a website, I added ToHtmlString, which gives the same output in HTML for debugging purposes.
SqlRecordset
The vast majority of what I do involves pulling data out of the database and presenting it to the user, so SqlRecordset gets used a lot.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Public Class SqlRecordset
Private pSelectParameters As SqlParameterList
Private pSelectCommand As String
Private pTable As DataTable
Public ReadOnly Property Columns() As DataColumnCollection
Get
Return pTable.Columns
End Get
End Property
Default Public ReadOnly Property Row(ByVal Index As Integer) As DataRow
Get
If Index < 0 OrElse Index > pTable.Rows.Count - 1 Then
Throw New ArgumentOutOfRangeException
Else
Return pTable.Rows(Index)
End If
End Get
End Property
Public ReadOnly Property Rows() As DataRowCollection
Get
Return pTable.Rows
End Get
End Property
Public Property SelectParameters() As SqlParameterList
Get
Return pSelectParameters
End Get
Friend Set(ByVal value As SqlParameterList)
If value Is Nothing Then
pSelectParameters = Nothing
Else
pSelectParameters = value.Copy
End If
End Set
End Property
Public Property SelectCommand() As String
Get
Return pSelectCommand
End Get
Set(ByVal value As String)
pSelectCommand = value
End Set
End Property
Public Property Table() As DataTable
Get
Return pTable
End Get
Friend Set(ByVal value As DataTable)
pTable = value.Copy
End Set
End Property
Public Sub New()
pSelectCommand = ""
pSelectParameters = Nothing
pTable = Nothing
End Sub
End ClassThis is basically a wrapper around the Framework’s DataTable object while keeping track of the command and parameters that were used to populate it. The default property, Row, makes it easier to access a specific row:
If RS.Rows.Count > 5 Then DR = RS(4)
This fragment makes sure that the object has a fifth row, then retrieves it. Note also that the Set method in the property Table uses the Friend modifier: this allows the property to be set from within the assembly, but makes the property read-only when it is used outside the assembly.
SqlDbException
This object is an extension of the System.Exception class. It serves two purposes: it allows the coder to filter out exceptions raised by the toolbox in a Catch tree, and it encapsulates the command string and parameter list of the call that generated the error. The code is pretty basic; reference the accompanying source code file if you want to see it.
SqlLib
The heart of the SqlLib toolbox is found in three functions:
Execute - Execute a SQL command and return the number of rows affected.ExecuteScalar - Perform a SQL select and return the value of the first column in the first row, or Nothing if the returned value was null.OpenRecordset - Perform a SQL select and return the results in a SqlRecordset object.
The code for OpenRecordset is pretty typical of how all three work.
Public Shared Function OpenRecordset( _
ByVal SelectCommand As String, _
ByVal Params As SqlParameterList, _
ByVal ConnectionString As String) As SqlRecordset
Dim Conn As SqlConnection = Nothing
Dim Cmd As SqlCommand = Nothing
Dim Adapter As SqlDataAdapter = Nothing
Dim DS As DataSet = Nothing
Dim RS As SqlRecordset = Nothing
Try
Conn = New SqlConnection(ConnectionString)
Cmd = New SqlCommand(SelectCommand, Conn)
Cmd.CommandTimeout = 300
If Params IsNot Nothing Then Cmd.Parameters.AddRange(Params.ToArray)
Adapter = New SqlDataAdapter(Cmd)
DS = New DataSet
Adapter.Fill(DS)
RS = New SqlRecordset
RS.Table = DS.Tables(0)
RS.SelectParameters = Params
RS.SelectCommand = SelectCommand
Catch ex As Exception
Dim Msg As New StringBuilder
Msg.AppendFormat("Error opening recordset: {0}" + vbCrLf, Command)
For Each p As SqlParameter In Params
Msg.AppendFormat("{0} = {1}" + vbCrLf, p.ParameterName, p.Value)
Next
Throw New SqlDbException(Msg.ToString, ex, Command, Params)
Finally
If DS IsNot Nothing Then DS.Dispose()
If Adapter IsNot Nothing Then Adapter.Dispose()
If Conn IsNot Nothing Then
If Conn.State <> ConnectionState.Closed Then Conn.Close()
Conn.Dispose()
End If
If Cmd IsNot Nothing Then
Cmd.Parameters.Clear()
Cmd.Dispose()
End If
End Try
Return RS
End FunctionThis should be pretty familiar to most readers. Pass in a SQL selection command, parameter list (which can be Nothing if the command does not use parameters) and a connection string. Open the connection, execute the command into a data adapter, put the first (and normally only) table into the return value along with the parameters and command, close and dispose, then return the result.
The Execute and ExecuteScalar methods are very similar, except that these return the values returned by SqlCommand.ExecuteNonQuery and SqlCommand.ExecuteScalar, respectively.
The toolbox has another method that I have found useful from time to time:
Public Shared Function GetTableIdentity(ByVal TableName As String, ByVal ConnectionString As String) _
As Object
Dim Sql As New StringBuilder
Dim Params As New SqlParameterList
Sql.Append("SELECT IDENT_CURRENT(@TableName)")
Params.Add("@TableName", SqlDbType.VarChar, TableName)
Return ExecuteScalar(Sql.ToString, Params, ConnectionString)
End FunctionThis looks at a table and returns its current identity value, or DB.Null if the table has no identity.
Transactions
Sometimes, you need to execute a series of commands as an all-or-nothing batch. SQL supports this with its transaction model.
SqlTransaction is rather long, so I will refer you to the downloadable source code. Basically, it holds instances of the Framework’s SqlConnection and SqlCommand objects, plus a copy of the connection string. It implements methods that let the coder execute commands, commit the actions, and roll them back.
The constructor is marked as Friend, so it is unavailable to the coder. To open a transaction, you would call SqlLib.BeginTransaction, which takes the connection string and returns a new transaction. Transacted commands are executed by calling the transaction object’s Execute method. Final disposition is done by calling either Commit or Rollback. By way of example:
Public Sub AddNewBook(ByVal Title As String, ByVal Author As String, ByVal ISBN As String, _
ByVal PublisherId As Integer, ByVal DistributorId As Integer)
Dim Trans As SqlTransaction = Nothing
Dim Sql As New StringBuilder
Dim Params As New SqlParameterList
Trans = SqlLib.BeginTransaction(ConnStr)
Params.Add("@Title", SqlDbType.VarChar, Title)
Params.Add("@Author", SqlDbType.VarChar, Author)
Params.Add("@ISBN", SqlDbType.VarChar, ISBN)
Params.Add("@PublisherId", SqlDbType.Int, PublisherId)
Params.Add("@DistributorId", SqlDbType.Int, DistributorId)
Try
Sql.Append("INSERT BookPublishers (PublisherId, ISBN) VALUES (@PublisherId, @ISBN)")
Trans.Execute(Sql.ToString, Params, ConnStr)
Sql.Length = 0
Sql.Append("INSERT BookDistributors (DistributorId, ISBN) VALUES (@DistributorId, @ISBN)")
Trans.Execute(Sql.ToString, Params, ConnStr)
Sql.Length = 0
Sql.Append("INSERT Inventory (Title, Author, ISBN, BookCount) ")
Sql.Append("VALUES (@Title, @Author, @ISBN, 0)")
Trans.Execute(Sql.ToString, Params, ConnStr)
Trans.Commit()
Catch ex As Exception
Try
Trans.Rollback()
Catch ex1 As Exception
Dim Bad As New SqlDbException("Exception thrown in AddNewBook.", ex, Sql.ToString, Params)
Throw New SqlDbException("Unable to roll back transaction.", Bad, Sql.ToString, Params)
End Try
Throw New SqlDbException("Exception thrown in AddNewBook.", ex, Sql.ToString, Params)
End Try
End SubSqlTransaction implements IDisposable, so you could also write this within a Using block. Also, please note that the Framework has its own SqlTransaction object, in System.Data.SqlClient namespace. If you reference that namespace, you may need to use qualifiers to indicate which object you are using.
Utility methods
The last bit is the DbLib toolbox, which contains some general purpose methods that would be applicable to any database technology. Mostly, these serve to translate from the Object that is returned by the Framework into the expected data type. For example, suppose you are expecting an integer:
Public Shared Function ToInteger(ByVal Value As Object) As Integer
If IsNumeric(Value) Then
Return CInt(Value)
Else
Return Integer.MinValue
End If
End Function
Public Shared Function ToIntegerNull(ByVal Value As Object) As Nullable(Of Integer)
Dim Result As New Nullable(Of Integer)
If IsNumeric(Value) Then
Result = CInt(Value)
ElseIf Value Is DBNull.Value OrElse Value Is Nothing Then
Result = Nothing
Else
Throw New ArgumentException("Value is neither a number nor DBNull.")
End If
End FunctionThe first method, ToInteger, will return the integer value of Value if it is numeric, otherwise it will return Integer.MinValue. Because value types cannot be set to Nothing, there is no way to trap the possibility that the column holds a null value. That is where ToIntegerNull comes in: it will return an integer value if possible, Nothing if the value is null, and throw an exception otherwise.
Examples
As far as actually using the toolbox, I have included some examples in the source code.
To infinity, and beyond
Using the model for DbToolbox.SqlClient, you can easily write versions for OLE, ODBC, Access, Oracle or any other database technology that can be implemented in .Net. If you come up with anything interesting, or have suggestions for improving this toolbox, please let me know in the comments below.
Updates
2014-01-15: PLEASE NOTE! I have updated the source code to fix a bug that would cause the toolbox to crash if Nothing is passed instead of a SqlParameterList object. If you have previously downloaded the code, please make sure you have DbToolbox_VB_v2.zip.
