1、定义:
sqlHelper是一个基于·NET Framework的数据库操作组件。组件中包含数据库操作方法。sqlHelper用于简化你重复的去写那些数据库连接(sqlConnection),sqlCommand,sqlDataReader等等。sqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串,sql参数等,就可以访问数据库了,很方便。
sqlHelper其实就是一个封装的类,是对连接数据的一次抽象。
所谓无知者无谓,刚开始用vb.net连接sql数据库的时候,每次都手动敲一遍,现在有巨人给我们总结出来sqlHelper类,只要调用这个类,就可以很容易的连接数据库,而且大幅度减少了出错的可能。
2、方法:
3、代码
'/************************************************* '作者:金福林 '小组: 无 '说明:sqlHelper类 '创建日期:2014/5/4 19:47:29 '版本号:1.1.0 '**********************************************/ Imports Entity Imports System.Data.sqlClient ''' <summary> ''' 对数据库的增删改查 ''' </summary> ''' <remarks></remarks> Public Class sqlHelper Dim con As String = System.Configuration.ConfigurationSettings.AppSettings("con") Dim sqlcon As sqlConnection = New sqlConnection(con) Dim cmd As New sqlClient.sqlCommand '使用command对象执行命令并返回 ''' <summary> ''' 执行增删改三个操作,有参数 ''' </summary> ''' <param name="cmdText"></param> ''' <param name="cmdType"></param> ''' <param name="sqlParams"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function ExecAddDelUpdate(ByVal cmdText As String,ByVal cmdType As String,ByVal sqlParams As sqlParameter()) As Integer cmd.Parameters.AddRange(sqlParams) '将参数传入 cmd.CommandType = cmdType cmd.Connection = sqlcon '设置连接 cmd.CommandText = cmdText Try sqlcon.Open() '打开连接 Return cmd.ExecuteNonQuery() '执行增删改 cmd.Parameters.Clear() '清除参数 Catch ex As Exception Return 0 Finally Call CloseCon(sqlcon) Call CloseCmd(cmd) End Try End Function ''' <summary> ''' 执行增删改,无参数 ''' </summary> ''' <param name="cmdText"></param> ''' <param name="cmdType"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function ExecAddDelUpdate(ByVal cmdText As String,ByVal cmdType As String) As Integer cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = sqlcon Try sqlcon.Open() Return cmd.ExecuteNonQuery() Catch ex As Exception Return 0 Finally Call CloseCon(sqlcon) Call CloseCmd(cmd) End Try End Function ''' <summary> ''' 执行查询,有参数 ''' </summary> ''' <param name="cmdText"></param> ''' <param name="cmdType"></param> ''' <param name="sqlParams"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function ExecSelect(ByVal cmdText As String,ByVal sqlParams As sqlParameter()) As DataTable Dim sqlAdapter As sqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = sqlcon cmd.Parameters.AddRange(sqlParams) sqlAdapter = New sqlDataAdapter(cmd) Try sqlAdapter.Fill(ds) '填充dataset dt = ds.Tables(0) 'dt为dataset的第一个表 cmd.Parameters.Clear() Catch ex As Exception Return Nothing Finally Call CloseCmd(cmd) End Try Return dt End Function ''' <summary> ''' 执行查询,无参数 ''' </summary> ''' <param name="cmdText"></param> ''' <param name="cmdType"></param> ''' <returns></returns> ''' <remarks></remarks> Public Function ExecSelect(ByRef cmdText As String,ByVal cmdType As String) As DataTable Dim sqlAdapter As sqlDataAdapter Dim ds As New DataSet cmd.CommandText = cmdText cmd.CommandType = cmdType cmd.Connection = sqlcon 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 ''' <summary> ''' 关闭数据库连接 ''' </summary> ''' <param name="con"></param> ''' <remarks></remarks> Public Sub CloseCon(ByVal con As sqlConnection) If (con.State <> ConnectionState.Closed) Then con.Close() con = Nothing End If End Sub ''' <summary> ''' 关闭命令 ''' </summary> ''' <param name="cmd"></param> ''' <remarks></remarks> Public Sub CloseCmd(ByVal cmd As sqlCommand) If Not IsNothing(cmd) Then cmd.Dispose() cmd = Nothing End If End Sub End Class
我们只需要在D层,添加sqlHelper类,然后将上述代码复制到你的类中
sqlHelper是一次思想的升华,有了它仍需要我们的进一步理解。