How to use oracle queries in Enterprise Library

Topics: Data Access Application Block
Jul 7, 2008 at 4:19 PM

Hi,

   I am using Enterprise Library 2.0 with Oracle backend in my C# project. I have couple of queries on this,

  1. How to use insert, update and delete queries (not through stored proc) in enterprise library 2.0? some sample to excute the query.

  2. I need to insert Master table first, take the primary key (system generated key id) then pass that value to Child table as a reference. How to accomplish this? i.e. like @@identity command in SQL Server, we have sequence.currval in oracle. how to get this current identity value as soon as insert the master insert query?

     When i implement this, i need to take care of concurreny also. Should use transaction for this? if yes how to do that?

 Pelase help me in this

 

-Anbu

 

  

Jul 7, 2008 at 7:51 PM
Hi

1. You can use text commands with your DML queries as shown in http://msdn.microsoft.com/en-us/library/cc511552.aspx
2. This should help http://msdn.microsoft.com/en-us/library/bb399230.aspx, although you'll need to translate the raw ADO.NET sample to the DAAB.
3. You can use ADO.NET's transactions directly, or rely on the .NET 2.0 TransactionScope class.

Fernando




anbuzone wrote:

Hi,

   I am using Enterprise Library 2.0 with Oracle backend in my C# project. I have couple of queries on this,

  1. How to use insert, update and delete queries (not through stored proc) in enterprise library 2.0? some sample to excute the query.

  2. I need to insert Master table first, take the primary key (system generated key id) then pass that value to Child table as a reference. How to accomplish this? i.e. like @@identity command in SQL Server, we have sequence.currval in oracle. how to get this current identity value as soon as insert the master insert query?

     When i implement this, i need to take care of concurreny also. Should use transaction for this? if yes how to do that?

 Pelase help me in this

 

-Anbu

 

  




Jul 8, 2008 at 1:28 PM
Thanks for the reply. The link sent by you talks about handling oracle sequences through datatables. but I am trying to execute the insert query and a select query which returns sequence of the table through .net Executesclar() method.

I have given my sample code here.

  string strquery = " insert into Table1 (Col1, Col2, Col3) Values(Table1_Seq.NextVal, 'Anbu', 'Test');     
                             Select Table1_Seq.CurrVal  from Dual;"

  Database Db = DatabaseFactory.CreateDatabase();

  dbcommand = db.GetSqlStringCommand(strQuery);
  object returnSeqValue = db.ExecuteScalar(dbcommand);

When i execute this, throws error like "Invalid Character /n;" since i execute multiple statement as batch.

This is possible in SQL Server by using 'Select @@Identity' with the insert query with ';' seperated.

How to do this same kind of operation for oracle. Please help me in this.

Thanks,
Anbu
Jul 8, 2008 at 2:14 PM
Hi,

While the article does show a DataAdapter, you can see that it uses an insert command that returns a value in a single DML sentence. You wouldn't use ExecuteScalar but ExecuteNonQuery with it. Unfortunately, I don't have an Oracle server handy to test it.

Fernando


anbuzone wrote:
Thanks for the reply. The link sent by you talks about handling oracle sequences through datatables. but I am trying to execute the insert query and a select query which returns sequence of the table through .net Executesclar() method.

I have given my sample code here.

  string strquery = " insert into Table1 (Col1, Col2, Col3) Values(Table1_Seq.NextVal, 'Anbu', 'Test');     
                             Select Table1_Seq.CurrVal  from Dual;"

  Database Db = DatabaseFactory.CreateDatabase();

  dbcommand = db.GetSqlStringCommand(strQuery);
  object returnSeqValue = db.ExecuteScalar(dbcommand);

When i execute this, throws error like "Invalid Character /n;" since i execute multiple statement as batch.

This is possible in SQL Server by using 'Select @@Identity' with the insert query with ';' seperated.

How to do this same kind of operation for oracle. Please help me in this.

Thanks,
Anbu