Jerry Nixon on Windows: Building a Custom Collection in VB.NET

Jerry Nixon on Windows

Monday, December 20, 2004

Building a Custom Collection in VB.NET

Over and over I keep having to write custom collections. I keep going back to my original version to get a pointer to start. So, I am moving my template up here so I can get it from anywhere at anytime and if you want to use it yourself, go ahead. I wrote this sample to hit against Northwind, but since I am not including the SQL statements - which database is the target doesn't really matter.

Someday I will rewrite this in C#.




Imports System.Data.SqlClient
Imports System.Configuration.ConfigurationSettings

Public Class Product

#Region " constructors "

Public Sub New()

' purpose: do nothing

End Sub

Public Sub New(ByVal ProductId As Int16)

' purpose: load single product from database

Dim sqlText As String
sqlText = LoadFromFile("product_select.sql")

Dim sh As New SqlHelper
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = sh.SqlCommand(sqlText, CommandType.Text)

sqlCmd.Parameters.Add("ProductId", ProductId)

Dim dt As DataTable
Try
dt = sh.ExecuteDataTable(sqlCmd)
Catch ex As Exception
Throw New Exception(String.Format( _
"Error loading product {0}: {1}", ProductId, ex.Message))
End Try

If dt.Rows.Count = 0 Then
Throw New Exception( _
String.Format("Unable to find product {0}.", ProductId))
End If

Me.DataRow = dt.Rows(0)
LoadFromRow()

End Sub

Public Sub New(ByVal DataRow As DataRow)

Me.DataRow = DataRow
LoadFromRow()

End Sub

#End Region

#Region " properties "

Dim prop_DataRow As DataRow
Public Property DataRow() As DataRow
Get
Return prop_DataRow
End Get
Set(ByVal Value As DataRow)
prop_DataRow = Value
End Set
End Property

Dim prop_Id As Int16 = -1
Public Property Id() As Int16
Get
Return prop_Id
End Get
Set(ByVal Value As Int16)
prop_Id = Value
End Set
End Property

Dim prop_Name As String = String.Empty
Public Property Name() As String
Get
Return prop_Name
End Get
Set(ByVal Value As String)
prop_Name = Value
End Set
End Property

Dim prop_UnitPrice As Decimal = -1
Public Property UnitPrice() As Decimal
Get
Return prop_UnitPrice
End Get
Set(ByVal Value As Decimal)
prop_UnitPrice = Value
End Set
End Property

Dim prop_Discontinued As Boolean = False
Public Property Discontinued() As Boolean
Get
Return prop_Discontinued
End Get
Set(ByVal Value As Boolean)
prop_Discontinued = False
End Set
End Property

#End Region

#Region " methods "

Public Sub Save()

' purpose: insert or update the product to the database

Dim sqlText As String
sqlText = LoadFromFile(IIf(IsNumeric(Me.Id), _
"product_update.sql", "product_insert.sql"))

Dim sh As New SqlHelper
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = sh.SqlCommand(sqlText, CommandType.Text)

sqlCmd.Parameters.Add("ProductId", Me.Id)
sqlCmd.Parameters.Add("ProductName", Me.Name)
sqlCmd.Parameters.Add("UnitPrice", Me.UnitPrice)
sqlCmd.Parameters.Add("Discontinued", Me.Discontinued)

Dim dt As DataTable
Try
dt = sh.ExecuteDataTable(sqlCmd)
Catch ex As Exception
Throw New Exception(String.Format( _
"Error saving product {0}: {1}", _
IIf(IsNumeric(Me.Id), Me.Id, "NEW"), ex.Message))
End Try

If dt.Rows.Count = 0 Then
Throw New Exception(String.Format( _
"Unable to save product {0}.", _
IIf(IsNumeric(Me.Id), Me.Id, "NEW")))
End If

Me.DataRow = dt.Rows(0)
LoadFromRow()

End Sub

Public Sub Delete()

' purpose: delete the product from the database

Dim sqlText As String
sqlText = LoadFromFile("product_delete.sql")

Dim sh As New SqlHelper
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = sh.SqlCommand(sqlText, CommandType.Text)

sqlCmd.Parameters.Add("ProductId", Me.Id)

Try
sh.ExecuteNonQuery(sqlCmd)
Catch ex As Exception
Throw New Exception(String.Format( _
"Error deleting product {0}: {1}", _
IIf(IsNumeric(Me.Id), Me.Id, "NEW"), ex.Message))
End Try

End Sub

Private Sub LoadFromRow()

' purpose: load properties from data row

On Error Resume Next

Me.Id = Me.DataRow("ProductId").ToString()
Me.Name = Me.DataRow("ProductName").ToString()
Me.UnitPrice = Me.DataRow("UnitPrice").ToString()
Me.Discontinued = Me.DataRow("Discontinued").ToString()

End Sub

Private Function LoadFromFile(ByVal path As String) As String

' purpose: read content of a file

Dim sr As System.IO.StreamReader
sr = New System.IO.StreamReader(path)

Dim result As String = sr.ReadToEnd()
sr.Close()

Return result

End Function

#End Region

End Class

Public Class ProductCollection
Implements IEnumerable
Private m_ArrayList As New ArrayList

#Region " constructors "

Public Sub New()

Dim sqlText As String
sqlText = LoadFromFile("products_select.sql")

Dim sh As New SqlHelper
Dim sqlCmd As SqlClient.SqlCommand
sqlCmd = sh.SqlCommand(sqlText, CommandType.Text)

Dim dt As DataTable
Try
dt = sh.ExecuteDataTable(sqlCmd)
Catch ex As Exception
Throw New Exception(String.Format("Error loading products: {1}", ex.Message))
End Try

Me.DataTable = dt
LoadFromTable()

End Sub

Sub LoadFromTable()

Me.m_ArrayList.Clear()

Dim dr As DataRow
For Each dr In Me.DataTable.Rows

Dim l_Product As Product
l_Product = New Product(dr)
Me.Add(l_Product)

Next

End Sub

Private Function LoadFromFile(ByVal path As String) As String

' purpose: read content of a file

Dim sr As System.IO.StreamReader
sr = New System.IO.StreamReader(path)

Dim result As String = sr.ReadToEnd()
sr.Close()

Return result

End Function

#End Region

#Region " properties "

Dim prop_DataTable As DataTable
Public Property DataTable() As DataTable
Get
Return prop_DataTable
End Get
Set(ByVal Value As DataTable)
prop_DataTable = Value
End Set
End Property

Public ReadOnly Property Count() As Int32
Get
Return m_ArrayList.Count
End Get
End Property

#End Region

#Region " methods "

Public Sub Insert(ByVal Index As Int16, ByVal Product As Product)
m_ArrayList.Insert(Index, Product)
End Sub

Public Sub Add(ByVal Product As Product)
m_ArrayList.Add(Product)
End Sub

Public Sub Remove(ByVal Product As Product)
m_ArrayList.Remove(Product)
End Sub

Public Sub Remove(ByVal ProductId As Int16)
Dim l_product As Product
For Each l_product In Me.m_ArrayList
If l_product.Id = ProductId Then
Me.Remove(l_product)
End If
Next
End Sub

Public Function Find(ByVal ProductId As Int16) As Product
Dim l_product As Product
For Each l_product In Me.m_ArrayList
If l_product.Id = ProductId Then
Return l_product
End If
Next
End Function

Public Function GetEnumerator() As IEnumerator _
Implements System.Collections.IEnumerable.GetEnumerator
Return New GenericEnumerator(m_ArrayList)
End Function

#End Region

End Class

Public Class GenericEnumerator
Implements IEnumerator

' do not change this generic class

Private m_ArrayList As ArrayList
Private m_position As Int32 = -1

Public Sub New(ByVal ArrayList As ArrayList)
m_ArrayList = ArrayList
End Sub

Public ReadOnly Property Current() As Object _
Implements System.Collections.IEnumerator.Current
Get
Return m_ArrayList(m_position)
End Get
End Property

Public Function MoveNext() As Boolean _
Implements System.Collections.IEnumerator.MoveNext
m_position += 1
Return Not (m_position >= m_ArrayList.Count)
End Function

Public Sub Reset() _
Implements System.Collections.IEnumerator.Reset
m_position = -1
End Sub

End Class

Public Class SqlHelper

' this class is a subset of the complete class

#Region " constructors "

Public Sub New()

ConnectionString = AppSettings("DBConnStr")

End Sub

#End Region

#Region " properties "

Dim prop_ConnectionString As String
Public Property ConnectionString() As String
Get
Return prop_connectionstring
End Get
Set(ByVal Value As String)
prop_connectionstring = Value
End Set
End Property

Dim prop_SqlConnection As SqlConnection
Public Property SqlConnection() As SqlConnection
Get

If prop_sqlConnection Is Nothing Then

prop_sqlConnection = New SqlConnection(Me.ConnectionString)
prop_sqlConnection.Open()

End If

Return prop_SqlConnection

End Get
Set(ByVal Value As SqlConnection)
prop_SqlConnection = Value
End Set
End Property

#End Region

#Region " object builders "

Public Function SqlCommand(ByVal CommandText As String) As SqlCommand

Dim l_SqlCmd As SqlCommand
l_SqlCmd = New SqlCommand(CommandText)
l_SqlCmd.Connection = Me.SqlConnection
Return l_SqlCmd

End Function

#End Region

#Region " methods "

Public Function ExecuteDataTable(ByVal SqlCmd As SqlCommand) As DataTable

If SqlCmd.Connection Is Nothing Then
SqlCmd.Connection = Me.SqlConnection
ElseIf Not SqlCmd.Connection.State = Closed Then
SqlCmd.Connection = Me.SqlConnection
End If

Dim da As SqlDataAdapter
Dim dt As DataTable
da.Fill(dt)
Return dt

End Function

Public Function ExecuteNonQuery(ByVal SqlCmd As SqlCommand) As Int16

If SqlCmd.Connection Is Nothing Then
SqlCmd.Connection = Me.SqlConnection
ElseIf Not SqlCmd.Connection.State = Closed Then
SqlCmd.Connection = Me.SqlConnection
End If

Return SqlCmd.ExecuteNonQuery()

End Function

#End Region

End Class