更新/插入使用SQLCeResultSet表 [英] Update/Insert to a table using SQLCeResultSet

问题描述

我有定期更新（通过Web服务）一个SQL精简版数据库。

I have a SQL Compact Edition Database that I update periodically (via web services).

在我写数据库的部分占用的时间太长了。我目前使用LINQ做它的数据集（如此可见问题）。我已经听说，如果我。与SQLCeResultSet，这将工作做得更快

The part where I write to the database is taking way too long. I am currently doing it with Linq to Datasets (as seen in this question). I have heard that if I do it with with SQLCeResultSet that it will work faster.

所以，因为我有一个表是这样的：

So, given that I have a table like this:

tblClient
   +- CLIENT_ID      {Unique identifier} (Primary Key)
   +- CLIENT_NAME    {varchar (100)}
   +- CLIENT_ACTIVE  {bit}

和我有它的对象，我从这个样子我的Web服务得到：

And I have it in object that I get from my web services that look like this:

class Client
{
   public Guid ClientID { get; set; }
   public String ClientName { get; set; }
   public bool Active { get; set; }
}

我将如何得到100客户端对象到数据库？

How would I get 100 Client objects into the database?

更新现有行，并插入行已不在数据库中（按主键确定）？

Updating existing rows and inserting rows that are not already in the database (determined by the primary key)?

任何示例代码将是巨大的。我有一个的SqlCeConnection ，但仅此而已。

Any example code would be great. I have an SqlCeConnection, but nothing else.

感谢您的帮助！

推荐答案

这将是这个样子：

（编辑的插入或更新）

void Foo(SqlCeConnection connection)
{
    using (var cmd = new SqlCeCommand())
    {
        cmd.CommandType = CommandType.TableDirect;
        cmd.CommandText = "MyTableName";
        cmd.Connection = connection;
        cmd.IndexName = "PrimakryKeyIndexName";

        using (var result = cmd.ExecuteResultSet(
                            ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
        {
            int pkValue = 100; // set this, obviously

            if (result.Seek(DbSeekOptions.FirstEqual, pkValue))
            {
                // row exists, need to update
                result.Read();

                // set values
                result.SetInt32(0, 1);
                // etc. 

                result.Update();
            }
            else
            {
                // row doesn't exist, insert
                var record = result.CreateRecord();

                // set values
                record.SetInt32(0, 1);
                // etc. 

                result.Insert(record);
            }
        }
    }
} 

这篇关于更新/插入使用SQLCeResultSet表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！