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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
Public ReadOnly Property DbConnection() As IDbConnection
Get
Return _dbConnection
End Get
End Property
#End Region
End Class
End Namespace