C# 与 Oracle PL/SQL 处理

Title:       "Processing with C# and Oracle PL/SQL "BIG" transactions"
Author:      Dimitar Nikolaev Madjarov 
Email:       madjarov_d_n@yahoo.com
Language:    MS Visual Studio 2003, 2005, ASP.NET, C# and Oracle DB
Platform:    Windows, .NET 3.0 etc
Technology:  ASP.NET, GDI+
Level:       Beginner, Intermediate, Advanced
Description: An article which describes an alternative way of cooperation between Oracle PL/SQL and C#
Section      SQL, PL/SQL, Database and C# programming
SubSection   General

引言

这篇文章实际上是我关于 Oracle 和 C# 编程的第一个主题文章的继承者。第一篇文章可以在以下网址阅读 “C#、Oracle 事务和 Oracle 异常处理”。在我的第一篇文章中，我描述了一种使用强大的 Oracle 事务的替代方法。通过这种方法，我们使用原始的 PL/SQL 代码来控制事务，而不是使用 C# 代码从应用程序的源代码中管理事务。当一个接一个地使用我们的 Oracle 存储过程时，这是有效的。在这种情况下，每个过程都使用存储过程体内的本地提交和回滚/事务控制。对于简单的应用程序来说，这很完美，但不幸的是，现实生活要复杂一些。在实际的业务逻辑中，我们有许多不同的执行存储过程的组合，它们以复杂的方式执行/许多时候这种顺序取决于先前存储过程的执行状态/当它们都执行了它们的 PL/SQL 代码后，我们就必须提交所有更改，或者当我们的执行列表顺序中有一个存储过程未能执行其 PL/SQL 时，我们必须回滚所有更改并触发一个带有关于结果的合适消息的事件，是我们的源代码失败还是成功完成了它的任务。

使用的 PL/SQL

首先，我们需要创建我们的演示表和演示包。以下是我们将在演示中使用到的所有 PL/SQL 代码。在本节中，我们将创建我们的表。请注意，我将字段 'CREATEDATE' 的数据类型创建为 'DATE'。我这样做是有意为之，主要目的是向您展示一个关于如何在 Oracle 和 C# 之间处理/交换 'DATE' 类型数据的小技巧，当您在 C# 代码中构建 PL/SQL 时。

CREATE TABLE T1
  (
    ID         NUMBER   (4) NOT NULL,
    NAME       VARCHAR2 (100),
    CREATEDATE DATE DEFAULT SYSDATE NOT NULL
 )
/

ALTER TABLE T1 ADD CONSTRAINT PK_T1_ID
  PRIMARY KEY (ID)
/



CREATE TABLE T2
  (
   ID   NUMBER   (4) NOT NULL,
   NAME VARCHAR2 (100)
 )
/

ALTER TABLE T2 ADD CONSTRAINT PK_T2_ID
  PRIMARY KEY (ID)
/

在本节中，我们将创建我们的插入存储过程

CREATE OR REPLACE PACKAGE PACKAGEDEMO IS

PROCEDURE T1ADD(ID   in number,  
                Name in varchar2, 
                CRDATE DATE,
                error_sql_value out varchar2,
                DoTrans in number default 1);
PROCEDURE T2ADD(ID   in number,  
                Name in varchar2, 
                error_sql_value out varchar2,
                DoTrans in number default 1);

END PACKAGEDEMO;
/

create or replace package body PACKAGEDEMO is

PROCEDURE T1ADD(ID in number, Name in varchar2, CRDATE DATE, 
                error_sql_value out varchar2, DoTrans in number default 1)
AS BEGIN
    error_sql_value   := '-1';
    INSERT INTO T1 (ID, NAME, CREATEDATE) VALUES (ID, Name, CRDATE);
    IF (DoTrans = 1) THEN
         COMMIT;
        END IF;
    EXCEPTION
    WHEN OTHERS THEN BEGIN
      IF (DoTrans = 1) THEN
       ROLLBACK;
      END IF;
     error_sql_value := 'STOREP ROCEDURE "T1ADD" in PACKAGE "PACKAGEDEMO" ERROR: '||SUBSTR(SQLERRM, 1, 255)||', ERROR CODE: '||SQLCODE;
    END;
END;


PROCEDURE T2ADD(ID in number, Name in varchar2, 
                error_sql_value out varchar2, DoTrans in number default 1)
AS BEGIN
    error_sql_value   := '-1';
    INSERT INTO T2 (ID, NAME) VALUES (ID, Name);
     IF (DoTrans = 1) THEN
           COMMIT;
         END IF;
    EXCEPTION
    WHEN OTHERS THEN BEGIN
      IF (DoTrans = 1) THEN
        ROLLBACK;
      END IF;
     error_sql_value := 'STOREP ROCEDURE "T2ADD" in PACKAGE "PACKAGEDEMO" ERROR: '||SUBSTR(SQLERRM, 1, 255)||', ERROR CODE: '||SQLCODE;
    END;
END;
end PACKAGEDEMO;
/     

PL/SQL 术语中的主要思想

现在我们将简要描述我的 PL/SQL 控制 Oracle 事务的思想是如何工作的

[I]。在第一点中，我们将使用 C# 的 'ExecuteNonQuery' 代码执行一个单独的存储过程作为 PL/SQL。当然，您也可以使用其他 C# 方法来执行存储过程，但我将使用 'ExecuteNonQuery' ADO.NET 方法，因为在下一节中，当我们必须构建一个复杂的业务逻辑时，我将确切地使用这种方法。（ExecuteNonQuery）

The deal here is to build the Pl/SQL above into your C# code and to 
execute it as using 'ExecuteNonQuery' method. Into a try/catch passage 
you have to catch the error and manipulate it. This example is how to 
use a simple transaction. Executed store procedure is alone and 
transactions management as Commit/Rollback statements are ruled 
into body of our stored procedure 't2add'.

DECLARE
error_sql_value varchar2 (4000);  
BEGIN
PACKAGEDEMO.t2add(10,'Test_10',error_sql_value);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;
 EXCEPTION
    WHEN OTHERS THEN BEGIN
    ROLLBACK;
    RAISE_APPLICATION_ERROR (-20000, error_sql_value);
 END;
COMMIT;
END;       

[II]。在第二点中，我们将执行一个包含两个不同存储过程的复杂方案。我们将使用相同的 ADO.NET 方法 'ExecuteNonQuery'，但现在我们无法控制每个存储过程中的事务，因为逻辑是如果第一个过程失败，我们必须回滚所有更改。同样，如果第一个过程通过但第二个过程失败，我们也必须回滚所有更改。我们必须在两个过程都通过的情况下设置提交更改。

The deal here is to build the Pl/SQL above into your C# code and to execute it as using 'ExecuteNonQuery' method. 

Into a try/catch passage you have to catch the error and manipulate it. 
Please notice that now parameter 'DoTrans" has value '0' and by this way we send a command to our store procedures
to stop manage transactions statements locally (inside the procedure body).

 Now we manage transactions as Pl/SQL in the SQL statement which we build inside of our C# code.

DECLARE
error_sql_value varchar2(4000);  
BEGIN
SAVEPOINT LOCALSP1;

PACKAGEDEMO.t1add(0,'Test_0', TO_DATE('17.04.2007 16:04:12','DD.MM.YYYY HH24:MI:SS'), error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

PACKAGEDEMO.t2add(0,'Test_0',error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

PACKAGEDEMO.t2add(1,'Test_1',error_sql_value,0);
IF (error_sql_value <> '-1') THEN
 RAISE_APPLICATION_ERROR (-20000, error_sql_value);
END IF;

 EXCEPTION
    WHEN OTHERS THEN BEGIN
    ROLLBACK TO LOCALSP1;
    RAISE_APPLICATION_ERROR (-20000, error_sql_value);
 END;
COMMIT;
END;       

C# 代码

到目前为止，我们已经完成了这篇文章中最有价值的一步。我希望您能理解上面的主要思想。在本节中，我将做更简单的任务，使用 C# 代码构建我们第 II 点的 PL/SQL，并使用 ADO.NET 方法 'ExecuteNonQuery' 执行该 PL/SQL。您可以使用我上一篇文章中的 Oracle 方法来访问 Oracle 数据库并从中管理数据。

using System;
using System.Data;
using System.Configuration;
using System.Text;
using System.IO;

namespace ORacle.Logic
{
        public class ORATransactions 
    {   
               public ORATransactions()
               {
                 //
                 // An empty method
                 //
               }
               
                
        private string OraString(string inputStr)
        {
            if (inputStr == null)
            {
                return "NULL";
            }
                        
              //
             // Replacing oracle special characters
             //
            inputStr = inputStr.Replace("'", "''");
                        
            //
            // Adding ' to string
            //
            inputStr = string.Format("'{0}'", inputStr);
                        
            return inputStr;
        }
                

                
        private string OraDateTime(DateTime date)
        {
                return date.ToString("dd.MM.yyyy HH:MM:ss");
        }
                
                
        public string UpdateOraSQL(ourTestData data) 
        {
            string ExecuteSQL  = String.Empty;
            
            ExecuteSQL  = "DECLARE \n";
            ExecuteSQL += "error_sql_value varchar2(4000); \n";
            ExecuteSQL += "BEGIN \n";
            ExecuteSQL += "SAVEPOINT LOCALSP1; \n";  
                        
                        //
            // Bind PACKAGEDEMO.t1add store procedure
            //
            ExecuteSQL += String.Format("PACKAGEDEMO.t1add({0}, {1}, TO_DATE('{2}','DD.MM.YYYY HH24:MI:SS'), error_sql_value, 0); \n",
                                     data.ID[0].ToString(), OraString(data.NAME[0].ToString()), OraDateTime(data.CRDATE[0].CREATED)
                                    );
            ExecuteSQL += "IF (error_sql_value <> '-1') THEN \n";
            ExecuteSQL += "RAISE_APPLICATION_ERROR (-20000, error_sql_value||' '||error_sql_descr); \n";
            ExecuteSQL += "END IF; \n";
                        
            //
            // Bind PACKAGEDEMO.t2add store procedure
            //
            ExecuteSQL     += String.Format("PACKAGEDEMO.t2add({0},{1},error_sql_value,0); \n",
                                        data.ID[1].ToString(), OraString(data.Name[1].ToString())
                                                        );
            ExecuteSQL     += "IF (error_sql_value <> '-1') THEN \n";
            ExecuteSQL     += "RAISE_APPLICATION_ERROR (-20000, error_sql_value||' '||error_sql_descr); \n";
            ExecuteSQL     += "END IF; \n";                      
                                               
                        ExecuteSQL += "EXCEPTION \n";
            ExecuteSQL += "WHEN OTHERS THEN BEGIN \n";
            ExecuteSQL += "ROLLBACK TO LOCALSP1; \n";
            ExecuteSQL += "RAISE_APPLICATION_ERROR (-20000, error_sql_descr||' '||error_sql_value); \n";
            ExecuteSQL += "END; \n";
            ExecuteSQL += "COMMIT; \n";
            ExecuteSQL += "END; \n";

            return ExecuteSQL;
         }
                
                
         public string UpdateOra(ourTestData data) 
         {

            string ReturnValue  = "-1";
                   OracleBuisinessDalc dalc        = new OracleBuisinessDalc();
            
            try
            {
                     ReturnValue = dalc.Execute_Non_Query(UpdateOraSQL(data));
            }
            catch (Exception ex)
            {
                ReturnValue = ex.Message;
            }
            finally
            {
                    return ReturnValue;
            }        
        }
        }
}



Finally you my execute the method string UpdateOra(ourTestData data) into your 'Page-Load' method for an example and you will 
receive '-1' if all is Ok or the full error description in case that something was wrong. 
Of course this is also a simple example but by this way you may compose very complicate business logic and finally to generate a 'big' string
which contain the PL/SQL similar as this from point.II. 

After that you may execute this string from your C#, VB.NET code and receive the status of executing SQL.

结论

在本文中，我试图解释一种稍微不同的处理 Oracle 事务和直接在存储过程中捕获 Oracle 异常的方法，并在不冒任何风险的情况下处理它们，以免影响与存储在 Oracle 中的数据交互的 ASP.NET/C# 应用程序。我想说的是，这只是一种与 Oracle 和 C# 交互的不同方式，也许不是完美的方式，但本文的作者知道，在生活中，拥有选择的自由是一件大事。我希望您能评价这篇文章，以及它向您描述的通过另一种方式处理 Oracle 事务的选项将对您和您的业务有用。