Bulk insert from DataTable to SQLCE DataSource

问题:

This an C# WPF application with SQL CE as DataSource:

I have a DataTable (display as DataGrid) and a SQL CE DataSource. I populate my DataTable from SQL CE using DataAdapter, DataSet and DataTable. Then bind my DataGrid to the DataTable.

I may add rows (>10,000) rows to my DataTable and may have data edited before propagating all my changes all together to my Sql CE DataSource.

My current approach is DROP TABLE, CREATE TABLE, and re-INSERT rows by brute force to SQLCE. SQL CE has no bulk insert, and I do not want to use a 3rd library or dll. The performance is slow...

I am looking or a faster way to "bulk-insert" without the need to drop, create and insert row one-by-one.

I read something about SqlCeResultSet but I can't find any documentation and wonder does it has anything to do with what I'm trying to do.

[EDIT]
Following the answer and checking up on this:
http://ruudvanderlinden.com/2010/10/13/bulk-insert-into-sql-ce-in-c/

I tried to use the function but it didn't seem to work. Below is my code. Footable is my database table and I have two columns - "id" and "FooName".

Hashtable idHash = new Hashtable();
Hashtable fooNameHash = new Hashtable();
foreach(DataRow row in dt.Rows)
{
    idHash.Add("id",row["id"]);
    fooNameHash.Add("FooName",row["FooName"]);
}

List<Hashtable> colHashList = new List<Hashtable>();
colHashList.Add(idHash);
colHashList.Add(fooNameHash);

BulkInsertDatabase(colHashList, "FooTable");

It didn't work but I don't see any problem in my above code so hope someone can point it out..

[EDIT - 2nd][ANSWER]
Finally I get the code to work (though doubtful with the performance):

List<Hashtable> colHashList = new List<Hashtable>();

Hashtable[] idHash = new Hashtable[dt.Rows.Count];
Hashtable[] fooNameHash = new Hashtable[dt.Rows.Count];

int i=0;
foreach(DataRow row in dt.Rows)
{
    idHash[i] = new Hashtable();
    idHash[i].Add("id", row["id"]);
    colHashList.Add(idHash[i]);

    fooNameHash[i] = new Hashtable();
    fooNameHash[i].Add("FooName", row["FooName"]);
    colHashList.Add(fooNameHash[i]);

    i++;
}

BulkInsertDatabase(colHashList, "FooTable");

回答1:

You should use the SqlCeResultSet Class

it is allow bulk import in SQL CE data base I've imported 100000 row in a 10 seconds look at the example

回答2:

You can use the C# source code from my Bulk Insert Library here: http://sqlcebulkcopy.codeplex.com

回答3:

its work, try this.

 public bool CopyDataTableToTable(DataTable dataTable, string tableName, bool deleteTable)
        {
            Boolean returnValue = true;
            if (sqlCeConnection.State == ConnectionState.Closed)
                sqlCeConnection.Open();

            SqlCeTransaction transaction = sqlCeConnection.BeginTransaction();
            SqlCeCommand cmd = sqlCeConnection.CreateCommand();
            SqlCeResultSet rs = null;
            try
            {
                if (deleteTable)
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "DELETE " + tableName;
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandType = System.Data.CommandType.TableDirect;
                cmd.CommandText = tableName;
                rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    SqlCeUpdatableRecord rec = rs.CreateRecord();
                    DataRow row = dataTable.Rows[i];
                    for (int k = 0; k < dataTable.Columns.Count - 1; k++)
                    {
                        rec.SetValue(k + 1, row[k]);
                    }
                    rs.Insert(rec);
                }
                transaction.Commit();
            }

            catch (Exception ex)
            {
                returnValue = false;
                transaction.Rollback();
            }
            finally
            {
                rs.Close();
                if (sqlCeConnection.State == ConnectionState.Open)
                    sqlCeConnection.Close();

            }
            return returnValue;
        }