2、方法：

在 SqlHelper 类中实现的方法包括：

ExecuteNonQuery。此方法用于执行不返回任何行或值的命令。这些命令通常用于执行数据库更新，但也可用于返回存储过程的输出参数。

ExecuteReader。此方法用于返回SqlDataReader对象，该对象包含由某一命令返回的结果集。

ExecuteDataset。此方法返回DataSet对象，该对象包含由某一命令返回的结果集。

ExecuteScalar。此方法返回一个值。该值始终是该命令返回的第一行的第一列。

ExecuteXmlReader。此方法返回 FOR XML 查询的 XML 片段。

 

 

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 cmdType 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