Is there a way to execute multiple pl/sql statement in c#

[DanielXIX]

Hi,
..
..

OracleCommand cmd = this.oraConn.CreateCommand();
cmd.CommandText = sqlStr;
oraConn.Open();
cmd.ExecuteNonQuery();
..
.
.

here ,i try to use batch sql statements in "sqlstr" but i got error.
is it possible ? Any ideas?


thanx

 

[ralphtrent]

If you can, it may be better to create an oracle funtion to execute the multiple request. Do you have the luxary?

 

[Aptitude]

I did this once for SQL Server, but I am sure the principal would work with Oracle as well.

const string BATCH_SEPERATOR = "GO";

string[] commands = sqlStr.Split(BATCH_SEPERATOR);
SqlCommand cmd = this.sqlConn.CreateCommand();
sqlConn.Open();
foreach (string commandText in commands)
{
    cmd.CommandText = commandText;
    cmd.ExecuteNonQuery();
}
sqlConn.Close();

The split is a little crude but it worked for me. The issue is if your script has GO anywhere that is not the end of a batch. I'm sure Oracle uses something more sensible like ; anyway.

 

[ralphtrent]

Here is full code for SQL

[blue]private void[/blue] Page_Load([blue]object[/blue] sender, System.EventArgs e)
{
	SqlConnection objConn = [blue]new[/blue] SqlConnection("server=MyServer;uid=sa;password=sa;database=Northwind;pooling=true;max pool size=6");
	objConn.Open();
	DataSet ds = [blue]new[/blue] DataSet();
	SqlCommand objComm = [blue]new[/blue] SqlCommand("select * from Products go; select * from Categories go");
	SqlDataAdapter objDA = [blue]new[/blue] SqlDataAdapter(objComm.CommandText,objConn);
	objDA.Fill(ds);
	[blue]if[/blue] (objConn.State == System.Data.ConnectionState.Open)
	{
	objConn.Close();
	}
	objDA = [blue]null[/blue];
	objComm = [blue]null[/blue];
	objConn =  [blue]null[/blue];

	[blue]foreach[/blue](DataTable dt [blue]in[/blue] ds.Tables)
	{
	DataGrid dg = [blue]new[/blue] DataGrid();
	dg.DataSource = dt;
	dg.DataBind();
	Page.Controls.Add(dg);
	}
	ds = [blue]null[/blue];
}

 

[IT4EVR]

The BindArray feature of ODP.NET will allow you to run a batch of commands as if they were one statement to the database.