使用 SQLCeResultSet 更新/插入表

发布于 2024-09-08 09:01:27 字数 944 浏览 4 评论 0原文

我有一个定期更新的 SQL Compact Edition 数据库（通过 Web 服务）。

我写入数据库的部分花费的时间太长。我目前正在使用 Linq to Datasets 进行此操作（如 this 中所示）问题）。我听说如果我使用 SQLCeResultSet 执行此操作会更快。

因此，假设我有一个像这样的表：

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

并且我将它放在从 Web 服务获取的对象中，如下所示：

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

如何将 100 个客户端对象放入数据库？

更新现有行并插入数据库中尚未存在的行（由主键确定）？

任何示例代码都会很棒。我有一个 SqlCeConnection，但没有其他东西。

感谢您的帮助！

原文

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

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}

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

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

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

Thanks for any help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人生百味 2024-09-15 09:01:27

它看起来像这样：（

编辑插入或更新）

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

It's going to look something like this:

(Edited for insert or update)

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

回复收藏 0 原文

~没有更多了~