VBDotNET三层架构数据库增删改查学习心得(原创).doc

（原创）VB.NET三层架构数据库增删改查学习心得-WinForm通过一段时间对三层架构的学习，现将我的学习心得体会与大家分享。本程序所使用的界面为Window应用程序（WinForm）界面，是客户端/服务器（C/S）模式的程序。界面（UI）对三层架构来说使用WinForm还是WebForm异或移动设备都是一样的，无论换了什么皮肤都能基本使用才是三层架构意义之所在。本程序在SQL-Server2005+VB.NET2008下测试通过（附源码）。一、首先说说三层架构的概念及作用，见下表。三层架构(3-tier architecture) 通常意义上的三层架构就是将整个业务应用划分为：表现层（UI）、业务逻辑层（BLL）、数据访问层（DAL）。区分层次的目的即为了“高内聚，低耦合”的思想。分层（tier）概念作用表现层（UI）通俗讲就是展现给用户的界面，即用户在使用一个系统的时候他的所见所得。主要表示WEB方式，也可以表示成WINFORM方式，WEB方式也可以表现成：aspx，如果逻辑层相当强大和完善，无论表现层如何定义和更改，逻辑层都能完善地提供服务。业务逻辑层（BLL）针对具体问题的操作，也可以说是对数据层的操作，对数据业务逻辑处理。主要是针对具体的问题的操作，也可以理解成对数据层的操作，对数据业务逻辑处理，如果说数据层是积木，那逻辑层就是对这些积木的搭建。数据访问层（DAL）该层所做事务直接操作数据库，针对数据的增添、删除、修改、查找等。主要是对原始数据（数据库或者文本文件等存放数据的形式）的操作层，而不是指原始数据，也就是说，是对数据的操作，而不是数据库，具体为业务逻辑层或表示层提供数据服务数据库实体类（Entity）对数据对象进行封装，也有一些简单的功能他在三层中并不是作为一层而存在的，而是作为一个类库，起到封装的作用。由上表可以看出，多出的一个数据库实体类（Entity），他在三层中并不是作为一层而存在的，而是作为一个类库，起到封装数据库表的作用。再说说各层及类库之间的联系，见下图：其他的三层架构的优点及缺点本文就不论述了，请查阅相关资料。二、本程序三层架构具体内容简介1、解决方案概览2、各层的内部属性、方法、函数分层（tier）对象名属性、方法、函数作用表现层（UI）UI_Tier过程：DataGridViewLoad将查询的所有记录填充到DataGridView中业务逻辑层（BLL）B_UserInfoInsert添加记录Delete删除记录Update修改记录FindAll查询所有记录FindOne查询一条记录DataIsExsit查询一条记录是否存在数据访问层（DAL）D_UserInfoInsert添加记录Delete删除记录Update修改记录FindAll查询所有记录FindOne查询一条记录DataIsExsit查询一条记录是否存在数据库实体类（Entity）E_UserInfo方法：Fill填充一条记录属性：UserID属性：UserName属性：UserPwd属性：UserBirth属性：UserQQ封装数据库表三、三层架构建立前期准备工作。1、建立一张UserInfo表，源码如下：CREATE TABLE dbo.UserInfo(UserID int NULL,UserName varchar(12) ,UserPwd varchar(16) ,UserBirth datetime ,UserQQ varchar(14) ) ON PRIMARY2、建立一个Windows窗体应用程序文件新建项目Visual BasicWindowsWindows窗体应用程序。这里是中文版VB.NET2008，不同版本上述内容有些不同翻译。3、建立Entity、DAL、BLL类库在第二步所创建的解决方案下，选择文件添加新建项目Visual BasicWindows类库。本程序是将所有类库建立在了一个解决方案之下，也可以独立分别建立独立的类库方案。就是调用的时候不太方便。4、设计UI操作界面如下：四、开始三层架构的代码设计顺序是数据库实体类（Entity）、数据访问层（DAL）、业务逻辑层（BLL）、表现层（UI）。本文是在ASP界面小燕雀DaoNet三层架构基础上结合我的想法而设计的。这个视频对我学习三层架构有很大启发。1、数据库实体类（Entity）代码如下：Public Class E_UserInfo#Region 定义userInfo表中各个属性变量 Private _UserID As Integer Private _UserName As String Private _UserPwd As String Private _UserBirth As Date Private _UserQQ As String#End Region#Region 定义数据表中各个属性 Public Property UserID() As Integer Get Return _UserID End Get Set(ByVal value As Integer) _UserID = value End Set End Property Public Property UserName() As String Get Return _UserName End Get Set(ByVal value As String) _UserName = value End Set End Property Public Property UserPwd() As String Get Return _UserPwd End Get Set(ByVal value As String) _UserPwd = value End Set End Property Public Property UserBirth() As Date Get Return _UserBirth End Get Set(ByVal value As Date) _UserBirth = value End Set End Property Public Property UserQQ() As String Get Return _UserQQ End Get Set(ByVal value As String) _UserQQ = value End Set End Property#End Region#Region 填充一条记录 Public Overloads Sub Fill(ByVal PUserID As Integer, ByVal PUserName As String, ByVal PUserPwd As String, ByVal PUserBirth As Date, ByVal PUserQQ As String) 重载方法fill方便填充数据，否则引用该对象的时候得写一堆上面的填值代码，UI界面使用非常方便 UserID = PUserID UserName = PUserName UserPwd = PUserPwd UserBirth = PUserBirth UserQQ = PUserQQ End Sub#End RegionEnd Class2、数据访问层（DAL）代码如下：本程序DAL层与小燕雀的代码设计有很多不同，很多方法是我个人习惯。手动添加引用本项目Entity代码添加引用System.Data.SqlClientImports System.Data.SqlClientPublic Class D_UserInfo#Region 连接字符串 Public Function ConnStr() As String Return Data Source=49;Initial Catalog=Wink;User ID=wink;Pwd=wcp6810662 End Function#End Region#Region 添加记录 Public Function Insert(ByVal EntityUserInfo As Entity.E_UserInfo) As Boolean Dim sqlStr As String = insert into UserInfo (UserID,UserName,UserPwd,UserBirth,UserQQ) values (UserID,UserName,UserPwd,UserBirth,UserQQ) Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) cmd.Parameters.Add(UserID, SqlDbType.Int).Value = EntityUserInfo.UserID cmd.Parameters.Add(UserName, SqlDbType.VarChar).Value = EntityUserInfo.UserName cmd.Parameters.Add(UserPwd, SqlDbType.VarChar).Value = EntityUserInfo.UserPwd cmd.Parameters.Add(UserBirth, SqlDbType.DateTime).Value = EntityUserInfo.UserBirth cmd.Parameters.Add(UserQQ, SqlDbType.VarChar).Value = EntityUserInfo.UserQQ Try conn.Open() Return cmd.ExecuteNonQuery() 0 Catch ex As Exception Return False Finally If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End Region#Region 删除记录 Public Function Delete(ByVal PuserId As Integer) As Boolean Dim sqlStr As String = delete from UserInfo where UserID=UserID Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) cmd.Parameters.Add(UserID, SqlDbType.Int).Value = PuserId Try conn.Open() Return cmd.ExecuteNonQuery() 0 Catch ex As Exception Return False Finally If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End Region#Region 修改记录 Public Function Update(ByVal EntityUserInfo As Entity.E_UserInfo) As Boolean Dim sqlStr As String = update UserInfo set UserName=UserName,UserPwd=UserPwd,UserBirth=UserBirth,UserQQ=UserQQ where UserID=UserID Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) cmd.Parameters.Add(UserID, SqlDbType.Int).Value = EntityUserInfo.UserID cmd.Parameters.Add(UserName, SqlDbType.VarChar).Value = EntityUserInfo.UserName cmd.Parameters.Add(UserPwd, SqlDbType.VarChar).Value = EntityUserInfo.UserPwd cmd.Parameters.Add(UserBirth, SqlDbType.DateTime).Value = EntityUserInfo.UserBirth cmd.Parameters.Add(UserQQ, SqlDbType.VarChar).Value = EntityUserInfo.UserQQ Try conn.Open() Return cmd.ExecuteNonQuery() 0 Catch ex As Exception Return False Finally If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End Region#Region 查询所有记录 返回recordset不好使，ASP.NET的gridview控件可以接收recordset,VB.NET的datagridview需要接收datatable Public Function FindAll() As DataTable Dim sqlStr As String = select * from UserInfo Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) Dim ds As New DataSet Dim dt As New Data.DataTable Try da.Fill(ds, UserInfo) dt = ds.Tables(UserInfo) Return dt Catch ex As Exception Return Nothing Finally If Not IsNothing(da) Then da.Dispose() da = Nothing End If If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End Region#Region 查询一条记录 Public Function FindOne(ByVal PuserId As Integer) As Entity.E_UserInfo Dim sqlStr As String = select * from UserInfo where UserID=UserID Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) cmd.Parameters.Add(UserID, SqlDbType.Int).Value = PuserId Dim EntityUserInfo As Entity.E_UserInfo = New Entity.E_UserInfo Dim ds As New DataSet Try conn.Open() da.Fill(ds, UserInfo) Dim dr As DataRow = ds.Tables(UserInfo).Rows(0) EntityUserInfo.UserID = dr(UserID) EntityUserInfo.UserName = dr(UserName) EntityUserInfo.UserPwd = dr(UserPwd) EntityUserInfo.UserBirth = dr(UserBirth) EntityUserInfo.UserQQ = dr(UserQQ) Return EntityUserInfo Catch ex As Exception Return Nothing Finally If Not IsNothing(da) Then da.Dispose() da = Nothing End If If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End Region#Region 查询一条记录是否存在 这个很重要，否则经常会出现-未处理 System.NullReferenceException，未将对象引用设置到对象的实例。 例如查询结果是空时，你仍继续对“空（null、nothing）”进行操作，就会出现此异常。 Public Function DataIsExsit(ByVal PuserId As Integer) As Boolean Dim sqlStr As String = select UserID from UserInfo where UserID=UserID Dim conn As SqlConnection = New SqlConnection(ConnStr) Dim cmd As SqlCommand = New SqlCommand(sqlStr, conn) cmd.Parameters.Add(UserID, SqlDbType.Int).Value = PuserId Try conn.Open() Dim result = cmd.ExecuteScalar If Not IsNothing(result) Then Return True Else Return False End If Catch ex As Exception Return False Finally If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If If Not IsNothing(conn) Then conn.Close() conn = Nothing End If End Try End Function#End RegionEnd Class3、业务逻辑层（BLL）代码如下：大家可以看到本程序的BLL代码非常简单，有点类似于工厂，实际上不是，这里还需要很多业务逻辑进行处理，看看其他文章关于登录操作的三层架构就可以知道这层的重要作用。手动引用Entity、DALPublic Class B_UserInfo#Region 添加记录 Public Function Insert(ByVal EntityUserInfo As Entity.E_UserInfo) As Boolean Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.Insert(EntityUserInfo) End Function#End Region#Region 删除记录 Public Function Delete(ByVal PuserId As Integer) As Boolean Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.Delete(PuserId) End Function#End Region#Region 修改记录 Public Function Update(ByVal EntityUserInfo As Entity.E_UserInfo) As Boolean Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.Update(EntityUserInfo) End Function#End Region#Region 查询所有记录 Public Function FindAll() As DataTable Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.FindAll End Function#End Region#Region 查询一条记录 Public Function FindOne(ByVal PuserId As Integer) As Entity.E_UserInfo Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.FindOne(PuserId) End Function#End Region#Region 查询一条记录是否存在 Public Function DataIsExsit(ByVal PuserId As Integer) As Boolean Dim DalUserInfo As New DAL.D_UserInfo Return DalUserInfo.DataIsExsit(PuserId) End Function#End RegionEnd Class4、表现层（UI）代码如下：下面的代码只是实现功能的主干代码，可以进行简单测试，如要进行实际应用需要进行若干的防止BUG代码（如判断是否为空等）。手动引用BLL、DAL、EntityPublic Class UI_Tier Private Sub UI操作界面_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load DataGridViewLoad() End Sub Private Sub DataGridViewLoad() Dim bllUserInfo As New BLL.B_UserInfo DGVUserInfo.DataSource = bllUserInfo.FindAll() End Sub Private Sub B_Insert_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Insert.Click Dim bllUserInfo As New BLL.B_UserInfo Dim entityUserInfo As New Entity.E_UserInfo entityUserInfo.Fill(TextID.Text, TextName.Text, TextPwd.Text, DtpBirth.Value, TextQQ.Text) bllUserInfo.Insert(entityUserInfo) DataGridViewLoad() End Sub Private Sub B_Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_Delete.Click Dim bllUserInfo As New BLL.B_UserInfo If bllUserInfo.Delete(TextID.Text) Then MessageBox.Show(成功删除记录！, 删除记录, MessageBoxButtons.OK, MessageBoxIcon.Information) DataGridViewLoad() Else MessageBox.Show(未删除记录！记录不存在或输入为空！, 删除记录, MessageBoxButtons.OK, MessageBoxIcon.Error) End If End Sub Private Sub B_FindOne_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles B_FindOne.Click Dim bllUserInfo As New BLL.B_UserInfo Dim entityUserInfo As New Entity.E_UserInfo Dim tmpUserid As Integer tmpUserid = TextID.Text entityUserInfo = bllUserInfo.FindOne(tmpUserid) TextID.Text = entityUserInfo.UserID TextName.Text = entityUserInfo.UserName TextPwd.Text = entityUserInfo.UserPwd DtpBirth.Value = entityUserInfo.UserBirth TextQQ.Text = ent