C# SQLite数据库访问封装类

最新推荐文章于 2024-09-11 07:00:00 发布

imxiangzi

最新推荐文章于 2024-09-11 07:00:00 发布

阅读量2.8k

点赞数

分类专栏： C#

C# 专栏收录该内容

80 篇文章 5 订阅

订阅专栏

在客户端配置文件<configuration>节点下，添加：

 
   [html]  
   view plaincopy
<connectionStrings>  
    <add name="localdb" connectionString="Data Source=config/local.db;Version=3;UseUTF16Encoding=True;" providerName="System.Data.SQLite.SQLiteFactory"/>  
</connectionStrings>  

其中【localdb】是本地SQLite数据库的名称，【config/local.db】是在当前程序运行目录下SQLite数据库位置

C# SQLite数据库访问封装类代码：

 
   [csharp]  
   view plaincopy
/// <summary>  
   /// 本类为SQLite数据库帮助静态类,使用时只需直接调用即可,无需实例化  
   /// </summary>  
   public static class SQLiteHelper  
   {  
       // Application.StartupPath  
       public static string LocalDbConnectionString = ConfigurationManager.ConnectionStrings["localdb"].ConnectionString;  
 
       #region ExecuteNonQuery  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="cmd">SqlCommand对象</param>  
       /// <returns>所受影响的行数</returns>  
       public static int ExecuteNonQuery(string connectionString, SQLiteCommand cmd)  
       {  
           int result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);  
               try  
               {  
                   result = cmd.ExecuteNonQuery();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <returns>所受影响的行数</returns>  
       public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType)  
       {  
           int result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
           SQLiteCommand cmd = new SQLiteCommand();  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, commandType, commandText);  
               try  
               {  
                   result = cmd.ExecuteNonQuery();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <param name="cmdParms">SQL参数对象</param>  
       /// <returns>所受影响的行数</returns>  
       public static int ExecuteNonQuery(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)  
       {  
           int result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
  
           SQLiteCommand cmd = new SQLiteCommand();  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, commandType, commandText);  
               try  
               {  
                   result = cmd.ExecuteNonQuery();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
       #endregion  
 
       #region ExecuteScalar  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="cmd">SqlCommand对象</param>  
       /// <returns>查询所得的第1行第1列数据</returns>  
       public static object ExecuteScalar(string connectionString, SQLiteCommand cmd)  
       {  
           object result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, cmd.CommandType, cmd.CommandText);  
               try  
               {  
                   result = cmd.ExecuteScalar();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <returns>查询所得的第1行第1列数据</returns>  
       public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType)  
       {  
           object result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
           SQLiteCommand cmd = new SQLiteCommand();  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, commandType, commandText);  
               try  
               {  
                   result = cmd.ExecuteScalar();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
  
       /// <summary>  
       /// 执行数据库操作(新增、更新或删除)同时返回执行后查询所得的第1行第1列数据  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <param name="cmdParms">SQL参数对象</param>  
       /// <returns>查询所得的第1行第1列数据</returns>  
       public static object ExecuteScalar(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)  
       {  
           object result = 0;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
  
           SQLiteCommand cmd = new SQLiteCommand();  
           using (SQLiteConnection con = new SQLiteConnection(connectionString))  
           {  
               SQLiteTransaction trans = null;  
               PrepareCommand(cmd, con, ref trans, true, commandType, commandText);  
               try  
               {  
                   result = cmd.ExecuteScalar();  
                   trans.Commit();  
               }  
               catch (Exception ex)  
               {  
                   trans.Rollback();  
                   throw ex;  
               }  
           }  
           return result;  
       }  
       #endregion  
 
       #region ExecuteReader  
       /// <summary>  
       /// 执行数据库查询，返回SqlDataReader对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="cmd">SqlCommand对象</param>  
       /// <returns>SqlDataReader对象</returns>  
       public static DbDataReader ExecuteReader(string connectionString, SQLiteCommand cmd)  
       {  
           DbDataReader reader = null;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);  
           try  
           {  
               reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           return reader;  
       }  
  
       /// <summary>  
       /// 执行数据库查询，返回SqlDataReader对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <returns>SqlDataReader对象</returns>  
       public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType)  
       {  
           DbDataReader reader = null;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteCommand cmd = new SQLiteCommand();  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, commandType, commandText);  
           try  
           {  
               reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           return reader;  
       }  
  
       /// <summary>  
       /// 执行数据库查询，返回SqlDataReader对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <param name="cmdParms">SQL参数对象</param>  
       /// <returns>SqlDataReader对象</returns>  
       public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)  
       {  
           DbDataReader reader = null;  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteCommand cmd = new SQLiteCommand();  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);  
           try  
           {  
               reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           return reader;  
       }  
       #endregion  
 
       #region ExecuteDataSet  
       /// <summary>  
       /// 执行数据库查询，返回DataSet对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="cmd">SqlCommand对象</param>  
       /// <returns>DataSet对象</returns>  
       public static DataSet ExecuteDataSet(string connectionString, SQLiteCommand cmd)  
       {  
           DataSet ds = new DataSet();  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, cmd.CommandType, cmd.CommandText);  
           try  
           {  
               SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);  
               sda.Fill(ds);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           finally  
           {  
               if (cmd.Connection != null)  
               {  
                   if (cmd.Connection.State == ConnectionState.Open)  
                   {  
                       cmd.Connection.Close();  
                   }  
               }  
           }  
           return ds;  
       }  
  
       /// <summary>  
       /// 执行数据库查询，返回DataSet对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <returns>DataSet对象</returns>  
       public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType)  
       {  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
           DataSet ds = new DataSet();  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteCommand cmd = new SQLiteCommand();  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, commandType, commandText);  
           try  
           {  
               SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);  
               sda.Fill(ds);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           finally  
           {  
               if (con != null)  
               {  
                   if (con.State == ConnectionState.Open)  
                   {  
                       con.Close();  
                   }  
               }  
           }  
           return ds;  
       }  
  
       /// <summary>  
       /// 执行数据库查询，返回DataSet对象  
       /// </summary>  
       /// <param name="connectionString">连接字符串</param>  
       /// <param name="commandText">执行语句或存储过程名</param>  
       /// <param name="commandType">执行类型</param>  
       /// <param name="cmdParms">SQL参数对象</param>  
       /// <returns>DataSet对象</returns>  
       public static DataSet ExecuteDataSet(string connectionString, string commandText, CommandType commandType, params SQLiteParameter[] cmdParms)  
       {  
           if (connectionString == null || connectionString.Length == 0)  
               throw new ArgumentNullException("connectionString");  
           if (commandText == null || commandText.Length == 0)  
               throw new ArgumentNullException("commandText");  
           DataSet ds = new DataSet();  
           SQLiteConnection con = new SQLiteConnection(connectionString);  
           SQLiteCommand cmd = new SQLiteCommand();  
           SQLiteTransaction trans = null;  
           PrepareCommand(cmd, con, ref trans, false, commandType, commandText, cmdParms);  
           try  
           {  
               SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);  
               sda.Fill(ds);  
           }  
           catch (Exception ex)  
           {  
               throw ex;  
           }  
           finally  
           {  
               if (con != null)  
               {  
                   if (con.State == ConnectionState.Open)  
                   {  
                       con.Close();  
                   }  
               }  
           }  
           return ds;  
       }  
       #endregion  
 
       #region 通用分页查询方法  
       /// <summary>  
       /// 通用分页查询方法  
       /// </summary>  
       /// <param name="connString">连接字符串</param>  
       /// <param name="tableName">表名</param>  
       /// <param name="strColumns">查询字段名</param>  
       /// <param name="strWhere">where条件</param>  
       /// <param name="strOrder">排序条件</param>  
       /// <param name="pageSize">每页数据数量</param>  
       /// <param name="currentIndex">当前页数</param>  
       /// <param name="recordOut">数据总量</param>  
       /// <returns>DataTable数据表</returns>  
       public static DataTable SelectPaging(string connString, string tableName, string strColumns, string strWhere, string strOrder, int pageSize, int currentIndex, out int recordOut)  
       {  
           DataTable dt = new DataTable();  
           recordOut = Convert.ToInt32(ExecuteScalar(connString, "select count(*) from " + tableName, CommandType.Text));  
           string pagingTemplate = "select {0} from {1} where {2} order by {3} limit {4} offset {5} ";  
           int offsetCount = (currentIndex - 1) * pageSize;  
           string commandText = String.Format(pagingTemplate, strColumns, tableName, strWhere, strOrder, pageSize.ToString(), offsetCount.ToString());  
           using (DbDataReader reader = ExecuteReader(connString, commandText, CommandType.Text))  
           {  
               if (reader != null)  
               {  
                   dt.Load(reader);  
               }  
           }  
           return dt;  
       }  
       #endregion  
 
       #region  预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化  
       /// <summary>  
       /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化  
       /// </summary>  
       /// <param name="cmd">Command对象</param>  
       /// <param name="conn">Connection对象</param>  
       /// <param name="trans">Transcation对象</param>  
       /// <param name="useTrans">是否使用事务</param>  
       /// <param name="cmdType">SQL字符串执行类型</param>  
       /// <param name="cmdText">SQL Text</param>  
       /// <param name="cmdParms">SQLiteParameters to use in the command</param>  
       private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)  
       {  
  
           if (conn.State != ConnectionState.Open)  
               conn.Open();  
  
           cmd.Connection = conn;  
           cmd.CommandText = cmdText;  
  
           if (useTrans)  
           {  
               trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);  
               cmd.Transaction = trans;  
           }  
  
  
           cmd.CommandType = cmdType;  
  
           if (cmdParms != null)  
           {  
               foreach (SQLiteParameter parm in cmdParms)  
                   cmd.Parameters.Add(parm);  
           }  
       }  
 
       #endregion  
   }  

使用demo：

 
   [csharp]  
   view plaincopy
/// <summary>  
       /// 获取数据库关键字信息  
       /// </summary>  
       /// <param name="category">分类</param>  
       /// <param name="versions">版本</param>  
       /// <returns></returns>  
       private DataSet GetSystemDataBaseKeyWords(string category, string versions)  
       {  
           StringBuilder sql = new StringBuilder();  
           sql.Append("SELECT Keywords , Versions , Type , Description , Category  , Id , Extends ");  
           sql.Append(" FROM A_DataBaseKeyWords ");  
           sql.AppendFormat(" WHERE 1={0} ", "1");  
           if (!String.IsNullOrEmpty(category))  
           {  
               sql.AppendFormat(" AND Category='{0}'", category);  
           }  
           if (!String.IsNullOrEmpty(versions))  
           {  
               sql.AppendFormat(" AND Versions='{0}'", versions);  
           }  
           return SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sql.ToString(), CommandType.Text);  
       }  