A Wrapper Class for DML Statements Using Visual Basic.NET

---

A downloadable file for this article is available here.

---

The entire source code for this article is available in the form of downloadable zip.  The solution was developed using Microsoft Visual Studio 2003 Enterprise Architect with Microsoft SQL Server 2005 Developer Edition on Microsoft Windows Server 2003 Enterprise Edition.  Even though I believe that the source code available with this contribution can work with Microsoft Visual Studio.NET 2002, I didn't really test it in any other environment. I request that you post in the discussion area if you have any problems with execution.

What exactly is this DML support class doing?

In several applications, we use DML statements which execute dynamically. If we are working with an INSERT statement that has too many columns, it would be rather daunting. The same would be true for an UPDATE statement with many columns.

To solve all these issues, I developed a simple wrapper class specifically to work with DML statements flexibly. As part of what was needed, I declared the following enumerations:

PublicEnum CommandType
        Insert
        Update
    End Enum
PublicEnum DataType
        NumericType
        CharacterType
        DateType
EndEnum

When we work with a DML statement, it would be generally an INSERT, UPDATE or DELETE (putting SELECT aside). DELETE is very simple and I simply don't want to handle it in this class. That is why I declared only "Insert" and "Update" as part of the "Command Type."

When working with DML commands, the syntax will be different based on the data types. To support different data types, I declared the above enumeration.

To execute the DML command, I am using the Data Helper class developed by me in my previous articles. If you want to include your own code, I request that you  go ahead by forgetting the data helper code. You can also modify this class with respect to Microsoft Data Access Blocks.

The beginning of the class

Let us first go through the following code:

PublicClass DMLLib
...   

    Private m_SQL As String = ""
    Private m_dbObject As Object = Nothing 'this should be either
'WebLib' or 'WinLib' objects
    Private m_ColNameList As String = ""
    Private m_ColValList As String = ""
    Private m_CommandType As CommandType
    'only for storing files directly in database 
    Private m_FileColNames() As String = Nothing 'to hold all
column names to which files are to be uploaded
    Private m_FileByteArrays() As Object = Nothing 'the content
of each file to be uploaded..

    Public Sub New(ByVal DMLCommandType As CommandType, ByRef
dbObject As Object) 'dbObject should be either 'WebLib' or
'WinLib' objects
        If dbObject Is Nothing Then
            Throw New Exception("DBLib Object is currently
nothing")
        End If
        m_dbObject = dbObject
        m_CommandType = DMLCommandType
        Select Case DMLCommandType
            Case CommandType.Insert
                m_SQL = "INSERT INTO "
            Case CommandType.Update
                m_SQL = "UPDATE "
        End Select
    End Sub

The above code contains the constructor, which accepts two parameters. The first is "CommandType" and the second is the "database" object.  Based on the command type, I initially started with the command itself.

To provide the table name, I created the following property:

PublicWriteOnly Property TableName()
        Set(ByVal Value)
            Select Case m_CommandType
                Case CommandType.Insert
                    m_SQL &= Value & " () VALUES ()"
                Case CommandType.Update
                    m_SQL &= Value & " SET "
            End Select
        End Set
    End Property

Adding a column to the INSERT or UPDATE command

The following is the code necessary for adding a new column to the respective DML command:

PublicSub addColumn(ByVal ColName As String, ByVal ColValue As
String, Optional ByVal ColType As DataType =
DataType.CharacterType)
        'change the value according to datatype
        Select Case m_CommandType
            Case CommandType.Insert 'INSERT command
                Select Case ColType
                    Case DataType.CharacterType
                        ColValue = "'" & ColValue & "'"
                    Case DataType.DateType
                        Dim s As String = Format(CDate(ColValue),
"yyyy/MM/dd")
                        Select Case m_dbObject.ConnectionType
                            Case DBConnType.MSSQL
                                ColValue = "'" & s & "'"
                                'Case "JET"
                                'ColValue = "#" & s & "#"
                            Case Else
                                ColValue = "'" & s & "'"
                        End Select
                    Case DataType.NumericType
                        ColValue = ColValue
                End Select
                If m_ColNameList = "" Then
                    m_ColNameList = ColName
                Else
                    m_ColNameList &= ", " & ColName
                End If
                If m_ColValList = "" Then
                    m_ColValList = ColValue
                Else
                    m_ColValList &= ", " & ColValue
                End If
            Case CommandType.Update 'UPDATE command
                Select Case ColType
                    Case DataType.CharacterType
                        m_SQL &= ColName & "='" & ColValue & "',"
                    Case DataType.DateType
                        Dim s As String = Format(CDate(ColValue),
"yyyy/MM/dd")
                        Select Case m_dbObject.ConnectionType
                            Case DBConnType.MSSQL
                                m_SQL &= ColName & "='" & s &
"',"
                                'Case "JET"
                                '    m_SQL &= ColName & "=#" & s
& "#,"
                        End Select
                    Case DataType.NumericType
                        m_SQL &= ColName & "=" & ColValue & ","
                End Select
        End Select
    End Sub

Adding code to add binary data for INSERT command

The following is the code necessary for adding a column (of type binary) to the INSERT command.

PublicSub addFileColumn(ByVal ColName As String, ByRef UpFile As System.Web.HttpPostedFile)
        'make sure to include this attribute in your form tag b4 using this method ...enctype="multipart/form-data"
        'change the value according to datatype
        Dim FileStream As System.IO.Stream
        Select Case m_CommandType
            Case CommandType.Insert 'INSERT command
                Select Case m_dbObject.ConnectionType
                    Case DBConnType.MSSQL 'copy the same below in UPDATE case also
                        Dim fileLength As Integer = UpFile.ContentLength
                        If (fileLength = 0) Then
                            Throw New Exception("No information is available in selected File..try giving another existing filename")
                        End If
                        Dim FileByteArray(fileLength) As Byte
                        FileStream = UpFile.InputStream
                        FileStream.Read(FileByteArray, 0, fileLength)
                        'append this information to already existing arrays
                        If m_FileColNames Is Nothing Then
                            ReDim m_FileColNames(0)
                            ReDim m_FileByteArrays(0)
                        Else
                            ReDim Preserve m_FileColNames(UBound(m_FileColNames) + 1)
                            ReDim Preserve m_FileByteArrays(UBound(m_FileByteArrays) + 1)
                        End If
                        m_FileColNames(m_FileColNames.Length - 1) = ColName
                        m_FileByteArrays(m_FileByteArrays.Length - 1) = FileByteArray
                    Case Else
                        Throw New Exception("currently this library supports only SQL.NET Provider to upload files")
                End Select
                m_ColNameList = IIf(m_ColNameList = "", ColName, m_ColNameList & "," & ColName)
                m_ColValList = IIf(m_ColValList = "", "@" & Trim(ColName), m_ColValList & ",@" & Trim(ColName))
                'this is for oledb type
                'm_ColValList = IIf(m_ColValList = "", "?", m_ColValList & ",?")

 

Adding code to add binary data for UPDATE command

The following is the code necessary for adding a column (of type binary) to the UPDATE command.

CaseCommandType.Update 'UPDATE command
                Select Case m_dbObject.ConnectionType
                    Case DBConnType.MSSQL
                        m_SQL &= ColName & "=" & "@" & ColName & ","
                        'the following is same as the above case
                        Dim fileLength As Integer = pFile.ContentLength
                        If (fileLength = 0) Then
                            Throw New Exception("No information is available in selected File..try giving another existing filename")
                        End If
                        Dim FileByteArray(fileLength) As Byte
                        FileStream = UpFile.InputStream
                        FileStream.Read(FileByteArray, 0, fileLength)
                        'append this information to already existing arrays
                        If m_FileColNames Is Nothing Then
                            ReDim m_FileColNames(0)
                            ReDim m_FileByteArrays(0)
                        Else
                            ReDim Preserve m_FileColNames(UBound(m_FileColNames) + 1)
                            ReDim Preserve m_FileByteArrays(UBound(m_FileByteArrays) + 1)
                        End If
                        m_FileColNames(m_FileColNames.Length - 1) = ColName
                        m_FileByteArrays(m_FileByteArrays.Length - 1) = FileByteArray
                    Case Else
                        Throw New Exception("currently this library supports only SQL.NET Provider to upload files")
                End Select
        End Select

Executing the DML command

Once the DML command is framed, we need to execute it with a separate method.  The method looks something like the following:

PublicSub ExecuteCommand()
        If m_FileColNames Is Nothing Then 'if no images exist in the command
            m_dbObject.SQLExecute(Me.GeneratedSQLCommand)
        Else 'insert images as well
            m_dbObject.SQLExecute(Me.GeneratedSQLCommand, m_FileColNames, m_FileByteArrays)
        End If
    End Sub

The above method works with another method called "GeneratedSQLCommand," which is defined as follows:

PublicReadOnly Property GeneratedSQLCommand()
        Get
            Select Case m_CommandType
                Case CommandType.Insert
                    m_SQL = Replace(m_SQL, "()", "(" & m_ColNameList & ")", , 1)
                    m_SQL = Replace(m_SQL, "()", "(" & m_ColValList & ")")
                Case CommandType.Update
                    If InStr(m_SQL, " WHERE ") = 0 Then
                        Return Left(m_SQL, Len(m_SQL) - 1)
                    End If
            End Select
            Return m_SQL
        End Get
    End Property

The method "ExecuteCommand" executes the DML command with the help of my data helper class provided in my previous articles. You can embed your own logic to execute the DML command at that point.

Completing the class

Before we complete the class, we need to examine two more methods. Let us consider the first one:

PublicSub Criteria(ByVal Condition As String)
        Select Case m_CommandType
            Case CommandType.Update
                m_SQL = Left(m_SQL, Len(m_SQL) - 1)
                m_SQL &= " WHERE " & Condition
        End Select
    End Sub

The above method is mainly used to add a condition to the DML command. The second is the following:

PublicSub Clear()
        Select Case m_CommandType
            Case CommandType.Insert
                m_SQL = "INSERT INTO "
                m_ColValList = ""
                m_ColNameList = ""
                m_FileColNames = Nothing
                m_FileByteArrays = Nothing
            Case CommandType.Update
                m_SQL = "UPDATE "
                m_FileColNames = Nothing
                m_FileByteArrays = Nothing
        End Select
    End Sub

If you wanted to clear the existing DML command frame, you can use the above command.  It is mainly used when you want to reuse the same DML object several times.

Working with the above class is as simple as the following code:

        Dim ins As New DMLLib(DMLLib.CommandType.Insert, db)
        With ins
            .TableName = "emp"
            .addColumn("empno", "1001")
            .addColumn("ename", "jag")
            .ExecuteCommand()
        End With

If necessary, you can even put "addColumn" in a loop to work with a larger number of columns. The only important issue is that the "TableName" must be the first assignment to the DMLLib object.

Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.

blog comments powered by Disqus