SQlite数据创建加密列举增删改查代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 SQlite数据创建加密列举增删改查
作者:xudianlong / 发布于2014/7/4/ 933

	/// <summary>
	 /// 说明：这是一个针对System.Data.SQLite的数据库常规操作封装的通用类。
	 /// 日期：2012-07-21

	 /// </summary>
	 public class SQLiteDBHelper
	 {

	 public SQLiteDBHelper() { }

	 private string connectionString = string.Empty;
	 /// <summary>
	 /// 构造函数
	 /// </summary>
	 /// <param name="dbPath">SQLite数据库文件路径</param>
	 public SQLiteDBHelper(string dbPath)
	 {
	 this.connectionString = "Data Source=" + dbPath;
	 }
	 /// <summary>
	 /// 连接数据
	 /// </summary>
	 /// <param name="path"></param>
	 /// <returns></returns>
	 private static string GetConstr()
	 {
	 // path += "D:\\Demo.db3";
	 return "Data Source=" + "D:\\Demo.db3";// +"\";Version=3;Cache Size=20000000;Password=iodfodif36oasdfs45adjflkdjfkdjfk1212jdfkdjfk"; //
	 }

	 /// <summary>
	 /// 创建SQLite数据库文件
	 /// </summary>
	 /// <param name="dbPath">要创建的SQLite数据库文件路径</param>
	 public static void CreateDB(string dbPath)
	 {
	 using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath))
	 {
	 connection.Open();
	 using (SQLiteCommand command = new SQLiteCommand(connection))
	 {
	 command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
	 command.ExecuteNonQuery();
	 command.CommandText = "DROP TABLE Demo";
	 command.ExecuteNonQuery();
	 }
	 }
	 }

	 /// <summary>
	 /// 查询方法。返回一个Dateset结果集
	 /// </summary>
	 /// <param name="sql">一个SQL语句</param>
	 /// <returns></returns>
	 public static DataSet GetDataSet(string sql)
	 {
	 DataSet ds = new DataSet();
	 SQLiteConnection conn = new SQLiteConnection(GetConstr());
	 try
	 {
	 conn.Open();
	 SQLiteDataAdapter mda = new SQLiteDataAdapter(sql, conn);
	 mda.Fill(ds, "table1");
	 conn.Close();
	 }
	 catch (Exception ex)
	 {
	 LogInfo.WLlog(sql + ex.ToString());
	 }
	 finally
	 {
	 if (conn.State == ConnectionState.Open)
	 {
	 conn.Close();
	 }
	 }
	 return ds;
	 }

	 /// <summary>
	 /// 查询方法。返回一个Dateset结果集
	 /// </summary>
	 /// <param name="sql">第一个SQL语句</param>
	 /// <param name="sqlCount">第二个SQL语句</param>
	 /// <returns></returns>
	 public static DataSet GetDataSet(string sql, string sqlCount)
	 {
	 DataSet ds = new DataSet();
	 SQLiteConnection conn = new SQLiteConnection(GetConstr());
	 try
	 {
	 conn.Open();

	 SQLiteDataAdapter mda = new SQLiteDataAdapter(sql, conn);

	 mda.Fill(ds, "table1");

	 mda = new SQLiteDataAdapter(sqlCount, conn);
	 mda.Fill(ds, "table2");

	 conn.Close();
	 }
	 catch (Exception ex)
	 {
	 LogInfo.WLlog(sql + " " + sqlCount + " " + ex.ToString());
	 }
	 finally
	 {
	 if (conn.State == ConnectionState.Open)
	 {
	 conn.Close();
	 }
	 }
	 return ds;
	 }

	 /// <summary>
	 /// 对SQLite数据库执行增删改操作，返回受影响的行数。
	 /// </summary>
	 /// <param name="sql">要执行的增删改的SQL语句</param>
	 /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
	 {
	 int affectedRows = 0;
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 connection.Open();
	 using (DbTransaction transaction = connection.BeginTransaction())
	 {
	 using (SQLiteCommand command = new SQLiteCommand(connection))
	 {
	 command.CommandText = sql;
	 if (parameters != null)
	 {
	 command.Parameters.AddRange(parameters);
	 }
	 affectedRows = command.ExecuteNonQuery();
	 }
	 transaction.Commit();
	 }
	 }
	 return affectedRows;
	 }
	 /// <summary>
	 /// 对SQLite数据库执行增删改操作，返回受影响的行数。
	 /// </summary>
	 /// <param name="sql">要执行的增删改的SQL语句</param>
	 /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public static int ExecuteNonQuery(string sql)
	 {
	 int affectedRows = 0;
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 connection.Open();
	 using (DbTransaction transaction = connection.BeginTransaction())
	 {
	 using (SQLiteCommand command = new SQLiteCommand(sql,connection))
	 {
	 affectedRows = command.ExecuteNonQuery();
	 }
	 }
	 }
	 return affectedRows;
	 }
	 /// <summary>
	 /// 对SQLite数据库执行增删改操作，返回受影响的行数。
	 /// </summary>
	 /// <param name="sql">要执行的增删改的SQL语句</param>
	 /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public int ListExecuteNonQuery(string sql, SQLiteParameter[] parameters)
	 {
	 int affectedRows = 0;
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 connection.Open();
	 using (DbTransaction transaction = connection.BeginTransaction())
	 {
	 using (SQLiteCommand command = new SQLiteCommand(connection))
	 {
	 command.CommandText = sql;
	 if (parameters != null)
	 {
	 command.Parameters.AddRange(parameters);
	 }
	 affectedRows = command.ExecuteNonQuery();
	 }
	 transaction.Commit();
	 }
	 }
	 return affectedRows;
	 }

	 /// <summary>
	 /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例
	 /// </summary>
	 /// <param name="sql">要执行的查询语句</param>
	 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
	 {
	 SQLiteConnection connection = new SQLiteConnection(GetConstr());
	 SQLiteCommand command = new SQLiteCommand(sql, connection);
	 if (parameters != null)
	 {
	 command.Parameters.AddRange(parameters);
	 }
	 connection.Open();
	 return command.ExecuteReader(CommandBehavior.CloseConnection);
	 }
	 /// <summary>
	 /// 执行一个查询语句，返回一个包含查询结果的DataTable
	 /// </summary>
	 /// <param name="sql">要执行的查询语句</param>
	 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
	 {
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
	 {
	 if (parameters != null)
	 {
	 command.Parameters.AddRange(parameters);
	 }
	 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
	 DataTable data = new DataTable();
	 adapter.Fill(data);
	 return data;
	 }
	 }
	 }
	 /// <summary>
	 /// 执行一个查询语句，返回查询结果的第一行第一列
	 /// </summary>
	 /// <param name="sql">要执行的查询语句</param>
	 /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
	 /// <returns></returns>
	 public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
	 {
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 using (SQLiteCommand command = new SQLiteCommand(sql, connection))
	 {
	 if (parameters != null)
	 {
	 command.Parameters.AddRange(parameters);
	 }
	 SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
	 DataTable data = new DataTable();
	 adapter.Fill(data);
	 return data;
	 }
	 }
	 }
	 /// <summary>
	 /// 查询数据库中的所有数据类型信息
	 /// </summary>
	 /// <returns></returns>
	 public DataTable GetSchema()
	 {
	 using (SQLiteConnection connection = new SQLiteConnection(GetConstr()))
	 {
	 connection.Open();
	 DataTable data = connection.GetSchema("TABLES");
	 connection.Close();
	 return data;
	 }
	 }
	 }
试试其它关键字
　SQlite　　加密　
同语言下
可能有用的
xudianlong贡献的其它代码(25)