dataaccess.vb

ADO.net项目“教师信息管理系统”

Imports System.Data.SqlClient
Public Class DataAccess
    Implements IDAL.IDAL

#Region "定义成员 "
    Shared m_ConnectionString As String = "Data Source =(local); " & _
     "Initial Catalog = 教师信息管理系统;Integrated Security=SSPI "
    '创建一个sqlConnection对象
    Dim m_Connection As New SqlConnection
    Dim m_Command As New SqlCommand
    Dim m_OtherCommand As New SqlCommand
    Dim m_Adapter As New SqlDataAdapter
    Dim m_OtherAdapter As New SqlDataAdapter

#End Region

#Region "构造函数"
    Public Sub New()
        m_Connection.ConnectionString = m_ConnectionString
        '用SqlCommand对象向数据库索取所要的数据
        m_Command.Connection = m_Connection
        m_Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub
#End Region


#Region "定义方法 "
    '填充
    Public Function myFill( _
    ByVal myDataSet As DataSet, _
    ByRef myStoredProcedure As String, _
    ByRef tableName As String, _
    ByVal parameters() As SqlParameter) _
    As Boolean Implements IDAL.IDAL.myFill
        Try
            '用SqlConnection对象和数据库打开连接
            m_Connection.Open()
            '定义Command对象将执行存储过程
            m_Command.CommandType = CommandType.StoredProcedure
            m_Command.CommandText = myStoredProcedure
            '添加命令参数
            If Not parameters Is Nothing Then
                Dim myParameter As SqlParameter
                m_Command.Parameters.Clear()
                For Each myParameter In parameters
                    m_Command.Parameters.Add(myParameter)
                Next
            End If
            '将SqlCommand对象所取回来的数据放在SqlDataAdapter
            m_Adapter.SelectCommand = m_Command
            '把SqlDataAdapter对象的数据，填满DataSet对象
            m_Adapter.Fill(myDataSet, tableName)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message)
            Return False
        Finally
            '关闭SqlConnection对象
            m_Connection.Close()
        End Try
    End Function

    '更新
    Public Function myUpdate( _
    ByVal myDataSet As DataSet, _
    ByRef myStoredProcedure As String, _
    ByRef tableName As String, _
    ByVal parameters() As SqlParameter) _
    As Boolean Implements IDAL.IDAL.myUpdate
        Try
            '用SqlConnection对象和数据库打开连接
            m_Connection.Open()
            '用SqlCommand对象向数据库索取所要的数据
            m_Command.Connection = m_Connection
            '定义Command对象将执行StoredProcedure
            m_Command.CommandType = CommandType.StoredProcedure
            '添加命令参数
            If Not parameters Is Nothing Then
                Dim myParameter As SqlParameter
                m_Command.Parameters.Clear()
                For Each myParameter In parameters
                    m_Command.Parameters.Add(myParameter)
                Next
            End If
            m_Command.CommandText = myStoredProcedure
            '将SqlCommand对象所取回来的数据放在SqlDataAdapter
            m_Adapter.SelectCommand = m_Command
            'CommandBuilder对象为单个表的更新自动生成SQL语句。
            '创建一个CommandBuilder对象
            Dim sCBuilder As New SqlCommandBuilder(m_Adapter)
            '分别为sDAdapter对象的插入、删除和更新命令赋值
            m_Adapter.InsertCommand = sCBuilder.GetInsertCommand
            m_Adapter.DeleteCommand = sCBuilder.GetDeleteCommand
            m_Adapter.UpdateCommand = sCBuilder.GetUpdateCommand
            '把SqlDataAdapter对象的数据，填满DataSet对象
            m_Adapter.Update(myDataSet, tableName)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message)
            Return False
        Finally
            '关闭SqlConnection对象
            m_Connection.Close()
        End Try
    End Function


    '建立连接对象
    Public Function myDataRelation( _
    ByVal myDataSet As DataSet, _
    ByRef parentProcedure As String, _
    ByRef parentTableName As String, _
    ByRef parentColName As String, _
    ByRef childProcedure As String, _
    ByRef childTableName As String, _
    ByRef childColName As String, _
    ByRef relationName As String) _
    As Boolean Implements IDAL.IDAL.myDataRelation
        Try
            '用SqlConnection对象和数据库打开连接
            m_Connection.Open()
            '用SqlCommand对象向数据库索取所要的数据
            m_Command.Connection = m_Connection
            '定义Command对象将执行StoredProcedure
            m_Command.CommandType = CommandType.StoredProcedure
            m_Command.CommandText = parentProcedure
            '将SqlCommand对象所取回来的数据放在SqlDataAdapter
            m_Adapter.SelectCommand = m_Command
            '把SqlDataAdapter对象的数据，填满DataSet对象
            m_Adapter.Fill(myDataSet, parentTableName)
            '用SqlCommand对象向数据库索取所要的数据
            m_OtherCommand.Connection = m_Connection
            '定义Command对象将执行StoredProcedure
            m_OtherCommand.CommandType = CommandType.StoredProcedure
            m_OtherCommand.CommandText = childProcedure
            '将SqlCommand对象所取回来的数据放在SqlDataAdapter
            m_OtherAdapter.SelectCommand = m_OtherCommand
            '把SqlDataAdapter对象的数据，填满DataSet对象
            m_OtherAdapter.Fill(myDataSet, childTableName)
            Dim myRelation As DataRelation
            myRelation = New DataRelation(relationName, _
            myDataSet.Tables(parentTableName).Columns(parentColName), _
            myDataSet.Tables(childTableName).Columns(childColName))
            myDataSet.Relations.Add(myRelation)
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message)
            Return False
        Finally
            '关闭SqlConnection对象
            m_Connection.Close()
        End Try
    End Function

    '有参数数组执行无返回数据命令
    Public Function myExecuteNonQuery( _
    ByRef myStoredProcedure As String, _
    ByVal parameters() As SqlParameter) _
    As Boolean Implements IDAL.IDAL.myExecuteNonQuery
        Try
            '用SqlConnection对象和数据库打开连接
            m_Connection.Open()
            '定义Command对象将执行SQL
            m_Command.CommandType = CommandType.StoredProcedure
            '添加命令参数
            If Not parameters Is Nothing Then
                Dim myParameter As SqlParameter
                m_Command.Parameters.Clear()
                For Each myParameter In parameters
                    m_Command.Parameters.Add(myParameter)
                Next
            End If
            m_Command.CommandText = myStoredProcedure
            m_Command.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Throw New Exception(ex.Message)
            Return False
        Finally
            '关闭SqlConnection对象
            m_Connection.Close()
        End Try
    End Function

#End Region

End Class