在上篇文章写到我们为什么要分层.有很多读者提出来很多宝贵的意见.让我受益匪浅,深深的感觉到自己的水平"还有很大的提升空间".首先感谢这些朋友们,我会进一步总结完善自己的想法.
截取了部分朋友的留言,感谢他们:
这次我用对比的方式描述一下,分层到底分出了什么.俗话说:有分必有合,那么它是把什么合到了一起.
首先写出两个没有分层的demo:
<1>查询信息demo
1: Public Class Form2
2: Private sqlCon As String = "Data Source=LSH;Initial Catalog=ComputerLab;User ID=sa;Password=123456"
3: '查询数据库信息信息
4: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
5:
6: Dim conStr As New SqlConnection '数据库连接对象
7: Dim sqlCom As SqlCommand '数据库执行对象
8: Dim Res As DialogResult '消息提示框返回类型
9: Dim dr As SqlDataReader 'dataReader对象
10: Dim dt As New DataTable 'Datatable对象
11:
12: Dim sql As String = "select * from TableName where Name=@name" 'sql插入语句
13: conStr.ConnectionString = sqlCon '给数据库连接对象赋值
14: sqlCom = New SqlCommand(sql, conStr) '给数据库执行对象赋值
15: sqlCom.Parameters.Add("@name", SqlDbType.VarChar, TextBox1.Text) '给sql语句参数赋值 16:
17: Res = MessageBox.Show("是否添加", "提示", MessageBoxButtons.OKCancel) 18: '判断是否查询
19: If Res = DialogResult.Yes Then
20: Try
21: conStr.Open()
22: dr = sqlCom.ExecuteReader '执行查询语句
23: dt.Load(dr)
24: Catch ex As Exception
25: Throw ex
26: Finally
27: If Not IsNothing(conStr) Then '如果数据库打开,则关闭数据库
28: conStr.Close()
29: End If
30: End Try
31: End If
32:
33: MsgBox(dt.Rows.Count) '显示查询到的行数
34: End Sub
<2>添加信息demo
1: Public Class Form1
2: Private sqlCon As String = "Data Source=LSH;Initial Catalog=ComputerLab;User ID=sa;Password=123456"
3: '向数据库添加信息
4: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
5: Dim bln As Boolean = False '存储返回值
6: Dim conStr As New SqlConnection '数据库连接对象
7: Dim sqlCom As SqlCommand '数据库执行对象
8: Dim Res As DialogResult '消息提示框返回类型
9:
10: Dim sql As String = "insert into TableName(Name) value(@Name)" 'sql插入语句
11: conStr.ConnectionString = sqlCon '给数据库连接对象赋值
12: sqlCom = New SqlCommand(sql, conStr) '给数据库执行对象赋值
13: sqlCom.Parameters.Add("@Name", SqlDbType.VarChar, TextBox1.Text) '给sql语句参数赋值 14:
15: Res = MessageBox.Show("是否添加", "提示", MessageBoxButtons.OKCancel) 16: '判断是否同意插入
17: If Res = DialogResult.Yes Then
18: Try
19: conStr.Open()
20: bln = sqlCom.ExecuteNonQuery '执行插入语句
21: Catch ex As Exception
22: bln = False
23: Finally
24: If Not IsNothing(conStr) Then '如果数据库打开,则关闭数据库
25: conStr.Close()
26: End If
27: End Try
28: End If
29:
30: MsgBox(bln) '提示返回信息
31: End Sub
32: End Class
上面的两个没有分层的代码,当然在功能的实现上是完全没有问题.但是这样设计带来了很多的危机:
(1):写这个模块的人必须是一个能力非常强的人,因为他需要操作数据库,理解业务逻辑
(2):如果有另为一个模块需要这两个操作数据库的方法,只能重写或者调用该窗体下的方法.
(3):如果业务逻辑稍有变动,就需要拆开这个模块,重新编写,对于重新设计的人来说这个模块的所有设计都是可见的,很是不安全.
(4):如果要更换或者改动数据库,结果就是每一个模块都要重新编写
(5):如果有另为一个和这个相似的工程,想要复用以前的东西,难度很大
对于一个公司来说,当然不希望发生这样的事情.所以,合理的分层,降低系统的耦合性是必要的.
第一步:把数据库操作提取出来.
对每一个表,把完全对他的操作单独写成类也就是我们的DAO,数据访问类
每一个表对应一个DAO,每个DAO里面包含该数据库的所有增,删,查,改操作.这样做的好处就是对数据库的操作完全独立,达到功能单一.降低了和其他模块的耦合,写代码的时候,程序员不需要了解业务逻辑.
而且,在维护上和扩展上,如果对数据库改动,就会有针对性的去修改DAO.
当然,在设计的时候,我们尽量去封装不变的操作,这样不但能减少代码量,而且能达到很好的复用效果.在数据库操作中,对于数据库的打开,执行,关闭基本上都是一样的.所以我们单独提取出来sqlHelper(数据库助手类)
1: Imports System.Data.SqlClient2: Imports System.Configuration3:4: '''5: ''' 运行数据库6: '''7: Public Class SqlHelp8: Private cmd As SqlCommand 'sqlcommand对象9: Private con As SqlConnection 'sqlconnection对象10: Private dr As SqlDataReader 'sqlDataReader对象11: '''12: ''' 获取连接字符串13: '''14: '''连接字符串 15: '''16: Public Function GetCon() As SqlConnection17: Dim conStr As String18: conStr = System.Configuration.ConfigurationManager.AppSettings("ConnStr")19:20: con = New SqlConnection21: con.ConnectionString = conStr22:23: '打开数据库24: If (con.State = ConnectionState.Closed) Then25: con.Open()26: End If27:28: Return con29:30: End Function31:32: '''33: ''' 增 删 改方法34: '''35: ''' 数据库语句 活存储过程名36: ''' 参数数组37: ''' 数据库字符串 或者存储过程名 类型38: Public Function ExecuteNonQuery(ByVal sqlStr As String, ByVal Param() As SqlParameter, ByVal commandType As CommandType) As Boolean39:40: cmd = New SqlCommand(sqlStr, Me.GetCon)41: cmd.CommandType = commandType42:43: '添加参数44: If Param IsNot Nothing Then45: cmd.Parameters.AddRange(Param)46: End If47:48: '执行语句49: Try50: Return CBool(cmd.ExecuteNonQuery)51: Catch ex As Exception52: Return False53: Finally54: If Not IsNothing(con) Then55: con.Close()56: End If57: End Try58:59: End Function60:61: '''62: ''' 查询63: '''64: ''' sql语句 或者存储过程名65: ''' 参数数组66: ''' 执行类型67: Public Function ExecuteQuery(ByVal sqlStr As String, ByVal Param() As SqlParameter, ByVal commandType As CommandType) As DataTable68: Dim dt As New DataTable69: cmd = New SqlCommand(sqlStr, Me.GetCon)70: cmd.CommandType = commandType71:72: '添加参数73: If Param IsNot Nothing Then74: cmd.Parameters.AddRange(Param)75: End If76:77: '执行语句78: Try79: dr = cmd.ExecuteReader80: dt.Load(dr)81: Catch ex As Exception82: Return Nothing83: Finally84: If Not IsNothing(con) Then85: con.Close()86: End If87: End Try88: Return dt89: End Function提取后数据库访问层代码如下:1: Imports SqlHelper2: Imports System.Data.SqlClient3: Public Class DAL4: Private sqlHelper As New SqlHelp5: '''6: ''' 查询记录7: '''8: ''' 关键字9: '''记录集 10: '''11: Public Function SelectInfo(ByVal strSelect As String) As DataTable12: 'sql查询语句13: Dim sql As String = "select * from TableName where Name=@name"14: '向sql参数中添加实参15: Dim Para() As SqlParameter = { New SqlParameter("@name", strSelect)}16: '调用sqlhelper执行数据库操作17: Return sqlHelper.ExecuteQuery(sql, Para, Data.CommandType.Text)18: End Function19: '''20: ''' 添加记录21: '''22: ''' 记录信息23: '''是否成功 24: '''25: Public Function AddInfo(ByVal strAdd As String) As Boolean26: 'sql插入语句27: Dim sql As String = "insert into TableName(Name) value(@Name)"28: '向sql参数中添加实参29: Dim Para() As SqlParameter = { New SqlParameter("@name", strAdd)}30: '调用sqlhelper执行数据库操作31: Return sqlHelper.ExecuteNonQuery(sql, Para, Data.CommandType.Text)32: End Function33: End Class
第二:根据业务逻辑,个人习惯和用例相对应,把相关的逻辑判断,比如添加记录在什么情况下判断,添加前要判断记录是否存在等放到BLL层.
但是对于数据的有效性校验一般还是放在UI层,也可以有其他更好的方法,这些我在以后会总结出来.
代码如下:
1: Public Class BLL2: Private dal As New DAL3: '''4: ''' 业务逻辑层,添加方法5: '''6: ''' 添加信息7: '''8: Public Sub Add(ByVal strAdd As String)9: '定义用于存储查询结果的记录集10: Dim dt As New DataTable11: '执行查询,判断添加的信息是否已经存在12: dt = dal.SelectInfo(strAdd)13: If dt.Rows.Count > 0 Then14: Throw New Exception("记录已存在")15: Else16: '添加记录,返回是否成功17: If dal.AddInfo(strAdd) = True Then18: Throw New Exception("添加成功")19: Else20: Throw New Exception("添加失败")21: End If22: End If23:24: End Sub25:26: End Class第三:剩下的UI层里,除了判断数据有效性以为,只要调用bll的添加方法就可以了.这样制作UI的人就会减轻了很大的压力,让他们专心做美工或者其他.
代码如下:
1: Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click2: Dim bll As New BLL3: '调用bll层添加方法4: Try5: bll.Add(TextBox1.Text)6: Catch ex As Exception7: '显示添加结果给用户8: MessageBox.Show(ex.Message)9: End Try10: End Sub
当然三层方式还有其他一些技巧,比如为DAL加一层接口,用抽象工厂加反射的方法实现数据库的灵活操作,替换.这里先不叙述.
上述都是个人的一点总结,欢迎大家指导
接下文:解析三层架构(3)--