DBNameValueCollection Class

Posted by Blake on 10/7/2011
)

I wanted to share a simple class that inherits from NameValueCollection and automatically handles persisting that NameValueCollection into a database table. You provide a connection, the name of the table and the key/value field names. When it’s declared, all of the values from the database are read into the collection. When you make changes, you can call the “CommittChanges” method to write them back out to the database.

The collection will only be written back out if changes have occurred. There maybe a more efficient way to do this, but for this simple example the table data is deleted and re-loaded when changes are committed. A database transaction is used so that all changes must be successful otherwise the database table is kept in it’s current state.

Imports System.Data

Namespace Argus.Data

	''' <summary>
    ''' A class to handle persisting a NameValueCollection to a database.  It should be noted that this class does not yet handle
    ''' concurrent users, it was designed for easy storage of collections in a single user application.
    ''' </summary>
    ''' <remarks></remarks>
    Public Class DbNameValueCollection
        Inherits System.Collections.Specialized.NameValueCollection

        '*********************************************************************************************************************
        '
        '             Class:  DbNameValueCollection
        '      Initial Date:  10/07/2011
        '      Last Updated:  06/19/2012
        '     Programmer(s):  Blake Pell, bpell@indiana.edu
        '
        '*********************************************************************************************************************

#Region "Contructors"

        ''' <summary>
        ''' Constructor
        ''' </summary>
        ''' <param name="conn">The database connection to use.  The connection must be initialized but does not need to be open yet.  This class will not cleanup or close the connection when it's done with it.</param>
        ''' <param name="tableName">The name of the table the NameValueCollection should be stored in.</param>
        ''' <param name="fieldKeyName">The database field name for the key field.</param>
        ''' <param name="fieldValueName">The database field name for the value field.</param>
        ''' <remarks></remarks>
        Public Sub New(ByVal conn As IDbConnection, ByVal tableName As String, ByVal fieldKeyName As String, ByVal fieldValueName As String)
            Me.TableName = tableName
            Me.FieldKeyName = fieldKeyName
            Me.FieldValueName = fieldValueName

            If conn Is Nothing Then
                Throw New NullReferenceException("The database connection cannot be null.")
            End If
            _dbConnection = conn
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            Dim command As IDbCommand = conn.CreateCommand
            command.CommandText = String.Format("SELECT {0}, {1} FROM {2}", fieldKeyName, fieldValueName, tableName)
            Dim dr As IDataReader = command.ExecuteReader

            While dr.Read
                ' A null key will be skipped
                If IsDBNull(dr(fieldKeyName)) = True Then
                    Continue While
                End If
                ' A null value will be converted to default of blank
                If IsDBNull(dr(fieldValueName)) = True Then
                    Me.Add(dr(fieldKeyName), "")
                Else
                    ' The value is good, add it
                    Me.Add(dr(fieldKeyName), dr(fieldValueName))
                End If
            End While

            dr.Close() : dr = Nothing
            command.Dispose() : command = Nothing
        End Sub
#End Region

#Region "Methods"

        ''' <summary>
        ''' Clears and refreshes the contents of the collection from the database.
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub RefreshCollectionFromDatabase()
            If _dbConnection Is Nothing Then
                Throw New NullReferenceException("The database connection cannot be null.")
            End If

            If _dbConnection.State <> ConnectionState.Open Then
                _dbConnection.Open()
            End If

            Dim command As IDbCommand = _dbConnection.CreateCommand
            command.CommandText = String.Format("SELECT {0}, {1} FROM {2}", FieldKeyName, FieldValueName, TableName)
            Dim dr As IDataReader = command.ExecuteReader
            Me.Clear()

            While dr.Read
                ' A null key will be skipped
                If IsDBNull(dr(FieldKeyName)) = True Then
                    Continue While
                End If
                ' A null value will be converted to default of blank
                If IsDBNull(dr(FieldValueName)) = True Then
                    Me.Add(dr(FieldKeyName), "")
                Else
                    ' The value is good, add it
                    Me.Add(dr(FieldKeyName), dr(FieldValueName))
                End If
            End While

            dr.Close() : dr = Nothing
            command.Dispose() : command = Nothing
            _changed = False
        End Sub

        ''' <summary>
        ''' Commits the changes to the database only if any have taken place.
        ''' </summary>
        ''' <remarks>
        ''' This method will use a database transaction, if any records fail to be inserted then the entire transaction
        ''' will be rolled back and no changes will occured on the database side.  After the rollback occurs the exception
        ''' will be thrown.
        ''' </remarks>
        Public Sub CommitChanges()
            If _changed = False Then
                Exit Sub
            End If

            If _dbConnection Is Nothing Then
                Throw New NullReferenceException("The database connection cannot be null.")
            End If

            If _dbConnection.State <> ConnectionState.Open Then
                _dbConnection.Open()
            End If

            Dim transaction As IDbTransaction = _dbConnection.BeginTransaction
            Dim command As IDbCommand = _dbConnection.CreateCommand

            Try
                command.Transaction = transaction
                command.CommandText = String.Format("DELETE from {0}", Me.TableName)
                command.ExecuteNonQuery()
                command.CommandText = String.Format("INSERT INTO {0} ({1}, {2}) VALUES ({3}keyName, {3}valueName)", Me.TableName, Me.FieldKeyName, Me.FieldValueName, Me.ParameterQualifier)
                Dim pKeyName As System.Data.IDbDataParameter = command.CreateParameter
                pKeyName.ParameterName = String.Format("{0}keyName", Me.ParameterQualifier)
                pKeyName.DbType = DbType.String
                command.Parameters.Add(pKeyName)
                Dim pKeyValue As System.Data.IDbDataParameter = command.CreateParameter
                pKeyValue.ParameterName = String.Format("{0}valueName", Me.ParameterQualifier)
                pKeyValue.DbType = DbType.String
                command.Parameters.Add(pKeyValue)
                For Each key In Me.Keys
                    DirectCast(command.Parameters(String.Format("{0}keyName", Me.ParameterQualifier)), IDbDataParameter).Value = key
                    DirectCast(command.Parameters(String.Format("{0}valueName", Me.ParameterQualifier)), IDbDataParameter).Value = Me(key)
                    command.ExecuteNonQuery()
                Next
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
                Throw
            Finally
                command.Dispose() : command = Nothing
                transaction.Dispose()
            End Try
            _changed = False
        End Sub

        ''' <summary>
        ''' Removes an item from the NameValueCollection
        ''' </summary>
        ''' <param name="name"></param>
        ''' <remarks></remarks>
        Public Overrides Sub Remove(ByVal name As String)
            _changed = True
            MyBase.Remove(name)
        End Sub

        ''' <summary>
        ''' Adds an item to the NameValueCollection.
        ''' </summary>
        ''' <param name="name"></param>
        ''' <param name="value"></param>
        ''' <remarks></remarks>
        Public Overrides Sub Add(ByVal name As String, ByVal value As String)
            _changed = True
            MyBase.Add(name, value)
        End Sub

        ''' <summary>
        ''' Clears the NameValueCollection
        ''' </summary>
        ''' <remarks></remarks>
        Public Overrides Sub Clear()
            _changed = True
            MyBase.Clear()
        End Sub

        ''' <summary>
        ''' Sets a value based on it's provided key.
        ''' </summary>
        ''' <param name="name"></param>
        ''' <param name="value"></param>
        ''' <remarks></remarks>
        Public Overrides Sub [Set](ByVal name As String, ByVal value As String)
            _changed = True
            MyBase.[Set](name, value)
        End Sub

        Private _parameterQualifier As String = "@"
        ''' <summary>
        ''' The paramater qualifier that should be used.  SQL Server = @, MySql = ? and Oracle = :
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property ParameterQualifier() As String
            Get
                Return _parameterQualifier
            End Get
            Set(ByVal value As String)
                _parameterQualifier = value
            End Set
        End Property

#End Region

#Region "Properties"
        Private _changed As Boolean = False
        ''' <summary>
        ''' Whether or not the NameValueCollection has changed since it was last committed to the database.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property Changed() As Boolean
            Get
                Return _changed
            End Get
        End Property

        Private _tableName As String = ""
        ''' <summary>
        ''' The name of the table in the database that holds the NameValueCollection.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks>
        ''' This table will need to have 2 character fields, one for the key and one for the associated value.
        ''' </remarks>
        Public Property TableName() As String
            Get
                Return _tableName
            End Get
            Set(ByVal value As String)
                _tableName = value
            End Set
        End Property

        Private _fieldKeyName As String = ""
        ''' <summary>
        ''' The name of the database field which stores the key.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property FieldKeyName() As String
            Get
                Return _fieldKeyName
            End Get
            Set(ByVal value As String)
                _fieldKeyName = value
            End Set
        End Property

        Private _fieldValueName As String = ""
        ''' <summary>
        ''' The name of the database field that stores the value.
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property FieldValueName() As String
            Get
                Return _fieldValueName
            End Get
            Set(ByVal value As String)
                _fieldValueName = value
            End Set
        End Property

        Private _dbConnection As IDbConnection
        ''' <summary>
        ''' The underlaying database connection.  
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks>
        ''' </remarks>
        Public ReadOnly Property DbConnection() As IDbConnection
            Get
                Return _dbConnection
            End Get
        End Property

#End Region

    End Class
End Namespace