C# SQLite库使用
程序猿evint 人气:11、SQLite介绍
SQLite，是一款轻型的数据库，是遵守的ACID关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标嵌入式是的，而且已经在很多中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等。
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite是一个开源、免费的小型RDBMS(关系型数据库)，能独立运行、无服务器、零配置、支持事物，用C实现，内存占用较小，支持绝大数的SQL92标准。
SQLite数据库官方主页：http://www.sqlite.org/index.html
2、C#下调用SQLite数据库
在NuGet程序包内，搜索System.Data.Sqlite,安装Sqlite类库
3、在C#程序内添加SqliteHelper
sqliteHelper中主要用到2个方法：
a、ExecuteNonQuery 执行Insert，Update、Delete、创建库等操作，返回值是数据库影响的行数
b、ExecuteDataSet执行Select操作，返回查询数据集
public class SQLiteHelper
{
public static string ConnectionString = "Data Source =" + Environment.CurrentDirectory + @"\database.db" + ";Pooling = true; FailIfMissing = true";
/// <summary>
/// 执行数据库操作(新增、更新或删除)
/// </summary>
/// <param name="cmdstr">连接字符串</param>
/// <param name="cmdParms">SqlCommand对象</param>
/// <returns>受影响的行数</returns>
public int ExecuteNonQuery(string cmdstr, params SQLiteParameter[] cmdParms)
{
int result = 0;
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
SQLiteTransaction trans = null;
SQLiteCommand cmd = new SQLiteCommand(cmdstr);
PrepareCommand(cmd, conn, ref trans, true, cmd.CommandType, cmd.CommandText, cmdParms);
try
{
result = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return result;
}
/// <summary>
/// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
/// </summary>
/// <param name="cmd">Command对象</param>
/// <param name="conn">Connection对象</param>
/// <param name="trans">Transcation对象</param>
/// <param name="useTrans">是否使用事务</param>
/// <param name="cmdType">SQL字符串执行类型</param>
/// <param name="cmdText">SQL Text</param>
/// <param name="cmdParms">SQLiteParameters to use in the command</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, ref SQLiteTransaction trans, bool useTrans, CommandType cmdType, string cmdText, params SQLiteParameter[] cmdParms)
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (useTrans)
{
trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SQLiteParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
catch
{
}
}
/// <summary>
/// 数据库查询
/// </summary>
/// <param name="cmdstr">sql语句</param>
/// <param name="tableName">表名</param>
/// <returns>DataSet对象</returns>
public DataSet ExecuteDataSet(string cmdstr)
{
DataSet ds = new DataSet();
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
SQLiteTransaction trans = null;
SQLiteCommand cmd = new SQLiteCommand(cmdstr);
PrepareCommand(cmd, conn, ref trans, false, cmd.CommandType, cmd.CommandText);
try
{
SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
sda.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (cmd.Connection != null)
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
}
return ds;
}4、Sqlite部分技巧
a、SQLiteConnection类的CreateFile方法，在程序内动态创建数据库文件，通过下面的方法即可创建出Analysis.db名称的数据库
/// <summary>
/// 数据库路径
/// </summary>
private static string databasepath = AppDomain.CurrentDomain.BaseDirectory + "DataBase\\";
/// <summary>
/// 数据库名称
/// </summary>
private const string databasename = "Analysis.db";
/// <summary>
/// 创建数据库
/// </summary>
public static void CreateDataBase()
{
try
{
if (!File.Exists(databasepath + databasename))
{
if (!Directory.Exists(databasepath))
Directory.CreateDirectory(databasepath);
SQLiteConnection.CreateFile(databasepath + databasename);
LogHelper.Info("创建数据库:" + databasename + "成功！");
}
}
catch (Exception ex)
{
LogHelper.Debug(ex);
}
}b、在写入高频数据的时候，需要使用事务，如果反复进行（打开->插入>关闭）操作，sqlite效率1秒钟插入也就2条，使用程序进行插入就会发现输入的频率远低于获取到的数据，大量的数据被缓存到内存中，为了处理入库的速度慢，就要用到事务，事务流程：
①打开连接
②开始事务
③循环在内存中执行插入命令
④提交事务写入本地文件，如果出错回滚事务
⑤关闭连接
代码见下图，开始事务后通过SQLiteCommand的ExecuteNonQuery()方法进行内存提交
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
DbTransaction trans = null;
try
{
cmd.Connection = conn;
conn.Open();
//开启事务
using (trans = conn.BeginTransaction())
{
while (_list.Count > 0)
{
GpsDataClass _gps = _list[0];
try
{
if (_gps != null)
{
SQLiteHelper sh = new SQLiteHelper(cmd);
var dic = new Dictionary<string, object>();
dic["CarPlate"] = _gps.CarPlate;
dic["CarIpAddress"] = _gps.CarIpAddress;
dic["PosX1"] = _gps.PosX1;
dic["PosY1"] = _gps.PosY1;
dic["PosZ1"] = _gps.PosZ1;
dic["Heading1"] = _gps.Heading1;
dic["PosStatus1"] = _gps.PosStatus1;
dic["NumF1"] = _gps.NumF1;
dic["NumB1"] = _gps.NumB1;
dic["PosX2"] = _gps.PosX2;
dic["PosY2"] = _gps.PosY2;
dic["PosZ2"] = _gps.PosZ2;
dic["Heading2"] = _gps.Heading2;
dic["PosStatus2"] = _gps.PosStatus2;
dic["NumF2"] = _gps.NumF2;
dic["NumB2"] = _gps.NumB2;
dic["Speed"] = _gps.Speed;
dic["Signal"] = _gps.Signal;
dic["NowTime"] = _gps.NowTime;
sh.Insert("GpsRecord", dic);
_list.RemoveAt(0);
}
}
catch (Exception ex)
{
LogHelper.Debug(ex);
}
}
trans.Commit();
}
}
catch (Exception ex)
{
trans.Rollback();
LogHelper.Debug(ex);
}
conn.Close();
}
}加载全部内容