在OleDb数据库LINQ中读取/写入数据表 [英] Reading/Writing DataTables to and from an OleDb Database LINQ

问题描述

我当前的项目是从OleDbDatabase和.CSV文件中获取信息，并将其全部放入更大的OleDbDatabase中.

My current project is to take information from an OleDbDatabase and .CSV files and place it all into a larger OleDbDatabase.

我目前已经从两个.CSV文件和OleDbDatabase到DataTables ...中读了我需要的所有信息.要花大笔的地方是将所有信息写回到另一个OleDbDatabase.

I have currently read in all the information I need from both .CSV files, and the OleDbDatabase into DataTables.... Where it is getting hairy is writing all of the information back to another OleDbDatabase.

现在，我当前的方法是执行以下操作:

Right now my current method is to do something like this:

    OleDbTransaction myTransaction = null;

    try
    {
        OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" +
                                                   "Data Source=" + Database);
        conn.Open();
        OleDbCommand command = conn.CreateCommand();
        string strSQL;

        command.Transaction = myTransaction;

        strSQL = "Insert into TABLE " +
                 "(FirstName, LastName) values ('" +
                 FirstName + "', '" + LastName + "')";

        command.CommandType = CommandType.Text;
        command.CommandText = strSQL;

        command.ExecuteNonQuery();

        conn.close();

    catch (Exception)
        {
            // IF invalid data is entered, rolls back the database
            myTransaction.Rollback();
        }

当然，这是非常基本的，我正在使用SQL命令将事务提交到连接.我的问题是我可以做到这一点，但是我有大约200个字段需要插入多个表中.如果那是唯一的方法，我愿意做腿部工作.但是我觉得有一个更简单的方法. LINQ中有什么可以帮助我解决这个问题的吗?

Of course, this is very basic and I'm using an SQL command to commit my transactions to a connection. My problem is I could do this, but I have about 200 fields that need inserted over several tables. I'm willing to do the leg work if that's the only way to go. But I feel like there is an easier method. Is there anything in LINQ that could help me out with this?

推荐答案

如果DataTable中的列名与目标表中的列名完全匹配，则您可以使用OleDbCommandBuilder(警告:我还没有测试过).您可能会遇到问题的一个方面是，如果源数据表的数据类型与目标表的数据类型不匹配(例如，如果源列数据类型都是字符串).

If the column names in the DataTable match exactly to the column names in the destination table, then you might be able to use a OleDbCommandBuilder (Warning: I haven't tested this yet). One area you may run into problems is if the data types of the source data table do not match those of the destination table (e.g if the source column data types are all strings).

编辑 我以多种方式修改了原始代码.首先，我切换到在DataTable上使用Merge方法.这使我可以跳过循环中使用LoadDataRow的过程.

EDIT I revised my original code in a number of ways. First, I switched to using the Merge method on a DataTable. This allowed me to skip using the LoadDataRow in a loop.

using ( var conn = new OleDbConnection( destinationConnString ) )
{
    //query off the destination table. Could also use Select Col1, Col2..
    //if you were not going to insert into all columns.
    const string selectSql = "Select * From [DestinationTable]";

    using ( var adapter = new OleDbDataAdapter( selectSql, conn ) )
    {
        using ( var builder = new OleDbCommandBuilder( adapter ) )
        {
            conn.Open();

            var destinationTable = new DataTable();
            adapter.Fill( destinationTable );

            //if the column names do not match exactly, then they 
            //will be skipped
            destinationTable.Merge( sourceDataTable, true, MissingSchemaAction.Ignore );

            //ensure that all rows are marked as Added.
            destinationTable.AcceptChanges();
            foreach ( DataRow row in destinationTable.Rows )
                row.SetAdded();

            builder.QuotePrefix = "[";
            builder.QuoteSuffix= "]";

            //forces the builder to rebuild its insert command
            builder.GetInsertCommand();
            adapter.Update( destinationTable );
        }
    }
}

添加替代解决方案是使用类似 FileHelpers 的框架来阅读CSV文件并将其发布到您的数据库中.它确实具有OleDbStorage DataLink，可发布到OleDb源中.请参阅 SqlServerStorage InsertRecord 示例以了解操作方法(最后用OleDbStorage替代SqlServerStorage).

ADDITION An alternate solution would be to use a framework like FileHelpers to read the CSV file and post it into your database. It does have an OleDbStorage DataLink for posting into OleDb sources. See the SqlServerStorage InsertRecord example to see how (in the end substitute OleDbStorage for SqlServerStorage).

这篇关于在OleDb数据库LINQ中读取/写入数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！