VB.NET中Ssqlhelper类

概述'**********************************************'说明: 用于封装增 删 改 查 的 *** 作，在DAL层用的时候直接调用即可'命名空间：SqlHelper'机器名称：晓'创建日期：2014/12/15 16:41:31'作者：郑浩'版本号：V1.00'*********************************************

'**********************************************'说明: 用于封装增 删 改 查 的  *** 作，在DAL层用的时候直接调用即可'命名空间：sqlHelper'机器名称：晓'创建日期：2014/12/15 16:41:31'作者：郑浩'版本号：V1.00'**********************************************imports System.Dataimports System.Data.sqlClIEntimports System.Configuration  '必须要在管理器中添加引用  Public Class sqlHelper    '定义全局变量变量，获得数据库的连接字符串                                                              Private Shared Readonly strConnection As String = ConfigurationManager.AppSettings("ConnStr")    '定义全局变量，设置连接        Shared conn As sqlConnection = New sqlConnection(strConnection)    '定义全局cmd命令        Shared cmd As New sqlCommand    ''' <summary>        ''' 执行非查询 *** 作（增删改）有参数，        ''' </summary>        ''' <param name="cmdText">执行增删改查SQL语句或者存储过程</param>        ''' <param name="cmdType">命令类型文本或者是存储过程</param>        ''' <param name="paras">参数数组,无法确认有多少参数</param>        ''' <returns></returns>        ''' <remarks></remarks>        Public Shared Function ExecuteNoquery(ByVal cmdText As String,ByVal cmdType As CommandType,ByVal paras As sqlParameter()) As Integer        '将传入的值,分别为cmd的属性赋值          Dim result As Integer   '定义变量用来存放执行的结果        cmd.Parameters.AddRange(paras)   '将参数传入            cmd.CommandType = cmdType            '设置一个值,解释cmdText            cmd.Connection = conn                '设置连接,全局变量            cmd.CommandText = cmdText            '设置查询的语句            Try            conn.open()                      '打开连接                result = cmd.ExecuteNonquery()     '执行增删改 *** 作                cmd.Parameters.Clear()           '清除参数            Catch ex As Exception            MsgBox(ex.Message,"数据库 *** 作")                        '如果出错,返回0            Finally            '关闭数据连接            Call CloseConn(conn)            Call CloseCmd(cmd)        End Try        Return result        '返回受影响的行数    End Function    ''' <summary>        ''' 执行增删改三个 *** 作,(无参)        ''' </summary>        ''' <param name="cmdText">需要执行语句,一般是SQL语句,也有存储过程</param>        ''' <param name="cmdType">判断SQL语句的类型,一般都不是存储过程</param>        ''' <returns>Interger,受影响的行数</returns>        Public Shared Function ExecAddDelUpdateNo(ByVal cmdText As String,ByVal cmdType As CommandType) As Integer        '为要执行的命令cmd赋值            cmd.CommandText = cmdText       '先是查询的SQL语句            cmd.CommandType = cmdType       '设置SQL语句如何解释            cmd.Connection = conn           '设置连接            Dim result As Integer        '执行 *** 作            Try            conn.open()            result = cmd.ExecuteNonquery()        Catch ex As Exception            MsgBox(ex.Message,"数据库 *** 作")        Finally            Call CloseConn(conn)              Call CloseCmd(cmd)        End Try        Return result          '返回受影响的行数    End Function    '''' <summary>      '''' 执行查询的 *** 作,(有参),参数不限        '''' </summary>        ''' <param name="cmdText">需要执行语句,一般都不是存储过程</param>        ''' <param name="paras">传入的参数</param>        ''' <returns></returns>        ''' <remarks></remarks>        Public Shared Function ExecSelect(ByVal cmdText As String,ByVal paras As sqlParameter()) As Datatable        Dim sqlAdapter As sqlDataAdapter        Dim dt As New Datatable        Dim ds As New DataSet        '还是给cmd赋值            cmd.CommandText = cmdText        cmd.CommandType = cmdType        cmd.Connection = conn        cmd.Parameters.AddRange(paras)  '参数添加            sqlAdapter = New sqlDataAdapter(cmd)  '实例化adapter            Try            sqlAdapter.Fill(ds)           '用adapter将dataSet填充                 dt = ds.tables(0)             'datatable为dataSet的第一个表                cmd.Parameters.Clear()        '清除参数            Catch ex As Exception            MsgBox(ex.Message,"数据库 *** 作")        Finally                            '最后一定要销毁cmd                Call CloseCmd(cmd)        End Try        Return dt    End Function    'Public Shared Function GetDatatable(ByVal cmdTxt As String,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,'    adaptor = New sqlDataAdapter(cmd)    '    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    '        关闭数据库连接()    '        Call CloseConn(conn)    '        Call CloseCmd(cmd)    '    End Try    '    Return adataset.tables(0)    'End Function    ''' <summary>        ''' 执行查询的 *** 作,一般都不是存储过程</param>        ''' <returns>datatable,查询到的表格</returns>        ''' <remarks></remarks>        Public Shared Function ExecSelectNo(ByVal cmdText As String,ByVal cmdType As CommandType) As Datatable        Dim sqlAdapter As sqlDataAdapter        Dim ds As New DataSet        '还是给cmd赋值            cmd.CommandText = cmdText        cmd.CommandType = cmdType        cmd.Connection = conn        sqlAdapter = New sqlDataAdapter(cmd)  '实例化adapter            Try            sqlAdapter.Fill(ds)           '用adapter将dataSet填充                 Return ds.tables(0)             'datatable为dataSet的第一个表            Catch ex As Exception            Return nothing        Finally                            '最后一定要销毁cmd                Call CloseCmd(cmd)        End Try    End Function    ''' <summary>        ''' 关闭连接        ''' </summary>        ''' <param name="conn">需要关闭的连接</param>        ''' <remarks></remarks>        Public Shared Sub CloseConn(ByVal conn As sqlConnection)        If (conn.State <> ConnectionState.Closed) Then  '如果没有关闭                conn.Close()                    '关闭连接                conn = nothing                  '不指向原对象            End If    End Sub    ''' <summary>        ''' 关闭命令        ''' </summary>        ''' <param name="cmd">需要关闭的命令</param>        ''' <remarks></remarks>        Public Shared Sub CloseCmd(ByVal cmd As sqlCommand)        If Not Isnothing(cmd) Then          '如果cmd命令存在                cmd.dispose()                   '销毁                cmd = nothing        End If    End SubEnd Class

总结

以上是内存溢出为你收集整理的VB.NET中Ssqlhelper类全部内容，希望文章能够帮你解决VB.NET中Ssqlhelper类所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。