SQLLite的使用
interface
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 }
SqlLiteDalHelper 操作类
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 }
使用方法：
ClientCode
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; } }
View Code
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(); } }
运行结果：
