1、数据库访问基础类
/// <summary>
/// Copyright (C) ZYY
/// 数据访问基础类(基于SQLite)
/// </summary>
public class DbHelperSqlite
{
/// <summary>
/// 提示信息
/// </summary>
public static string StrLastError = string.Empty;
/// <summary>
/// 命令
/// </summary>
public enum SdaCmd
{
/// <summary>
/// 查询
/// </summary>
Select,
/// <summary>
/// 删除
/// </summary>
Delete,
/// <summary>
/// 更新
/// </summary>
Update,
/// <summary>
/// 插入
/// </summary>
Insert
}
/// <summary>
/// 连接字符串
/// </summary>
/// <value>
/// The connection string.
/// </value>
public static string ConnString { get; set; } = "";
/// <summary>
/// 创建数据库文件及数据库表
/// </summary>
/// <param name="strDbpath">数据库文件包含文件名的全路径信息</param>
/// <param name="sql">创建数据库表的SQL语句</param>
/// <param name="alwaysCreate">if set to <c>true</c> [always create].</param>
public static void CreateDbTable(string strDbpath, string sql, bool alwaysCreate = false)
{
if (!File.Exists(strDbpath) || alwaysCreate)
{
ConnString = $"Data Source={strDbpath};Pooling=false;FailIfMissing=false;syncchronous=normal;";
SQLiteConnection conn = new SQLiteConnection(ConnString);
SQLiteCommand cmd = new SQLiteCommand();
conn.Open();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
FileInfo fileInfo = new FileInfo(strDbpath);
if (fileInfo.Length == 0)
{
File.Delete(strDbpath);
conn = new SQLiteConnection(ConnString);
cmd = new SQLiteCommand();
conn.Open();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();
}
}
else
{
ConnString = $"Data Source={strDbpath};Pooling=false;FailIfMissing=false;syncchronous=normal;";
}
}
/// <summary>
/// DataAdapter方法 返回DataSet数据集
/// </summary>
/// <param name="sSqlCmd">SQL语句</param>
/// <param name="command">操作参数 枚举类型</param>
/// <param name="tabName">DataSet中的表名</param>
/// <param name="paraList">命令参数集合</param>
/// <returns></returns>
public static DataSet DataAdapter(string sSqlCmd, SdaCmd command, string tabName, params SQLiteParameter[] paraList)
{
DataSet ds = new DataSet();
try
{
SQLiteConnection con = new SQLiteConnection(ConnString);
SQLiteCommand cmd = new SQLiteCommand
{
Connection = con,
CommandText = sSqlCmd
};
if (paraList != null)
{
cmd.CommandType = CommandType.Text;
foreach (SQLiteParameter para in paraList)
{
cmd.Parameters.Add(para);
}
}
SQLiteDataAdapter sda = new SQLiteDataAdapter();
switch (command)
{
case SdaCmd.Select:
sda.SelectCommand = cmd;
break;
case SdaCmd.Insert:
sda.InsertCommand = cmd;
break;
case SdaCmd.Update:
sda.UpdateCommand = cmd;
break;
case SdaCmd.Delete:
sda.DeleteCommand = cmd;
break;
}
sda.Fill(ds, tabName);
con.Close();
SQLiteConnection.ClearAllPools();
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{sSqlCmd}");
StrLastError = ex.Message;
}
return ds;
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strSql, SQLiteParameter[] parameters)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
SQLiteConnection.ClearAllPools();
return data;
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 执行一个查询语句,返回一个包含查询结果的DataTable
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strSql)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable("ResultData");
adapter.Fill(data);
SQLiteConnection.ClearAllPools();
return data;
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="strSql">要执行的增删改的SQL语句</param>
/// <param name="parameters">>执行增删改语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static int ExecuteNonQuery(string strSql, SQLiteParameter[] parameters)
{
try
{
int affectedRows;
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = strSql;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
SQLiteConnection.ClearAllPools();
return affectedRows;
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return 0;
}
}
/// <summary>
/// 对SQLite数据库执行增删改操作,返回受影响的行数。
/// </summary>
/// <param name="strSql">要执行的增删改的SQL语句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string strSql)
{
try
{
int affectedRows;
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = strSql;
affectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
SQLiteConnection.ClearAllPools();
return affectedRows;
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return 0;
}
}
/// <summary>
/// 执行一个查询语句,返回一个关联的SQLiteDataReader实例
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string strSql, SQLiteParameter[] parameters)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
SQLiteCommand command = new SQLiteCommand(strSql, connection);
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 执行一个查询语句,返回一个关联的SQLiteDataReader实例
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <returns></returns>
public static SQLiteDataReader ExecuteReader(string strSql)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
SQLiteCommand command = new SQLiteCommand(strSql, connection);
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 执行一个查询语句,返回查询结果的第一行第一列
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数,参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public static object ExecuteScalar(string strSql, SQLiteParameter[] parameters)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
SQLiteConnection.ClearAllPools();
return data;
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 执行一个查询语句,返回查询结果的第一行第一列
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <returns></returns>
public static object ExecuteScalar(string strSql)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
SQLiteConnection.ClearAllPools();
return data;
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return null;
}
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="sqlStringList">SQL语句的哈希表(key为strSql语句,value是该语句的SQLiteParameter[])</param>
/// <returns></returns>
public static bool ExecuteSqlTran(Hashtable sqlStringList)
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
SQLiteCommand cmd = new SQLiteCommand();
try
{
foreach (DictionaryEntry myDe in sqlStringList)
{
string cmdText = myDe.Key.ToString();
SQLiteParameter[] cmdParms = (SQLiteParameter[])myDe.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
StrLastError = ex.Message;
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{sqlStringList}");
return false;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// 执行压缩数据库
/// </summary>
public static void ExecuteZip()
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand("VACUUM", connection))
{
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
connection.Close();
StrLastError = e.Message;
}
}
}
}
/// <summary>
/// 取dataset
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <returns>
/// 返回dataset
/// </returns>
public static DataSet GetDataSet(string strSql)
{
DataSet ds = null;
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand(strSql, connection))
{
try
{
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
ds = new DataSet();
dao.Fill(ds);
connection.Close();
}
catch (SQLiteException e)
{
connection.Close();
StrLastError = e.Message;
}
}
}
}
catch (Exception ex)
{
StrLastError = ex.Message;
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
}
finally
{
SQLiteConnection.ClearAllPools();
}
return ds;
}
/// <summary>
/// 取dataset
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <param name="paraList">命令参数集合</param>
/// <returns>
/// 返回dataset
/// </returns>
public static DataSet GetDataSet(string strSql, params SQLiteParameter[] paraList)
{
DataSet ds = null;
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand(strSql, connection))
{
try
{
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
ds = new DataSet();
dao.Fill(ds);
connection.Close();
}
catch (SQLiteException e)
{
connection.Close();
StrLastError = e.Message;
}
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
}
finally
{
SQLiteConnection.ClearAllPools();
}
return ds;
}
/// <summary>
/// 取datatable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>
/// 返回DataTable
/// </returns>
public static DataTable GetDataTable(string strSql)
{
DataTable dt = null;
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand(strSql, connection))
{
try
{
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
connection.Close();
}
catch (SQLiteException e)
{
connection.Close();
StrLastError = e.Message;
}
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
}
finally
{
SQLiteConnection.ClearAllPools();
}
return dt;
}
/// <summary>
/// 取datatable
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <param name="paraList">命令参数集合</param>
/// <returns>
/// 返回DataTable
/// </returns>
public static DataTable GetDataTable(string strSql, params SQLiteParameter[] paraList)
{
DataTable dt = null;
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
using (SQLiteCommand cmd = new SQLiteCommand(strSql, connection))
{
try
{
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
connection.Close();
}
catch (SQLiteException e)
{
connection.Close();
StrLastError = e.Message;
}
}
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
}
finally
{
SQLiteConnection.ClearAllPools();
}
return dt;
}
/// <summary>
/// 取最大的ID
/// </summary>
/// <param name="sKeyField">字段</param>
/// <param name="sTableName">表名</param>
/// <returns></returns>
public static int GetMaxId(string sKeyField, string sTableName)
{
DataTable dt = GetDataTable("select ifnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]");
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
/// <summary>
/// 取最大的ID
/// </summary>
/// <param name="sKeyField">字段</param>
/// <param name="sTableName">表名</param>
/// <param name="paraList">命令参数集合</param>
/// <returns></returns>
public static int GetMaxId(string sKeyField, string sTableName, params SQLiteParameter[] paraList)
{
DataTable dt = GetDataTable("select ifnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]",
paraList);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0].ToString());
}
return 0;
}
/// <summary>
/// 查询数据库中的所有数据类型信息
/// </summary>
/// <returns></returns>
public static DataTable GetSchema()
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
connection.Open();
DataTable data = connection.GetSchema("TABLES");
connection.Close();
SQLiteConnection.ClearAllPools();
return data;
}
}
/// <summary>
/// 取某个单一的元素
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <param name="paraList">命令参数集合</param>
/// <returns>
/// 返回dataset的第一行第一列的元素
/// </returns>
public static object GetSingle(string strSql, params SQLiteParameter[] paraList)
{
DataTable dt = GetDataTable(strSql, paraList);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
/// <summary>
/// 取某个单一的元素
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <returns>
/// 返回dataset的第一行第一列的元素
/// </returns>
public static object GetSingle(string strSql)
{
DataTable dt = GetDataTable(strSql);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
return null;
}
/// <summary>
/// 判断是否存在
/// </summary>
/// <param name="strSql">SQL</param>
/// <param name="paraList">命令参数集合</param>
/// <returns>
/// <c>true</c> if the specified string SQL is exist; otherwise, <c>false</c>.
/// </returns>
public static bool IsExist(string strSql, params SQLiteParameter[] paraList)
{
DataTable dt;
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
SQLiteCommand cmd = new SQLiteCommand
{
CommandText = strSql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
cmd.Connection = connection;
SQLiteDataAdapter dao = new SQLiteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
connection.Close();
SQLiteConnection.ClearAllPools();
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
return false;
}
if (dt.Rows.Count > 0)
{
if (Convert.ToInt32(dt.Rows[0][0]) > 0)
{
return true;
}
}
return false;
}
/// <summary>
/// 执行insert,update,delete 动作,也可以使用事务
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <param name="bUseTransaction">是否使用事务,默认不使用事务</param>
/// <returns></returns>
public static bool UpdateData(string strSql, bool bUseTransaction)
{
int iResult;
if (!bUseTransaction)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(ConnString))
{
SQLiteCommand comm = new SQLiteCommand(connection)
{
CommandText = strSql
};
iResult = comm.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
StrLastError = ex.Message;
iResult = -1;
}
}
else // 使用事务
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
SQLiteCommand comm = new SQLiteCommand(conn)
{
CommandText = strSql
};
iResult = comm.ExecuteNonQuery();
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
iResult = -1;
trans.Rollback();
StrLastError = ex.Message;
}
}
SQLiteConnection.ClearAllPools();
return iResult > 0;
}
/// <summary>
/// 执行insert,update,delete 动作,也可以使用事务
/// </summary>
/// <param name="strSql">strSql语句</param>
/// <param name="bUseTransaction">是否使用事务,默认不使用事务</param>
/// <param name="paraList">命令参数集合</param>
/// <returns></returns>
public static bool UpdateData(string strSql, bool bUseTransaction, params SQLiteParameter[] paraList)
{
int iResult;
if (!bUseTransaction)
{
try
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn)
{
CommandText = strSql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
iResult = cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
iResult = -1;
StrLastError = ex.Message;
}
}
else // 使用事务
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
SQLiteCommand cmd = new SQLiteCommand(conn)
{
CommandText = strSql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
iResult = cmd.ExecuteNonQuery();
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
LogHelper.Error($"执行sql输出错误:{ex.Message}\r\nSql语句:{strSql}");
iResult = -1;
trans.Rollback();
StrLastError = ex.Message;
}
}
SQLiteConnection.ClearAllPools();
return iResult > 0;
}
/// <summary>
/// Prepares the command.
/// </summary>
/// <param name="cmd">The command.</param>
/// <param name="conn">The connection.</param>
/// <param name="trans">The trans.</param>
/// <param name="cmdText">The command text.</param>
/// <param name="cmdParms">The command parms.</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = CommandType.Text;
if (cmdParms != null)
{
foreach (SQLiteParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
/// <summary>
/// Existses the specified string SQL.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <returns></returns>
public static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if (Equals(obj, null) || Equals(obj, DBNull.Value))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
return true;
}
/// <summary>
/// Existses the specified string SQL.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <param name="cmdParms">The command parms.</param>
/// <returns></returns>
public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if (Equals(obj, null) || Equals(obj, DBNull.Value))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
return true;
}
/// <summary>
/// Adds the column.
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="dataType">Type of the data.</param>
/// <returns></returns>
public static bool AddColumn(string tableName, string columnName, string dataType)
{
SQLiteConnection conn = new SQLiteConnection(ConnString);
conn.Open();
string pragmaSql = $"PRAGMA table_info(\"{tableName}\")";
DataSet ds = GetDataSet(pragmaSql);
List<string> nameList= new List<string>();
if (!Equals(ds, null) && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dataRow in ds.Tables[0].Rows)
{
if (dataRow["name"] != null)
{
nameList.Add(dataRow["name"].ToString());
}
}
}
if (!nameList.Contains(columnName))
{
string alterSql = $"ALTER TABLE {tableName} ADD {columnName} {dataType}";
int iResult = ExecuteNonQuery(alterSql);
return iResult > 0;
}
return true;
}
}
2、操作数据库类
//创建数据库表
static DataHelper()
{
try
{
string strDbSql = _createTableDataSql + _creatTableCaSql + _createTableTaskSql + _createTableTerminalSql + _createTableWorkerSql;
DbHelperSqlite.CreateDbTable(Gloabl.DataDbFile, strDbSql);
}
catch (Exception exception)
{
LogHelper.Error("创建数据文件出现错误:" + exception.Message);
}
}
partial class DataHelper
{
/// <summary>
/// The create data table SQL
/// </summary>
private static string _createTableDataSql = Properties.Resources.Table_Data;
/// <summary>
/// Gets the data.
/// </summary>
/// <param name="nsrsbh">The NSRSBH.</param>
/// <param name="period">The period.</param>
/// <param name="key">The key.</param>
/// <returns></returns>
public static string GetData(string nsrsbh, string period, string key)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select value from data");
strSql.Append(" where nsrsbh=@nsrsbh and period=@period and key=@key");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@nsrsbh", DbType.String),
new SQLiteParameter("@period", DbType.String),
new SQLiteParameter("@key", DbType.String)
};
parameters[0].Value = nsrsbh;
parameters[1].Value = period;
parameters[2].Value = key;
object objScalar = DbHelperSqlite.ExecuteScalar(strSql.ToString(), parameters);
return objScalar.ToString();
}
public static bool SetData(string nsrsbh, string period, string key, string value)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("replace into data");
strSql.Append(" (nsrsbh,period,key,value)");
strSql.Append(" values(@nsrsbh,@period,@key,@value)");
SQLiteParameter[] parameters =
{
new SQLiteParameter("@nsrsbh", DbType.String),
new SQLiteParameter("@period", DbType.String),
new SQLiteParameter("@key", DbType.String),
new SQLiteParameter("@value", DbType.String)
};
parameters[0].Value = nsrsbh;
parameters[1].Value = period;
parameters[2].Value = key;
parameters[3].Value = value;
int objScalar = DbHelperSqlite.ExecuteNonQuery(strSql.ToString(), parameters);
return objScalar > 0;
}
}
3、业务处理类
/// <summary>
/// 获取数据
/// </summary>
/// <param name="nsrsbh"></param>
/// <param name="period"></param>
/// <param name="key"></param>
/// <returns></returns>
public Result<string> GetData(string nsrsbh, string period, string key)
{
try
{
string value = DataHelper.GetData(nsrsbh, period, key);
return Result.Success(null, value);
}
catch (Exception ex)
{
LogHelper.Error(ex);
return Result.Fail(ex.Message, string.Empty);
}
}