问题描述

我从我的 CSV 文件中加载了 83 行，但是当我尝试更新 SQLite 数据库时，我得到了 0 行...我不知道我做错了什么.

I loaded 83 rows from my CSV file, but when I try to update the SQLite database I get 0 rows... I can't figure out what I'm doing wrong.

程序输出:

Num rows loaded is 83
Num rows updated is 0

源码为:

public void InsertData(String csvFileName, String tableName)
{
    String dir = Path.GetDirectoryName(csvFileName);
    String name = Path.GetFileName(csvFileName);

    using (OleDbConnection conn =
        new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        dir + @";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""))
    {
        conn.Open();
        using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn))
        {
            QuoteDataSet ds = new QuoteDataSet();
            adapter.Fill(ds, tableName);
            Console.WriteLine("Num rows loaded is " + ds.Tags.Rows.Count);
            InsertData(ds, tableName);
        }
    }
}

public void InsertData(QuoteDataSet data, String tableName)
{
    using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
    {

        using (SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM " + tableName, conn))
        {
            using (new SQLiteCommandBuilder(sqliteAdapter))
            {
                conn.Open();
                Console.WriteLine("Num rows updated is " + sqliteAdapter.Update(data, tableName));
            }
        }
    }
}

关于为什么它没有更新正确的行数的任何提示?

Any hints on why it's not updating the correct number of rows?

更新:

我尝试在调用 update 之前设置命令，但我仍然遇到同样的问题...代码现在是:

I tried to set the command before calling update and I'm still getting the same issue... the code is now:

sqliteAdapter.InsertCommand = cmndBldr.GetInsertCommand();
Console.WriteLine("Num rows updated is " + sqliteAdapter.Update(data, tableName));

当我调试它时，命令文本是:_commandText = "INSERT INTO [Tags] ([tagId], [tagName], [description], [colName], [dataType], [realTime]) VALUES (@param1、@param2、@param3、@param4、@param5、@param6)"

When I debug it the command text is: _commandText = "INSERT INTO [Tags] ([tagId], [tagName], [description], [colName], [dataType], [realTime]) VALUES (@param1, @param2, @param3, @param4, @param5, @param6)"

这是一个以 xml 格式显示数据集状态的粘贴图:http://pastie.org/936882

Here is a pastie showing the state of dataset in xml format: http://pastie.org/936882

推荐答案

更新:

阅读您的解决方案后，让我说我很尴尬，我没有抓住那个.枚举行以将行状态设置为添加将起作用.

After reading your solution let me say that I am embarrased that I did not catch that. Enumerating rows to set rowstate to added will work.

但是让我给你一个更简洁的方法来使用 adapter.AcceptChangesDuringFill.

But let me give you a cleaner way to do that using adapter.AcceptChangesDuringFill.

    using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn))
    {
        // this is the proper way to transfer rows
        adapter.AcceptChangesDuringFill = false;

        QuoteDataSet ds = new QuoteDataSet();
        adapter.Fill(ds, tableName);
        Console.WriteLine("Num rows loaded is " + ds.Tags.Rows.Count);
        InsertData(ds, tableName);
    }

<小时>

这会导入 83 行:

This imports 83 rows:

程序.cs

using System;
using System.Data;
using System.Data.SQLite;

namespace SqliteCsv
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            // fill your dataset
            QuoteDataSet ds = new QuoteDataSet();
            ds.ReadXml("data.xml", XmlReadMode.InferTypedSchema);


            // hack to flag each row as new as per lirik
            // OR just set .AcceptChangesDuringFill=false on adapter
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    row.SetAdded();
                }
            }

            int insertedRows;
            using (
                SQLiteConnection con =
                    new SQLiteConnection(@"data source=C:ProjectsStackOverflowAnswersSqliteCsvSqliteCsvQuotes.db"))
            {
                con.Open();

                // clear table - you may not want this
                SQLiteCommand cmd = con.CreateCommand();
                cmd.CommandText = "delete from Tags";
                cmd.ExecuteNonQuery();


                using (SQLiteTransaction txn = con.BeginTransaction())
                {
                    using (SQLiteDataAdapter dbAdapter = new SQLiteDataAdapter("select * from Tags", con))
                    {
                        dbAdapter.InsertCommand = new SQLiteCommandBuilder(dbAdapter).GetInsertCommand(true);
                        insertedRows = dbAdapter.Update(ds, "Tags");
                    }
                    txn.Commit();
                }
            }
            Console.WriteLine("Inserted {0} rows", insertedRows);

            Console.WriteLine("Press any key");
            Console.ReadKey();
        }
    }
}

原答案:

这是最终被调用的 SQLiteDataAdapter ctor 的来源.请注意，没有使用命令生成器.您需要在 SQLiteDataAdapter 上显式设置 InserCommand 属性，或许可以使用 SQLiteCommandBuilder?

This is the source of the SQLiteDataAdapter ctor that ultimately gets called. Note that no command builder is employed. You need to explicitly set the InserCommand property on the SQLiteDataAdapter, perhaps by using a SQLiteCommandBuilder?

public SQLiteDataAdapter(string commandText, SQLiteConnection connection)
{
    this.SelectCommand = new SQLiteCommand(commandText, connection);
}

这篇关于SQLiteDataAdapter 更新方法返回 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！