sqlbulkcopy using sql CE

Question

Is it possible to use SqlBulkcopy with Sql Compact Edition e.g. (*.sdf) files?

I know it works with SQL Server 200 Up, but wanted to check CE compatibility.

If it doesnt does anyone else know the fastest way of getting a CSV type file into SQL Server CE without using DataSets (puke here)?

Answer 1

No, I don't think that SqlBulkCopy is supported (see MSDN). Perhaps throw the data in as xml and strip it apart at the server? SQL/XML is pretty good in 2005/2008.

You might also want to look at table-value-parameters, but I doubt that CE supports these.

Answer 2

TableDirect is pretty fast.

Answer 3

            try
            {
                if (cn.State == ConnectionState.Closed)
                    cn.Open();

                using (SqlCeCommand cmd = new SqlCeCommand())
                {
                    cmd.Connection = cn;
                    cmd.CommandText = "YourTableName";
                    cmd.CommandType = CommandType.TableDirect;

                    using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
                    {
                        SqlCeUpdatableRecord record = rs.CreateRecord();

                        using (StreamReader sr = new StreamReader(YourSourcePath))
                        {
                            string line;
                            while ((line = sr.ReadLine()) != null)
                            {
                                int index = 0;
                                string[] values = line.Split('\t');

                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
                                record.SetValue(index, values[index++] == "NULL" ? null : values[index - 1]);
//
//write these lines above as many times as the number of columns in the table
//
                                rs.Insert(record);
                            }
                        }
                    }
                }

                cn.Close();
}
catch (Exception e)
            {  }

this is the fastest way if you have a huge number of rows in your table, insert is too slow!

benchmark:

table with 34370 rows

-with inserts: 38 rows written per second

-this way: 260 rows written per second

BULK is not supported in sqlce

Answer 4

Is possible to increase a lot this kind of operation. To turn this operation usefull (I mean fast and pretty safe), u can use CE DataAdapter.

By sample, no care about keys, the steps listed bellow can help u:

1.- Make sure that sorce and target tables have same fields structure; 2.- Clone a virtual datatable with a datatable from source database (your select); 3.- Create a CE command with the table name as commandtext (TableDirect as commandtype); 4.- Create a CE dataadapter from CE command; 4.- Create a CE commandbuilder from CE dataatapter; 5.- Pass the Insert command from CE commandbuilder to CE dataadapter; 6.- Copy "n" batch rows from your source datatable to the target datatable (the clone), doing something like this: '... previous codes For Each currentRow In sourceTable.Rows 'u can do RaiseEvent Processing(currentRow, totalRows) here with DoEvents If targetTable.Rows.Count < 100 Then targetTable.InportRow(currentRow) targetTable.Rows(targetTable.Rows.Count - 1).SetAdded Else '...Here you wll call the CE DataAdapter's Update method (da.Update(targetTable)) '...and then be sure you clone the targetTable again, erasing all previous rows. '...Do a clone again, don't do just a "clear" in the Rows collection. '...If u have an Autoincrement it will break all Foreign Keys. End If Next '... next codes

With this way u can update several rows with no much time. I've some applications using this method and the average rate is about 1500 rows per second in a table with 5 NTEXT fields (slow) and 800000 rows.

Of course, all depends of your table's structure. IMAGE and NTEXT are both slow datatypes.

P.S.: As I said, this method don't care so much about keys, so be carefull.