VB.NET学习(二)数据库操作

  1. 建立数据源的链接
    • Imports System.Web.Services
      Imports System.EnterpriseServices
      Imports Oracle.DataAccess.Client
      Imports Oracle.DataAccess.Types
      Imports System.Configuration
      Imports System.Net
      Imports System.Web
      Imports System.Xml

          
      Private OraConn As New OracleConnection
          
      Private OraCmd As New OracleCommand
          
      Private UserID As String
          
      Private Password As String
          
      Private DataSource As String
          
      Private OraTrans As OracleTransaction

          
      <WebMethod()> _
          
      Public Function OpenOraDatabase() As Boolean

              UserID 
      = ConfigurationSettings.AppSettings("UserID")
              Password 
      = ConfigurationSettings.AppSettings("Password")
              DataSource 
      = ConfigurationSettings.AppSettings("DataSource")

              OraConn.ConnectionString 
      = "user id=" & UserID & ";password=" & Password & ";data source=" & DataSource
              OraConn.Open()
              OraCmd.Connection 
      = OraConn

          
      End Function
    • Imports System.Web.Services
      Imports System.EnterpriseServices
      Imports System.Configuration
      Imports System.Net
      Imports System.Web
      Imports System.Xml
      Imports System.Text
      Imports System.Runtime.InteropServices
      Imports System.Threading

         
          Private OraConn As New OleDb.OleDbConnection
              
      Private OraCmd As New OleDb.OleDbCommand
              
      Private UserID As String
              
      Private Password As String
              
      Private DataSource As String
              
      Private OraTrans As OleDb.OleDbTransaction
              
      Private OraDa2 As OleDb.OleDbDataAdapter

              
      <WebMethod()> _
              
      Public Function OpenOraDatabase() As Boolean

                  UserID 
      = ConfigurationSettings.AppSettings("DenUserID")
                  Password 
      = ConfigurationSettings.AppSettings("DenPassword")
                  DataSource 
      = ConfigurationSettings.AppSettings("DenDataSource")

                  OraConn.ConnectionString 
      = "Provider=MSDAORA.1;user id=" & UserID & ";password=" & Password & ";data source=" & DataSource
                  OraConn.Open()
                  OraCmd.Connection 
      = OraConn

              
      End Function
  2. 关闭连接

            <WebMethod()> _
            
    Public Function CloseOraDatabase() As Boolean

                OraConn.Close()

            
    End Function
  3. 事务处理
    • WebService定义
              <WebMethod()> _
              
      Public Sub BeginTrans()
                  
                  OraTrans.Dispose()
                  OraTrans 
      = OraConn.BeginTransaction(IsolationLevel.ReadCommitted)

              
      End Sub

              
      <WebMethod()> _
              
      Public Sub CommitTrans()

                  OraTrans.Commit()

              
      End Sub

              
      <WebMethod()> _
              
      Public Sub RollbackTrans()

                  OraTrans.Rollback()

              
      End Sub
    • 客户端调用
      Private Function WriteData()
              
      Dim wSMgt As New ServiceManagemet

              wSMgt.OpenOraDatabase()
              wSMgt.BeginTrans()

              
      Try
                  aSql 
      = ""

                  wSMgt.OraCmd.CommandText 
      = aSql
                  wSMgt.OraCmd.ExecuteNonQuery()

                  
      Next

                  wSMgt.CommitTrans()

              
      Catch ex As Exception
                  wSMgt.RollbackTrans()
              
      Finally
                  wSMgt.CloseOraDatabase()
              
      End Try
      End Function

    • 服务器端事务处理
                  OpenOraDatabase()

                  OraTrans 
      = OraConn.BeginTransaction()
                  OraCmd.Transaction 
      = OraTrans
                  
      Try
              
                      
      '打开记录集
                      aSql = ""
                      OraCmd.CommandText 
      = aSql
                      OraDa2 
      = New OleDb.OleDbDataAdapter(OraCmd)
                      OraDa2.Fill(aDS, 
      "TableName")
                      
      If aDS.Tables("TableName").Rows.Count <> 0 Then

                     
      '执行SQL命令
                      OraCmd.CommandText = aSql
                      OraCmd.ExecuteNonQuery()

                      OraTrans.Commit()
                  
      Catch ex As Exception
                      OraTrans.Rollback()
                  
      Finally
                      CloseOraDatabase()
                  
      End Try
  4. 执行SQL命令

    • 建立连接

          
      Private Function ConnectOraDatabase() As Boolean

              UserID 
      = ConfigurationSettings.AppSettings("UserID")
              Password 
      = ConfigurationSettings.AppSettings("Password")
              DataSource 
      = ConfigurationSettings.AppSettings("DataSource")

              OraConn.ConnectionString 
      = "user id=" & UserID$ & ";password=" & Password$ & ";data source=" & DataSource$
              OraCmd.Connection 
      = OraConn

          
      End Function
    • 获得记录集
          <WebMethod()> _
          
      Public Function GetData(ByVal aSql As StringByVal aTab As StringByRef aDs As DataSet) As Boolean

              
      Dim aRet As Boolean

              ConnectOraDatabase()

              OraCmd.CommandText 
      = aSql

              
      Dim OraDa As New OracleDataAdapter(OraCmd)
              
      If aDs.Tables.Contains(aTab) Then aDs.Tables(aTab).Clear()
              OraDa.Fill(aDs, aTab)

              
      If aDs.Tables(aTab).Rows.Count = 0 Then
                  aRet 
      = False
              
      Else
                  aRet 
      = True
              
      End If

              CloseOraDatabase()

              
      Return aRet

          
      End Function
    • 执行SQL命令
          <WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _
          
      Public Sub UpdateData(ByVal aSql As String)
              OpenOraDatabase()
              OraCmd.CommandText 
      = aSql
              OraCmd.ExecuteNonQuery()
              CloseOraDatabase()

          
      End Sub
    • 执行包
              OpenOraDatabase()

              OraCmd.CommandText 
      = "PACKNAME.PROCENAME"
              OraCmd.CommandType 
      = CommandType.StoredProcedure

              
      Dim paramSESSIONID As OracleParameter = OraCmd.Parameters.Add( _
               
      New OracleParameter("inSESSIONID", OracleDbType.Char, ParameterDirection.Input))

          OraCmd.Parameters(
      "inSESSIONID").Value = inSESSIONID

              OraCmd.ExecuteNonQuery()

              CloseOraDatabase()