VB.NET学习（二）数据库操作

悠悠 2022-03-28 18:14 256阅读 0赞

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()