DAL层配合基类的代码记录

这是我第二遍做机房收费系时的DAL层配合基类的代码

DAL层的代码：

Imports System.Data.SqlClient
Imports Entity

Public Class da_User

'继承da_Base基类
Inherits da_Base
'连接数据库
Private connStr As String = System.Configuration.ConfigurationSettings.AppSettings("connStr")

''' <summary>
''' 获得一个用户信息
''' </summary>
''' <param name="UserID">用户ID</param>
''' <returns>一个用户对象</returns>
''' <remarks>2011/3/22 11:27 By NOVAGang</remarks>
Public Function GetObject(ByVal UserID As String) As Entity.user_Info

Dim sql As String = "select * from user_Info where UserID=@UserID"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As New SqlParameter("@UserID", SqlDbType.Char)
cmd.Parameters.Add(sqlParam)
sqlParam.Value = UserID
Dim sdr As SqlDataReader = Nothing
Dim EntityUserInfo As New Entity.user_Info

Try
conn.Open()
sdr = cmd.ExecuteReader()
While sdr.Read
EntityUserInfo.UserID = sdr.GetString(0)
EntityUserInfo.Password = sdr.GetString(1)
EntityUserInfo.Level = sdr.GetString(2)
EntityUserInfo.UserName = sdr.GetString(3)
EntityUserInfo.Head = sdr.GetString(4)
End While
Return EntityUserInfo
Catch ex As Exception
Return Nothing
Finally
Close(sdr)
Close(cmd)
Close(conn)
End Try

End Function

''' <summary>
''' 获得所有用户
''' </summary>
''' <returns>所有用户的DataSet集合</returns>
''' <remarks>2011/3/22 22:13 By NOVAGang</remarks>
Public Function SelectAll(ByVal Level As String) As DataSet
Dim sql As String = "select * from user_Info where Level=@Level"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim dap As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim ds As New DataSet

Dim sqlParam As New SqlParameter("@Level", SqlDbType.Char)
cmd.Parameters.Add(sqlParam)
sqlParam.Value = Level

Try
conn.Open()
dap.Fill(ds)
Return ds
Catch ex As Exception
Return Nothing
Finally
Close(cmd)
Close(conn)
End Try

End Function

''' <summary>
''' 删除一条用户信息
''' </summary>
''' <param name="EntityUserInfo">一个用户对象</param>
''' <returns>是否删除成功</returns>
''' <remarks>2011/3/22 22:18 By NOVAGang</remarks>
Public Function Del(ByVal EntityUserInfo As Entity.user_Info) As Boolean
Dim sql As String = "delete from user_Info where UserID=@UserID"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As New SqlCommand(sql, conn)
Dim sqlParam As New SqlParameter("@UserID", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.UserID
cmd.Parameters.Add(sqlParam)

Try
conn.Open()
Return cmd.ExecuteNonQuery > 0
Catch ex As Exception
Return False
Finally
Close(cmd)
Close(conn)
End Try

End Function

''' <summary>
''' 更新用户密码
''' </summary>
''' <param name="UserID">用户ID</param>
''' <param name="Password">用户密码</param>
''' <returns>更新是否成功</returns>
''' <remarks>2011/3/22 22:13 By NOVAGang</remarks>
Public Function UpdatePWD(ByVal UserID As String, ByVal Password As String) As Boolean

Dim sql As String = "update user_Info set Password=@Password where UserID=@UserID"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As SqlParameter

sqlParam = New SqlParameter("@UserID", SqlDbType.Char)
sqlParam.Value = UserID
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Password", SqlDbType.Char)
sqlParam.Value = Password
cmd.Parameters.Add(sqlParam)

Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
Close(cmd)
Close(conn)
End Try

End Function

''' <summary>
''' 更新一条用户信息
''' </summary>
''' <param name="UserID">一个UserID字段</param>
''' <param name="Head">一个Head字段</param>
''' <returns>更新是否成功</returns>
''' <remarks>2011/3/22 22:23 By NOVAGang</remarks>
Public Function Update(ByVal UserID As String, ByVal Head As String) As Boolean
Dim sql As String = "update user_Info set Head=@Head where UserID=@UserID"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As SqlParameter

sqlParam = New SqlParameter("@UserID", SqlDbType.Char)
sqlParam.Value = UserID
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Head", SqlDbType.Char)
sqlParam.Value = Head
cmd.Parameters.Add(sqlParam)

Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
Close(cmd)
Close(conn)
End Try
End Function

''' <summary>
''' 插入一条用户信息
''' </summary>
''' <param name="EntityUserInfo">用户信息表</param>
''' <returns>插入是否成功</returns>
''' <remarks>2011/3/22 22:13 By NOVAGang</remarks>
Public Function Inster(ByVal EntityUserInfo As Entity.user_Info) As Boolean
Dim sql As String = "insert into user_Info(UserID,Password,Level,UserName,Head) values (@UserID,@Password,@Level,@UserName,@Head)"
Dim conn As SqlConnection = CreateConn(connStr)
Dim cmd As SqlCommand = New SqlCommand(sql, conn)
Dim sqlParam As SqlParameter

sqlParam = New SqlParameter("@UserID", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.UserID
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Password", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.Password
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Level", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.Level
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@UserName", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.UserName
cmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Head", SqlDbType.Char)
sqlParam.Value = EntityUserInfo.Head
cmd.Parameters.Add(sqlParam)

Try
conn.Open()
Return cmd.ExecuteNonQuery() > 0
Catch ex As Exception
Return False
Finally
Close(cmd)
Close(conn)
End Try
End Function

End Class

基类da_Base的代码：

Imports System.Data.SqlClient

Public Class da_Base

''' <summary>
''' 创建一个数据连接
''' </summary>
''' <param name="connStr">数据库连接字符串</param>
''' <returns></returns>
''' <remarks>2011/3/22 22:23 By NOVAGang</remarks>
'''Private connStr As String = "Data Source=ASUS-PC;Initial Catalog=jftoll_sys;Persist Security Info=True;User ID=sa;Password=123456" （这是没有通过配置文件直接连接数据库的写法）

Public Function CreateConn(ByVal connStr As String) As SqlConnection
Return New SqlConnection(connStr)
End Function

''' <summary>
''' 关闭相关对象
''' </summary>
''' <param name="conn"></param>
''' <remarks>2011/3/22 22:23 By NOVAGang</remarks>
Public Sub Close(ByVal conn As SqlConnection)
If Not IsNothing(conn) Then
conn.Close()
conn = Nothing
End If
End Sub

''' <summary>
''' 关闭相关对象
''' </summary>
''' <param name="sdr"></param>
''' <remarks>2011/3/22 22:23 By NOVAGang</remarks>
Public Sub Close(ByVal sdr As SqlDataReader)
If Not IsNothing(sdr) Then
sdr.Close()
sdr = Nothing
End If
End Sub

''' <summary>
''' 关闭相关对象
''' </summary>
''' <param name="cmd"></param>
''' <remarks>2011/3/22 22:23 By NOVAGang</remarks>
Public Sub Close(ByVal cmd As SqlCommand)
If Not IsNothing(cmd) Then
cmd.Dispose()
cmd = Nothing
End If
End Sub
End Class