前端之家收集整理的这篇文章主要介绍了
VB.NET中Ssqlhelper类，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
'**********************************************
'说明: 用于封装增 删 改 查 的 操作，在DAL层用的时候直接调用即可
'命名空间：sqlHelper
'机器名称：晓
'创建日期：2014/12/15 16:41:31
'作者：郑浩
'版本号：V1.00
'**********************************************
Imports System.Data
Imports System.Data.sqlClient
Imports 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 Sub
End Class
