SQLite操作公共类
发布时间:2020-12-12 19:20:32 所属栏目:百科 来源:网络整理
导读:/// summary /// Copyright (C) ZYY /// 数据访问基础类(基于SQLite) /// /summary public class DbHelperSqlite { /// summary /// 提示信息 /// /summary public static string StrLastError = string.Empty; /// summary /// 命令 /// /summary public en
/// <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; } } (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |