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 String, ByVal aTab As String, ByRef 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()