现在的位置: 首页 > 综合 > 正文

基于动态数据库的三层体系

2012年05月20日 ⁄ 综合 ⁄ 共 23025字 ⁄ 字号 评论关闭
思路是利用arraylist 和hashtable来实现参数传递。

架构分为Data  实现具体的数据库操作

               EntityClass 对增加删除等操作的封装

              Business  业务逻辑处理层,用户可以自行添加代码

              WinForm    显示界面

一、Data

Data有两个文件,ConnectionFactory.vb和DataFactory.vb

ConnectionFactory.vb实现连接数据库,代码如下

Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace Data
    Public Class ConnectionFactory
        Private Const ConnectionKey As String = "ConnectionString"
        Public Sub New()
            ' TODO: 在此处添加构造函数逻辑
        End Sub
        Public Shared Function myConnection() As System.Data.SqlClient.SqlConnection
            Dim cmConn As New SqlConnection
            Dim strConnection As String = System.Configuration.ConfigurationSettings.GetConfig(ConnectionKey)
            strConnection = "server=200.200.200.101;database=TYTK;user id=sa;password=sa"
            If strConnection <> " " Then

                cmConn.ConnectionString = strConnection
                Return cmConn

            Else
                '应该返回null
                cmConn.Close()
                Return cmConn
            End If
        End Function
        Public Shared Function myConnection(ByVal strConnection As String) As System.Data.SqlClient.SqlConnection
            Dim ConnectionKey1 As String
            If strConnection <> " " Then
                ConnectionKey1 = strConnection
            End If
            Dim cmConn As SqlConnection = New SqlConnection
            strConnection = System.Configuration.ConfigurationSettings.GetConfig(ConnectionKey1)
            If (strConnection <> "") Then

                cmConn.ConnectionString = strConnection
                Return cmConn

            Else
                cmConn.Close()
                Return cmConn
            End If
        End Function

    End Class
End Namespace

DataFactory.vb 实现数据库的底层操作,代码如下

Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace Data

    Public Class DataFactory
        Protected myCommand As SqlCommand
        Protected myTrans As SqlTransaction
        Protected strErrMessage As String
        Protected myConnection As SqlConnection
        Protected myDataAdapter As SqlDataAdapter
        Protected thisTransactionOn As Boolean

        Public Sub New()

            ' TODO: 在此处添加构造函数逻辑

            Me.myConnection = ConnectionFactory.myConnection()
            '   this.myCommand = this.myConnection.CreateCommand();  
            '   this.myDataAdapter = new SqlDataAdapter(this.myCommand);
            'MsgBox("hehe" + Me.myConnection.Database.ToString)
            Me.strErrMessage = ""
            Me.thisTransactionOn = False
            '新增了一个初始化()

            Me.myCommand = New SqlClient.SqlCommand

        End Sub
        '设置SQL语句
        Protected Function SetCommandText(ByVal commandText As String, ByVal commandType As CommandType)
            'MsgBox(Me.myCommand.CommandText)
            'MsgBox(Me.thisTransactionOn)
            If (Me.thisTransactionOn) Then
                'MsgBox(5)
                If (Me.myCommand.ToString.Equals("")) Then

                    Me.myCommand = Me.myConnection.CreateCommand()
                    Me.myCommand.Transaction = Me.myTrans

                ElseIf (Me.myCommand.Connection.State = ConnectionState.Closed) Then

                    Me.myCommand.Connection = Me.myConnection
                    Me.myCommand.Transaction = Me.myTrans

                End If
            ElseIf ((Not Me.thisTransactionOn) And Me.myCommand.CommandText.Equals("")) Then
                'MsgBox(6)
                Me.myCommand = Me.myConnection.CreateCommand()

            End If
            Me.myDataAdapter = New SqlDataAdapter(Me.myCommand)
            Me.myCommand.CommandText = commandText
            Me.myCommand.CommandType = commandType
        End Function

        Protected Function SetCommandText(ByVal commandText As String)

            Me.myCommand.CommandText = commandText
        End Function
        '下面还有各种增加删除等

        'ddddddd

        Private Function ChackTransAndConnect() As Boolean

            If (Me.thisTransactionOn) Then

                If (Me.myConnection.State = ConnectionState.Closed) Then

                    '     this.strErrMessage = "事务连接已断开"
                    '     this.CloseTransation()
                    Try

                        Me.myConnection = Me.myTrans.Connection

                    Catch

                        Return False
                    End Try
                End If

            Else

                If (Me.myConnection.State = ConnectionState.Closed) Then

                    Me.myConnection.Open()
                End If
            End If
            Return True
        End Function

        '执行一条Sql指令,返回一张内存表
        Public Function DataTableResult(ByVal _strSql As String) As DataTable

            Dim tempDataTable As New DataTable
            Try
                If (Not Me.ChackTransAndConnect()) Then
                    Return tempDataTable
                End If
                'MsgBox(_strSql)
                'MsgBox(System.Data.CommandType.Text)
                Me.SetCommandText(_strSql, System.Data.CommandType.Text)
                'MsgBox(tempDataTable.ToString)
                Me.myDataAdapter.Fill(tempDataTable)

            Catch e As SqlException

                strErrMessage = "获取数据表出现未知异常。" + e.Message
                Throw

            Catch ex As Exception

                strErrMessage = ex.Message
                Throw
            Finally
                Me.myCommand.Parameters.Clear()
                If (Not Me.thisTransactionOn) Then
                    If (Me.myConnection.State = ConnectionState.Open) Then
                        Me.myConnection.Close()
                    End If
                End If
            End Try
            Return tempDataTable
        End Function

        ' 读取表中某列的一条记录

        Public Function ReadOneLog(ByVal _strSql As String) As Object

            Dim sResult As New Object
            Try

                If (Not Me.ChackTransAndConnect()) Then
                    Return Nothing
                End If
                Me.SetCommandText(_strSql, System.Data.CommandType.Text)
                Dim myReader As SqlDataReader = Me.myCommand.ExecuteReader()

                If (myReader.Read()) Then

                    sResult = myReader.GetValue(0)
                End If
                myReader.Close()

            Catch e As SqlException

                strErrMessage = "执行命令出现SQL异常。" + e.Message
                Throw

            Catch

                strErrMessage = "执行命令出现未知异常。"
                Throw

            Finally

                If (Not Me.thisTransactionOn) Then
                    If (Me.myConnection.State = ConnectionState.Open) Then
                        Me.myConnection.Close()
                    End If
                End If

            End Try
            Return sResult
        End Function

        ' 执行命令并返回受影响的行数

        Public Function ExecuteNonQuery(ByVal _strSql As String) As Integer

            Dim tempExecuteNonQueryResult As Integer = 0
            Try
                If (Not Me.ChackTransAndConnect()) Then
                    Return 0
                End If

                Me.SetCommandText(_strSql, System.Data.CommandType.Text)
                tempExecuteNonQueryResult = Me.myCommand.ExecuteNonQuery()

            Catch e As SqlException

                strErrMessage = "执行命令出现SQL异常。" + e.Message
                Throw

            Catch ex As Exception

                strErrMessage = ex.Message
                Throw

            Finally

                Me.myCommand.Parameters.Clear()
                Me.myCommand.Dispose()
                If (Not Me.thisTransactionOn) Then
                    If (Me.myConnection.State = ConnectionState.Open) Then
                        Me.myConnection.Close()
                    End If
                End If
            End Try
            Return tempExecuteNonQueryResult
        End Function

        Public Sub AddCommandParameter(ByVal Parameters As SqlParameter)
            Me.myCommand.Parameters.Add(Parameters)
        End Sub
        ' '向Command添加参数
        '</summary>
        ' <param name="ParameterName">参数名</param>
        '<param name="Type">数据类型</param>
        ' <param name="Value">参数值</param>
        ' <returns></returns>
        Public Sub AddCommandParameter(ByVal ParameterName As String, ByVal Type As System.Data.SqlDbType, ByVal Value As Object)

            Dim Parameters As SqlParameter = New SqlParameter
            Parameters.ParameterName = ParameterName
            Parameters.SqlDbType = Type
            Parameters.Value = Value
            Me.myCommand.Parameters.Add(Parameters)
        End Sub

        ' <summary>
        ' 打开事务
        ' </summary>
        Public Function BeginTransation() As Boolean
            If Me.thisTransactionOn Then
                Me.strErrMessage = "当前已有打开的事务"
                Return False
                If Me.myConnection.State = ConnectionState.Closed Then
                    Me.myConnection.Open()
                    Me.myTrans = Me.myConnection.BeginTransaction()
                    Me.myCommand = Me.myConnection.CreateCommand()
                    Me.myCommand.Transaction = Me.myTrans
                    Me.thisTransactionOn = True
                    Return True
                Else
                    Me.strErrMessage = "当前有未关闭的连接"
                    Return False
                    ' <summary>
                    ' 结束事务
                    ' </summary>
                End If
            End If
        End Function
        Public Sub CloseTransation()
            If Not Me.thisTransactionOn Then
                Me.strErrMessage = "当前没有打开的事务"
                If Me.myConnection.State = ConnectionState.Open Then
                    Me.myConnection.Close()
                    Me.myTrans.Dispose()
                    Me.thisTransactionOn = False
                End If
            End If
        End Sub
        ' <summary>
        ' 回滚事务
        '</summary>

        Public Function Rollback() As Boolean
            If Not Me.thisTransactionOn Then
                Me.strErrMessage = "事务尚未打开"
                Return False
                Try
                    Me.myTrans.Commit()
                    '   this.myCommand.Transaction.Rollback();
                    '    this.myCommand.Transaction = null;
                    If Me.myConnection.State = ConnectionState.Open Then
                        Me.myConnection.Close()
                    End If
                Catch ex As Exception
                    strErrMessage = "回滚事务失败:" + ex.Message
                    Return False
                Finally
                    Me.CloseTransation()
                End Try
            End If
            Return True

        End Function

        '<summary>
        '提交事务
        '</summary>
        Public Function Commit() As Boolean
            If Not Me.thisTransactionOn Then
                Me.strErrMessage = "事务尚未打开"
                Return False
                Try
                    Me.myTrans.Commit()
                    '    me.myCommand.Transaction.Commit()
                    '    this.myCommand.Transaction = null
                    If Me.myConnection.State = ConnectionState.Open Then
                        Me.myConnection.Close()
                    End If
                Catch ex As Exception
                    strErrMessage = "提交事务失败:" + ex.Message
                    Return False
                Finally
                    Me.CloseTransation()
                End Try
                Return True
            End If
            ' <summary>
            ' 根据事务状态决定是否打开连接,并判断操作是否可以继续
            ' </summary>
            ' <returns>true表示可以继续执行,false表示错误,中断操作</returns>
        End Function

    
      

        ' <summary>
        '执行命令并返回受影响的行数
        ' </summary>
        '<returns>受影响的行数</returns>
        Public Function ExecuteNonQuery(ByVal _strSql As String, ByVal Parameters As SqlParameter) As Integer
            Dim tempExecuteNonQueryResult As Integer = 0
            Try
                If Not Me.ChackTransAndConnect() Then
                    Return 0
                End If
                Me.SetCommandText(_strSql, System.Data.CommandType.Text)
                Me.AddCommandParameter(Parameters)

                tempExecuteNonQueryResult = Me.myCommand.ExecuteNonQuery()
            Catch e As Exception
                strErrMessage = "执行命令出现SQL异常。" + e.Message
                Throw
            Catch
                strErrMessage = "执行命令出现未知异常。"
                Throw
            Finally
                Me.myCommand.Parameters.Clear()
                If Not Me.thisTransactionOn Then
                    If Me.myConnection.State = ConnectionState.Open Then
                        Me.myConnection.Close()
                    End If
                End If
            End Try
            Return tempExecuteNonQueryResult
        End Function

        '<summary>
        ' 执行命令并返回最大流水号
        ' </summary>
        '<returns>最大流水号,-1表示执行错误</returns>
        Public Function ExecuteOnReader(ByVal _strSql As String) As Integer
            Dim tempExecuteReaderResult As Integer = -1
            Try
                If Not Me.ChackTransAndConnect() Then
                    Return tempExecuteReaderResult
                End If
                Me.SetCommandText(_strSql, System.Data.CommandType.Text)
                Dim myreader As SqlDataReader = Me.myCommand.ExecuteReader()
                If myReader.Read() Then
                    tempExecuteReaderResult = Convert.ToInt32(myreader.GetValue(0))
                End If
            Catch e As SqlException
                strErrMessage = "执行命令出现SQL异常。" + e.Message
                Throw
            Catch ex As Exception
                strErrMessage = ex.Message
                Throw
            Finally
                Me.myCommand.Parameters.Clear()
                If Not Me.thisTransactionOn Then
                    If Me.myConnection.State = ConnectionState.Open Then
                        Me.myConnection.Close()
                    End If
                End If
            End Try
            Return tempExecuteReaderResult
        End Function
    
        ' <summary>
        ' 填充Dataset
        ' </summary>
        ' <param name="dataset"></param>
        ' <param name="connection"></param>
        ' <param name="query"></param>
        ' <returns></returns>
        Public Sub SelectSqlSrvRows(ByVal dataset As DataSet, ByVal query As String)
            Try
                If Not Me.ChackTransAndConnect() Then
                    Return
                End If
                Me.SetCommandText(query, System.Data.CommandType.Text)
                Me.myDataAdapter.Fill(dataset)
            Catch e As SqlException
                strErrMessage = "获取数据表出现未知异常。" + e.Message
                Throw
            Catch
                strErrMessage = "获取数据表出现未知异常。"
                Throw
            Finally
                Me.myCommand.Parameters.Clear()
                If Not Me.thisTransactionOn Then
                    If Me.myConnection.State = ConnectionState.Open Then
                        Me.myConnection.Close()
                    End If
                End If
            End Try

        End Sub

        ' public SqlConnection ConnectionObj
        '  {
        '   set
        ''  {
        '    me.myConnection = value;
        '   }
        '  get
        '   {
        '    return me.myConnection;
        '  }
        ' }
        Public Function set_TransStatus(ByVal value As Boolean) As Boolean
            Me.thisTransactionOn = value
        End Function
        Public Function get_TransStatus() As Boolean
            Return Me.thisTransactionOn
        End Function

        Public Function set_TransObj(ByVal value As SqlTransaction) As SqlTransaction
            Me.myTrans = value
        End Function
        Public Function get_TransObj(ByVal value As SqlTransaction) As SqlTransaction
            Return Me.myTrans
        End Function

    End Class
End Namespace

二、EntityClass完成对数据库表的访问操作,根据传进来的表名,包含一个基础文件,BaseEntity.vb,代码如下:

Imports System.Data
Namespace EntityClass
    Public Class BaseEntity
        Public strSql As String
        Public database As Data.DataFactory
        Public dt As DataTable

        Const ARRAYLENGTH = 100

        Dim columnarray(ARRAYLENGTH) As String
        Dim columnarraylen As Integer = 0
        Private TABLE_NAME As String
        Private mydataset As New DataSet

        ''构造函数
        'Public Sub New()
        '    Me.database = New Data.DataFactory
        '    Me.dt = New DataTable
        'End Sub
        Public Sub New(ByVal tablename As String)
            Me.database = New Data.DataFactory
            Me.dt = New DataTable
            TABLE_NAME = tablename
            SetDBColoum()
        End Sub
        '得到对应数据库中的表的信息
        Public Sub LoadDynamicDataset(ByRef dataset As DataSet)
            strSql = "select * from " + TABLE_NAME + " where ID_I=0"
            database.SelectSqlSrvRows(dataset, strSql)
        End Sub
        '通过读取数据库中的列名,对array1赋值
        Private Sub SetDBColoum()
            LoadDynamicDataset(mydataset)
            Dim m_i As Integer = 0

            columnarraylen = Me.mydataset.Tables.Item(0).Columns.Count

            ReDim columnarray(columnarraylen)

            For m_i = 0 To columnarraylen - 1
                columnarray(m_i) = Me.mydataset.Tables.Item(0).Columns(m_i).ColumnName.ToString
            Next

        End Sub
        '利用已有的hashtable对新的hashtable赋值
        Public Function setEntity(ByRef newhashtable As Hashtable, ByVal oldhashtable As Hashtable) As Integer

            For Each de As DictionaryEntry In oldhashtable
                newhashtable.Add(de.Key, de.Value)
            Next
        End Function

        ''根据sql语句查询
        'Public Function GetObjByQry(ByVal str As String) As ArrayList
        '    Dim myarralist As New ArrayList

        '    strSql = "select * from " + TABLE_NAME + str
        '    Dim dt As DataTable = database.DataTableResult(strSql)

        '    If (dt.Rows.Count > 0) Then
        '        For m_ai As Integer = 0 To dt.Rows.Count() - 1
        '            Dim myhashtable As New Hashtable
        '            For m_hi As Integer = 0 To columnarraylen - 1
        '                myhashtable.Add(columnarray(m_hi), dt.Rows(m_ai)(columnarray(m_hi)))
        '            Next
        '            myarralist.Add(myhashtable)
        '            'myhashtable.Clear()
        '        Next
        '        Return myarralist
        '    Else
        '        Return Nothing
        '    End If
        'End Function

        '根据sql语句查询
        Public Function GetObjByQry(ByVal str As String, ByRef myarralist As ArrayList)

            strSql = "select * from " + TABLE_NAME + str
            Dim dt As DataTable = database.DataTableResult(strSql)

            If (dt.Rows.Count > 0) Then
                For m_ai As Integer = 0 To dt.Rows.Count() - 1
                    Dim myhashtable As New Hashtable
                    For m_hi As Integer = 0 To columnarraylen - 1
                        myhashtable.Add(columnarray(m_hi), dt.Rows(m_ai)(columnarray(m_hi)))
                    Next
                    myarralist.Add(myhashtable)
                Next

            Else

            End If
        End Function
        '新建
        Public Shadows Function Creat(ByVal myhashtable As Hashtable) As Integer

            If myhashtable.ContainsKey("Id_I") Then
                myhashtable.Remove("Id_I")
            End If

            strSql = "insert into " + TABLE_NAME + " ("
            For Each de As DictionaryEntry In myhashtable
                strSql = strSql + de.Key + ","
            Next
            strSql = strSql.Substring(0, strSql.Length - 1)
            strSql = strSql + ") values ('"

            For Each de As DictionaryEntry In myhashtable
                strSql = strSql + de.Value + "','"
            Next
            strSql = strSql.Substring(0, strSql.Length - 2)
            strSql = strSql + ")"

            Return database.ExecuteNonQuery(strSql)

        End Function
        ''更新
        Public Shadows Function Updata(ByVal myhashtable As Hashtable) As Integer

            Dim ID_I As String = myhashtable.Item("ID_I")
            'Dim ID_I As String = myhashtable.Item("Id_I")
            If myhashtable.ContainsKey("Id_I") Then
                myhashtable.Remove("Id_I")
            End If

            strSql = "update " + TABLE_NAME + " set "
            For Each de As DictionaryEntry In myhashtable
                strSql = strSql + de.Key + " ='" + de.Value + "',"
            Next
            strSql = strSql.Substring(0, strSql.Length - 1)
            strSql = strSql + " where ID_I=" + ID_I

            Return database.ExecuteNonQuery(strSql)
        End Function
        '删除
        Public Shadows Function Delete(ByVal myhashtable As Hashtable) As Integer
            Dim ID_I As String = myhashtable.Item("ID_I")
            'Dim ID_I As String = myhashtable.Item("Id_I")

            strSql = "delete from " + TABLE_NAME + " where ID_I='" + ID_I + "'"
            Return database.ExecuteNonQuery(strSql)
        End Function
        '得到数据库中的列名,返回值为一数组
        Public Function GetColumnArray() As String()
            Return columnarray
        End Function
        '得到数据库中的列名,返回值为一数组
        Public Function GetColumnHashTable() As Hashtable
            Dim myhashtable As Hashtable

        End Function
        'Public Function GetDataOnTable() As DataTable

        '    Me.dt = Me.database.DataTableResult(Me.strSql)
        '    Return dt
        'End Function

        'Public Function GetOneObj() As Object

        '    Return Me.database.ReadOneLog(Me.strSql)
        'End Function

        'Public Function ExecuteSql() As Integer
        '    Return Me.database.ExecuteNonQuery(Me.strSql)

        'End Function
    End Class
End Namespace

三、Business层完成业务逻辑,主要实现业务逻辑以及为上层提供接口,包含一个最基本的文件BaseControl.vb,可以根据用户的需求添加自己的Control,要继承BaseControl,其代码如下:

Namespace Business
    Public Class BaseControl
        Private TABLE_NAME As String
        Dim entity As EntityClass.BaseEntity

        Public Sub New(ByVal tablename As String)
            MyBase.New()
            TABLE_NAME = tablename
            entity = New EntityClass.BaseEntity(TABLE_NAME)
        End Sub
        'Dim

        ''根据查询语句得到一个list,里面的每一项都是一个hashtable
        'Public Function GetObjByQry(ByVal str As String) As ArrayList

        '    Try
        '        Dim myarraylist As ArrayList = entity.GetObjByQry(str)
        '        If Not myarraylist Is Nothing Then
        '            Return myarraylist
        '        Else
        '            Return Nothing
        '        End If

        '    Catch ex As Exception
        '        MsgBox(ex.ToString)
        '    End Try

        'End Function

        '根据查询语句得到一个list,里面的每一项都是一个hashtable
        Public Function GetObjByQry(ByVal str As String, ByRef myarraylist As ArrayList)

            Try
                entity.GetObjByQry(str, myarraylist)
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End Function
        '增加
        Public Function AddObj(ByVal myhashtable As Hashtable)
            Try
                entity.Creat(myhashtable)
            Catch ex As Exception

            End Try
        End Function
        '更新
        Public Function UpdateObj(ByVal myhashtable As Hashtable)
            Try
                entity.Updata(myhashtable)
            Catch ex As Exception

            End Try
        End Function
        '删除
        Public Function DelObj(ByVal myhashtable As Hashtable)

            Try
                entity.Delete(myhashtable)
            Catch ex As Exception

            End Try
        End Function

    End Class
End Namespace

对于用户自己实现的Control文件,例如LogInControl,其基本的代码如下:

Namespace Business.LogIn
    Public Class LogInControl
        Inherits Business.BaseControl

        Public Sub New(ByVal tablename As String)
            MyBase.New(tablename)
        End Sub

    End Class
End Namespace

四、WinForm层

这层主要实现用户的界面方面,这里举一个例子,LogIn.vb

 Private Sub B_LOGIN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_LOGIN.Click
             Dim userlist As New ArrayList
        ' userlist = loginctrl.GetObjByQry(" where USER_NAME_S='" + Me.T_NAME.Text + "' and PASSWORD_S='" + Me.T_PASSWORD.Text + "'")
        loginctrl.GetObjByQry(" where USER_NAME_S='" + Me.T_NAME.Text + "' and PASSWORD_S='" + Me.T_PASSWORD.Text + "'", userlist)
        If Not userlist Is Nothing And userlist.Count > 0 Then
            '写系统运行日志
            Dim loghashtable As New Hashtable
            loghashtable.Add("IO_STATUS_S", "1")
            loghashtable.Add("LOGIN_ID_S", Me.T_NAME.Text)
            loghashtable.Add("CREATE_D", dt.Now)
            sys_run_log_ctrl.AddObj(loghashtable)
                    '进入主界面
            myForm.LogInUser = Me.T_NAME.Text
            myForm.LogForm = Me.ActiveForm
            Dim mainform As New WinForm.Main.MainWindow

            myForm.MainForm = mainform
            Me.Hide()

            mainform.Show()
        Else
            Dim loghashtable As New Hashtable
            loghashtable.Add("IO_STATUS_S", "0")
            loghashtable.Add("LOGIN_ID_S", Me.T_NAME.Text)
            loghashtable.Add("CREATE_D", dt.Now)
            sys_run_log_ctrl.AddObj(loghashtable)
            MsgBox("wrong user!")
        End If
     
    End Sub

总结:采用了这种架构之后,可以实现只要在声明Control时,将数据库中对应表的表名传递进去就可以了,这和以前文章中所述的架构相比,不用对每个表都要生成Entity和Parameter和Limits,而且现在的Control由于继承了 基类,所以也相对简单了。

抱歉!评论已关闭.