using System;using System.Collections.Generic;using System.Text;using System.Data.SQLite;using System.Data;namespace MySQLiteDemo{ public class SqliteHelper { private string _connectString = string.Empty; public string ConnectString { get { return _connectString; } set { _connectString = value; } } /// <summary> /// 检测 /// </summary> /// <returns></returns> public static bool TestLite(string strConn) { SQLiteConnection conn = new SQLiteConnection(strConn); conn.Open(); if (conn.State != ConnectionState.Open) { return false; } return true; } /// <summary> /// 获取数据 /// </summary> /// <returns></returns> public DataTable GetData(string strSql) { return GetData(strSql, null); } /// <summary> /// 获取数据 /// </summary> /// <returns></returns> public DataTable GetData(string strSql, SQLiteParameter[] cmdParams) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql, con); if (cmdParams != null) { foreach (SQLiteParameter param in cmdParams) { cmd.Parameters.Add(param); } } con.Open(); DataTable ret = new DataTable(); SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd); ad.Fill(ret); return ret; } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 获取SQLiteDataReader /// </summary> /// <returns></returns> public SQLiteDataReader GetReader(string strSql) { return GetReader(strSql, null); } /// <summary> /// 执行带参数的查询语句，返回SQLiteDataReader ( 注意：调用该方法后，一定要对SQLiteDataReader进行Close ) /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public SQLiteDataReader GetReader(string strSql, params SQLiteParameter[] cmdParms) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql, con); if (cmdParms != null) { foreach (SQLiteParameter param in cmdParms) { cmd.Parameters.Add(param); } } con.Open(); SQLiteDataReader myReader = cmd.ExecuteReader(); cmd.Parameters.Clear(); return myReader; } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 执行语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteSql(string strSql) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql, con); con.Open(); return cmd.ExecuteNonQuery(); } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 执行语句 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecuteSql(string strSql, SQLiteParameter[] cmdParams) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql, con); if (cmdParams != null) { foreach (SQLiteParameter param in cmdParams) { cmd.Parameters.Add(param); } } con.Open(); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 获取当前第一行第一列值 /// </summary> /// <param name="strsql"></param> /// <param name="cmdParams"></param> /// <returns></returns> public object GetSingle(string strSql) { return GetSingle(strSql, null); } /// <summary> /// 获取当前第一行第一列值 /// </summary> /// <param name="strsql"></param> /// <param name="cmdParams"></param> /// <returns></returns> public object GetSingle(string strSql, SQLiteParameter[] cmdParams) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql, con); if (cmdParams != null) { foreach (SQLiteParameter param in cmdParams) { cmd.Parameters.Add(param); } } con.Open(); return cmd.ExecuteScalar(); } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 页级操作 /// </summary> /// <param name="sql"></param> /// <param name="startRecord"></param> /// <param name="maxRecord"></param> /// <returns></returns> public DataTable FindByPaging(string strSql, SQLiteParameter[] cmdParams, int startRecord, int maxRecord) { using (SQLiteConnection con = new SQLiteConnection(_connectString)) { try { SQLiteCommand cmd = new SQLiteCommand(strSql + " LIMIT " + +startRecord + "," + maxRecord, con); if (cmdParams != null) { foreach (SQLiteParameter param in cmdParams) { cmd.Parameters.Add(param); } } con.Open(); DataTable ret = new DataTable(); SQLiteDataAdapter ad = new SQLiteDataAdapter(cmd); ad.Fill(ret); return ret; } catch (SQLiteException ex) { throw new Exception(ex.Message); } } } /// <summary> /// 启用事务. /// </summary> /// <param name="objTrans"></param> /// <returns></returns> public SQLiteTransaction BeginSQLTrans() { SQLiteConnection con = new SQLiteConnection(_connectString); con.Open(); return con.BeginTransaction(); } /// <summary> /// 执行语句 /// </summary> /// <param name="strSql"></param> /// <param name="cmdParams"></param> /// <param name="trans"></param> /// <returns></returns> public int ExecuteSql(string strSql, SQLiteTransaction trans) { return ExecuteSql(strSql, null, trans); } /// <summary> /// 执行语句 /// </summary> /// <param name="strSql"></param> /// <param name="cmdParams"></param> /// <param name="trans"></param> /// <returns></returns> public int ExecuteSql(string strSql, SQLiteParameter[] cmdParams, SQLiteTransaction trans) { int ret = 0; using (SQLiteCommand cmd = new SQLiteCommand(strSql, trans.Connection)) { try { if (cmdParams != null) { foreach (SQLiteParameter param in cmdParams) { cmd.Parameters.Add(param); } } if (trans != null) cmd.Transaction = trans; ret = cmd.ExecuteNonQuery(); } catch (SQLiteException ex) { throw ex; } catch (Exception ex) { throw ex; } } return ret; } }}