public class SQLiteBase : IDisposable { SQLiteConnection conn; SQLiteTransaction trans = null; /// <summary> /// 连接字符串 /// </summary> private static string ConnectionString { get { string strConnection = @"Data Source=" + Application.StartupPath + @"EPMSData.db" + ";Version=3;"; return strConnection; } } /// <summary> /// 新建数据库连接,构造函数 /// </summary> public SQLiteBase() { conn = new SQLiteConnection(ConnectionString); conn.Open(); } #region 释放资源 /// <summary> /// 释放资源 /// </summary> public void Dispose() { Dispose(true); GC.SuppressFinalize(true); } /// <summary> /// 释放资源 /// </summary> /// <param name="disposing"></param> protected virtual void Dispose(bool disposing) { if (!disposing) { return; } if (trans != null) { try { trans.Commit(); } catch { } } this.conn.Close(); } #endregion #region 执行ExecuteNonQuery /// <summary> /// 执行查询语句 /// </summary> /// <param name="strSQL">SQL语句</param> /// <returns></returns> public int ExecuteNonQuery(string strSQL) { return ExecuteNonQuery(strSQL, (SQLiteParameter[])null, CommandType.Text); } /// <summary> /// 执行查询语句 /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="CT"></param> /// <returns></returns> public int ExecuteNonQuery(string strSQL, CommandType CT) { return ExecuteNonQuery(strSQL, (SQLiteParameter[])null, CT); } /// <summary> /// 执行查询语句或者存储过程 /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="arrParameter"></param> /// <returns></returns> public int ExecuteNonQuery(string strSQL, SQLiteParameter[] arrParameter) { return ExecuteNonQuery(strSQL, arrParameter, CommandType.Text); } /// <summary> /// 执行查询语句或者存储过程 /// </summary> /// <param name="strSQL"></param> /// <param name="arrParameter"></param> /// <returns></returns> public int ExecuteNonQuery(string strSQL, List<SQLiteParameter> arrParameter) { return ExecuteNonQuery(strSQL, arrParameter.ToArray(), CommandType.Text); } /// <summary> /// 执行查询语句或者存储过程 /// </summary> /// <param name="strSQL"></param> /// <param name="arrParameter"></param> /// <param name="CT"></param> /// <returns></returns> public int ExecuteNonQuery(string strSQL, List<SQLiteParameter> arrParameter, CommandType CT) { return ExecuteNonQuery(strSQL, arrParameter.ToArray(), CT); } /// <summary> /// 执行查询语句 /// </summary> /// <param name="strSQL"></param> /// <param name="arrParameter"></param> /// <param name="CT"></param> /// <returns></returns> public int ExecuteNonQuery(string strSQL, SQLiteParameter[] arrParameter, CommandType CT) { using (SQLiteCommand cmd = new SQLiteCommand(strSQL, conn)) { if (trans == null && CT != CommandType.StoredProcedure) { trans = conn.BeginTransaction(IsolationLevel.ReadCommitted); } if (CT != CommandType.StoredProcedure) { cmd.Transaction = trans; } cmd.CommandType = CT; if (arrParameter != null) cmd.Parameters.AddRange(arrParameter); try { int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } catch (SqlException SE) { if (CT != CommandType.StoredProcedure) { trans.Rollback(); trans.Dispose(); } throw new Exception("执行" + strSQL + "发生错误，错误信息是：" + SE.Message); } } } #endregion #region 返回 SqlDataReader /// <summary> /// 执行SQL命令 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public static SQLiteDataReader ExecuteReader(string strSQL) { return ExecuteReader(strSQL, (SQLiteParameter[])null, CommandType.Text); } /// <summary> /// 执行SQL命令和没有参数的存储过程 /// </summary> /// <param name="strSQL"></param> /// <param name="CT"></param> /// <returns></returns> public static SQLiteDataReader ExecuteReader(string strSQL, CommandType CT) { return ExecuteReader(strSQL, (SQLiteParameter[])null, CT); } /// <summary> /// 执行SQL命令 /// </summary> /// <param name="strSQL"></param> /// <param name="arrParameter"></param> /// <returns></returns> public static SQLiteDataReader ExecuteReader(string strSQL, SQLiteParameter[] arrParameter) { return ExecuteReader(strSQL, arrParameter, CommandType.Text); } /// <summary> /// 执行SQL命令或没有参数的或者存储过程 /// </summary> /// <param name="strSQL"></param> /// <param name="arrParameter"></param> /// <returns></returns> public static SQLiteDataReader ExecuteReader(string strSQL, SQLiteParameter[] arrParameter, CommandType CT) { SQLiteConnection conn = new SQLiteConnection(ConnectionString); try { using (SQLiteCommand command = new SQLiteCommand(strSQL, conn)) { command.CommandType = CT; if (arrParameter != null) command.Parameters.AddRange(arrParameter); conn.Open(); SQLiteDataReader reader = command.ExecuteReader(); return reader; } } catch { conn.Dispose(); throw; } } #endregion #region 执行ExecuteSql /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SQLite.SQLiteException E) { connection.Close(); throw new Exception(E.Message); } } } } /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms) { using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (System.Data.SQLite.SQLiteException E) { throw new Exception(E.Message); } } } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.SQLite.SQLiteException ex) { throw new Exception(ex.Message); } return ds; } } /// <summary> /// 执行查询语句，返回DataSet /// </summary> /// <param name="SQLString">查询语句</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms) { using (SQLiteConnection connection = new SQLiteConnection(ConnectionString)) { SQLiteCommand cmd = new SQLiteCommand(); PrepareCommand(cmd, connection, null, SQLString, cmdParms); using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) { DataSet ds = new DataSet(); try { da.Fill(ds, "ds"); cmd.Parameters.Clear(); } catch (System.Data.SQLite.SQLiteException ex) { throw new Exception(ex.Message); } return ds; } } } 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;//cmdType; if (cmdParms != null) { foreach (SQLiteParameter parm in cmdParms) cmd.Parameters.Add(parm); } } #endregion }
调用方法
public class SQLiteOperation { /// <summary> /// 添加 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="sqlpar">参数</param> /// <returns></returns> public static int AddSQlite(string sql, SQLiteParameter[] sqlpar) { using (SQLiteBase mySQLiteBase = new SQLiteBase()) { if (sqlpar!=null) return mySQLiteBase.ExecuteNonQuery(sql, sqlpar); else return mySQLiteBase.ExecuteNonQuery(sql); } } /// <summary> /// 修改 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="sqlpar">参数</param> /// <returns></returns> public static int UpdataSQlite(string sql, SQLiteParameter[] sqlpar) { using (SQLiteBase mySQLiteBase = new SQLiteBase()) { if (sqlpar != null) return mySQLiteBase.ExecuteNonQuery(sql, sqlpar); else return mySQLiteBase.ExecuteNonQuery(sql); } } /// <summary> /// 删除 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="sqlpar">参数</param> /// <returns></returns> public static int DeleteSQlite(string sql, SQLiteParameter[] sqlpar) { try { using (SQLiteBase mySQLiteBase = new SQLiteBase()) { if (sqlpar != null) return mySQLiteBase.ExecuteNonQuery(sql, sqlpar); else return mySQLiteBase.ExecuteNonQuery(sql); } } catch (Exception ex) { LogHelper.WriteLog(typeof(SQLiteOperation), ex); return -1; } } /// <summary> /// 查询 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="sqlpar">参数</param> /// <returns></returns> public static SQLiteDataReader SelectSQlite(string sql, SQLiteParameter[] sqlpar) { //SQLiteDataReader dt = new SQLiteDataReader(); if (sqlpar != null) { using (SQLiteDataReader dt = SQLiteBase.ExecuteReader(sql, sqlpar)) { return dt; } } else { using (SQLiteDataReader dt = SQLiteBase.ExecuteReader(sql)) { return dt; } } } /// <summary> /// 查询，返回dataset /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="sqlpar">参数</param> /// <returns></returns> public static DataSet Query(string sql, SQLiteParameter[] sqlpar) { if (sqlpar != null) { using (DataSet ds = SQLiteBase.Query(sql, sqlpar)) { return ds; } } else { using (DataSet ds = SQLiteBase.Query(sql)) { return ds; } } } }