SQLLite的使用

namespace SQLLiteHelper
{
    /// <summary>
    /// 数据库操作抽象
    /// </summary>
    public interface IDataAccess
    {
        /// <summary>
        /// 打开
        /// </summary>
        void Open();
        /// <summary>
        /// 关闭
        /// </summary>
        void Close();

        /// <summary>
        /// 执行 增 删 改 查
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="queryParameter">参数</param>
        int ExecuteNonQuery(string sql, QueryParameter[] queryParameter);

        /// <summary>
        /// 查询某一个值
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>Object</returns>
        object GetScalar(string sql, QueryParameter[] parameters);

        /// <summary>
        /// 查询多条条数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="dt">DataTabel </param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable</returns>
        DataTable GetTable(string sql, DataTable dt, QueryParameter[] parameters);

        /// <summary>
        /// 查询多条值
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataReader</returns>
        IDataReader GetReader(string sql, QueryParameter[] parameters);

        /// <summary>
        /// 开始事务
        /// </summary>
        void BeginTran();
        /// <summary>
        /// 提交事务
        /// </summary>
        void CommitTran();
        /// <summary>
        ///  回滚事务
        /// </summary>
        void RollBackTran();

    }
}

 public  class DataAccessFactory
    {
      
        public static IDataAccess CreateDataAccess()
        {
            string conStr = System.Configuration.ConfigurationManager.AppSettings["conStr"];
            string conType = System.Configuration.ConfigurationManager.AppSettings["conType"]; 
            switch (conType)
            {
                case "SQLLite":return new SqlLiteDalHelper(conStr);
                    break;
                case "SQLServer":
                    return new SqlLiteDalHelper(conStr);
                    break;

                default:throw new Exception("该数据库不支持!");
            }
        }

    }

 1     public class QueryParameter
 2     {
 3 
 4         public string Name
 5         {
 6             get;
 7             set;
 8         }
 9         public object Value
10         {
11             get;
12             set;
13         }
14         public DbType DbType
15         {
16             get;
17             set;
18         }
19 
20         private ParameterDirection _driection = ParameterDirection.Input;
21 
22         public ParameterDirection Driection
23         {
24             get { return _driection; }
25             set { _driection = value; }
26         }
27         public QueryParameter(string name, object value, DbType dbType)
28         {
29             this.DbType = dbType;
30             this.Value = value;
31             this.Name = name;
32         }
33     }

  1  public class SqlLiteDalHelper : IDataAccess
  2     {
  3         private readonly SQLiteConnection _connection;
  4         private SQLiteTransaction _tran;
  5         public SqlLiteDalHelper(string constr)
  6         {
  7             _connection = new SQLiteConnection(constr);
  8         }
  9 
 10         public void Open()
 11         {
 12             if (this._connection == null || this._connection.State == ConnectionState.Closed)
 13             {
 14                 try
 15                 {
 16                     _connection.Open();
 17                 }
 18                 catch (ArgumentException)
 19                 {
 20                     throw;
 21                 }
 22               }
 23         }
 24 
 25         public void Close()
 26         {
 27             if (this._connection != null || _tran != null)
 28             {
 29                 _connection.Close();
 30             }
 31         }
 32 
 33         public int ExecuteNonQuery(string sql, QueryParameter[] queryParameter)
 34         {
 35             SQLiteCommand cmd = new SQLiteCommand();
 36             PrepareCommond(cmd, CommandType.Text, queryParameter, sql);
 37             int i = cmd.ExecuteNonQuery();
 38             cmd.Parameters.Clear();
 39             return i;
 40         }
 41 
 42         public object GetScalar(string sql, QueryParameter[] parameters)
 43         {
 44             SQLiteCommand cmd = new SQLiteCommand();
 45             PrepareCommond(cmd, CommandType.Text, parameters, sql);
 46             object obj = cmd.ExecuteScalar();
 47             cmd.Parameters.Clear();
 48             return obj;
 49         }
 50 
 51         public System.Data.DataTable GetTable(string sql, DataTable dt, QueryParameter[] parameters)
 52         {
 53             SQLiteCommand cmd = new SQLiteCommand();
 54             PrepareCommond(cmd, CommandType.Text, parameters, sql);
 55             SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
 56             sda.Fill(dt);
 57             cmd.Parameters.Clear();
 58             return dt;
 59         }
 60 
 61         public System.Data.IDataReader GetReader(string sql, QueryParameter[] parameters)
 62         {
 63             SQLiteCommand cmd = new SQLiteCommand();
 64             PrepareCommond(cmd, CommandType.Text, parameters, sql);
 65             SQLiteDataReader dr = cmd.ExecuteReader();
 66             cmd.Parameters.Clear();
 67             return dr;
 68         }
 69 
 70         public void BeginTran()
 71         {
 72             this._tran = this._connection.BeginTransaction();
 73         }
 74 
 75         public void CommitTran()
 76         {
 77             this._tran.Commit();
 78         }
 79 
 80         public void RollBackTran()
 81         {
 82             this._tran.Rollback();
 83         }
 84 
 85         private void PrepareCommond(SQLiteCommand cmd, CommandType commandType, QueryParameter[] parameters, string commandtext)
 86         {
 87             cmd.CommandType = commandType;
 88             cmd.CommandText = commandtext;
 89             cmd.Connection = _connection;
 90             cmd.Transaction = this._tran;
 91             if (parameters != null && parameters.Length > 0)
 92             {
 93                 for (int i = 0; i < parameters.Length; i++)
 94                 {
 95                     cmd.Parameters.AddWithValue(parameters[i].Name, parameters[i].Value);
 96                 }
 97             }
 98         }
 99 
100 
101     }

 

使用方法：

    public DataTable   FindAllTRoleOperator()
    {
        try
        {
            string sql;
            sql="SELECT COMMENTARY,OPERATORID,ROLEID from T_ROLE_OPERATOR";
            DataTable dt = new DataTable("MINORITY");
            data.GetTable(sql, dt, null);
            return dt;
        }
        catch(Exception e)
        {
            throw e;
        }
    }

public DataTable   FindAllTRoleOperator()
    {
        IDataAccess dataAccess  =null;
            try
            {
                dataAccess  =  DataAccessFactory.CreateDataAccess();
                dataAccess.Open();
                RoleOperatorService dal  =  new RoleOperatorService(dataAccess);
                return dal.FindAllTRoleOperator();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (dataAccess != null) dataAccess.Close();
            }
       }

运行结果：