读取的代码：
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;
}
}
}
}