Sqlite 操作类代码

发布时间:2018-11-15  栏目:sqlite  评论:0 Comments

  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);
  • “\\sqliteTest.db”;
    private static string connString = string.Format(“Data Source
    =\”{0}\””, path, pwd);
    /**//// <summary>
    /// 返回数据库链接字符串
    /// </summary>
    public static string ConnString
    {
    get { return connString; }
    }
    /**//// <summary>
    /// 执行SQL语句,返回给影响之行数
    /// </summary>
    /// <param name=”cmdText”>需要让实践之SQL语句</param>
    /// <returns>受影响之行数</returns>
    public static int ExecuteNonQuery(string cmdText)
    {
    return ExecuteNonQuery(ConnString, cmdText);
    }
    /**//// <summary>
    /// 执行带有事务的SQL语句
    /// </summary>
    /// <param name=”trans”>事务</param>
    /// <param name=”cmdText”>SQL语句</param>
    /// <returns>受影响的行数</returns>
    public static int ExecuteNonQuery(SQLiteTransaction trans, string
    cmdText, params SQLiteParameter[] parameters)
    {
    int val = 0;
    using (SQLiteCommand cmd = new SQLiteCommand())
    {
    PrepareCommand(cmd, (SQLiteConnection)trans.Connection, trans, cmdText,
    parameters);
    val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    }
    return val;
    }
    /**//// <summary>
    /// 执行SQL语句,返回给影响的行数
    /// </summary>
    /// <param name=”connString”>连接字符串</param>
    /// <param name=”cmdText”>SQL语句</param>
    /// <param name=”parameters”>SQL的参数</param>
    /// <returns>受影响之行数</returns>
    public static int ExecuteNonQuery(string connString, string cmdText,
    params SQLiteParameter[] parameters)
    {
    using (SQLiteConnection conn = new SQLiteConnection(connString))
    {
    return ExecuteNonQuery(conn, cmdText, parameters);
    }
    }
    /**//// <summary>
    /// 执行SQL语句,返回给影响之行数
    /// </summary>
    /// <param name=”connection”>数据库链接</param>
    /// <param name=”cmdText”>SQL语句</param>
    /// <param name=”parameters”>参数</param>
    /// <returns>受影响之行数</returns>
    public static int ExecuteNonQuery(SQLiteConnection connection, string
    cmdText, params SQLiteParameter[] parameters)
    {
    int val = 0;
    using (SQLiteCommand cmd = new SQLiteCommand())
    {
    PrepareCommand(cmd, connection, null, cmdText, parameters);
    val = cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    }
    return val;
    }
    /**//// <summary>
    /// 执行查询,并返结果集的首先履之率先列.其他具有的行和列被忽略.
    /// </summary>
    /// <param name=”cmdText”>SQL 语句</param>
    /// <returns>第一行之首先排列的值</returns>
    public static object ExecuteScalar(string cmdText)
    {
    return ExecuteScalar(ConnString, cmdText);
    }
    /**//// <summary>
    /// 执行查询,并返结果集的首先履行的首先列.其他具备的行和列被忽略.
    /// </summary>
    /// <param name=”connString”>连接字符串</param>
    /// <param name=”cmdText”>SQL 语句</param>
    /// <returns>第一履的第一列的值</returns>
    public static object ExecuteScalar(string connString, string cmdText)
    {
    using (SQLiteConnection conn = new SQLiteConnection(connString))
    {
    return ExecuteScalar(conn, cmdText);
    }
    }
    /**//// <summary>
    /// 执行查询,并回到结果集的第一实行的第一列.其他有的行和列被忽略.
    /// </summary>
    /// <param name=”connection”>数据库链接</param>
    /// <param name=”cmdText”>SQL 语句</param>
    /// <returns>第一实施的首先排列的值</returns>
    public static object ExecuteScalar(SQLiteConnection connection, string
    cmdText)
    {
    object val;
    using (SQLiteCommand cmd = new SQLiteCommand())
    {
    PrepareCommand(cmd, connection, null, cmdText);
    val = cmd.ExecuteScalar();
    }
    return val;
    }
    /**//// <summary>
    /// 执行SQL语句,返回结果集的DataReader
    /// </summary>
    /// <param name=”cmdText”>SQL语句</param>
    /// <param name=”parameters”>参数</param>
    /// <returns>结果集的DataReader</returns>
    public static SQLiteDataReader ExecuteReader(string cmdText, params
    SQLiteParameter[] parameters)
    {
    return ExecuteReader(ConnString, cmdText, parameters);
    }
    /**//// <summary>
    /// 执行SQL语句,返回结果集的DataReader
    /// </summary>
    /// <param name=”connString”>连接字符串</param>
    /// <param name=”cmdText”>SQL语句</param>
    /// <param name=”parameters”>参数</param>
    /// <returns>结果集的DataReader</returns>
    public static SQLiteDataReader ExecuteReader(string connString, string
    cmdText, params SQLiteParameter[] parameters)
    {
    SQLiteConnection conn = new SQLiteConnection(connString);
    SQLiteCommand cmd = new SQLiteCommand();
    try
    {
    PrepareCommand(cmd, conn, null, cmdText, parameters);
    SQLiteDataReader rdr =
    cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return rdr;
    }
    catch
    {
    conn.Close();
    throw;
    }
    }
    /**//// <summary>
    /// 预处理Command对象,数据库链接,事务,需要履行的对象,参数等之初始化
    /// </summary>
    /// <param name=”cmd”>Command对象</param>
    /// <param name=”conn”>Connection对象</param>
    /// <param name=”trans”>Transcation对象</param>
    /// <param name=”cmdText”>SQL Text</param>
    /// <param name=”parameters”>参数实例</param>
    private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection
    conn, SQLiteTransaction trans, string cmdText, params
    SQLiteParameter[] parameters)
    {
    if (conn.State != ConnectionState.Open)
    conn.Open();
    cmd.Connection = conn;
    cmd.CommandText = cmdText;
    if (trans != null)
    cmd.Transaction = trans;
    if (null != parameters && parameters.Length > 0)
    {
    cmd.Parameters.AddRange(parameters);
    }
    }
    }
    }

采用教程:

 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地图