views:

260

answers:

2

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.

The program outputs:

Num rows loaded is 83
Num rows updated is 0

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?

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

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

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

+2  A: 

Update:

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.

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

This imports 83 rows:

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

Original Answer:

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);
}
Sky Sanders
@Sky, Thanks... I tried setting the InsertCommand manually and I verified it was correctly set in the debugger (see my update for more info), but it's still not working.
Lirik
Ah, the infamous "it's still not working" problem. I hear it's a hard one.
Lasse V. Karlsen
quite hard to solve the 'its still not working' problem. But i have found the function global::DoStuff() to work wonders in these cases
TerrorAustralis
@Dave yeah, the ole' `DoesntWorkException` strikes again! http://meta.stackoverflow.com/questions/19478/the-many-memes-of-meta/41735#41735 - but I think I got it whipped. ;-)
Sky Sanders
@Sky, I figured out the problem: the DataAdapter only checks if the DataSet has any flags indicating it's out of sync with respect to the database it was populated from and there is no way for it to know that there is a change even though I'm trying to insert the data into a different database. I had to call SetAdded for each row... it works like a charm now. Thanks for your time!
Lirik
@Sky and it doesn't seem like the column names as parameters made any difference, I tried both and both worked just fine...
Lirik
@lirik - yeah, i was just noting the only real difference I saw between the two bits of code. Hey listen, why don't I update my answer to include your fix and then you can accept it otherwise the bounty will be lost, as you can accept your own answer but not the bounty. http://meta.stackoverflow.com/questions/18841/lost-reputation-after-answering-my-own-question-with-bounty
Sky Sanders
@Sky sounds good, thanks for all your help... update your question with the answer and I'll accept it.
Lirik
@lirik - one better - I knew I was missing something, and have added it - AcceptChangesDuringFill - try that and let me know if that works for ya.
Sky Sanders
@lirik, oh, and know that I am not disparaging your conclusion when I characterise it as a hack in the updated source. It is just reflex, I regularly flag my own code as //HACK: (reason) when I feel there might be a cleaner way to do it. ;-)
Sky Sanders
@Sky yah, I didn't know of a more gracious solution, but turning off AcceptChangesDuringFill is much better. I have to wait for about 9 hours before I can accept your answer now :).
Lirik
+1  A: 

I was able to get around the issue by going through each row and changing it's state by calling SetAdded();... after I did that the Update command worked like a charm.

public void InsertData(QuoteDataSet dataSet, String tableName)
{
    int numRowsUpdated = 0;
    using (SQLiteConnection conn = new SQLiteConnection(_connectionString))
    {
        conn.Open();
        using (SQLiteTransaction transaction = conn.BeginTransaction())
        using (SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM " + tableName, conn))
        {
            using (sqliteAdapter.InsertCommand = new SQLiteCommandBuilder(sqliteAdapter).GetInsertCommand())
            {
                var rows = dataSet.Tags.AsEnumerable();
                foreach (var row in rows)
                {
                    row.SetAdded();
                }
                numRowsUpdated = sqliteAdapter.Update(dataSet, tableName);
            }
            transaction.Commit();
        }
    }
    Console.WriteLine("Num rows updated is " + numRowsUpdated);
}

I assume that when the DataSet is filled from the CSV file and I then I attempt to call Update in order to insert the data into the database, the state of the row does not indicate any changes. We have to tell the DataAdapter that there is a change in the DataSet because all it sees is that are no changes to the DataSet with respect to the CSV file it was populated from and it doesn't realize that these are brand new rows for the database I'm trying to put the data in.

Lirik
wow, now isn't that obvious? lol. good catch
Sky Sanders
+1 for fingering it out
Sky Sanders