none
inserting bulk records with parameterized query RRS feed

  • Question

  • Hi all,

    I need some help regarding  bulk records insertion into Oracle DB using  parameterized query.

    like

     

     

    Current scenario in our application is to insert bulk records (>3000) into the Database table with the parameterized query.

    Eg :"INSERT INTO employees VALUES (?, ?, ?)";

    but the issue is the executenonquery(); is taking a long time and the operation is not successful.

    In our applciation Stored procedures are not suggested. so we need use only queries.

    Thanks in Advance

     Seshasai

    • Moved by edhickey Wednesday, July 27, 2011 3:08 PM (From:.NET 4: Windows Workflow Foundation)
    • Moved by edhickey Thursday, July 28, 2011 3:11 PM (From:Off-Topic Posts (Do Not Post Here))
    Wednesday, July 27, 2011 5:30 AM

Answers

All replies

  • Hi,

    You have asked this in a Windows Workflow forum.  What is the app you are trying to load data from?  Do you think this would be a better question for an Oracle-based forum, or can you give us some details about what you are doing so we can try and help you in one of these forums?

    Ed


    Ed Hickey http://blogs.msdn.com/b/edhickey/archive/2010/04/21/how-to-post-effectively-in-forums.aspx
    Wednesday, July 27, 2011 3:08 PM
  • Hi Ed,

    Thanks for your quick response  the app is in silverlight  and the database operations are from WCF services. This is devloped in c#.net  and the  task is to insert the bulk records(>3000) in oracle database through parameterized queries.

    The following code snippet is for inserting 3000 records into table. For testing purpose i am inserting the same values for all records that is not an issue.

                OleDbConnection con = new OleDbConnection();
                OleDbCommand cmd = con.CreateCommand();          
                try
                {
                    con.ConnectionString = DBConnectionstring;              
                    string tablecolumns = " LINE, REG, SUBREG, SUBREGI, SUBREG2, CTY_CD, CH_ID, PART_ID, PFORM_NBR, RET";  

                    string values = "4;02;55;10;25;BD;M;1111111;107090-999;0";
                   

                     string[] valArray = values.Split(';');


                    string sql = "INSERT INTO TABLETEST(" + tablecolumns + ") VALUES          (?,?,?,?,?,?,?,?,?,?,?)"; 

                   con.Open();
                    cmd.CommandText = sql;
                    cmd.Prepare();               

                    for (int i = 0; i < 3000; i++)
                    {
                       
                        for (int k = 0; k < valArray.Length; k++)
                        {

                            cmd.Parameters.Add(new OleDbParameter());

                            cmd.Parameters[k].Value = valArray[k].ToString();

                        }                 

                        cmd.ExecuteNonQuery();                  
                    }

                    con.Close();
                }
                catch (Exception ex)
                {
                    con.Close();
                    throw ex;
                } 

    Here cmd.ExecuteNonQuery(); is taking a long time and never comes back. Actually I have tried it for few records aswell like 2-3 records but it is not able to process.

    Thursday, July 28, 2011 5:03 AM
  • Hi,

    Thanks for the details - I'm going to try a Forum with OLEDB experts in it for you, hopefully they can help.

    Ed


    Ed Hickey http://blogs.msdn.com/b/edhickey/archive/2010/04/21/how-to-post-effectively-in-forums.aspx
    Thursday, July 28, 2011 3:10 PM
  • Hi,

    I suggest you use SQL Server Profiler to show how SQL Server resolves queries internally.

    Second, from your code, there are some places were costed many times to execute. E.g. You should create more than 3000 times about the object of OleDbParameter. You should execute 3000 times ExecuteNonQuery method.

    Regarding the performance considerations of bulk insertation, I suggest you can check the following two documents, may be let you have some ideas.

    http://msdn.microsoft.com/en-us/library/ms187042.aspx

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 29, 2011 3:39 AM