taskdao.vb

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

                    biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                    biz.DateDue = dbUtility.NullToDate(row("DateDue"))
                    biz.DateModified = dbUtility.NullToDate(row("DateModified"))
                    biz.Description = dbUtility.NullToString(row("Description"))
                    biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                    biz.PriorityID = dbUtility.NullToZero(row("PriorityID"))
                    biz.Progress = dbUtility.NullToZero(row("Progress"))
                    biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                    biz.StateID = dbUtility.NullToZero(row("StateID"))
                    biz.Summary = dbUtility.NullToString(row("Summary"))
                    biz.UserID = dbUtility.NullToZero(row("UserID"))
                    recordList.Add(biz)
                    i = (i + 1)
                Loop
                Return recordList
            Else
                Return Nothing
            End If
        End Function
        
        Function GetAllTasks() As System.Collections.ArrayList
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("SELECT * FROM [Tasks]")
            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 Task = New Task
                biz.TaskID = dbUtility.NullToZero(row("TaskID"))
                biz.AssignedID = dbUtility.NullToZero(row("AssignedID"))
                biz.DateCreated = dbUtility.NullToDate(row("DateCreated"))
                biz.DateDue = dbUtility.NullToDate(row("DateDue"))
                biz.DateModified = dbUtility.NullToDate(row("DateModified"))
                biz.Description = dbUtility.NullToString(row("Description"))
                biz.IsDeleted = dbUtility.NullToBool(row("IsDeleted"))
                biz.PriorityID = dbUtility.NullToZero(row("PriorityID"))
                biz.Progress = dbUtility.NullToZero(row("Progress"))
                biz.ProjectID = dbUtility.NullToZero(row("ProjectID"))
                biz.StateID = dbUtility.NullToZero(row("StateID"))
                biz.Summary = dbUtility.NullToString(row("Summary"))
                biz.UserID = dbUtility.NullToZero(row("UserID"))
                recordList.Add(biz)
                i = (i + 1)
            Loop
            Return recordList
        End Function
        
        Function Insert(ByVal biz As Task) As Integer
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("INSERT INTO [Tasks] ( ")
            sql.Append("[AssignedID], ")
            sql.Append("[DateCreated], ")
            sql.Append("[DateDue], ")
            sql.Append("[DateModified], ")
            sql.Append("[Description], ")
            sql.Append("[IsDeleted], ")
            sql.Append("[PriorityID], ")
            sql.Append("[Progress], ")
            sql.Append("[ProjectID], ")
            sql.Append("[StateID], ")
            sql.Append("[Summary], ")
            sql.Append("[UserID] ")
            sql.Append(") VALUES ( ")
            sql.Append((("'" + biz.AssignedID.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.DateCreated.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.DateDue.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.DateModified.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("""" + biz.Description.ToString)  _
                            + """"))
            sql.Append(", ")
            sql.Append((("" + biz.IsDeleted.ToString)  _
                            + ""))
            sql.Append(", ")
            sql.Append((("'" + biz.PriorityID.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.Progress.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.ProjectID.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("'" + biz.StateID.ToString)  _
                            + "'"))
            sql.Append(", ")
            sql.Append((("""" + biz.Summary.ToString)  _
                            + """"))
            sql.Append(", ")
            sql.Append((("'" + biz.UserID.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 Task)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("UPDATE [Tasks] ")
            sql.Append(" SET ")
            sql.Append(((" [AssignedID]='" + biz.AssignedID.ToString)  _
                            + "', "))
            sql.Append(((" [DateCreated]='" + biz.DateCreated.ToString)  _
                            + "', "))
            sql.Append(((" [DateDue]='" + biz.DateDue.ToString)  _
                            + "', "))
            sql.Append(((" [DateModified]='" + biz.DateModified.ToString)  _
                            + "', "))
            sql.Append(((" [Description]=""" + biz.Description.ToString)  _
                            + """, "))
            sql.Append(((" [IsDeleted]=" + biz.IsDeleted.ToString)  _
                            + ", "))
            sql.Append(((" [PriorityID]='" + biz.PriorityID.ToString)  _
                            + "', "))
            sql.Append(((" [Progress]='" + biz.Progress.ToString)  _
                            + "', "))
            sql.Append(((" [ProjectID]='" + biz.ProjectID.ToString)  _
                            + "', "))
            sql.Append(((" [StateID]='" + biz.StateID.ToString)  _
                            + "', "))
            sql.Append(((" [Summary]=""" + biz.Summary.ToString)  _
                            + """, "))
            sql.Append(((" [UserID]='" + biz.UserID.ToString)  _
                            + "' "))
            sql.Append((" WHERE [TaskID]=" + biz.TaskID.ToString))
            Dim db As AccessDb = New AccessDb
            db.Update(sql.ToString)
            db = Nothing
        End Sub
        
        Sub Delete(ByVal TaskID As Integer)
            Dim sql As StringBuilder = New StringBuilder
            sql.Append("DELETE * FROM [Tasks] WHERE ")
            sql.Append((" [TaskID]=" + TaskID.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 [Tasks] WHERE ")
            sql.Append(WhereSQL)
            Dim db As AccessDb = New AccessDb
            db.GetDataTable(sql.ToString)
            db = Nothing
        End Sub
    End Class
End Namespace