[机房合作]―SqlHelper我们又约了 - 数据库编程

rams) '将参数传入 'cmd = New SqlCommand(cmdText, conn) sqlAdapter = New SqlDataAdapter(cmd) '实例化适配器 '执行操作 Try sqlAdapter.Fill(ds) '用适配器对ds进行填充 dt = ds.Tables(0) '返回数据集的第一个表 cmd.Parameters.Clear() '清楚参数 Catch ex As Exception MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告") Finally Call CloseConn(conn) Call CloseCmd(cmd) End Try Return dt End Function '''

''' 无参数的查询操作 '''

'''需要执行的命令 '''所执行命令的类型，一般是sql语句，也有可能是存储过程或者表 ''' 返回执行查询得到的结果，为DataTable类型 ''' Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable Dim sqlAdapter As SqlDataAdapter '声明适配器 Dim dt As New DataTable '声明数据表 Dim ds As New DataSet '声明数据缓存 '将传入的值分别给cmd属性赋值 cmd.CommandType = cmdType '设置一个值，届时cmdtext cmd.Connection = conn cmd.CommandText = cmdText sqlAdapter = New SqlDataAdapter(cmd) '实例化适配器 '执行操作 Try sqlAdapter.Fill(ds) '用适配器对ds进行填充 dt = ds.Tables(0) '返回数据集的第一个表 Catch ex As Exception MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告") Finally Call CloseConn(conn) Call CloseCmd(cmd) End Try Return dt End Function End Class

D层调用SqlHelper类中对应方法的实例：

    '''调用有参数的增删改操作

    '''  ''' 向基本数据设定表中插入一行信息 ''' 
    '''传入BasicData表中属性的值
    ''' 
   
    返回布尔值
   
    ''' 
   
    Public Function InsertBasicData(enBasicData As BasicDataEntity) As Boolean Implements IDAL.IBasicData.InsertBasicData
        '定义数据库连接字符串
        Dim sql As String = "Insert into T_BasicData (Rate,TmpRate,UnitTime,LeastTime,PrepareTime,LimitCash,Date,Time,UserID) value(@rate,@tmprate,@unittime,@leasttime,@reparetime,@limitcash,@date,@time,@userid)"
        '提出要插入的字符串，并赋值
        Dim sqlparams As SqlParameter() = {New SqlParameter("@rate", enBasicData.Rate),
                                            New SqlParameter("@tmprate", enBasicData.TmpRate),
                                            New SqlParameter("@unittime", enBasicData.UnitTime),
                                            New SqlParameter("@leasttime", enBasicData.LeastTime),
                                            New SqlParameter("@preparetime", enBasicData.PrepareTime),
                                            New SqlParameter("@limitcash", enBasicData.LimitCash),
                                            New SqlParameter("@date", enBasicData.zDate),
                                            New SqlParameter("@time", enBasicData.Time),
                                            New SqlParameter("@userid", enBasicData.UserID)}
        '实例化一个sqlhelper类的对象
        Dim helper As New SQLHelper
        '调用sqlhelper类的方法
        Dim InsertOK = helper.ExecuteNoQuery(sql, CommandType.Text, sqlparams)
        Return InsertOK
    End Function


    '''无参数的增删改操作

    '''  ''' 通过用户账号删除用户记录表中的所有信息 ''' 
    ''' 
   
    返回布尔值
   
    ''' 
   
    Public Function DeleteByUserID(enUser As UserEntity) As Boolean Implements IDAL.IUser.DeleteByUserID
        '建立删除连接数据库语句
        Dim sql As String = "delete from T_User"
        '定义一个sqlHelper类的对象
        Dim helper As New SQLHelper
        '调用sqlHelper中的删除方法
        Dim DeleteOK = helper.ExecuteNoQuery(sql, CommandType.Text)
        Return DeleteOK
    End Function


    '''调用有参数的查询操作

    '''  ''' 通过卡号查询卡表中的信息 ''' 
    '''传入CardID的值
    ''' 
   
    返回泛型集合
   
    ''' 
   
    Public Function SelectByCardID(enCard As CardEntity) As List(Of CardEntity) Implements IDAL.ICard.SelectByCardID
        '定义数据库连接字符串
        Dim sql As String = "Select * from T_Card where CardID=@cardid"
        '提出查询的字符串，并赋值
        Dim sqlparams As SqlParameter() = {New SqlParameter("@cardid", enCard.CardID)}
        '实例化一个临时表
        Dim table As New DataTable
        '实例化一个sqlhelper类对象
        Dim helper As New SQLHelper
        '调用sqlhelper类的查询方法，并将查询到的信息放到临时表中
        table = helper.ExecSelect(sql, CommandTyp

[机房合作]―SqlHelper我们又约了(二)