读取的代码：
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 Columns = new List();
Columns.Add("Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL");
List> dtList = new List>();
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();
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";
///
/// 增删改
/// 20180723
///
/// sql语句
/// sql参数
/// 受影响的行数
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;
}
}
///
/// 创建表
///
/// 指定数据库文件
/// 表名称
public void CreateTable(string table, List 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();
}
}
///
/// 判断表是否存在
///
///
///
/// 增删改
/// 20180723
///
/// sql语句
/// sql参数
/// 受影响的行数
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> dt, List 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> dt, List 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 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 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;
}
}
}
///
/// 查询
/// 20180723
///
/// sql语句
/// sql参数
/// 首行首列
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;
}
}
///
/// 查询
///
/// sql语句
/// sql参数
/// 首行首列
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();
}
}
}
///
/// 多行查询
///
/// sql语句
/// sql参数
/// SQLiteDateReader
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;
}
}
}
}
///
/// 查询多行数据
///
/// sql语句
/// sql参数
/// 一个表
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;
}
///
/// 数据插入
/// 20180725
///
/// 表名
/// 需要插入的数据字典
/// 受影响行数
public int ExecuteInsert(string tbName, Dictionary insertData)
{
string point = "";//分隔符号(,)
string keyStr = "";//字段名拼接字符串
string valueStr = "";//值的拼接字符串
List param = new List();
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());
}
///
/// 执行Update语句
/// 20180725
///
/// 表名
/// 更新条件：id=1
/// 需要更新的数据
/// 受影响行数
public int ExecuteUpdate(string tbName, string where, Dictionary insertData)
{
string point = "";//分隔符号(,)
string kvStr = "";//键值对拼接字符串(Id=@Id)
List param = new List();
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());
}
///
/// 查询
/// 20180723
///
/// sql语句
/// sql参数
/// 首行首列
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;
}
}
}
}
猜你喜欢
网友评论
- 搜索
- 最新文章
- 热门文章