本来通宇的项目都要做完了，可是今天公司的老板忽然又提出了新的要求，说要实现完全地动态建库，连一开始的总的完全表都没有，这就使得我们的整个项目的体系都要改。再前面的文章中，我曾经写过项目本来的架构，那个架构是基于一个固定的完全表的，然后以后再从完全表中选出字段来进行动态建库。
搞得我们所有人都非常郁闷，不过也没办法，还是要改。我考虑了一下，觉得也还是可以做的，可以封装好，实现三层架构。思路是利用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和Ｐａｒａｍｅｔｅｒ和Ｌｉｍｉｔｓ，而且现在的Ｃｏｎｔｒｏｌ由于继承了基类，所以也相对简单了。
