1、定义：
sqlHelper是一个基于·NET Framework的数据库操作组件。组件中包含数据库操作方法。sqlHelper用于简化你重复的去写那些数据库连接（sqlConnection）,sqlCommand,sqlDataReader等等。sqlHelper 封装过后通常是只需要给方法传入一些参数如数据库连接字符串，sql参数等，就可以访问数据库了，很方便。
sqlHelper其实就是一个封装的类，是对连接数据的一次抽象。
所谓无知者无谓，刚开始用vb.net连接sql数据库的时候，每次都手动敲一遍，现在有巨人给我们总结出来sqlHelper类，只要调用这个类，就可以很容易的连接数据库，而且大幅度减少了出错的可能。
'/*************************************************
'作者：金福林
'小组: 无
'说明：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是一次思想的升华，有了它仍需要我们的进一步理解。