projectdao.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:  ProjectDAO
' Purpose:  Data Access Object (DAO)
' Description: Sends and receives data from the database
'***************************************************************
Namespace Database.DataAccess
    
    Public Class ProjectDAO
        
        Public Sub New()
            MyBase.New
            Me.InitializeClass
        End Sub
        
        Private Sub InitializeClass()
        End Sub
        
        Function GetByPrimaryKey(ByVal ProjectID As Integer) As Project
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Projects] WHERE ")
            sql.Append((" [ProjectID]=" + ProjectID.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 Project = New Project
            biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
            biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
            biz.Description = dbUtility.NullToString(row("Description"))
            biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
            biz.Name = dbUtility.NullToString(row("Name"))
            Return biz
        End Function
        
        Overloads Function GetProjectsWhere(ByVal WhereSQL As String) As Project()
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Projects] 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 Project = New Project
                biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                biz.Description = dbUtility.NullToString(row("Description"))
                biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                biz.Name = dbUtility.NullToString(row("Name"))
                recordList.Add(biz)
                i = (i + 1)
            Loop
            Return CType(recordList.ToArray(GetType(Project)),Project())
        End Function
        
        Function GetProjectsTableWhere(ByVal WhereSQL As String) As DataTable
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Projects] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
            db = Nothing
            dt.TableName = "Projects"
            Return dt
        End Function
        
        Overloads Function GetProjectsWhere(ByVal args() As Object) As System.Collections.ArrayList
            If (args.Length = 3) Then
                Dim sql As StringBuilder = New StringBuilder
                sql.Append("SELECT * FROM [Projects] WHERE ")
                sql.Append(String.Format("[Projects].{0}{1}{2}", args))
                Dim db As AccessDb = New AccessDb
                Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
                db = Nothing
                dt.TableName = "Projects"
                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 Project = New Project
                    biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                    biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                    biz.Description = dbUtility.NullToString(row("Description"))
                    biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                    biz.Name = dbUtility.NullToString(row("Name"))
                    recordList.Add(biz)
                    i = (i + 1)
                Loop
                Return recordList
            Else
                Return Nothing
            End If
        End Function
        
        Function GetProjectsWhereAndAlso(ByVal args() As Object) As System.Collections.ArrayList
            If (args.Length = 6) Then
                Dim sql As StringBuilder = New StringBuilder
                sql.Append("SELECT * FROM [Projects] WHERE ")
                sql.Append(String.Format("[Projects].{0}{1}{2} AND [Projects].{3}{4}{5}", args))
                Dim db As AccessDb = New AccessDb
                Dim dt As System.Data.DataTable = db.GetDataTable(sql.ToString)
                db = Nothing
                dt.TableName = "Projects"
                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 Project = New Project
                    biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                    biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                    biz.Description = dbUtility.NullToString(row("Description"))
                    biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                    biz.Name = dbUtility.NullToString(row("Name"))
                    recordList.Add(biz)
                    i = (i + 1)
                Loop
                Return recordList
            Else
                Return Nothing
            End If
        End Function
        
        Function GetAllProjects() As System.Collections.ArrayList
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Projects]")
            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 Project = New Project
                biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                biz.Description = dbUtility.NullToString(row("Description"))
                biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                biz.Name = dbUtility.NullToString(row("Name"))
                recordList.Add(biz)
                i = (i + 1)
            Loop
            Return recordList
        End Function
        
        Function Insert(ByVal biz As Project) As Integer
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("INSERT INTO [Projects] ( ")
            sql.Append("[DateCreated], ")
            sql.Append("[Description], ")
            sql.Append("[IsDeleted], ")
            sql.Append("[Name] ")
            sql.Append(") VALUES ( ")
            sql.Append((("'" + biz.DateCreated.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("""" + biz.Description.ToString)  _
                            + """"))
            sql.Append(", ")
            sql.Append((("" + biz.IsDeleted.ToString)  _
                            + ""))
            sql.Append(", ")
            sql.Append((("""" + biz.Name.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 Project)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("UPDATE [Projects] ")
            sql.Append(" SET ")
            sql.Append(((" [DateCreated]='" + biz.DateCreated.ToString)  _
                            + "', "))
            sql.Append(((" [Description]=""" + biz.Description.ToString)  _
                            + """, "))
            sql.Append(((" [IsDeleted]=" + biz.IsDeleted.ToString)  _
                            + ", "))
            sql.Append(((" [Name]=""" + biz.Name.ToString)  _
                            + """ "))
            sql.Append((" WHERE [ProjectID]=" + biz.ProjectID.ToString))
            Dim db As AccessDb = New AccessDb
            db.Update(sql.ToString)
            db = Nothing
        End Sub
        
        Sub Delete(ByVal ProjectID As Integer)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("DELETE * FROM [Projects] WHERE ")
            sql.Append((" [ProjectID]=" + ProjectID.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 [Projects] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            db.GetDataTable(sql.ToString)
            db = Nothing
        End Sub
        
        Function GetTasks(ByVal biz As Project) 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].[ProjectID]=" + biz.ProjectID.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