OleDbDataAdapter.Update()将一个表复制到另一个表并更新数据库 [英] OleDbDataAdapter.Update() to copy one table to another and update database

问题描述

我在使用OleDbAdapter.Update(table)时遇到问题，我想做的是从SQL Server数据库填充表并将该表的内容复制到SqlServerCe(.sdf)首先使用具有相同架构的表，稍后将处理differnet架构，但现在我正在像这样测试它.

我通过在表上创建新行并调用.Update方法来尝试在SQL Server上进行更新，并且更改将反映在我的数据库中，但是当尝试在.sdf上执行完全相同的操作时，应用程序停止工作，(vshost.exe停止工作，Windows正在搜索....)所以我不知道错误在哪里，我无法调试.

我可以使用command.ExecuteNonQuery()进行插入，但是我认为更新方法可以提高性能，因为我们正试图从DTS迁移到CLR SP，并且某些资源有限，所以我正在寻找这里的性能替代.

这是我的代码:

Hi, I have a problem when using the OleDbAdapter.Update(table), what I''m trying to do is fill a table from a SQL server database and copy the contents of that table to a SqlServerCe (.sdf) table with the same schema first, I''ll handle differnet schemas later but right now I''m testing it like this.

I''ve tried the update on the SQL Server by creating a new row on the table and calling the .Update Method and the changes are reflected on my database, but when trying to do the exact same thing on the .sdf, the application stops working, (vshost.exe stopped working, windows is searching for....) so I have no idea where the error is, I can''t debug.

I can do inserts with the command.ExecuteNonQuery(), but I was thinking that the update method could improve performance since we''re trying to migrate from DTS to CLR SPs and some resources are limited, so I''m looking for the performance alternatie here.

Here''s my code:

<code>
 SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["PC"].ConnectionString);
            SqlCommand command2 = new SqlCommand("Select * from t_i_Association", conn2);
            DataTable table2 = new DataTable();
            conn2.Open();
            SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
            adapter2.Fill(table2);
            
            OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Movil"].ConnectionString);
            OleDbCommand command = new OleDbCommand("Select * from t_i_Association", conn);
            DataTable table = new DataTable();
            conn.Open();
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(table);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
            
            table.Merge(table2, true);

            //foreach (DataRow r in table.Rows)
            //{
            //    r.SetAdded();
            //}

            int rows = adapter.Update(table);
            conn.Close();
            conn2.Close();
</code>

总体思路是从两个表中进行选择，一个包含数据，另一个创建但没有数据，因此我填充了两个表，然后将空表与数据合并，并尝试更新.sdf数据库.
如果我在r.setAdded()上留下评论，则Update不会引发任何错误，但会返回0行更新.如果我在每一行上调用SetAdded()，则应用程序将停止工作，并且在通过代码向表中添加新行并更新更改时，它会执行相同的操作.我尝试了table.copy，从表中添加了一个新行，其中包含数据，几乎是我能想到的所有内容，我也不知道是什么问题:/

我应该坚持使用update方法还是使每一行的插入效果更好?你怎么看?

The overall idea is to make a select from both tables, one contains data, the other is created but has no data, so I fill both tables, then I merge the empty table with the data and try to update the .sdf database.
If I leave the comment on the r.setAdded() the Update throws no error but returns 0 rows updated. If I call the SetAdded() on each row, the application stops working and it does the same when adding a new row by code to the table and updating the changes. I tried the table.copy, adding a new row from the table with data, almost everything I could think of and I have no idea what''s the problem :/

Should I stick to the update method or is making an insert for each row better? what do you think?

thanks in advance!

推荐答案

此链接将为您提供帮助

DataAdapter.Update()-如何复制表

This link will help you

DataAdapter.Update() - How to Duplicate a Table

这篇关于OleDbDataAdapter.Update()将一个表复制到另一个表并更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！