SQLHelper蓦然回首

    自己前面写过一篇博客是关于Ado.net和数据库的但是自己并没有怎么注意直到机房重构的时候发现其实好多东西封装起来可以减少DAL层的代码量没错啦这就是我在读了很多前人的博客和峰哥借的书之后明白的sqlhelper之前看大家很多人一直在写关于这方面的博客然后自己没有太注意，一直以为他是一个配置文件直接拿来就可以用的觉得他很高大上但是自己开始动手做之后才发现原来他其实就是我们在对数据库进行操作的时候会用到的，比如我们在进行上一次的数据库的操作时我们会做下面几步：

    1.加载驱动

    2.对数据库进行连接

    3.对数据库进行操作（包括query和update，其中update又可以分为insert、delete、update）

    4.关闭数据库

    5.返回结果集

但是如果我们一直都在每一个界面都这么进行操作的话那么代码量就是很大的所以这就有了我之前提过的sqlhelper，他可以看做是一个封装的类，可以调用里面的方法，下面是我做的一个注释和理解

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data



Public Class sqlHelper
    Public Shared Function ExecuteNoQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("Connstr") '加载驱动并且定义一个连接对象
        Dim conn As SqlConnection = New SqlConnection(strConnStr)                                '“ConnStr”是你web.config 这个配置文件里面连接数据库的的关键字,'
        Dim cmd As New SqlCommand                                                                  '也就是你在每个.vb页面引用这一句就可以连接数据库了
        Dim res As Integer                                                                      '使用SQLCommand的作用是用来调用sql语句的
        cmd = New SqlCommand(cmdText, conn)
        cmd.CommandType = cmdType
        cmd.Parameters.AddRange(paras) '用来添加参数
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()

            End If
            res = cmd.ExecuteNonQuery()              '如果正确的设置了cmd的属性就可以通过executenonquery来进行执行SQL语句

        Catch ex As Exception
            MsgBox(ex.Message, "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If

        End Try
        Return res
    End Function
    Public Shared Function ExecuteNoQuery(ByVal cmdTxt As String, ByVal cmdType As CommandType) As Integer
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
        Dim conn As SqlConnection = New SqlConnection(strConnStr)
        Dim cmd As New SqlCommand
        Dim res As Integer
        cmd = New SqlCommand(cmdTxt, conn)
        cmd.CommandType = cmdType
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            res = cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()

            End If
        End Try
        Return res
    End Function
    Public Shared Function GetDataTable(ByVal cmdtxt As String, ByVal cmdType As CommandType) As DataTable
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
        Dim conn As SqlConnection = New SqlConnection(strConnStr)
        Dim cmd As New SqlCommand
        Dim adataset As DataSet
        Dim adaptor As SqlDataAdapter
        cmd = New SqlCommand(cmdtxt, conn)
        adaptor = New SqlDataAdapter(cmd) '增加一个适配器对象
        adataset = New DataSet '创建DataSet实例
        cmd.CommandType = cmdType
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
                adaptor.Fill(adataset) '填充数据集

            End If
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close() '关闭数据库

            End If
        End Try
        Return adataset.Tables(0)
    End Function
    Public Shared Function GetDataTable(ByVal cmdtxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
        Dim conn As SqlConnection = New SqlConnection(strConnStr)
        Dim cmd As New SqlCommand
        Dim adataset As DataSet
        Dim adaptor As SqlDataAdapter
        cmd = New SqlCommand(cmdtxt, conn)
        adaptor = New SqlDataAdapter
        adataset = New DataSet
        cmd.CommandType = cmdType
        cmd.Parameters.AddRange(paras)

        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            adaptor.Fill(adataset)

        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        Return adataset.Tables(0)

    End Function
    Public Shared Function GetReader(ByVal cmdtxt As String, ByVal cmdType As CommandType) As SqlDataReader
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
        Dim conn As SqlConnection = New SqlConnection(strConnStr)
        Dim cmd As SqlCommand
        cmd = New SqlCommand(cmdtxt, conn)
        cmd.CommandType = cmdType
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()

            End If
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally

        End Try

        Return cmd.ExecuteReader(CommandBehavior.CloseConnection) '
    End Function
    Public Shared Function GetReader(ByVal cmdtxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As SqlDataReader
        Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
        Dim conn As SqlConnection = New SqlConnection(strConnStr)
        Dim cmd As New SqlCommand
        cmd = New SqlCommand(cmdtxt, conn)
        cmd.CommandType = cmdType
        cmd.Parameters.AddRange(paras)
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()

            End If
        Catch ex As Exception
            MsgBox(ex.Message, , "数据库操作")
        Finally

        End Try
        Return cmd.ExecuteReader() '尽可能快地对数据库进行查询并得到结果一定要关闭！
    End Function
End Class