Sqlite作为一种轻量级的嵌入式数据,常常在C/S项目中扮演重要的角色,而且它具有免费,跨平台,占用空间小等特点。
下面就来说说C#如何快速地搭建Sqlite的环境吧,首先我的电脑是Win10操作系统,VS2017,.NET 4.0(要兼容XP系统)
1.打开https://www.sqlite.org/download.html下载这两个文件
解压第一个文件会得到sqlite的dll文件,解压第二个会得到sqldiff.exe(显示SQLite数据库之间的差异),sqlite3.exe(命令行工具),sqlite3_analyzer.exe(显示使用sqlite数据库文件的单个表和索引使用的空间大小和效率),然后把这几个文件复制到同一个文件夹下,再把这个文件夹设置为环境变量。再打开命令提示符,输入sqlite3显示版本号就说明设置的是正确的。
2.但是命令行工具开发起来效率不高,怎么办呢?当然是在借助一下数据库管理工具啦,我选择的是SQLiteStudio这个工具,用起来也蛮方便的,具体使用方法它的下载页面都有。
3.接着我们就可以打开VS新建一个项目,然后打开它的Nuget,搜索Sqlite的ADO.NET包并安装它
为了方便起见我又下载了dapper这个ORM工具,借助以前写过的帮助类,就能查询,修改数据库了。
帮助类如下:
DapperManage连接类:
using System.Configuration;
using System.Data;
using System.Data.SQLite;
namespace XCYN.Common.Dapper
{
public class DapperManager
{
public static string connectionString = ConfigurationManager.ConnectionStrings["XXX"].ConnectionString;
private static IDbConnection _instance = null;
private static object _lock = new object();
private DapperManager()
{
}
/// <summary>
/// 连接实例
/// </summary>
/// <returns></returns>
public static IDbConnection GetConnection()
{
if (_instance == null)
{
lock (_lock)
{
if (_instance == null)
{
//连接SQLServer数据库
//_instance = new SqlConnection(connectionString);
_instance = new SQLiteConnection(connectionString);
}
}
}
return _instance;
}
}
}
DapperHelper执行Sqlite命令:
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
namespace XCYN.Common.Dapper
{
/// <summary>
/// 增删改查命令
/// </summary>
public static class DapperHelper
{
/// <summary>
/// 添加操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static int Execute(string sql,object param = null,IDbTransaction transaction = null)
{
int count = 0;
IDbConnection conn = null;
try
{
conn = Dapper.DapperManager.GetConnection();
conn.Open();
count = conn.Execute(sql, param, transaction);
return count;
}
catch(Exception ex)
{
//封装异常处理
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static List<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null)
{
IDbConnection conn = null;
try
{
conn = Dapper.DapperManager.GetConnection();
conn.Open();
var list = conn.Query<T>(sql, param, transaction);
return list as List<T>;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 单值查询
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public static int ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null)
{
IDbConnection conn = null;
try
{
conn = Dapper.DapperManager.GetConnection();
var obj = conn.ExecuteScalar<int>(sql, param, transaction, 3, CommandType.Text);
return obj;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
}
最后新建一个单元测试项目测试一下能否查询和修改,别忘了在单元测试项目中添加App.Config文件并加入连接字符串:
App.Config:
<connectionStrings>
<add name="XXX" connectionString="Data Source=XXX.db;Version=3;" />
</connectionStrings>