要开始开发项目了，在我参加之前，他们都比较关注技术细节，比如曲线，报表之类的，对于整个软件的体系结构基本没有考虑过，这令我比较奇怪。由于我有些java开发大型项目的经验，知道一个好的体系结构对项目开发的重要性。所以开始着手架构问题。
采用分层的体系结构，将用户界面、业务逻辑处理、数据访问、以及一些操作数据库必须的操作分离开来。
Business
Data
EntityClass
WinForm
层次如上所示。具体每一层里面的内容。
一、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=winlog;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
未完待续…………
