Bulk insert in SQL Server CE from text file or DataTable in Windows Mobile 6.1

Question

I want to insert more than 5 lac records in SQL Server CE in Windows Mobile 6.1

The code I am using is taking about 8 rows per second. which is just unbearable.

My code is as follows - I have DataTable with about 5 lac records:

     SqlCeCommand cmd1;
        for (int i = 0; i < table.Rows.Count; i++)
        {
            cmd1 = new SqlCeCommand("Insert into Master values('" + table.Rows[i].ItemArray[0].ToString() + "')", con);
            con.Open();
            cmd1.ExecuteNonQuery();
            con.Close();
        }

Please suggest alternatives (preferably bulk insert) that are faster.

check this link for more info about bulk insert: http://www.morgantechspace.com/2013/08/bulk-insert-records-into-sql-server.html — Ali Baghdadi, Nov 08 '13 at 12:04
Did you Google this? http://stackoverflow.com/a/1612453/242520 — ta.speot.is, Nov 08 '13 at 12:17
yes,you can create procedure and pass datatable,it will prevent round trip. — Anand, Nov 08 '13 at 12:24
@Anand This is SQL Server CE. There are no stored procedures. Are you confusing SQL Server CE with SQL Server? — ta.speot.is, Nov 08 '13 at 12:31

Binke · Answer 1 · 2013-11-11T09:28:39.077

Since you are not using SqlCommand for adding parameters safely as you should:

SqlCeCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO Master (MasterValue) VALUES (@masterValue)";
SqlCeParameter param = null;
param = new SqlCeParameter("@masterValue", SqlDbType.Text);
command.Parameters.Add(param); 
command.Parameters["@masterValue"].Value = table.Rows[i].ItemArray[0].ToString();

You can just go with string concatenation and build a long string containing all the inserts (this is not a very clean way of doing it, but if you know your data and there's no problem doing the inserts unsafe, then this could be an option)

string insert = string.Empty;
for(int i = 0; i < table.rows.Count; i++)
{
    insert += "Insert into Master values('" + table.Rows[i].ItemArray[0].ToString() + "');";
    if(i % 1000 == 0 || i+1 == table.rows.Count)
    {
        using (SqlCommand cmd1 = new SqlCommand(insert, con))
        {
            con.Open();
            cmd1.ExecuteNonQuery();
            con.Close();
            insert = string.Empty;
        }
    }   
}

Note that there is a semicolon at the end of each insert, and this is needed to run the inserts separately. Adjust the modulus to the number of inserts you want to do at a time.

ADO .NET Connection Pooling is good but you could save yourself the `Open` and `Close` calls, as well `Dispose`ing the `SqlCeCommand`. — ta.speot.is, Nov 08 '13 at 12:22

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Use SqlCeResultSet.Insert with a SqlCeUpdatableRecord. This answer demonstrates the process.

edited May 23 '17 at 12:21

Community

1
1

answered Nov 08 '13 at 12:19

ta.speot.is

26,914
8
68
96

score 0 · Answer 3 · answered Nov 09 '13 at 09:07

Instead of open/close the db for every entry, open before and after the sqlCommand:

CHANGE that

     SqlCeCommand cmd1;
    for (int i = 0; i < table.Rows.Count; i++)
    {
        cmd1 = new SqlCeCommand("Insert into Master values('" + table.Rows[i].ItemArray[0].ToString() + "')", con);
        con.Open();
        cmd1.ExecuteNonQuery();
        con.Close();
    }

TO

 SqlCeCommand cmd1 = new SqlCeCommand();
 con.Open();
 cmd1.Connection = con;
 for (int i = 0; i < table.Rows.Count; i++)
 {
     cmd1.CommandText = "Insert into Master values('" + table.Rows[i].ItemArray[0].ToString() + "')";
     cmd1.ExecuteNonQuery();
 }
 con.Close();

There may be faster techniques, for example using a parameterized query or a transaction that encapsulates all inserts and is committed at end.

 con.Open();
 SqlCeTransaction tx = conn.BeginTransaction();
 SqlCeCommand cmd1 = con.CreateCommand();
 cmd1.Transaction = tx;
 for (int i = 0; i < table.Rows.Count; i++)
 {
     cmd1.CommandText = "Insert into Master values('" + table.Rows[i].ItemArray[0].ToString() + "')";
     cmd1.ExecuteNonQuery();
 }
 tx.Commit();
 con.Close();

(untested code according to: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.transaction%28v=vs.100%29.aspx)

Bulk insert in SQL Server CE from text file or DataTable in Windows Mobile 6.1

3 Answers3