using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SQLite;
|
|
namespace HMI.Core.Util
|
{
|
public class Sqlite3Helper : IDisposable
|
{
|
public string DataBaseFile { get; private set; } = string.Empty;
|
|
private string ConnectString = string.Empty;
|
|
public const int MAX_ATTACHED = 30;
|
|
private SQLiteConnection conn = null;
|
|
public Sqlite3Helper(string DBFile)
|
{
|
DataBaseFile = DBFile;
|
ConnectString = "DataSource=" + DataBaseFile + ";Version=3;Pooling=False;Max Pool Size=100;";
|
conn = new SQLiteConnection(ConnectString);
|
Connect();
|
}
|
|
public static bool CreateDatabase(string DataBaseFile)
|
{
|
try
|
{
|
SQLiteConnection.CreateFile(DataBaseFile);
|
return true;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception($"Create Database [${DataBaseFile}] Failed.", ex);
|
}
|
}
|
|
public int Execute(string Sql)
|
{
|
try
|
{
|
using (var cmd = new SQLiteCommand(conn))
|
{
|
cmd.CommandType = CommandType.Text;
|
cmd.CommandText = Sql;
|
return cmd.ExecuteNonQuery();
|
}
|
}
|
catch (Exception ex)
|
{
|
string message = "ExecuteSql Error:" + ex.Message;
|
throw new Exception(message);
|
}
|
}
|
|
public long InsertWithId(string Sql)
|
{
|
try
|
{
|
if (Sql.EndsWith(";"))
|
{
|
Sql += "SELECT last_insert_rowid();";
|
}
|
else
|
{
|
Sql += ";SELECT last_insert_rowid();";
|
}
|
using (var cmd = new SQLiteCommand(Sql, conn))
|
{
|
return (long)cmd.ExecuteScalar();
|
}
|
}
|
catch (Exception ex)
|
{
|
string message = "ExecuteSql Error:" + ex.Message;
|
throw new Exception(message);
|
}
|
}
|
|
public object SelectOne(string Sql)
|
{
|
try
|
{
|
using (var cmd = new SQLiteCommand(Sql, conn))
|
{
|
return cmd.ExecuteScalar();
|
}
|
}
|
catch (Exception ex)
|
{
|
string message = "ExecuteSql Error:" + ex.Message;
|
throw new Exception(message);
|
}
|
}
|
|
public int ExecuteBatch(IEnumerable<string> sqls)
|
{
|
SQLiteTransaction tr = null;
|
int res = 0;
|
try
|
{
|
Connect();
|
tr = conn.BeginTransaction();
|
using (var cmd = new SQLiteCommand(conn))
|
{
|
cmd.CommandType = CommandType.Text;
|
foreach (string sql in sqls)
|
{
|
if (string.IsNullOrEmpty(sql) || string.IsNullOrWhiteSpace(sql))
|
continue;
|
cmd.CommandText = sql;
|
res += cmd.ExecuteNonQuery();
|
}
|
}
|
tr.Commit();
|
return res;
|
}
|
catch (Exception ex)
|
{
|
tr?.Rollback();
|
throw ex;
|
}
|
}
|
|
public DataTable GetDataTable(string Sql)
|
{
|
DataTable dataTable = new DataTable();
|
try
|
{
|
Connect();
|
using (var cmd = new SQLiteCommand(conn)
|
{
|
CommandType = CommandType.Text,
|
CommandText = Sql
|
})
|
using (SQLiteDataAdapter sQLiteDataAdapter = new SQLiteDataAdapter()
|
{
|
SelectCommand = cmd
|
})
|
{
|
sQLiteDataAdapter.Fill(dataTable);
|
}
|
}
|
catch (Exception ex)
|
{
|
string message = "ExecuteSql Error:" + ex.Message;
|
throw new Exception(message);
|
}
|
return dataTable;
|
}
|
|
public string[,] GetDataTableToArray(string Sql)
|
{
|
try
|
{
|
DataTable dataTable = GetDataTable(Sql);
|
string[,] array = new string[dataTable.Rows.Count, dataTable.Columns.Count];
|
int num = 0;
|
int num2 = 0;
|
foreach (DataRow row in dataTable.Rows)
|
{
|
num2 = 0;
|
object[] itemArray = row.ItemArray;
|
foreach (object obj in itemArray)
|
{
|
array[num, num2] = obj.ToString();
|
num2++;
|
}
|
num++;
|
}
|
return array;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
}
|
|
public void Close()
|
{
|
try
|
{
|
if (conn.State == ConnectionState.Open)
|
{
|
conn.Close();
|
}
|
if (conn != null)
|
{
|
conn.Dispose();
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
}
|
|
public bool HasData(string sql)
|
{
|
DataTable dataTable = GetDataTable(sql);
|
return dataTable.Rows.Count > 0;
|
}
|
|
public string[] GetColumnNameFromTable(string TableName)
|
{
|
try
|
{
|
string sql = $"PRAGMA table_info('{TableName}')";
|
DataTable dataTable = GetDataTable(sql);
|
string[] array = new string[dataTable.Rows.Count];
|
int num = 0;
|
foreach (DataRow row in dataTable.Rows)
|
{
|
array[num] = row.ItemArray[1].ToString();
|
num++;
|
}
|
return array;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("获取表字段名称失败:" + ex.Message);
|
}
|
}
|
|
public string[] GetAllTableInFile()
|
{
|
try
|
{
|
string sql = "select name from sqlite_master where type = 'table' order by name";
|
DataTable dataTable = GetDataTable(sql);
|
string[] array = new string[dataTable.Rows.Count];
|
int num = 0;
|
foreach (DataRow row in dataTable.Rows)
|
{
|
array[num] = row.ItemArray[0].ToString();
|
num++;
|
}
|
|
return array;
|
}
|
catch (Exception ex)
|
{
|
throw new Exception("获取表名失败:" + ex.Message);
|
}
|
}
|
|
private void Connect()
|
{
|
if (conn.State == ConnectionState.Closed)
|
{
|
conn.ConnectionString = ConnectString;
|
conn.Open();
|
}
|
}
|
|
private void DisConnect()
|
{
|
if (conn.State == ConnectionState.Open)
|
{
|
conn.Close();
|
}
|
}
|
|
public void Dispose()
|
{
|
this.Close();
|
}
|
}
|
}
|
