send several SQL stements in one call....

  • Thread starter Thread starter Lucas Sain
  • Start date Start date
L

Lucas Sain

Hi,

I have a method that loads a ArrayList with SQL statements that where
created by this same mehtod, These can be INSERTS, DELETES, UPDATES. Then it
calls the BLL which has a simle swicth and a call to the DAL:

case DBType.Oracle:
{
foreach(string constructedClause in storedConstructedClauses) //this is
the array
{
oracleDAL.TableMaintenance(constructedClause.Trim());
}
break;
}
......

Fore each row it finds in the ArrayList it calles:
try
{
OracleConnection oracleConnection = new OracleConnection(
ConnectionString );
OracleCommand com = new OracleCommand(constructedClause,
oracleConnection);
com.CommandType = CommandType.Text;
ExecuteNonQueryAndClose(oracleConnection,com);
}
catch( Exception ex)
{
throw new LidersoftDataAccessException(ex.Message);
}

This works great but how can I (if possible) send Just one string with ALL
the statemnts in the array list to the DB SERVER in this case ORACLE.. I
tried this but kept getting an oracle error, first I got the error
"statement not ended correctly..." becasue I was not putting the ";" at the
end of each SQL statement so I added them and then GOT err5or "0911",
illegal character.... SO.... can this be done... how so....

Regards
Lucas
 
Lucas Sain said:
Hi,

I have a method that loads a ArrayList with SQL statements that where
created by this same mehtod, These can be INSERTS, DELETES, UPDATES. Then it
calls the BLL which has a simle swicth and a call to the DAL:

case DBType.Oracle:
{
foreach(string constructedClause in storedConstructedClauses) //this is
the array
{
oracleDAL.TableMaintenance(constructedClause.Trim());
}
break;
}
.....

Fore each row it finds in the ArrayList it calles:
try
{
OracleConnection oracleConnection = new OracleConnection(
ConnectionString );
OracleCommand com = new OracleCommand(constructedClause,
oracleConnection);
com.CommandType = CommandType.Text;
ExecuteNonQueryAndClose(oracleConnection,com);
}
catch( Exception ex)
{
throw new LidersoftDataAccessException(ex.Message);
}

This works great but how can I (if possible) send Just one string with ALL
the statemnts in the array list to the DB SERVER in this case ORACLE.. I
tried this but kept getting an oracle error, first I got the error
"statement not ended correctly..." becasue I was not putting the ";" at the
end of each SQL statement so I added them and then GOT err5or "0911",
illegal character.... SO.... can this be done... how so....
Click to expand...

This is the confusing distinction between SQL and PL\SQL. ";" is illegal in
SQL. It is the statement terminator in PL\SQL.

To send multiple statements, you need to use a PL\SQL program or block. A
trivial program looks like:

begin
insert into foo(i) values(1);
insert into foo(i) values(2);
end;

or

cmd.CommandText = "
begin insert into foo(i) values(1); insert into foo(i) values(2); end;"

The line breaks are not necessary.

BTW what you should _really_ do is go to
http://otn.oracle.com/software/tech/windows/odpnet/
and get the oracle ODP.NET data provider for DotNet. It supports array
binding of parameters, where you send one command and a whole array of
parameter values. Which is a whole lot more efficient than sending all the
SQL statements seperately. For instance this is how SQL*LOADER's
Conventional path inserts work. In other words, it's fast.


David

David
 
hmmm. Trying again.



This is the confusing distinction between SQL and PL\SQL. ";" is illegal in
SQL. It is the statement terminator in PL\SQL.

To send multiple statements, you need to use a PL\SQL program or block. A
trivial program looks like:

begin
insert into foo(i) values(1);
insert into foo(i) values(2);
end;

or

cmd.CommandText = "begin insert into foo(i) values(1); insert into foo(i)
values(2); end;"

The line breaks are not necessary.

BTW what you should _really_ do is go to
http://otn.oracle.com/software/tech/windows/odpnet/
and get the oracle ODP.NET data provider for DotNet. It supports array
binding of parameters, where you send one command and a whole array of
parameter values. Which is a whole lot more efficient than sending all the
SQL statements seperately. For instance this is how SQL*LOADER's
Conventional path inserts work. In other words, it's fast.


David
 
David,

I'll try your sugestion and I will check out the Web you are pointing me
to.

Regards
Lucas
 
Back
Top