前端之家收集整理的这篇文章主要介绍了
SQLite操作公共类，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
/// <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>
public static void CreateDbTable(string strDbpath,string sql)
{
if (!File.Exists(strDbpath))
{
ConnString = $"Data Source={strDbpath};Pooling=false;FailIfMissing=false";
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";
}
}
/// <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)
{
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)
{
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)
{
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)
{
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)
{
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)
{
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)
{
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,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)
{
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)
{
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;
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand cmd = new sqliteCommand
{
CommandText = strsql,Connection = conn
};
sqliteDataAdapter dao = new sqliteDataAdapter(cmd);
ds = new DataSet();
dao.Fill(ds);
conn.Close();
}
catch (Exception ex)
{
StrLastError = ex.Message;
}
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand cmd = new sqliteCommand
{
CommandText = strsql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
cmd.Connection = conn;
sqliteDataAdapter dao = new sqliteDataAdapter(cmd);
ds = new DataSet();
dao.Fill(ds);
conn.Close();
}
catch (Exception ex)
{
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand cmd = new sqliteCommand
{
CommandText = strsql,Connection = conn
};
sqliteDataAdapter dao = new sqliteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
conn.Close();
}
catch (Exception ex)
{
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand cmd = new sqliteCommand
{
CommandText = strsql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
cmd.Connection = conn;
sqliteDataAdapter dao = new sqliteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
conn.Close();
}
catch (Exception ex)
{
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand cmd = new sqliteCommand
{
CommandText = strsql
};
if (paraList != null && paraList.Length > 0)
{
cmd.Parameters.AddRange(paraList);
}
cmd.Connection = conn;
sqliteDataAdapter dao = new sqliteDataAdapter(cmd);
dt = new DataTable();
dao.Fill(dt);
conn.Close();
sqliteConnection.ClearAllPools();
}
catch (Exception ex)
{
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
{
sqliteConnection conn = new sqliteConnection(ConnString);
conn.Open();
sqliteCommand comm = new sqliteCommand(conn)
{
CommandText = strsql
};
iResult = comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
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)
{
iResult = -1;
trans.Rollback();
StrLastError = ex.Message;
}
}
sqliteConnection.ClearAllPools();
return iResult > 0;
}
/// <summary>
/// 执行insert,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)
{
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)
{
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,DBNull.Value))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
return true;
}
} 原文链接：https://www.f2er.com/sqlite/198333.html
