none
How to insert multiple rows? RRS feed

  • Question

  •  

    hello,

     

    i got a problem. i'm working on a school project now i need to insert in C# around 300 rows in ±6seconds i have tryed alot of ways of inserting it all worked well but my problem now is it takes around 140 seconds till they all are inserted now my question is does any one maybe know a faster way for me ?

    Tuesday, December 2, 2008 9:06 AM

Answers

  • If you need to execute multiple inserts, you do not need to close connection after each insert. You can execute them all and then close connection. You also can reuse same command object. Your code should be like

     

    string tabel = "B_Keuring_H1";

    string rapnr = TaXXa_dim.Rapnr;

    string new_query = "";

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + Application.StartupPath + "\\database\\" + TaXXa_dim.database_rapportjaar + ";");

    OleDbCommand cmd2 = new OleDbCommand(new_query, con);

    con.Open();

    new_query = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '1', 'is er geheid', '01-01', 'n.v.t.');";

    cmd2.ExecuteNonQuery();

    cmd2.CommandText = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '2', 'welke ondergrond is van toepassing', '01-01', 'n.v.t.');";

    cmd2.ExecuteNonQuery();

    con.Close();

     

     

    Friday, December 5, 2008 10:44 AM
    Moderator

All replies

  • Even if you do not use bulk insert, you should expect better performance. You might have any issue with the way you do it (your code) or something wrong with the database. Can you post your code here and provide information about database? Do you load any data before inserting rows into database?

     

    Tuesday, December 2, 2008 11:28 AM
    Moderator
  •  VMazur wrote:
    Even if you do not use bulk insert, you should expect better performance. You might have any issue with the way you do it (your code) or something wrong with the database. Can you post your code here and provide information about database? Do you load any data before inserting rows into database?

     

     

    i'm useing a MS access database

    i found a little solution to the problem some ppl sayed to me that this could work

     

    Code Snippet

    string tabel = "B_Keuring_H1";

    string rapnr = TaXXa_dim.Rapnr;

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + Application.StartupPath + "\\database\\" + TaXXa_dim.database_rapportjaar + ";");

    string new_query = "INSERT INTO " + tabel + " VALUES " +

    // *** Hoofdstuk 1 *** //

    "('" + rapnr + "', '1', 'is er geheid', '01-01', 'n.v.t.')," +

    "('" + rapnr + "', '2', 'welke ondergrond is van toepassing', '01-01', 'n.v.t.');";

     

     

     

    but somehow i get a issue, hes saying that hes missing a ; at the end but as you see its there

     

    so i dunno if there something wrong with the code, neither i aint sure if this is a fast working code i did like to hear your opinion and maybe also a solution on this problem.

     

    Tuesday, December 2, 2008 2:45 PM
  • Your code is incorrect. You cannot insert multiple rows in one INSERT SQL statement just specifying values for multiple rows. I also do not see any specific reason why you are concatenating table name if it is predefined. So in simple situation your code should execute INSERT statement multiple times, one time per each row, something like

     

    string tabel = "B_Keuring_H1";

    string rapnr = TaXXa_dim.Rapnr;

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + Application.StartupPath + "\\database\\" + TaXXa_dim.database_rapportjaar + ";");

    string new_query = "INSERT INTO B_Keuring_H1 VALUES ("" + rapnr + "', '1', 'is er geheid', '01-01', 'n.v.t.')"

    execute SQL statement here

     ........

     

    new_query = "INSERT INTO B_Keuring_H1 VALUES ("" + rapnr + "', '2', 'welke ondergrond is van toepassing', '01-01', 'n.v.t.');";

    execute another SQL statement here

     

     

    To improve performance and avoid potential SQL injection, you also could use parameterized query to pass values. See next example about how to pass values to your SQL statement/stored procedure as parameters

     

    http://support.microsoft.com/kb/310070

     

    I am also not sure how you execute this statement, but the fastest way to execute any action queries, would be to use ExecuteNonQuery method of OleDbCommand class.

     

     

    Wednesday, December 3, 2008 11:15 AM
    Moderator
  • One correction for using string variable new_query. I think Mazur is trying to reuse this string variable every time you insert data, without newing new ones, thanks.

    Wednesday, December 3, 2008 10:49 PM
    Answerer
  • Thank you vmazur that you are helping me Smile i will take a look at the things that you say, by the way i'm useing the

    ExecuteNonQuery method of the OleDbCommand class, if you need more information to help me just say so and i will give you as much information as i can. thanks Smile

    Thursday, December 4, 2008 9:18 AM
  • I just corrected posted code, removing duplicated string variable declaration. Anyway, try to use this approach and see if it works for you. Using ExecuteNonQuery is what you need to execute actual SQL statements

    Thursday, December 4, 2008 11:28 AM
    Moderator
  •  VMazur wrote:

    I just corrected posted code, removing duplicated string variable declaration. Anyway, try to use this approach and see if it works for you. Using ExecuteNonQuery is what you need to execute actual SQL statements

     

    Okey, i will try it Smile

    Thursday, December 4, 2008 12:10 PM
  • i have tryed it, seems not to work or i'm doing something wrong, i will try the parameters.

     

    by the way about the script you posted i did this:

     

    Code Snippet

    string tabel = "B_Keuring_H1";

    string rapnr = TaXXa_dim.Rapnr;

    string new_query = "";

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + Application.StartupPath + "\\database\\" + TaXXa_dim.database_rapportjaar + ";");

    new_query = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '1', 'is er geheid', '01-01', 'n.v.t.');";

    OleDbCommand cmd2 = new OleDbCommand(new_query, con);

    cmd2.Connection.Open();

    cmd2.ExecuteNonQuery();

    cmd2.Connection.Close();

    new_query = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '2', 'welke ondergrond is van toepassing', '01-01', 'n.v.t.');";

    OleDbCommand cmd2 = new OleDbCommand(new_query, con);

    cmd2.Connection.Open();

    cmd2.ExecuteNonQuery();

    cmd2.Connection.Close();

     

     

    thats going actuely very slow, because i only got 2 inserts here but if the code is working it will be around 300+ inserts

    Thursday, December 4, 2008 12:58 PM
  • If you need to execute multiple inserts, you do not need to close connection after each insert. You can execute them all and then close connection. You also can reuse same command object. Your code should be like

     

    string tabel = "B_Keuring_H1";

    string rapnr = TaXXa_dim.Rapnr;

    string new_query = "";

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + Application.StartupPath + "\\database\\" + TaXXa_dim.database_rapportjaar + ";");

    OleDbCommand cmd2 = new OleDbCommand(new_query, con);

    con.Open();

    new_query = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '1', 'is er geheid', '01-01', 'n.v.t.');";

    cmd2.ExecuteNonQuery();

    cmd2.CommandText = "INSERT INTO " + tabel + " VALUES ('" + rapnr + "', '2', 'welke ondergrond is van toepassing', '01-01', 'n.v.t.');";

    cmd2.ExecuteNonQuery();

    con.Close();

     

     

    Friday, December 5, 2008 10:44 AM
    Moderator
  • thanks this looks great Smile i will try it.

    Friday, December 5, 2008 12:39 PM
  • your code worked, its great and fast thank you Smile

     

    this post can be closed my issue is fixed.

     

    Friday, December 5, 2008 1:22 PM