c# - 从 CSV 文件填充 SQLite Db 表

Question

我正在努力让我的代码正常工作。

我正在结合我在此处找到的示例： Parsing CSV using OleDb using C# and Save Dataset to SQLite format file

我正在使用 OleDbConnection 和 OleDbDataAdapter（正在工作）将 CSV 文件读入 DataSet，然后使用 SQLiteConnection 和 SQLiteDataAdapter（不起作用）将该 DataSet 保存到 SQLite Db。

上面第二个示例中的测试代码工作得很好，其中数据集是从代码创建的，然后保存到数据库中。

数据库中的 MasterDataTable 已经存在。我直接在目标数据库中创建它。

这是我的代码：

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

任何帮助将非常感激。

标签： c#sqlitecsv

Answer 1

我找到了原因和解决方案。

原因是行从 OleDbDataAdapter.Fill() 添加到数据集中，状态为“未更改”。SQLiteDataAdapter.Update() 方法读取“未更改”行状态并且不执行插入查询。

我的解决方案是：

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

在 Fill() 调用后将每行的 RowState 设置为“已添加”。

最终的工作解决方案是：

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

这段代码只是一个大型项目的工作原型，但它可以按预期工作。