Asp.net executes multiple oralce statements at a time.

 

Asp.net needs to execute multiple statements at a time, mainly by combining multiple SQL statements into statements that can be executed by Oracle.

 

Combination method can refer to Web site: http://www.cnblogs.com/scottckt/archive/2009/03/30/1425300.html

The statement to be executed can be a delete statement, a upadate statement, or an insert statement, but not a query statement.

ExampleCode:

  String Ssql =   " Delete from hp_satisfactionitem where sfiid = '{0 }'; " ;
Ssql =   String . Format (ssql, "ID ");
String Ssubsql =   " Delete from hp_satisfactionresult where sfrid = '{0 }'; " ;
Ssubsql =   String . Format (ssubsql, "ID ");

Stringbuilder sbbuilder =   New Stringbuilder ();
Sbbuilder. append ( " Begin " ). Append ( " \ N " );
Sbbuilder. append (ssql). append ( " \ N " );
Sbbuilder. append (ssubsql). append ( " \ N " );
Sbbuilder. append ( " End; " );

//Execute multiple statements
Executenonsql (sbbuilder. tostring ());

 

Executenonsql method code:

  ///   <Summary>
/// Execution statement
///   </Summary>
///   <Param name = "ssqlstring"> </param>
///   <Returns> </returns>
Public   Static   Int Executenonsql ( String Ssqlstring)
{
Int Iretruncount =   0 ;
Try
{
Sconnectionstring = Configurationmanager. configurettings. Get ( " Userconnection " );
Oracleconnection occonnection =   New Oracleconnection (sconnectionstring );
Oraclecommand occommand = Occonnection. createcommand ();
// The command type is stored procedure.
Occommand. commandtype = Commandtype. text;
Occommand. commandtext = Ssqlstring;
Occonnection. open ();
Iretruncount = Occommand. executenonquery ();
Occommand. Dispose ();
Occonnection. Close ();

}
Catch(Exception ex)
{
ThrowEx;
}

ReturnIretruncount;
}

 

 

Note:

In this example, the update statement cannot be placed in multiple rows. For example:

Ssql = @" Update Hp_satisfactionitem Set Sfititle = ' Test ' , Sfistyle = 0 , Sfiisdescription = ' 1 ' ,
Sfidescriptiontitle = ' Test ' , Sfiorder = 1 , Sfideptid = ' D024 ' , Sfinote = ' 11 '   Where Sfiid = ' D6857915-d3ae-4d7f-b908-876d80bce909 ' ;
Update Hp_satisfactionresult Set Sfrvaluetype = 1 , Sfritemlength = 10 , Sfritemone = ' 11 ' , Sfritemtwo = ' 22 ' ,
Sfritemthree = ' 33 ' , Sfritemfour = ' 44 ' , Sfritemfive = ' 55 ' , Sfritemsix = ' 66 ' , Sfritemseven = ' 77 ' , Sfritemeight = ' 88 ' , Sfritemnine = ' 99 ' , Sfritemten = ' 110 '   Where Sfrid = ' D6857915-d3ae-4d7f-b908-876d80bce909 ' ;";

In this way, the following error is reported during running.

PLS-00103: the sign "" appears when one of the following is required:
Begin case declare end
Exception exit for goto if loop mod null Pragma raise return
Select update while with <an identifier>
<A double-quoted delimited-identifier> <A Bind Variable> <
Close current Delete fetch lock insert open rollback
Savepoint set SQL execute commit forall merge
<A single-quoted SQL string> Pipe