SQLiteDataAdapter更新方法返回0 [英] SQLiteDataAdapter Update method returning 0

问题描述

我装83行，从我的CSV文件，但是当我尝试更新的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

该人士$ ​​C $ c是：

The source code is:

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?

更新：

我打过电话更新之前设置的命令，我仍然得到同样的问题......在code现在是：

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 [标签]（[TAGID]，[标签名]，[说明]，[COLNAME]，[dataType的]，[实时] ）VALUES（@参数1，@参数2，@参数3，@ 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)"

下面是一个pastie显示数据集以XML格式的状态： http://pastie.org/936882

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

推荐答案

更新

读你的解决方案，让我说，我不会让人说我没有听清楚之后。枚举行设置为RowState为添加的工作。

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:

Program.cs的

Program.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:\Projects\StackOverflowAnswers\SqliteCsv\SqliteCsv\Quotes.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构造函数，最终被调用的来源。请注意，没有命令生成器采用。你需要明确的InserCommand财产上的SQLiteDataAdapter使用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！