SQL Server数据库的链接

[d157199717]

编写一个函数，该函数实现对SQL Server数据库的链接，执行数据库查询语句，并且最后返回一个DataTable对象。函数有两个String类型的参数，其中一个参数(sqlCmd)传入将要执行的SQL语句（Select），另一个参数(conCng) 传入数据库链接串。

[myhope88]

不用写了，自己下个sqlhelper类，就可以用了吧

[gxingmin]

给你一个sql通用类，
按你的需求，先给ConnectionString属性赋值conCng
然后调用GetDataTableBySQL函数传入sql语句就可以了

Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Public Class clsDAL
    Implements IDisposable
    Private m_strConnStr As String = String.Empty
    Private m_conn As System.Data.SqlClient.SqlConnection = Nothing
    Private m_da As System.Data.SqlClient.SqlDataAdapter = Nothing
    Private m_cmd As System.Data.SqlClient.SqlCommand = Nothing


    Private m_strErrorMessage As String
    Private m_strStackTrace As String

    ''' <summary>
    ''' 数据库连接串
    ''' </summary>
    Public Property ConnectionString() As String
        Get
            Return m_strConnStr
        End Get
        Set(ByVal value As String)
            m_strConnStr = value
            m_conn.ConnectionString = m_strConnStr
        End Set
    End Property

    ''' <summary>
    ''' 构造函数，传入数据库连接串
    ''' </summary>
    ''' <param name="strConnStr">数据库连接串</param>
    Public Sub New(ByVal strConnStr As String)
        m_strConnStr = strConnStr
        m_conn = New System.Data.SqlClient.SqlConnection()
        m_conn.ConnectionString = m_strConnStr
        m_cmd = m_conn.CreateCommand()
        m_da = New System.Data.SqlClient.SqlDataAdapter(m_cmd)

        m_strErrorMessage = String.Empty


        m_strStackTrace = String.Empty
    End Sub

    ''' <summary>
    ''' 打开数据库连接
    ''' </summary>
    Public Sub OpenConnect()
        If m_conn IsNot Nothing Then
            If m_conn.State <> ConnectionState.Open Then
                m_conn.Open()
            End If
        End If

    End Sub

    ''' <summary>
    ''' 关闭数据库连接
    ''' </summary>
    Public Sub CloseConnect()
        If m_conn IsNot Nothing Then
            If m_conn.State = ConnectionState.Open Then
                m_conn.Close()
            End If
        End If
    End Sub

    ''' <summary>
    ''' 根据表名返回DataTable
    ''' </summary>
    ''' <param name="strTableName">表名</param>
    Public Function GetDataTableByTableName(ByVal strTableName As String) As DataTable
        Return GetDataTableBySQL(String.Format("select * from {0}", strTableName), strTableName)
    End Function

    ''' <summary>
    ''' 根据表名返回空DataTable
    ''' </summary>
    ''' <param name="strTableName">表名</param>
    Public Function GetEmptyDataTableByTableName(ByVal strTableName As String) As DataTable
        Return GetDataTableBySQL(String.Format("select * from {0} where 1=-1", strTableName), strTableName)
    End Function

    ''' <summary>
    ''' 根据SQL语句查询记录数据
    ''' </summary>
    ''' <param name="strSql">传入的SQL语句</param>
    ''' <returns>返回查询结果，以DataTable形式</returns>
    Public Function GetDataTableBySQL(ByVal strSql As String) As DataTable
        Return GetDataTableBySQL(strSql, "NewTable1")
    End Function

    ''' <summary>
    ''' 根据SQL语句查询记录数据
    ''' </summary>
    ''' <param name="strSql">传入的SQL语句</param>
    ''' <param name="strTableName">表名称</param>
    ''' <returns>返回查询结果，以DataTable形式</returns>
    Public Function GetDataTableBySQL(ByVal strSql As String, ByVal strTableName As String) As DataTable
        Dim dt As New DataTable()
        m_da.SelectCommand.CommandText = strSql
        m_da.SelectCommand.CommandType = CommandType.Text
        Try

            m_da.Fill(dt)

            dt.ExtendedProperties.Add("SQL", strSql)
            '将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder

            Return dt
        Catch ex As Exception
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            Throw ex
        End Try
    End Function

    Public Function GetDataTableBySQL(ByVal strSql As String, ByVal ParamArray cmdParms As SqlParameter()) As DataTable


        Dim cmd As New SqlCommand()
        PrepareCommand(cmd, m_conn, Nothing, strSql, cmdParms)
        Dim dt As New DataTable()
        m_da.SelectCommand = cmd
        Try

            m_da.Fill(dt)

            dt.ExtendedProperties.Add("SQL", strSql)
            '将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder

            Return dt
        Catch ex As Exception
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            Throw ex
        End Try
    End Function

[gxingmin]

 ''' <summary>
    ''' 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行...
    ''' </summary>
    ''' <param name="strSQL"></param>
    ''' <returns></returns>
    Public Function GetValueBySQL(ByVal strSQL As String) As String
        OpenConnect()
        Dim sc As New SqlCommand(strSQL, m_conn)
        Dim Value As String = sc.ExecuteScalar().ToString()
        CloseConnect()
        Return Value
    End Function

    ''' <summary>
    ''' 执行Sql语句
    ''' </summary>
    ''' <param name="strSqls">SQL语句数组</param>
    Public Sub ExecuteSQL(ByVal ParamArray strSqls As String())
        If m_cmd.Parameters IsNot Nothing Then
            m_cmd.Parameters.Clear()
        End If
        m_cmd.CommandType = CommandType.Text
        Try
            OpenConnect()
            m_cmd.Transaction = m_conn.BeginTransaction()
            For Each sql As String In strSqls
                m_cmd.CommandText = sql
                m_cmd.ExecuteNonQuery()
            Next
            m_cmd.Transaction.Commit()
        Catch ex As Exception
            m_cmd.Transaction.Rollback()
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            Throw ex
        Finally
            CloseConnect()
        End Try

    End Sub

    ''' <summary>
    ''' 保存最近一次发生异常或错误的信息
    ''' </summary>
    Public ReadOnly Property ErrorMessage() As String
        Get
            Return m_strErrorMessage
        End Get
    End Property

    ''' <summary>
    ''' 保存最近一次发生异常或错误的堆栈调用信息
    ''' </summary>
    Public ReadOnly Property StackTrace() As String
        Get
            Return m_strStackTrace
        End Get
    End Property

    ''' <summary>
    ''' 更新数据
    ''' </summary>
    ''' <param name="dtDataTables">数据表记录</param>
    Public Sub UpdateByDataTable(ByVal ParamArray dtDataTables As DataTable())
        Dim cb As New System.Data.SqlClient.SqlCommandBuilder(m_da)
        Try
            m_da.SelectCommand.CommandType = CommandType.Text
            OpenConnect()
            m_da.SelectCommand.Transaction = m_conn.BeginTransaction()
            '''//////////////开始事务					
            If dtDataTables Is Nothing OrElse dtDataTables.Length = 0 Then
                Throw New ApplicationException("没有可用于保存的数据！")
            End If
            For Each dt As DataTable In dtDataTables
                m_da.SelectCommand.CommandText = dt.ExtendedProperties("SQL").ToString()
                cb.RefreshSchema()
                m_da.Update(dt)
            Next

            '''//提交事务
            m_da.SelectCommand.Transaction.Commit()
        Catch ex As System.Data.OleDb.OleDbException
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            m_da.SelectCommand.Transaction.Rollback()
            '回滚事务
            Throw ex
        Catch ex As Exception
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            m_da.SelectCommand.Transaction.Rollback()
            '回滚事务
            Throw ex
        Finally
            CloseConnect()
        End Try
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="strProcedureName">存储过程名称</param>
    ''' <param name="param">参数列表</param>
    ''' <returns></returns>
    Public Function ExecuteProcedure(ByVal strProcedureName As String, ByVal param As SqlParameter()) As DataTable
        Dim dt As New DataTable()
        m_da.SelectCommand.CommandText = strProcedureName
        m_da.SelectCommand.CommandType = CommandType.StoredProcedure

        Try
            m_da.SelectCommand.Parameters.Clear()
            If param IsNot Nothing AndAlso param.Length > 0 Then
                For Each p As SqlParameter In param
                    m_da.SelectCommand.Parameters.Add(p)
                Next
            End If
            m_da.Fill(dt)

            Return dt
        Catch ex As Exception
            m_strErrorMessage = ex.Message
            m_strStackTrace = ex.StackTrace
            Throw ex
        End Try
    End Function


    ''' <summary>
    ''' 开启事务...
    ''' </summary>
    ''' <returns></returns>
    Public Function BeginTransaction() As SqlTransaction
        OpenConnect()
        Return m_conn.BeginTransaction()
    End Function
    ''' <summary>
    ''' 提交事务...
    ''' </summary>
    ''' <param name="Tran"></param>
    Public Sub CommitTransaction(ByVal Tran As SqlTransaction)
        If Tran IsNot Nothing Then
            Tran.Commit()
            Tran.Dispose()
            CloseConnect()
        End If
    End Sub
    ''' <summary>
    ''' 回滚事务...
    ''' </summary>
    ''' <param name="Tran"></param>
    Public Sub RollTransaction(ByVal Tran As SqlTransaction)
        If Tran IsNot Nothing Then
            Tran.Rollback()
            Tran.Dispose()
            CloseConnect()
        End If
    End Sub

    ''' <summary>
    ''' 继承事务，执行SQL...
    ''' </summary>
    ''' <param name="Tran"></param>
    ''' <param name="strSqls"></param>
    Public Sub ExecuteTranSQL(ByVal Tran As SqlTransaction, ByVal ParamArray strSqls As String())
        If m_cmd.Parameters IsNot Nothing Then
            m_cmd.Parameters.Clear()
        End If
        m_cmd.CommandType = CommandType.Text
        m_cmd.Transaction = Tran
        For Each sql As String In strSqls
            m_cmd.CommandText = sql
            m_cmd.ExecuteNonQuery()
        Next
    End Sub

    ''' <summary>
    ''' 继承事务，更新Table...
    ''' </summary>
    ''' <param name="Tran"></param>
    ''' <param name="dtDataTables"></param>
    Public Sub UpdateTranByDataTable(ByVal Tran As SqlTransaction, ByVal ParamArray dtDataTables As DataTable())
        Dim cb As New System.Data.SqlClient.SqlCommandBuilder(m_da)

        m_da.SelectCommand.CommandType = CommandType.Text
        m_da.SelectCommand.Transaction = Tran

        Dim ChangsCount As Integer = 0
        Dim ModifyCount As Integer = 0

        If dtDataTables Is Nothing OrElse dtDataTables.Length = 0 Then
            Throw New ApplicationException("没有可用于保存的数据！")
        End If
        For Each dt As DataTable In dtDataTables
            m_da.SelectCommand.CommandText = dt.ExtendedProperties("SQL").ToString()
            If dt.GetChanges() IsNot Nothing Then
                ChangsCount = dt.GetChanges().Rows.Count
                ModifyCount = m_da.Update(dt)

                If ModifyCount <> ChangsCount Then
                    Throw New Exception("更新失败！")
                End If


            End If
        Next
    End Sub

    ''' <summary>
    ''' 继承事务，执行存储过程...
    ''' </summary>
    ''' <param name="Tran"></param>
    ''' <param name="strProcedureName"></param>
    ''' <param name="param"></param>
    ''' <returns></returns>
    Public Function ExecuteTranProcedure(ByVal Tran As SqlTransaction, ByVal strProcedureName As String, ByVal param As SqlParameter()) As DataTable
        Dim dt As New DataTable()
        m_da.SelectCommand.Transaction = Tran
        m_da.SelectCommand.CommandText = strProcedureName
        m_da.SelectCommand.CommandType = CommandType.StoredProcedure

        m_da.SelectCommand.Parameters.Clear()
        If param IsNot Nothing AndAlso param.Length > 0 Then
            For Each p As SqlParameter In param
                m_da.SelectCommand.Parameters.Add(p)
            Next
        End If
        m_da.Fill(dt)
        Return dt

    End Function

    ''' <summary>
    ''' 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行...
    ''' </summary>
    ''' <param name="strSQL"></param>
    ''' <returns></returns>
    Public Function GetTranValueBySQL(ByVal Tran As SqlTransaction, ByVal strSQL As String) As String
        If m_cmd.Parameters IsNot Nothing Then
            m_cmd.Parameters.Clear()
        End If
        m_cmd.CommandType = CommandType.Text
        m_cmd.CommandText = strSQL
        m_cmd.Transaction = Tran
        Dim Value As String = m_cmd.ExecuteScalar().ToString()
        Return Value
    End Function


    ''' <summary>
    ''' 执行SQL语句，返回影响的记录数
    ''' </summary>
    ''' <param name="SQLString">SQL语句</param>
    ''' <returns>影响的记录数</returns>
    Public Function ExecuteSql(ByVal SQLString As String, ByVal ParamArray cmdParms As SqlParameter()) As Integer

        Using cmd As New SqlCommand()
            Try
                PrepareCommand(cmd, m_conn, Nothing, SQLString, cmdParms)
                Dim rows As Integer = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                Return rows
            Catch e As System.Data.SqlClient.SqlException
                Throw e
            End Try
        End Using

    End Function

    Private Sub PrepareCommand(ByVal cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdText As String, ByVal cmdParms As SqlParameter())
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
        cmd.Connection = conn
        cmd.CommandText = cmdText
        If trans IsNot Nothing Then
            cmd.Transaction = trans
        End If
        cmd.CommandType = CommandType.Text
        'cmdType;
        If cmdParms IsNot Nothing Then


            For Each parameter As SqlParameter In cmdParms
                If (parameter.Direction = ParameterDirection.InputOutput OrElse parameter.Direction = ParameterDirection.Input) AndAlso (parameter.Value Is Nothing) Then
                    parameter.Value = DBNull.Value
                End If
                cmd.Parameters.Add(parameter)
            Next
        End If
    End Sub

[gxingmin]

#Region "IDisposable 成员"

    ''' <summary>
    ''' 释放资源，关闭连接
    ''' </summary>
    Public Sub Dispose() Implements IDisposable.Dispose
        ' TODO:  添加 clsDBAccess.Dispose 实现
        If m_conn IsNot Nothing Then
            If m_conn.State = ConnectionState.Open Then
                m_conn.Close()
            End If
            m_conn.Dispose()
            m_conn = Nothing

            If m_cmd IsNot Nothing Then
                m_cmd.Dispose()
            End If
            If m_da IsNot Nothing Then
                m_da.Dispose()
            End If
        End If
    End Sub

#End Region
End Class