C# 將數據表(DataTable)插入到 sqlite 表中

資料來源: https://juejin.cn/s/c%23%20insert%20datatable%20into%20sqlite%20table

https://www.twblogs.net/a/5ef4b7ec26bc8c4a8eb40ff1

01.首先需要安裝System.Data.SQLite 程序包。

02.然後創建一個SQLite 連接，並打開連接。

03.創建一個SQL 命令，用於將數據插入到SQLite 表中。

04.將DataTable 中的數據循環讀取，並使用SqlCommand.ExecuteNonQuery() 方法執行插入命令。

code01:

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

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // 创建 DataTable
            DataTable table = new DataTable();
            table.Columns.Add("Id", typeof(int));
            table.Columns.Add("Name", typeof(string));

            // 添加数据
            table.Rows.Add(1, "张三");
            table.Rows.Add(2, "李四");
            table.Rows.Add(3, "王五");

            // 创建 SQLite 连接
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=test.db"))
            {
                connection.Open();

                // 创建表
                using (SQLiteCommand command = new SQLiteCommand("CREATE TABLE IF NOT EXISTS User (Id INTEGER PRIMARY KEY, Name TEXT)", connection))
                {
                    command.ExecuteNonQuery();
                }

                // 插入数据
                foreach (DataRow row in table.Rows)
                {
                    using (SQLiteCommand command = new SQLiteCommand("INSERT INTO User (Id, Name) VALUES (@Id, @Name)", connection))
                    {
                        command.Parameters.AddWithValue("@Id", row["Id"]);
                        command.Parameters.AddWithValue("@Name", row["Name"]);
                        command.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}

code02:

 //建表
    string createSql = $"CREATE TABLE {table}" +
               "(EPC varchar(32),UID  varchar(20),q_time varchar(8),flag INTEGER," +
               " PRIMARY KEY(EPC, UID, q_time))";
    SqlHelper.ExecuteNonQuery(createSql);
    //insert 語句
    string commandText = $"INSERT INTO {table}(EPC,UID,q_time,flag)VALUES(@EPC,@UID,@q_time,@flag)";
    SqlHelper.ExecuteMutliQuery(commandText, dt);

    
    ////調用方法
        public int ExecuteMutliQuery(string commandText, DataTable dtData)
        {
            int res = 0;
            if (Conn.State == ConnectionState.Closed)
                Conn.Open();
            using (SQLiteTransaction dbTrans = Conn.BeginTransaction())
            {
                try
                {
                    foreach (DataRow row in dtData.Rows)
                    {
                        res += ExecuteNonQuery(dbTrans, commandText, row.ItemArray);
                    }
                    dbTrans.Commit();
                }
                catch (Exception ex)
                {
                    res = -1;
                    dbTrans.Rollback();
                    throw ex;
                }
                finally
                {
                    //Conn.Close();
                }
            }
            return res;
        }

///調用方法
 public int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException("transaction is null");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,please provide an open transaction.", "transaction");
            using (IDbCommand cmd = transaction.Connection.CreateCommand())
            {
                cmd.CommandText = commandText;
                AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
                if (transaction.Connection.State == ConnectionState.Closed)
                    transaction.Connection.Open();
                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }