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