同系列文章：
/* ----------------------------------------------------------
* 文件名称：IssueSQLiteHandler.cs
*
* 作者：秦建辉
*
* QQ：36748897
*
* 博客：http://www.firstsolver.com/wordpress/
*
* 开发环境：
* Visual Studio V2015
* .NET Framework 4 Client Profile
*
* 版本历史：
* V1.0 2016年05月25日
* 实现与SQLite数据库交互
* ---------------------------------------------------------- */
using System;
using System.Data.SQLite;
namespace Splash
{
public class IssueSQLiteHandler : IDisposable
{
/// <summary>
/// 数据库文件名
/// </summary>
public readonly string DataSource;
/// <summary>
/// 连接字符串
/// </summary>
private string ConnectionString;
/// <summary>
/// 是否已经释放资源
/// </summary>
private bool disposed = false;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="path">数据库文件名</param>
public IssueSQLiteHandler(string path)
{
DataSource = path;
// 生成数据库连接字符串
ConnectionString = new SQLiteConnectionStringBuilder()
{
DataSource = path,
}.ToString();
// 如果文件不存在，创建数据库文件
if (!System.IO.File.Exists(path))
{
SQLiteConnection.CreateFile(path);
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
// 创建源设备信息表
cmd.CommandText = "CREATE TABLE IF NOT EXISTS SourceDevices(Address TEXT PRIMARY KEY, SecretKey TEXT)";
cmd.ExecuteNonQuery();
// 创建目标设备信息表
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TargetDevices(Address TEXT PRIMARY KEY, SecretKey TEXT)";
cmd.ExecuteNonQuery();
}
}
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{ // 释放托管资源
}
// 释放非托管资源
disposed = true;
}
}
/// <summary>
/// 读取源设备信息
/// </summary>
/// <returns>
/// 源设备集合
/// </returns>
public System.Collections.IEnumerable SourceDevices
{
get
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
cmd.CommandText = "SELECT * FROM SourceDevices";
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
yield return new DeviceInformation(dr.GetString(0), dr.GetString(1));
}
yield break;
}
}
}
}
set
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{ // 清空表
cmd.CommandText = "DELETE FROM SourceDevices";
cmd.ExecuteNonQuery();
// 保存信息
foreach (DeviceInformation item in value)
{
cmd.CommandText = string.Format("INSERT OR REPLACE INTO SourceDevices(Address, SecretKey) VALUES('{0}', '{1}')", item.Address, item.SecretKey);
cmd.ExecuteNonQuery();
}
}
}
}
}
/// <summary>
/// 读取目标设备信息
/// </summary>
/// <returns>
/// 目标设备集合
/// </returns>
public System.Collections.IEnumerable TargetDevices
{
get
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
cmd.CommandText = "SELECT * FROM TargetDevices";
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
yield return new DeviceInformation(dr.GetString(0), dr.GetString(1));
}
yield break;
}
}
}
}
set
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{ // 清空表
cmd.CommandText = "DELETE FROM TargetDevices";
cmd.ExecuteNonQuery();
// 保存信息
foreach (DeviceInformation item in value)
{
cmd.CommandText = string.Format("INSERT OR REPLACE INTO TargetDevices(Address, SecretKey) VALUES('{0}', '{1}')", item.Address, item.SecretKey);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}