VB.NET中Ssqlhelper类
发布时间:2020-12-17 07:49:06 所属栏目:百科 来源:网络整理
导读:'**********************************************'说明: 用于封装增 删 改 查 的 操作,在DAL层用的时候直接调用即可'命名空间:SqlHelper'机器名称:晓'创建日期:2014/12/15 16:41:31'作者:郑浩'版本号:V1.00'****************************************
'********************************************** '说明: 用于封装增 删 改 查 的 操作,在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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |