mssqlconn.vb

<Visual Basic 数据库开发实例精粹（第二版）>一书首先介绍了Visual Basic(简称VB)开发的技巧和重点技术

Imports System.Data.SqlClient


Namespace ConnSqlServer
  Public Class SQLClass

    Private ls_sql As String
    Private Shared m_strConn As String = "server=localhost; uid =sa; pwd=; database=InfoProcSystemNet"   'InfoPro

    Public Sub New()
      MyBase.New()
    End Sub

    Public Property SQL() As String
      Get
        Return ls_sql
      End Get
      Set(ByVal Value As String)
        ls_sql = Value
      End Set
    End Property

    Public Shared Property ConnStr() As String
      Get
        Return m_strConn
      End Get
      Set(ByVal Value As String)
        m_strConn = Value
      End Set
    End Property

    Public Function Populate() As DataView
      Dim myConnection As SqlConnection
      Dim myCommand As SqlDataAdapter
      Dim ds As New DataSet
      myConnection = New SqlConnection(ConnStr)
      myCommand = New SqlDataAdapter(SQL, myConnection)
      'myCommand.Fill((ds, "vTable")
      'Populate = ds.Tables("vTable").DefaultView
      Return Nothing
    End Function

    '执行有返回值的SQL语句，返回ds
    Function GetDataSet(ByVal vsql As String, ByRef ds As DataSet) As String
      Dim szMessage As String = ""

      Try
        Dim myConnection As New SqlConnection(ConnStr)
        Dim mycommand As New SqlDataAdapter(vsql, myConnection)

        mycommand.Fill(ds)

        myConnection.Close()
        mycommand = Nothing
        myConnection = Nothing
      Catch ex As SqlException
        Dim errItem As SqlError
        Dim errString As String = ""
        For Each errItem In ex.Errors
          errString += ex.Message + " "
        Next
        szMessage = "SQL Error.Details follow:<br/><br/>" & errString
      Catch myException As Exception
        szMessage = "Exception: " + myException.ToString()
      End Try

      Return szMessage
    End Function

    Function SafeDbString(ByVal szSQL As String) As String
      Return Replace(szSQL, "'", "''")
    End Function

    '执行有返回值的SQL语句，返回dv
    Function GetDataView(ByVal vsql As String, ByRef dv As DataView) As String
      Dim szMessage As String = ""

      Try
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection(ConnStr)
        Dim mycommand As New SqlDataAdapter(vsql, myConnection)
        Dim ds As New DataSet
        Dim table As String = "tmpTable"
        mycommand.Fill(ds, table)
        dv = ds.Tables(table).DefaultView

        myConnection.Close()
        mycommand = Nothing
        myConnection = Nothing

      Catch ex As SqlException
        Dim errItem As SqlError
        Dim errString As String = ""
        For Each errItem In ex.Errors
          errString += ex.Message + " "
        Next
        szMessage = "SQL Error.Details follow:<br/><br/>" & errString

      Catch myException As Exception
        szMessage = "Exception: " + myException.ToString()
      Finally
        If dv Is Nothing Then
          dv = New DataView
        End If
      End Try

      Return szMessage
    End Function


    '执行无返回值的SQL语句
    Function RunSql(ByVal vsql As String) As String
      Dim szMessage As String = ""
      Try

        Dim myConnection As SqlConnection
        myConnection = New SqlConnection(ConnStr)

        Dim mycommand As New SqlCommand(vsql, myConnection)

        myConnection.Open()
        mycommand.ExecuteNonQuery()
        myConnection.Close()


      Catch ex As SqlException
        Dim errItem As SqlError
        Dim errString As String = ""
        For Each errItem In ex.Errors
          errString += ex.Message + " "
        Next
        szMessage = "SQL Error.Details follow:<br/><br/>" & errString

      Catch myException As Exception
        szMessage = "Exception: " + myException.ToString()
      End Try

      RunSql = szMessage

    End Function

    ' 取得刚插入记录后自动编号的值
    ' 如果失败，返回-1
    Function InsertAndGetNewID(ByVal vsql1 As String) As Long
      Dim nNewID As Long
      Dim vsql2 As String = " SELECT @@identity AS NewID "
      Dim szMessage As String = ""
      Dim myConnection As New SqlConnection(ConnStr)
      Dim DataRead As SqlDataReader

      myConnection.Open()

      Dim myCommand As New SqlCommand
      Dim myTrans As SqlTransaction
      Dim traName As String = "ST"

      ' Start a local transaction
      '            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, traName)
      myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, traName)
      ' Must assign both transaction object and connection
      ' to Command object for a pending local transaction
      myCommand.Connection = myConnection
      myCommand.Transaction = myTrans

      Try

        myCommand.CommandText = vsql1
        myCommand.ExecuteNonQuery()

        myCommand.CommandText = vsql2
        DataRead = myCommand.ExecuteReader()
        If DataRead.Read() Then
          nNewID = DataRead("NewID")
        End If
        DataRead.Close()

        myTrans.Commit()
      Catch e As Exception
        '// !!! Rollback 前不可加任何其它语句
        'DataRead.Close()
        myTrans.Rollback(traName)
        szMessage = e.Message.ToString
        szMessage &= "Neither record was written to database"
        nNewID = -1
        'Console.WriteLine(e.ToString())
      Finally
        myConnection.Close()
        myTrans = Nothing
        myConnection = Nothing
        myCommand = Nothing

      End Try

      Return nNewID

    End Function 'RunSqlTransaction


    Function RunSqlTransaction(ByVal vsql1 As String, ByVal vsql2 As String) As String
      Dim szMessage As String = ""
      Dim myConnection As New SqlConnection(ConnStr)
      myConnection.Open()

      Dim myCommand As New SqlCommand
      Dim myTrans As SqlTransaction
      Dim traName As String = "ST"

      ' Start a local transaction
      '            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, traName)
      myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, traName)
      ' Must assign both transaction object and connection
      ' to Command object for a pending local transaction
      myCommand.Connection = myConnection
      myCommand.Transaction = myTrans

      Try
        myCommand.CommandText = vsql1
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql2
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
      Catch e As Exception
        '// !!! Rollback 前不可加任何其它语句
        myTrans.Rollback(traName)
        szMessage = e.Message.ToString
        szMessage &= "Neither record was written to database"
        'Console.WriteLine(e.ToString())
      Finally
        myConnection.Close()
        myTrans = Nothing
        myConnection = Nothing
        myCommand = Nothing

      End Try

      Return szMessage
    End Function 'RunSqlTransaction

    Function RunSqlTransaction(ByVal vsql1 As String, ByVal vsql2 As String, ByVal vsql3 As String) As String
      Dim szMessage As String = ""
      Dim myConnection As New SqlConnection(ConnStr)
      myConnection.Open()

      Dim myCommand As New SqlCommand
      Dim myTrans As SqlTransaction

      ' Start a local transaction
      myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "ST")
      ' Must assign both transaction object and connection
      ' to Command object for a pending local transaction
      myCommand.Connection = myConnection
      myCommand.Transaction = myTrans

      Try
        myCommand.CommandText = vsql1
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql2
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql3
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
      Catch e As Exception
        myTrans.Rollback("ST")
        szMessage = e.Message.ToString
        szMessage &= "Neither record was written to database"
        'Console.WriteLine(e.ToString())
      Finally
        myConnection.Close()
        myTrans = Nothing
        myConnection = Nothing
        myCommand = Nothing

      End Try
      Return szMessage
    End Function 'RunSqlTransaction

    Function RunSqlTransaction(ByVal vsql1 As String, ByVal vsql2 As String, ByVal vsql3 As String, ByVal vsql4 As String) As String
      Dim szMessage As String = ""
      Dim myConnection As New SqlConnection(ConnStr)
      myConnection.Open()

      Dim myCommand As New SqlCommand
      Dim myTrans As SqlTransaction

      ' Start a local transaction
      myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "ST")
      ' Must assign both transaction object and connection
      ' to Command object for a pending local transaction
      myCommand.Connection = myConnection
      myCommand.Transaction = myTrans

      Try
        myCommand.CommandText = vsql1
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql2
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql3
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql4
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
      Catch e As Exception
        '// !!! Rollback 前不可加任何其它语句
        myTrans.Rollback("ST")
        szMessage = e.Message.ToString
        szMessage &= "Neither record was written to database"
        'Console.WriteLine(e.ToString())
      Finally
        myConnection.Close()
        myTrans = Nothing
        myConnection = Nothing
        myCommand = Nothing
      End Try
      Return szMessage
    End Function 'RunSqlTransaction

    Function RunSqlTransaction(ByVal vsql1 As String, _
                                ByVal vsql2 As String, _
                                ByVal vsql3 As String, _
                                ByVal vsql4 As String, _
                                ByVal vsql5 As String) As String
      Dim szMessage As String = ""
      Dim myConnection As New SqlConnection(ConnStr)
      myConnection.Open()

      Dim myCommand As New SqlCommand
      Dim myTrans As SqlTransaction

      ' Start a local transaction
      myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "ST")
      ' Must assign both transaction object and connection
      ' to Command object for a pending local transaction
      myCommand.Connection = myConnection
      myCommand.Transaction = myTrans

      Try
        myCommand.CommandText = vsql1
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql2
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql3
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql4
        myCommand.ExecuteNonQuery()
        myCommand.CommandText = vsql5
        myCommand.ExecuteNonQuery()
        myTrans.Commit()
      Catch e As Exception
        '// !!! Rollback 前不可加任何其它语句
        myTrans.Rollback("ST")
        szMessage = e.Message.ToString
        szMessage &= "Neither record was written to database"
        'Console.WriteLine(e.ToString())
      Finally
        myConnection.Close()
        myTrans = Nothing
        myConnection = Nothing
        myCommand = Nothing
      End Try
      Return szMessage
    End Function 'RunSqlTransaction

    Private Sub myMsgBox(ByVal str As String)
      Dim szMsg As String
      szMsg = "<script language=""javascript"">"
      szMsg &= "window.alert('" & str & "');"
      szMsg &= "window.navigate(""..\index.html"")"
      szMsg &= "</script>"
      'm_Response.Write(szMsg)
    End Sub

    Public Function TestDB(ByRef msg As String) As Boolean

      Dim myConnection As New SqlConnection(ConnStr)
      Dim mycommand As New SqlCommand
      mycommand.Connection = myConnection

      Try
        mycommand.Connection.Open()
        Return True
      Catch ex As Exception
        msg = ex.Message
        Return False
      Finally
        ' Finally, blocks are a great way to ensure that the connection 
        ' is always closed.
        mycommand.Connection.Close()
      End Try
    End Function


  End Class

End Namespace