sqlhelper的使用

          随着机房收费系统的不断深入，越来越发现每个功能都要连接数据库，通过不断查阅博客，根据常用归一的原则，把数据库连接实现一个类成为必然；SQLhelper就充当了为配置文件搭配链接文字，为D层数据的增删改查提供了归一化处理。

        

        初始化连接配置文件：

    'strConnection负责生成配置时所需要的字符串
    Private ReadOnly strConnection As String = System.Configuration.ConfigurationSettings.AppSettings("ConnStr")
    'conn实现数据连接
    Dim conn As SqlConnection = New SqlConnection(strConnection)
    'cmd控制命令
    Dim cmd As New SqlCommand

       带参数增删改：

    '增删改函数（有参数），传进参数为sql命令字，命令类型，参数数组
    Public Function ExecAddDelUpdate(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As Integer

        Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter()  '数据适配器
        Dim dt As New DataTable  '创建数据表（虚拟表）
        Dim ds As New DataSet   'dataset内存中的数据库（不依赖真是数据库）

        cmd.CommandText = cmdText   '获取和执行SQL命令
        cmd.CommandType = cmdType    'cmd类型
        cmd.Connection = conn      '连接对象
        cmd.Parameters.AddRange(paras)  '传进去参数

        Try
            conn.Open()  '打开连接
            Return cmd.ExecuteNonQuery()   '执行操作
            cmd.Parameters.Clear()   '清除参数
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConnn(conn)  '关闭连接
            Call CloseCmd(cmd)   '关闭命令
        End Try

    End Function

  不带参数增删改：

    Public Function ExecAddDelUpdateNo(ByVal cmdText As String, ByVal cmdType As CommandType) As Integer
        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn

        Try
            conn.Open()  '连接打开
            Return cmd.ExecuteNonQuery()   '无条件执行
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConnn(conn)
            Call CloseCmd(cmd)

        End Try

    End Function

         查询操作的数据要供给其他层使用，这样需要以DataTable（虚拟表）的形式返回。

带参数的查询：

    Public Function ExecSelect(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable

        Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter  '实例化适配器
        Dim dt As New DataTable  '创建网格化数据库
        Dim ds As New DataSet    '存在内存中的数据表

        cmd.CommandText = cmdText   '获取SQL命令语句
        cmd.CommandType = cmdType    '命令类型
        cmd.Connection = conn    '连接
        cmd.Parameters.AddRange(paras)    '添加参数
        sqlAdapter = New SqlDataAdapter(cmd)   '适配器生成
        Try
            sqlAdapter.Fill(ds)   '适配器中添加内存中数据表
            dt = ds.Tables(0)      '生成第一个数据表
            cmd.Parameters.Clear()   '清空参数
        Catch ex As Exception
            MsgBox("查询失败", CType(vbOKOnly + MsgBoxStyle.Exclamation, MsgBoxStyle), "警告")
        Finally
            Call CloseCmd(cmd)     '销毁cmd
        End Try
        Return dt  '最终返回数据表（内存）
    End Function

不带参数：

    Public Function ExecSelectNo(ByVal cmdText As String, ByVal cmdType As CommandType) As DataTable
        Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter()
        Dim ds As New DataSet


        cmd.CommandText = cmdText
        cmd.CommandType = cmdType
        cmd.Connection = conn
        sqlAdapter = New SqlDataAdapter(cmd)
        Try
            sqlAdapter.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            Return Nothing
        Finally
            Call CloseCmd(cmd)
        End Try
    End Function

释放连接和关闭按钮连接：

    Private Sub CloseConnn(ByVal conn As SqlConnection)   '释放连接
        If (conn.State <> ConnectionState.Closed) Then
            conn.Close()     '关闭连接
            conn = Nothing     '不指向原来对象
        End If
    End Sub

    Private Sub CloseCmd(ByVal cmd As SqlCommand)     '关闭命令
        If Not IsNothing(cmd) Then
            cmd.Dispose()     '销毁cmd命令
            cmd = Nothing
        End If
    End Sub

          调用过程的关键是SQL语句的实现以及数组的添加，以注册为例：

        Dim sqlcmd As String = "insert into Student(CardID,StuID,Password,Name,Seariers,class,cash,state,registDate,registTime,PC,Others)values(@CardID,@StuID,@Seariers,@Password,@Name,@class,@cash,@state,@registDate,@registTime,@PC,@Others)"  'SQL语句实现插入

        Dim insertSQL As New SQLHelper  ‘初始化SQLHelper类

        '由于字段较多，容易出现问题
        Dim paras As SqlParameter() = {New SqlParameter("@CardID", Dstu.cardID),
                                        New SqlParameter("@Password", Dstu.cardID),
                                        New SqlParameter("@StuID", Dstu.stuID),
                                        New SqlParameter("@Name", Dstu.name),
                                        New SqlParameter("@Seariers", Dstu.serial),
                                        New SqlParameter("@class", Dstu.classNum),
                                        New SqlParameter("@cash", Dstu.balance),
                                        New SqlParameter("@state", Dstu.state),
                                        New SqlParameter("@registDate", Date.Today),
                                        New SqlParameter("@registTime", Date.Now),
                                        New SqlParameter("@PC", "Whp-PC"),
                                        New SqlParameter("@Others", Dstu.others)}
        insertSQL.ExecAddDelUpdate(sqlcmd, CommandType.Text, paras)   '调用helper函数来进行操作