#Region " Imports " Imports Statistics.Ado Imports System.Data Imports System.Text #End Region Namespace Data Public Class ProgramStatus #Region " Instance Fields " Shared _lastName As String = String.Empty Shared _lastNameIsSet As Boolean = False Shared _firstName As String = String.Empty Shared _firstNameIsSet As Boolean = False Const _connectionStringKey As String = "ACEProgramStatus" Shared _id As Integer = -1 Shared _isValid As Boolean = False Shared _lastUpdate As String = String.Empty Shared _lastUpdateIsSet As Boolean = False #End Region #Region " Enumerations " Public Enum Selection As Integer AllRecords End Enum #End Region #Region " Properties " Public Shared Property lastName() As String Get Return _lastName End Get Set(ByVal value As String) If Not value = _lastName Then _lastNameIsSet = True _isValid = False _lastName = value End If End Set End Property Public Shared Property firstName() As String Get Return _firstName End Get Set(ByVal value As String) If Not value = _firstName Then _firstNameIsSet = True _isValid = False _firstName = value End If End Set End Property Public Shared ReadOnly Property id() As Integer Get Return _id End Get End Property Public Shared Property lastUpdate() As String Get Return _lastUpdate End Get Set(ByVal value As String) If Not value = _lastUpdate Then _lastUpdateIsSet = True _isValid = False _lastUpdate = value End If End Set End Property #End Region #Region " Static Methods " Public Shared Sub Clear() _lastName = String.Empty _firstName = String.Empty _id = -1 _lastUpdate = String.Empty End Sub Public Shared Function Connect() As IDbConnection Return Ado.Connection(_connectionStringKey) End Function Public Overloads Shared Function Create(ByRef cn As IDbConnection) As Integer If Not lastName = String.Empty Then Dim sql As New StringBuilder sql.Append("INSERT INTO users (") If _lastNameIsSet Then sql.Append("last_name, ") If _firstNameIsSet Then sql.Append("first_name ") sql.Append(") VALUES (") If _lastNameIsSet Then sql.Append("@lastName, ") If _nameIsSet Then sql.Append("@firstName") sql.Append("); ") sql.Append("SELECT @id = scope_identity();") Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString) If _lastNameIsSet Then Parameter(cmd, "@lastName", _lastName) If _firstNameIsSet Then Parameter(cmd, "@firstName", _firstName) Parameter(cmd, "@id", DbType.Int32) Ado.Execute(cmd) _isValid = True Return cmd.Parameters.Item("@id").Value Else HttpContext.Current.Response.Write("ERROR: Cannot create new record without providing the 'Last Name' property.") HttpContext.Current.Response.End() End If End Function Public Overloads Shared Sub Delete(ByRef cn As IDbConnection, ByVal id As Integer) Dim sql As New StringBuilder sql.Append("DELETE ") sql.Append("FROM users ") sql.Append("WHERE ID = @id") sql.Append(";") Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString) Ado.Parameter(cmd, "@id", id) Ado.Execute(cmd) End Sub Public Shared Sub Disconnect(ByRef cn As IDbConnection) Ado.Dispose(cn) End Sub Public Overloads Shared Function Initialize(ByRef cn As IDbConnection, ByVal SelectBy As Selection, Optional ByVal sortOrder As String = "") As DataSet Dim sql As New StringBuilder sql.Append("SELECT ") sql.Append("ID, ") sql.Append("last_name, ") sql.Append("first_name ") sql.Append("FROM users ") 'Select Case SelectBy ' Case Else 'sql.Append(" ") 'End Select If Not sortOrder = String.Empty Then sql.Append(" ORDER BY " & sortOrder) End If sql.Append(";") Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString) 'Select Case SelectBy ' Case ' Case Else 'End Select Dim adpt As IDbDataAdapter = Ado.Adapter(cmd) Dim ds As DataSet = Ado.Dataset(cmd) Return ds End Function Public Overloads Shared Sub Initialize(ByRef cn As IDbConnection, ByVal id As Integer) Dim sql As New StringBuilder sql.Append("SELECT ") sql.Append("last_name, ") sql.Append("first_name, ") sql.Append("last_update ") sql.Append("FROM users ") sql.Append("WHERE (ID = @id);") Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString) Ado.Parameter(cmd, "@id", id) Dim adpt As IDbDataAdapter = Ado.Adapter(cmd) Dim ds As DataSet = Ado.Dataset(cmd) If ds.Tables(0).Rows.Count > 0 Then If Not ds.Tables(0).Rows(0).Item("last_name") Is DBNull.Value Then _lastName = CStr(ds.Tables(0).Rows(0).Item("last_name")) End If If Not ds.Tables(0).Rows(0).Item("first_name") Is DBNull.Value Then _firstName = CStr(ds.Tables(0).Rows(0).Item("first_name")) End If If Not ds.Tables(0).Rows(0).Item("program_ID") Is DBNull.Value Then _id = CInt(ds.Tables(0).Rows(0).Item("program_ID")) End If If Not ds.Tables(0).Rows(0).Item("last_update") Is DBNull.Value Then _lastUpdate = CStr(ds.Tables(0).Rows(0).Item("last_update")) End If _isValid = True End If End Sub Public Overloads Shared Sub Update(ByRef cn As IDbConnection, ByVal id As Integer) Dim sql As New StringBuilder sql.Append("UPDATE program_status SET ") If _lastNameIsSet Then sql.Append("last_name = @lastName, ") If _firstNameIsSet Then sql.Append("first_name = @rirstName, ") sql.Append("last_update = @lastUpdate ") sql.Append("WHERE Program_ID = @id;") Dim cmd As IDbCommand = Ado.Command(cn, sql.ToString) If _aslLastNameIsSet Then Parameter(cmd, "@aslLastName", _aslLastName) If _aslNameIsSet Then Parameter(cmd, "@aslName", _aslName) Parameter(cmd, "@lastUpdate", _lastUpdate) Parameter(cmd, "@id", id) Ado.Execute(cmd) _isValid = True End Sub #End Region End Class End Namespace