VB.NET学习，二数据库操作

• 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 ;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 ;password=" & Password & ";data source=" & DataSource

  OraConn.Open()

  OraCmd.Connection = OraConn

  End Function

<WebMethod()> _

Public Function CloseOraDatabase() As Boolean

OraConn.Close()

End Function

• 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

• 建立连接

  Private Function ConnectOraDatabase() As Boolean

  UserID = ConfigurationSettings.AppSettings("UserID")

  Password = ConfigurationSettings.AppSettings("Password")

  DataSource = ConfigurationSettings.AppSettings("DataSource")

  OraConn.ConnectionString = "user ;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()