Using sql CE and sqlbulkcopy

c# sqlbulkcopy sql-server-ce

Question

Can SqlBulkcopy be used with Sql Compact Edition files, such as (*.sdf) files?

While I am aware that it works with SQL Server 200 Up, I was curious about CE compatibility.

If not, does anybody else know how to import a CSV-type file into SQL Server CE quickly and without the use of DataSets?

1
14
5/23/2010 1:53:50 AM

Expert Answer

Not at all, in my opinion.SqlBulkCopy is endorsed (see MSDN). Maybe enter the data as xml and decode it at the server? In 2005 and 2008, SQL and XML are rather decent.

Table-value-parameters are another option you may want to consider, however I suspect CE would support them.

0
10/22/2009 11:09:08 AM

Popular Answer

SQL CE does not support BULKCOPY. If your table has a large number of rows, this is the quickest method since insert is too sluggish!

using (SqlCeConnection cn = new SqlCeConnection(yourConnectionString))
{
    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 (var sr = new System.IO.StreamReader(yourTextFilePath))
            {
                string line;
                while ((line = sr.ReadLine()) != null)
                {
                    int index = 0;
                    string[] values = line.Split('\t');

                    //write these lines as many times as the number of columns in the table...
                    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]);

                    rs.Insert(record);
                }
            }
        }
    }
}

Benchmark: 34370 rows in a table

  • 38 rows are written per second with inserts.

  • such a way: Writing 260 rows per second



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow