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