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

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 28 Jan 2009 08:29:34 -0800 (PST)
Message-ID: <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.

Received on Wed Jan 28 2009 - 10:29:34 CST

This message: [ Message body ]
Next message: Andreas Mosmann: "Re: EXISTS"
Previous message: Michel Cadot: "Re: EXISTS"
In reply to: dana_at_work_at_yahoo.com: "Submitting a batch/block of insert statements using C#?"
Next in thread: Malcolm Dew-Jones: "Re: Submitting a batch/block of insert statements using C#?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message