June 18, 2008 at 3:12 pm
Folks,
When it comes to working with Oracle, I'm relatively inexperienced and so I cannot figure out why these two operations don't appear to be working. I have similar parameterized queries working which return data from Oracle that is parsed/processed with an OracleDataReader.
The purpose of this code is to grab data that has already been placed in a SQL Server table and then place it into an Oracle table. Immediately before inserting this data, I want to ensure that any existing data in the Oracle table is deleted in order to prevent duplication of it. So the code attempts to run a delete query to remove existing data by it's store number and then the date, then it inserts the data from SQL Server to Oracle by store and date.
I wrapped a try/catch block around the code, but neither of the statements are throwing errors.
I can run a SELECT COUNT(*) WHERE STORNUM = 1 against the Oracle table in Toad and see that it has records, the DELETE runs, and yet all of the records are still present in the Oracle table...as if the process never executed.
No errors are being generated by either SQL or Oracle, and no errors are coming from the C# code.
I'd sure appreciate it if anyone can point me toward a "fix" for this code of mine! 😀
Here it is:
public static void OracleInsert( DateTime tgtDate, NagMsg nag, tlogStatus myStat )
{
string sConn, sOraDEL, sOraINS;
sConn = "Data Source=ITEM;User ID=myUSERID;Password=myPASSWORD";
sOraDEL = "DELETE FROM FSM.FSM_SALIENT_TLOG t " +
"WHERE (t.STORNUM = :store) " +
" AND (t.DAYDT = TO_DATE( :strDate, 'mm/dd/yyyy')); ";
sOraINS = "INSERT INTO FSM.FSM_SALIENT_TLOG t " +
"FROM @TLOG-SERVER.TLOG.FSM_SALIENT_TLOG s " +
"WHERE (s.STORNUM = :store) " +
" AND (s.DAYDT = CONVERT( datetime, :strDate)); ";
/* adapter to Status table */
TLogDataSetTableAdapters.TLogStatusTableAdapter taStatus =
new TLogHome.TLogDataSetTableAdapters.TLogStatusTableAdapter( );
TLogDataSet.TLogStatusDataTable Status = taStatus.GetData( tgtDate ); /* get the status table */
foreach ( TLogDataSet.TLogStatusRow iRow in Status )
{
try
{
/* clear the Oracle table for this store */
using ( OracleConnection cn = new OracleConnection( sConn ) )
{
OracleCommand cmd = new OracleCommand( sOraDEL, cn );
cmd.Parameters.AddWithValue( ":store", iRow.Store );
cmd.Parameters.AddWithValue( ":strDate", tgtDate.ToShortDateString( ) );
nag.AddLine( "Clearing Oracle data before INSERT for store: " + iRow.Store.ToString( ) );
cn.Open( ); /* this OPEN executes query command */
cn.Close( ); /* now close it up */
cmd.Dispose( ); /* clean up obj */
cn.Dispose( );
}
using ( OracleConnection cn = new OracleConnection( sConn ) )
{
OracleCommand cmd = new OracleCommand( sOraINS, cn );
cmd.Parameters.AddWithValue( ":store", iRow.Store );
cmd.Parameters.AddWithValue( ":strDate", tgtDate.ToShortDateString( ) );
nag.AddLine( "Inserting Oracle FSM_SALIENT_TLOG data for store: " + iRow.Store.ToString( ) );
cn.Open( );
cn.Close( );
cmd.Dispose( );
cn.Dispose( );
}
}
catch ( Exception e )
{
Console.WriteLine( "Exception thrown from Oracle operations for store: " + iRow.Store.ToString( ) );
Console.WriteLine( "\tException message: " + e.Message + "\tInner Exception: " + e.InnerException.Message );
}
} /* end foreach */
nag.AddLine( "Completed INSERT INTO FSM_SALIENT_TLOG." );
} /* end OracleInsert() */
DANG!!! Sorry that this code didn't come out formatted a little better!
July 8, 2008 at 4:04 am
Hi Grasshopper,
I spotted a simple mistake, you forgot to execute the command! You open the connection and then close it.
Try putting
cmd.ExecuteNonQuery();
between the Open and Close statements this is way nothing appears to happen.
By the way the logic you are using is a bit about face. You just need to create a connection/command object once and then loop though rows and update the parameters value with the new row value, thats will improve performance a lot if you are updating a couple of hundred rows, instead of creating the connection/command and parameters for each row. If you are just updating one row then forget what I have just said!
Regards
Richard...
http://www.linkedin.com/in/gbd77rc
July 8, 2008 at 7:40 am
Wow!
Thanks a bunch Richard! Let me tear back into my code and see what I have it doing now. I think I figured out the ExecuteNonQuery() piece, but I'm still likely to have the same looping structure.
July 8, 2008 at 12:55 pm
Many thanks again Richard.
The ExecuteNonQuery() call was already present, but I restructured the loop a bit so that it only initialized the OracleConnection object once before entering the foreach( ) loop.
Although it is very hard to quantify any performance improvements yet, it does run slightly faster running the new mods. Old run time was 3:56 and the modified version you suggested is running at 3:31...about a 14% improvement in run duration.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply