Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

DbToolbox: Utilities for data access and management

Rate me:
Please Sign up or sign in to vote.
4.81/5 (19 votes)
15 Jan 2014CPOL6 min read 33.9K   624   31   19
Database access and management can be a chore in .Net. This toolbox can make it easier.

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:

VB.NET
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
            'Do something with the data
        Loop
    Else
        'Do something if there is no data
    End If
    Reader.Close()
Catch ex As Exception
    'Handle exception
Finally
    Conn.Close()
End Try

when you can instead have 20 instances of this?

VB.NET
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
    'Do something with the data
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:

VB.NET
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 Class

The overload of the default property Item allows you to reference parameters by their name:

VB.NET
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.

VB.NET
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 Class

This 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:

VB.NET
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.

VB.NET
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 Function

This 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:

VB.NET
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 Function

This 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:

VB.NET
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

    'First, we create the transaction
    Trans = SqlLib.BeginTransaction(ConnStr)

    'We will be using all of the method parameters, so set them up.
    'We can pass the whole list into SqlLib, even when the associated
    'command or query uses only some of the parameters.
    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
        'All actions are done using the Transaction object, not SqlLib
        Sql.Append("INSERT BookPublishers (PublisherId, ISBN) VALUES (@PublisherId, @ISBN)")
        Trans.Execute(Sql.ToString, Params, ConnStr)

        'Reset Sql for reuse
        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)

        'If we haven't blown up yet, we can try to commit the transaction
        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

    'Trans is disposed when it goes out of scope.
End Sub

SqlTransaction 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:

VB.NET
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 Function

The 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
Gregory Gadow recently graduated from Central Washington University with a B.S. that combined economics and statistical analysis, and currently works for the Washington Department of Fish & Wildlife as an IT developer. He has been writing code for 30 years in more than a dozen programming languages, including Visual Basic, VB.Net, C++, C#, ASP, HTML, XML, SQL, and R.

Comments and Discussions

 
PraiseNice Pin
Member 126660564-Aug-16 1:53
Member 126660564-Aug-16 1:53 
QuestionDbToolbox: How about Stored Procedure? Pin
Fidell13-Feb-14 23:11
Fidell13-Feb-14 23:11 
AnswerRe: DbToolbox: How about Stored Procedure? Pin
Gregory Gadow24-Feb-14 10:13
Gregory Gadow24-Feb-14 10:13 
QuestionOr how about Pin
Sacha Barber12-Feb-14 21:52
Sacha Barber12-Feb-14 21:52 
GeneralMy vote of 5 Pin
Prasad Khandekar10-Feb-14 18:46
professionalPrasad Khandekar10-Feb-14 18:46 
GeneralReally Helpful! Pin
SkinnyGlass23-Jan-14 14:31
professionalSkinnyGlass23-Jan-14 14:31 
Questionreinvent the wheel Pin
kiquenet.com16-Jan-14 3:24
professionalkiquenet.com16-Jan-14 3:24 
"We invented our own web/persistence/messaging/caching framework because none of the existing ones was good enough."

Better working together with many projects implemented yet in github, codeplex, etc, for improvement open source components to Community.
kiquenet.com

AnswerRe: reinvent the wheel Pin
ENOTTY18-Jan-14 22:59
ENOTTY18-Jan-14 22:59 
AnswerRe: reinvent the wheel Pin
Gregory Gadow19-Jan-14 5:03
Gregory Gadow19-Jan-14 5:03 
GeneralRe: reinvent the wheel Pin
Espen Harlinn19-Jan-14 5:38
professionalEspen Harlinn19-Jan-14 5:38 
GeneralThoughts Pin
PIEBALDconsult14-Jan-14 9:02
mvePIEBALDconsult14-Jan-14 9:02 
GeneralRe: Thoughts Pin
Gregory Gadow14-Jan-14 9:27
Gregory Gadow14-Jan-14 9:27 
GeneralRe: Thoughts Pin
PIEBALDconsult14-Jan-14 9:52
mvePIEBALDconsult14-Jan-14 9:52 
GeneralRe: Thoughts Pin
Gregory Gadow15-Jan-14 4:23
Gregory Gadow15-Jan-14 4:23 
GeneralRe: Thoughts Pin
PIEBALDconsult15-Jan-14 4:35
mvePIEBALDconsult15-Jan-14 4:35 
GeneralRe: Thoughts Pin
Gregory Gadow15-Jan-14 5:16
Gregory Gadow15-Jan-14 5:16 
GeneralRe: Thoughts Pin
PIEBALDconsult15-Jan-14 5:42
mvePIEBALDconsult15-Jan-14 5:42 
QuestionExcellent toolbox that is really, genuinely useful Pin
Carl Edwards In SA13-Jan-14 6:06
professionalCarl Edwards In SA13-Jan-14 6:06 
AnswerRe: Excellent toolbox that is really, genuinely useful Pin
Gregory Gadow13-Jan-14 8:52
Gregory Gadow13-Jan-14 8:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.