SqlBulkCopy的使用SQL CE [英] sqlbulkcopy using sql CE

问题描述

是否有可能使用使用SqlBulkCopy的SQL精简版例如（*。自卫队）文件？

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

我知道它的工作原理与SQL Server 200，但要检查CE兼容性。

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

如果它不没有任何人知道得到一个CSV文件类型到SQL Server CE无需使用数据集的最快的方法（在这里吐）？

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)?

推荐答案

BULKCOPY没有在SQL CE支持。这里是如果你在你的表行的数量庞大的最快方法;插入太慢了！

BULKCOPY is not supported in SQL CE. Here is the fastest way if you have a huge number of rows in your table; insert is too slow!

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);
                }
            }
        }
    }
}

基准：表34370行

Benchmark: table with 34370 rows

• 用刀片：每秒写38行

• with inserts: 38 rows written per second

是这样的：每秒写入260行

this way: 260 rows written per second

这篇关于SqlBulkCopy的使用SQL CE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！