C#读取一个百万条数据的文件，同时批量一次性导入sqlitedb，需要花费多长时间

读取的代码：

      public void CSV2DataTableTest(string fileName)
      {
          FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
          StreamReader sr = new StreamReader(fs, new UnicodeEncoding());
          //记录每次读取的一行记录
          string strLine = "";
          //记录每行记录中的各字段内容
          string[] aryLine;
          //标示列数
          int columnCount = 0;
          //标示是否是读取的第一行
          bool IsFirst = true;

          List<string> Columns = new List<string>();
          Columns.Add("Id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL");

          List<List<string>> dtList = new List<List<string>>();
          while ((strLine = sr.ReadLine()) != null)
          {
              aryLine = strLine.Split(',');
              if (IsFirst == true)
              {
                  IsFirst = false;
                  columnCount = aryLine.Length;
                  //创建列
                  for (int i = 0; i < columnCount; i++)
                  {
                      Columns.Add((string)aryLine[i]);
                  }
              }
              else
              {
                  dtList.Add(aryLine.ToList());
              }
          }
          sr.Close();
          fs.Close();
          sr.Dispose();
          fs.Dispose();

          var insertColumns = new List<string>();
          insertColumns.AddRange(Columns);
          insertColumns[0] = "Id";

          SqliteHelper helper = new SqliteHelper();
          string table = "AA3";
          if (!helper.TableExist(table))
              helper.CreateTable(table, Columns);
          else
          {
              helper.ExecuteNonQuery("delete from AA3");
              helper.ExecuteNonQuery("update sqlite_sequence SET seq = 0 where name ='AA3';");
          }
          var dateStart = DateTime.Now;
          helper.OpenConnection();
          helper.InsertDataTest(dtList, insertColumns, table);
          helper.CloseConnection();
          string times = "导入sqlite花费时间" + (DateTime.Now - dateStart).TotalMilliseconds + "ms";
          label2.Text = times;
          dtList.Clear();
          GC.Collect();
      }

插入sqlite代码：

using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestExpression
{
    public class SqliteHelper
    {
        // string db = "Data Source=:memory:";

        //连接字符串
     //   private static string str = "Data source=" + AppDomain.CurrentDomain.BaseDirectory + "\data.db";
        private static string str = "Data source=" + AppDomain.CurrentDomain.BaseDirectory + "\D518DCC2-149A-48CC-8355-682B0BF92D26.DB";

        /// <summary>
        /// 增删改
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SQLiteParameter[] param)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                    {
                        con.Open();
                        if (param != null)
                        {
                            cmd.Parameters.AddRange(param);
                        }

                        string sql2 = cmd.CommandText;
                        //  con.Close();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException se)
            {
                return 0;
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        public void CreateTable(string table, List<string> Columns)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                con.Open();
                string Column = "";
                for (int i = 0; i < Columns.Count; i++)
                {
                    Column += Columns[i] + ",";
                }
                Column = Column.Substring(0, Column.Length - 1);
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = con;
                cmd.CommandText = " CREATE TABLE " + table + "(" + Column + ")";
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool TableExist(string table)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                con.Open();
                SQLiteCommand mDbCmd = con.CreateCommand();
                mDbCmd.CommandText = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='" + table + "';";
                int row = Convert.ToInt32(mDbCmd.ExecuteScalar());
                con.Close();
                if (0 < row)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 增删改
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                    {
                        con.Open();
                        string sql2 = cmd.CommandText;
                        //con.Close();
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SQLiteException se)
            {
                return 0;
            }
        }

        SQLiteConnection SqlConnection = null;

        public void OpenConnection()
        {
            SqlConnection = new SQLiteConnection(str);
            SqlConnection.Open();
        }

        public void CloseConnection()
        {
            SqlConnection.Close();
        }

        public void InsertDataTest(List<List<string>> dt, List<string> Columns, string tableName)
        {
            SQLiteBulkInsert sbi = new SQLiteBulkInsert(SqlConnection, tableName);
            sbi.AddParameter("Id", DbType.Int32);
            for (int i = 1; i < Columns.Count; i++)
            {
                sbi.AddParameter(Columns[i], DbType.String);
            }


            for (int j = 0; j < dt.Count; j++)
            {
                object[] objects = new object[Columns.Count];
                objects[0] = null;
                for (int i = 1; i < Columns.Count; i++)
                {
                    objects[i]= dt[j][i-1];
                }
                sbi.Insert(objects);
            }
            sbi.Flush();
        }


        public int InsertDataBulk3(List<List<string>> dt, List<string> Columns, string tableName)
        {

            using (SQLiteTransaction tran = SqlConnection.BeginTransaction())
            {
                try
                {
                    string columnStr = string.Join(",", Columns);
                    string columnStr2 = "@" + string.Join(",@", Columns);
                    using (SQLiteCommand command = new SQLiteCommand("Insert into " + tableName + "(" + columnStr + ") values(" + columnStr2 + ")", SqlConnection))
                    {
                        for (int j = 0; j < dt.Count; j++)
                        {
                            var drData = dt[j];
                            command.Parameters.Add(new SQLiteParameter("@Id", null));
                            for (int i = 1; i < Columns.Count; i++)
                            {
                                command.Parameters.Add(new SQLiteParameter("@" + Columns[i], drData[i - 1]));
                            }
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                    }
                    tran.Commit();
                    return 0;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return 1;
                }
            }
        }


        public int InsertDataBulk(DataTable dt, List<string> Columns, string tableName)
        {
            using (SQLiteTransaction tran = SqlConnection.BeginTransaction())
            {
                try
                {
                    string columnStr = string.Join(",", Columns);
                    string columnStr2 = "@" + string.Join(",@", Columns);
                    using (SQLiteCommand command = new SQLiteCommand("Insert into " + tableName + "(" + columnStr + ") values(" + columnStr2 + ")", SqlConnection))
                    {
                        foreach (DataRow drData in dt.Rows)
                        {
                            command.Parameters.Add(new SQLiteParameter("@Id", null));
                            for (int i = 1; i < Columns.Count; i++)
                            {
                                command.Parameters.Add(new SQLiteParameter("@" + Columns[i], drData[Columns[i]]));
                            }
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                    }
                    tran.Commit();
                    return 0;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return 1;
                }
            }
        }

        public int UpdateDataBulk(Dictionary<int, string?> dt, string changeColumn, string tableName)
        {
            using (SQLiteTransaction tran = SqlConnection.BeginTransaction())
            {
                try
                {
                    using (SQLiteCommand command = new SQLiteCommand("update " + tableName + " set " + changeColumn + "=@" + changeColumn + " where Id=@Id", SqlConnection))
                    {
                        foreach (var drData in dt)
                        {
                            command.Parameters.Add(new SQLiteParameter("@" + changeColumn, drData.Value));
                            command.Parameters.Add(new SQLiteParameter("@Id", drData.Key));
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                    }
                    tran.Commit();
                    return 0;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    return 1;
                }
            }
        }

        /// <summary>
        /// 查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public object ExecuteScalarFirst(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(con))
                    {
                        con.Open();
                        cmd.CommandText = sql;
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public object ExecuteScalar(string sql, params SQLiteParameter[] param)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                {
                    con.Open();
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }

                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 多行查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>SQLiteDateReader</returns>
        public SQLiteDataReader ExecuteReader(string sql, params SQLiteParameter[] param)
        {
            using (SQLiteConnection con = new SQLiteConnection(str))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    try
                    {
                        con.Open();
                        return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch (Exception ex)
                    {
                        con.Close();
                        con.Dispose();
                        throw ex;
                    }
                }
            }

        }

        /// <summary>
        /// 查询多行数据
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>一个表</returns>
        public DataTable ExecuteTable(string sql, params SQLiteParameter[] param)
        {
            DataTable dt = new DataTable();
            using (SQLiteDataAdapter sda = new SQLiteDataAdapter(sql, str))
            {
                if (param != null)
                {
                    sda.SelectCommand.Parameters.AddRange(param);
                }
                sda.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// 数据插入
        /// 20180725
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="insertData">需要插入的数据字典</param>
        /// <returns>受影响行数</returns>
        public int ExecuteInsert(string tbName, Dictionary<String, String> insertData)
        {
            string point = "";//分隔符号(,)
            string keyStr = "";//字段名拼接字符串
            string valueStr = "";//值的拼接字符串

            List<SQLiteParameter> param = new List<SQLiteParameter>();
            foreach (string key in insertData.Keys)
            {
                keyStr += string.Format("{0} `{1}`", point, key);
                valueStr += string.Format("{0} @{1}", point, key);
                param.Add(new SQLiteParameter("@" + key, insertData[key]));
                point = ",";
            }
            string sql = string.Format("INSERT INTO `{0}`({1}) VALUES({2})", tbName, keyStr, valueStr);

            //return sql;
            return ExecuteNonQuery(sql, param.ToArray());

        }

        /// <summary>
        /// 执行Update语句
        /// 20180725
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="where">更新条件：id=1</param>
        /// <param name="insertData">需要更新的数据</param>
        /// <returns>受影响行数</returns>
        public int ExecuteUpdate(string tbName, string where, Dictionary<String, String> insertData)
        {
            string point = "";//分隔符号(,)
            string kvStr = "";//键值对拼接字符串(Id=@Id)

            List<SQLiteParameter> param = new List<SQLiteParameter>();
            foreach (string key in insertData.Keys)
            {
                kvStr += string.Format("{0} {1}=@{2}", point, key, key);
                param.Add(new SQLiteParameter("@" + key, insertData[key]));
                point = ",";
            }
            string sql = string.Format("UPDATE `{0}` SET {1} WHERE {2}", tbName, kvStr, where);

            return ExecuteNonQuery(sql, param.ToArray());

        }

        /// <summary>
        /// 查询
        /// 20180723
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns>首行首列</returns>
        public DataTable GetDataTable(string sql)
        {
            try
            {
                using (SQLiteConnection con = new SQLiteConnection(str))
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, con))
                    {
                        con.Open();
                        SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd);
                        DataTable tb = new DataTable();
                        ad.Fill(tb);
                        con.Close();
                        return tb;

                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;

            }

        }


    }
}

代码007普通

打赏 收藏 海报 链接