Populating SQLite Db Table from CSV File

Question

I'm struggling to get my code to work.

I'm combining the examples I found here: Parsing CSV using OleDb using C# and Save Dataset to SQLite format file

I'm reading a CSV file into a DataSet using an OleDbConnection and OleDbDataAdapter (which is working), and then saving that DataSet to an SQLite Db using an SQLiteConnection and SQLiteDataAdapter (which is not working).

The test code from the 2nd example above works just fine, where the DataSet is created from code and then saved to the database.

The MasterDataTable in the database exists already. I created it directly in the target db.

Here's my code:

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.IO;

namespace DataAdapterExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a simple dataset
            DataTable table = new DataTable("MasterDataTable");
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            var filename = @"C:\Users\willi\Downloads\MasterDataReport2.csv";
            var connString = string.Format(
                @"Provider=Microsoft.Jet.OleDb.4.0; 
                Data Source={0};
                Extended Properties=""Text;
                HDR=YES;
                FMT=Delimited""", 
                Path.GetDirectoryName(filename));
            using (var conn = new OleDbConnection(connString))
            {
                conn.Open();
                var query = "SELECT * FROM [" + Path.GetFileName(filename) + "]";
                using (var adaptr = new OleDbDataAdapter(query,conn))
                {
                    adaptr.Fill(table);
                }
            }
            // Save in an SQLite file
            string desktopPath =
                Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string fullPath = desktopPath + "\\class.db";
            SQLiteConnection con = new SQLiteConnection("Data Source=" + fullPath);
            con.Open();
            // Create a table in the database to receive the information from the DataSet
            SQLiteCommand cmd = new SQLiteCommand(con);
            SQLiteDataAdapter adaptor = new SQLiteDataAdapter("SELECT * from MasterDataTable", con);
            adaptor.InsertCommand = new SQLiteCommand("INSERT INTO MasterDataTable VALUES (" +
                ":startTime, " +
                ":entryId, " +
                ":entryType, " +
                ":category, " +
                ":class, " +
                ":participants, " +
                ":studioName, " +
                ":routineTitle)",
                con
            );
            adaptor.InsertCommand.Parameters.Add("startTime", DbType.String, 0, "StartTime");
            adaptor.InsertCommand.Parameters.Add("entryId", DbType.Int16, 0, "EntryID");
            adaptor.InsertCommand.Parameters.Add("entryType", DbType.String, 0, "EntryType");
            adaptor.InsertCommand.Parameters.Add("category", DbType.String, 0, "Category");
            adaptor.InsertCommand.Parameters.Add("class", DbType.String, 0, "Class");
            adaptor.InsertCommand.Parameters.Add("participants", DbType.String, 0, "Participants");
            adaptor.InsertCommand.Parameters.Add("studioName", DbType.String, 0, "StudioName");
            adaptor.InsertCommand.Parameters.Add("routineTitle", DbType.String, 0, "Routine Title");
            adaptor.Update(ds, "MasterDataTable");
            //Check database by filling the dataset in the other direction and displaying
            ds = new DataSet();
            adaptor.Fill(ds, "MasterDataTable");
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine("Table {0}", dt.TableName);
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        Console.Write("{0,-18}", dr[dc]);
                    }
                    Console.WriteLine();
                }
            }
        }
    }
}

Any help would be much appreciated.

The parameters in the command do not have values assigned to them... Did you try running the insert query directly against the command, without using adapter or dataset? — Chetan, Feb 18 '20 at 00:49
The parameters get their values from each row in the DataSet, using the column headers as the key. I think what is happening is the Fill() method of the OleDbDataAdapter, which fills the DataSet from the CSV file, is setting the RowState property to "Unchanged"; and then the Update() method of the SQLiteDataAdapter adapter doesn't execute the INSERT, because the DataRow is "Unchanged". Is there a way to update the RowState? — Todd Williams, Feb 18 '20 at 01:33

score 0 · Answer 1 · answered Feb 18 '20 at 02:23

I found the cause and a solution.

The cause was that rows are added to the DataSet from the OleDbDataAdapter.Fill() with a state of "Unchanged". The SQLiteDataAdapter.Update() method reads the "Unchanged" RowState and doesn't execute the INSERT query.

My solution was to put:

foreach (DataRow row in ds.Tables[0].Rows)
{
   row.SetAdded();
}

after the Fill() call to set the RowState of each row to "Added".

The final working solution is:

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.IO;

namespace DataAdapterExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a simple dataset
            DataSet ds = new DataSet();
            var filename = @"C:\Users\willi\Downloads\MasterDataReport2.csv";
            var connString = string.Format(
                @"Provider=Microsoft.Jet.OleDb.4.0; 
                Data Source={0};
                Extended Properties=""Text;
                HDR=YES;
                FMT=Delimited""", 
                Path.GetDirectoryName(filename));
            using (var conn = new OleDbConnection(connString))
            {
                conn.Open();
                var query = "SELECT * FROM [" + Path.GetFileName(filename) + "]";
                using (var adaptr = new OleDbDataAdapter(query,conn))
                {
                    adaptr.Fill(ds);
                }
            }

            // Change the RowState to "Added" for the Update() method
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                row.SetAdded();
            }

            // Save in an SQLite file
            string desktopPath =
                Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            string fullPath = desktopPath + "\\class.db";
            SQLiteConnection con = new SQLiteConnection("Data Source=" + fullPath);
            con.Open();
            // receive the information from the DataSet into the db
            SQLiteCommand cmd = new SQLiteCommand(con);
            SQLiteDataAdapter adaptor = new SQLiteDataAdapter("SELECT * from MasterDataTable", con);
            adaptor.InsertCommand = new SQLiteCommand("INSERT INTO MasterDataTable VALUES (" +
                ":startTime, " +
                ":entryId, " +
                ":entryType, " +
                ":category, " +
                ":class, " +
                ":participants, " +
                ":studioName, " +
                ":routineTitle)",
                con
            );
            adaptor.InsertCommand.Parameters.Add("startTime", DbType.String, 0, "StartTime");
            adaptor.InsertCommand.Parameters.Add("entryId", DbType.Int16, 0, "EntryID");
            adaptor.InsertCommand.Parameters.Add("entryType", DbType.String, 0, "EntryType");
            adaptor.InsertCommand.Parameters.Add("category", DbType.String, 0, "Category");
            adaptor.InsertCommand.Parameters.Add("class", DbType.String, 0, "Class");
            adaptor.InsertCommand.Parameters.Add("participants", DbType.String, 0, "Participants");
            adaptor.InsertCommand.Parameters.Add("studioName", DbType.String, 0, "StudioName");
            adaptor.InsertCommand.Parameters.Add("routineTitle", DbType.String, 0, "Routine Title");
            adaptor.Update(ds);
            //Check database by filling the dataset in the other direction and displaying
            ds = new DataSet();
            adaptor.Fill(ds);
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine("Table {0}", dt.TableName);
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                        Console.Write("{0,-18}", dr[dc]);
                    }
                    Console.WriteLine();
                }
            }
        }
    }
}

This code is just a working prototype for a larger project, but it works as expected.

Populating SQLite Db Table from CSV File

1 Answers1