statedao.vb

使用Access数据库演示的任务分配管理程序 一个使用ADO.NET基于Microsoft Access数据库演示的任务分配管理的程序

'------------------------------------------------------------------------------
' <auto-generated>
'     This code was generated by a tool.
'     Runtime Version:2.0.50727.42
'
'     Changes to this file may cause incorrect behavior and will be lost if
'     the code is regenerated.
' </auto-generated>
'------------------------------------------------------------------------------

Option Strict Off
Option Explicit On

Imports Common.Business
Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Text

'*************************************************************
' Class Name:  StateDAO
' Purpose:  Data Access Object (DAO)
' Description: Sends and receives data from the database
'***************************************************************
Namespace Database.DataAccess
    
    Public Class StateDAO
        
        Public Sub New()
            MyBase.New
            Me.InitializeClass
        End Sub
        
        Private Sub InitializeClass()
        End Sub
        
        Function GetByPrimaryKey(ByVal StateID As Integer) As State
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [States] WHERE ")
            sql.Append((" [StateID]=" + StateID.ToString))
            Dim db As AccessDb = New AccessDb
            Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            Dim row As System.Data.DataRow = Nothing
            row = dt.Rows(0)
            Dim biz As State = New State
            biz.StateID = dbUtility.NullToZero(row("StateID"))
            biz.Text = dbUtility.NullToString(row("Text"))
            Return biz
        End Function
        
        Overloads Function GetStatesWhere(ByVal WhereSQL As String) As State()
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [States] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            Dim row As System.Data.DataRow = Nothing
            Dim i As Integer
            Dim recordList As System.Collections.ArrayList = New System.Collections.ArrayList
            i = 0
            Do While (i  _
                        <= (dt.Rows.Count - 1))
                row = dt.Rows(i)
                Dim biz As State = New State
                biz.StateID = dbUtility.NullToZero(row("StateID"))
                biz.Text = dbUtility.NullToString(row("Text"))
                recordList.Add(biz)
                i = (i + 1)
            Loop
            Return CType(recordList.ToArray(GetType(State)),State())
        End Function
        
        Function GetStatesTableWhere(ByVal WhereSQL As String) As DataTable
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [States] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            dt.TableName = "States"
            Return dt
        End Function
        
        Overloads Function GetStatesWhere(ByVal args() As Object) As System.Collections.ArrayList
            If (args.Length = 3) Then
                Dim sql As StringBuilder = New StringBuilder
                sql.Append("SELECT * FROM [States] WHERE ")
                sql.Append(String.Format("[States].{0}{1}{2}", args))
                Dim db As AccessDb = New AccessDb
                Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
                db = Nothing
                dt.TableName = "States"
                Dim row As System.Data.DataRow = Nothing
                Dim i As Integer
                Dim recordList As System.Collections.ArrayList = New System.Collections.ArrayList
                i = 0
                Do While (i  _
                            <= (dt.Rows.Count - 1))
                    row = dt.Rows(i)
                    Dim biz As State = New State
                    biz.StateID = dbUtility.NullToZero(row("StateID"))
                    biz.Text = dbUtility.NullToString(row("Text"))
                    recordList.Add(biz)
                    i = (i + 1)
                Loop
                Return recordList
            Else
                Return Nothing
            End If
        End Function
        
        Function GetStatesWhereAndAlso(ByVal args() As Object) As System.Collections.ArrayList
            If (args.Length = 6) Then
                Dim sql As StringBuilder = New StringBuilder
                sql.Append("SELECT * FROM [States] WHERE ")
                sql.Append(String.Format("[States].{0}{1}{2} AND [States].{3}{4}{5}", args))
                Dim db As AccessDb = New AccessDb
                Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
                db = Nothing
                dt.TableName = "States"
                Dim row As System.Data.DataRow = Nothing
                Dim i As Integer
                Dim recordList As System.Collections.ArrayList = New System.Collections.ArrayList
                i = 0
                Do While (i  _
                            <= (dt.Rows.Count - 1))
                    row = dt.Rows(i)
                    Dim biz As State = New State
                    biz.StateID = dbUtility.NullToZero(row("StateID"))
                    biz.Text = dbUtility.NullToString(row("Text"))
                    recordList.Add(biz)
                    i = (i + 1)
                Loop
                Return recordList
            Else
                Return Nothing
            End If
        End Function
        
        Function GetAllStates() As System.Collections.ArrayList
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [States]")
            Dim db As AccessDb = New AccessDb
            Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            Dim row As System.Data.DataRow = Nothing
            Dim i As Integer
            Dim recordList As System.Collections.ArrayList = New System.Collections.ArrayList
            i = 0
            Do While (i  _
                        <= (dt.Rows.Count - 1))
                row = dt.Rows(i)
                Dim biz As State = New State
                biz.StateID = dbUtility.NullToZero(row("StateID"))
                biz.Text = dbUtility.NullToString(row("Text"))
                recordList.Add(biz)
                i = (i + 1)
            Loop
            Return recordList
        End Function
        
        Function Insert(ByVal biz As State) As Integer
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("INSERT INTO [States] ( ")
            sql.Append("[Text] ")
            sql.Append(") VALUES ( ")
            sql.Append((("""" + biz.Text.ToString)  _
                            + """"))
            sql.Append(")")
            Dim db As AccessDb = New AccessDb
            Dim ScalarID As Integer = db.Insert(sql.ToString, true)
            db = Nothing
            Return ScalarID
        End Function
        
        Sub Update(ByVal biz As State)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("UPDATE [States] ")
            sql.Append(" SET ")
            sql.Append(((" [Text]=""" + biz.Text.ToString)  _
                            + """ "))
            sql.Append((" WHERE [StateID]=" + biz.StateID.ToString))
            Dim db As AccessDb = New AccessDb
            db.Update(sql.ToString)
            db = Nothing
        End Sub
        
        Sub Delete(ByVal StateID As Integer)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("DELETE * FROM [States] WHERE ")
            sql.Append((" [StateID]=" + StateID.ToString))
            Dim db As AccessDb = New AccessDb
            db.GetDataTable(sql.ToString)
            db = Nothing
        End Sub
        
        Sub DeleteWhere(ByVal WhereSQL As String)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("DELETE * FROM [States] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            db.GetDataTable(sql.ToString)
            db = Nothing
        End Sub
        
        Function GetTasks(ByVal biz As State) As ArrayList
            'TODO: Define more complex and custom query SQL here. 
            '  ex.: SELECT DISTINCT, RIGHT JOIN, LEFT JOIN, etc...
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Tasks] ")
            sql.Append(" WHERE ")
            sql.Append((" [Tasks].[StateID]=" + biz.StateID.ToString))
            Dim db As AccessDb = New AccessDb
            Dim dt As DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            Dim i As Integer
            Dim recordList As ArrayList = New ArrayList
            i = 0
            Do While (i  _
                        <= (dt.Rows.Count - 1))
                Dim row As Datarow = dt.Rows(i)
                Dim TaskID As Integer = dbUtility.NullToZero(row("TaskID"))
                Dim AssignedID As Integer = dbUtility.NullToZero(row("AssignedID"))
                Dim DateCreated As Date = dbUtility.NullToDate(row("DateCreated"))
                Dim DateDue As Date = dbUtility.NullToDate(row("DateDue"))
                Dim DateModified As Date = dbUtility.NullToDate(row("DateModified"))
                Dim Description As String = dbUtility.NullToString(row("Description"))
                Dim IsDeleted As Boolean = dbUtility.NullToBool(row("IsDeleted"))
                Dim PriorityID As Integer = dbUtility.NullToZero(row("PriorityID"))
                Dim Progress As Double = dbUtility.NullToZero(row("Progress"))
                Dim ProjectID As Integer = dbUtility.NullToZero(row("ProjectID"))
                Dim StateID As Integer = dbUtility.NullToZero(row("StateID"))
                Dim Summary As String = dbUtility.NullToString(row("Summary"))
                Dim UserID As Integer = dbUtility.NullToZero(row("UserID"))
                recordList.Add(New Task(TaskID,AssignedID,DateCreated,DateDue,DateModified,Description,IsDeleted,PriorityID,Progress,ProjectID,StateID,Summary,UserID))
                i = (i + 1)
            Loop
            Return recordList
        End Function
    End Class
End Namespace