Re: Submitting a batch/block of insert statements using C#?

From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Thu, 29 Jan 2009 06:46:28 GMT
Message-ID: <8hcgl.552$N5.164_at_nwrddc01.gnilink.net>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:823f1ba8-d60d-46ad-b225-12a52b9906ff_at_t26g2000prh.googlegroups.com... On Jan 28, 10:19 am, dana_at_w..._at_yahoo.com wrote:
> A programmer colleague of mine asked if he could submit a batch of
> insert statements to Oracle (9i) using C#. And if there's any way to
> identify which insert statement, if any failed during the batch
> execution.
>
> The idea is to improve performance by preventing client/server back
> and forths, e.g. better to submit 1,000 insert statements as a batch/
> block, have the server execute the statements in one go, then send
> back a single response than to have 1,000 round-trips for each insert
> (rolling back if there's an error).
>
> Can this be done in C# without any PL/SQL? If so, how? The programmer
> is using this mechanism to communicate with Oracle:
>
> http://msdn.microsoft.com/en-us/library/system.data.oracleclient.aspx
>
> Although you can get a transaction defined using the above, then loop
> through your inserts, I don't think it's submitting everything to the
> server as a block. One final note: we can't use any solution requiring
> stored procedures on the server side; the admins won't permit it and
> this is a rule we can't change.
>
> Thanks.
>
> Dana

The best way to do this is probably to determine how to set up a SQL statement with bind variables in C#. In such a setup, there would be one parse call, and then repeated execution calls with new bind variable values. I do not have a C# example which sets up bind variables, but here is a VB.Net example which performs a SELECT using bind variables, which should be similar:

        Dim comData As New OracleClient.OracleCommand
        Dim snpData As OracleClient.OracleDataReader
        Dim strSQL As String = ""
        Dim dteStartDate As Date = CDate(cboStartDate.Text)
        Dim dteEndDate As Date = CDate(cboEndDate.Text)

        strSQL = "SELECT /*+ ORDERED */" & vbCrLf
        strSQL = strSQL & "  SR.LOGICAL_GROUP," & vbCrLf
        strSQL = strSQL & "  SR.ID RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "  SUM(LT.HOURS) HOURS," & vbCrLf
        strSQL = strSQL & "  MIN(LT.DAY24) FIRST_DAY," & vbCrLf
        strSQL = strSQL & "  MAX(LT.DAY24) LAST_DAY" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "(SELECT" & vbCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "  LT.DAY24," & vbCrLf
        strSQL = strSQL & "  ROUND(COUNT(DISTINCT C2.COUNTER)/10)
HOURS" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  (SELECT /*+ INDEX(LT,IND_LT_SHIFT_DATE)
*/" & vbCrLf
        strSQL = strSQL & "    LT.RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "    (TO_DATE( :StartDate1,'DD-MON-YYYY')
+C1.COUNTER) DAY24," & vbCrLf
        strSQL = strSQL & "    DECODE(SIGN(LT.CLOCK_IN-(TO_DATE

( :StartDate2,'DD-MON-YYYY')+C1.COUNTER)),-1,0,TRUNC((LT.CLOCK_IN-TRUNC (LT.CLOCK_IN))*240)) CI_PERIOD," & vbCrLf

        strSQL = strSQL & " DECODE(SIGN(LT.CLOCK_OUT-(TO_DATE ( :StartDate3,'DD-MON-YYYY')+C1.COUNTER)),1,239,0,239,TRUNC ((LT.CLOCK_OUT-TRUNC(LT.CLOCK_OUT))*240)) CO_PERIOD" & vbCrLf

        strSQL = strSQL & "  FROM" & vbCrLf
        strSQL = strSQL & "   (SELECT" & vbCrLf
        strSQL = strSQL & "      ROWNUM-1 COUNTER" & vbCrLf
        strSQL = strSQL & "    FROM" & vbCrLf
        strSQL = strSQL & "      PART" & vbCrLf
        strSQL = strSQL & "    WHERE" & vbCrLf
        strSQL = strSQL & "      ROWNUM<= :NumberDays) C1," & vbCrLf
        strSQL = strSQL & "    LABOR_TICKET LT," & vbCrLf
        strSQL = strSQL & "    SHOP_RESOURCE SR" & vbCrLf
        strSQL = strSQL & "  WHERE" & vbCrLf
        strSQL = strSQL & "    LT.SHIFT_DATE BETWEEN (TO_DATE

( :StartDate4,'DD-MON-YYYY')+C1.COUNTER)-1 AND (TO_DATE ( :StartDate5,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf

        strSQL = strSQL & " AND SUBSTR(LT.EMPLOYEE_ID,4,1)<>'0'" & vbCrLf

        strSQL = strSQL & " AND LT.CLOCK_OUT>(TO_DATE ( :StartDate6,'DD-MON-YYYY')+C1.COUNTER)" & vbCrLf

        strSQL = strSQL & " AND LT.RESOURCE_ID IS NOT NULL" & vbCrLf

        strSQL = strSQL & " AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" & vbCrLf

        strSQL = strSQL & " AND SR.LOGICAL_GROUP IS NOT NULL" & vbCrLf

        strSQL = strSQL & "    AND LT.RESOURCE_ID=SR.ID) LT," & vbCrLf
        strSQL = strSQL & "  (SELECT" & vbCrLf
        strSQL = strSQL & "    ROWNUM-1 COUNTER" & vbCrLf
        strSQL = strSQL & "  FROM" & vbCrLf
        strSQL = strSQL & "    PART" & vbCrLf
        strSQL = strSQL & "  WHERE" & vbCrLf
        strSQL = strSQL & "    ROWNUM<=240) C2" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  C2.COUNTER BETWEEN CI_PERIOD AND
CO_PERIOD" & vbCrLf
        strSQL = strSQL & "GROUP BY" & vbCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "  LT.DAY24) LT," & vbCrLf
        strSQL = strSQL & "  SHOP_RESOURCE SR" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  SR.LOGICAL_GROUP IS NOT NULL" & vbCrLf
        strSQL = strSQL & "  AND NVL(SR.ACTIVE_RESOURCE,'Y')='Y'" &
vbCrLf
        strSQL = strSQL & "  AND SUBSTR(SR.LOGICAL_GROUP,3,1)=' '" &
vbCrLf
        strSQL = strSQL & "  AND SR.ID=LT.RESOURCE_ID(+)" & vbCrLf
        strSQL = strSQL & "GROUP BY" & vbCrLf
        strSQL = strSQL & "  SR.LOGICAL_GROUP," & vbCrLf
        strSQL = strSQL & "  SR.ID" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  SR.LOGICAL_GROUP," & vbCrLf
        strSQL = strSQL & "  SR.ID"

        With comData
            '.Connection = dbVMFG
            .CommandText = strSQL
            .CommandType = CommandType.Text

            .Parameters.AddWithValue("StartDate1", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
            .Parameters.AddWithValue("StartDate2", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
            .Parameters.AddWithValue("StartDate3", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
            .Parameters.AddWithValue("NumberDays", CInt
(dteEndDate.Subtract(dteStartDate).Days) + 1)
            .Parameters.AddWithValue("StartDate4", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
            .Parameters.AddWithValue("StartDate5", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
            .Parameters.AddWithValue("StartDate6", Format(CDate
(cboStartDate.Text), "dd-MMM-yyyy"))
        End With

        snpData = comData.ExecuteReader()

        DataGridView1.Rows.Clear()
        Do While snpData.Read()
            DataGridView1.Rows.Add(snpData("logical_group"), snpData
("resource_id"), snpData("hours"))
        Loop

        snpData.Close()

        snpData = Nothing
        comData.Dispose()

---

I wonder if an anonymous PL/SQL block using a FORALL could be
submitted to the database to send all of the data to the server in one
pass?
http://www.oracle.com/technology/sample_code/tech/pl_sql/forall/readme.html
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/forall_statement.htm

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Your best bet is to do as Charles says with bind variables.  This will be 
most performant. (by a wide margin)  If C# supports binding arrays then you 
can do that and send 1 statement with array of 100 as bind values.  (Oracle 
has had an array interface for a long time).  This is not using pl/sql.  The 
Oracle OCI, ole objects, and their c++ interface have this (the array 
interface) so I am assuming c# must also.  I would do a search on 
otn.oracle.com and see what you find.  But bind variables are the way to go. 
(and yes with array processing you would know what row didn't get processed 
if any)
Jim 

Received on Thu Jan 29 2009 - 00:46:28 CST