菜单

Sqlite 操作类代码

2018年11月16日 - sqlite
  1. ADO.NET Provider For SQLite.
      ADO.NET
    提供次是香港(貌似)一个店提供的.项目地址见:http://sourceforge.net/projects/sqlite-dotnet2
      2. 对SQLite.NET的包装,提供一个大概的操作帮助类.
    SQLiteHelper

功能:

/// <summary>
/// 创建Sqlite帮助类
/// </summary>
/// <param name="dbName">数据库路径</param>
/// <returns></returns>
SqliteUtil GetSqliteUtil(string dbName);
/// <summary>
/// 删除一个数据库文件
/// 失败返回异常
/// </summary>
/// <param name="dbName"></param>
/// <returns></returns>
bool DeleteDBFile(string dbName);

复制代码 代码如下:

ISqliteService 功能:

/// <summary>
/// 创建Sqlite帮助类
/// </summary>
/// <param name="dbName">数据库路径</param>
/// <returns></returns>
SqliteUtil GetSqliteUtil(string dbName);
/// <summary>
/// 删除一个数据库文件
/// 失败返回异常
/// </summary>
/// <param name="dbName"></param>
/// <returns></returns>
bool DeleteDBFile(string dbName);

/**//**
* SQLite操作的帮类.
*
* Author: egmkang.wang
* Date: 2009-06-21
*/
namespace System.Data.SQLite
{
using System.Data;
using System.Data.SQLite;
using System.IO;
public class SqliteHelper
{
private static string pwd = “PWD”;
private static string path =
Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)

SqliteUtil 功能:

/// <summary>
/// 创建数据库,前提是数据库不存在的情况下
/// </summary>
/// <returns></returns>
bool CreateDB(string sql);
/// <summary>
/// 判断数据库是否存在
/// </summary>
/// <returns></returns>
bool DBIsExist();
/// <summary>
/// 创建数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
bool SQLiteCreateTable(string sql);
/// <summary>
/// 获取数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
DataTable GetDataTable(string sql);
/// <summary>
/// 查询操作
/// </summary>
/// <param name="sql">SQL语句,例如:SELECT * FROM `table` WHERE `id` = @id </param>
/// <param name="parame">SQLiteParameter集合</param>
/// <returns></returns>
int ExecuteNonQuery(string sql, Dictionary<string, string> parame);
/// <summary>
/// 查询操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
int ExecuteNonQuery(string sql);
/// <summary>
/// 获取数据库符合要求的第一行第一个数据
/// </summary>
/// <param name="sql">SQL语句,例如:SELECT * FROM `table` WHERE `id` = @id </param>
/// <param name="parame">SQLiteParameter集合</param>
/// <returns></returns>
string ExecuteScalar(string sql, Dictionary<string, string> parame);
/// <summary>
/// 获取数据库符合要求的第一行第一个数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
string ExecuteScalar(string sql);
/// <summary>
/// 批量执行数据库语句
/// </summary>
/// <param name="sqls">数据库语句集合</param>
/// <returns></returns>
bool ExecuteByTransaction(List<string> sqls);
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="sql">UPDATE `table` SET `id` = @id </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Update(String sql, Dictionary<String, String> param);
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="data">数据字段+对应SQLiteParameter 例如 :new Dictionary<string,string>(){{"id","@id"}} </param>
/// <param name="where">判别条件 例如:`id`=@id AND `number`= @nubmer </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Update(String tableName, Dictionary<String, String> data, String where,Dictionary<String, String> param);
/// <summary>
/// 删除数据
/// </summary>
/// <param name="sql">DELETE `table` WHERE `id` = @id </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Delete(String sql, Dictionary<string, string> param);
/// <summary>
/// 删除数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="where">判别条件 例如:`id`=@id AND `number`= @nubmer </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Delete(String tableName, String where ,Dictionary<string ,string > param);
/// <summary>
/// 插入数据
/// </summary>
/// <param name="sql">INSERT INTO `table`(ID,data) VALUES(@id,@data) </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Insert(String sql, Dictionary<string, string> param);
/// <summary>
/// 插入数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="data">数据字段+对应SQLiteParameter 例如 :new Dictionary<string,string>(){{"id","@id"}} </param>
/// <param name="param">SQLiteParameter集合</param>
/// <returns></returns>
bool Insert(String tableName, Dictionary<String, String> data,Dictionary <string ,string > param);

行使教程:

 public void Start(IBundleContext context)
 {
      var sdkFactoryService = context.GetFirstOrDefaultService<SdkFactoryService>();
      ISqliteService _SqliteService = sdkFactoryService.GetSqliteService(context, token);
      _SqliteService.DeleteDBFile("test.db");  //删除数据库
      SqliteUtil sqlUtil = _SqliteService.GetSqliteUtil("test.db");  //获取数据库帮助类
}

  3. 增删改查:

复制代码 代码如下:

Insert,Delete,Update
const string s_AddressTreeIntoSQLite = “Insert into [AddressTree]
([Id],[ItemType],[ParentId],[Name]) values
(@Id,@ItemType,@ParentId,@Name);”;
SqliteHelper.ExecuteNonQuery(tran, s_AddressTreeIntoSQLite,
//new SQLiteParameter[] here
);
Select
const string s_AddresTreeFromSqlCE = “Select
[Id],[ItemType],[ParentId],[Name] From [AddressTree];”;
using (SqlCeDataReader rdr =
SqlCeHelper.ExecuteReader(s_AddresTreeFromSqlCE ))
{
while (rdr.Read())
{
//Read Data Here
}
}

  4. 其他
  SQLite性能绝对强悍.四表连接查询,查询200坏,SQL CE需要44秒(with
index),SQLite只需要3-6秒(with index).
栽,删除更新性能参见http://www.cnblogs.com/egmkang/archive/2009/06/06/1497678.html
  PS:最近意识实施sql的上,最好使用单一的增长连,而休是ConnectionString.原因好简短,嵌入式数据库没有连接池技术,
于进展数据库查询中的链接的开拓关闭费用相对来说比较大昂.这无异于接触在写程序的当儿注意为下.
  还有,有关二进制资源,需要及时放出,例如SqlCommand,这些当写Web
程序的时段体验不是挺十分,毕竟那种环境有大量
的内存,GC的效率又是较高.

相关文章

标签:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图