一、实现效果
??1.1、实现功能
? ①实现创建Sqlite数据库;
? ②实现创建指定Sqlite数据库的表;
? ③实现给Sqlite数据库的指定表【插入、查询、更新、删除、分页查询】数据;
? ④实现获取Sqlite数据库中的所有表名称及其表包含的所有列名称;
? ⑤实现判断数据是否存在、获取最大Id；
? ⑥实现内存版的sqlite数据库；
1.2、效果图
二、实现核心
2.1、项目工程
该项目的完整工程下载地址如下：
使用C#开发的Sqlite数据库创建、操作的源码项目工程
https://download.csdn.net/download/xiaochenXIHUA/85207095
2.2、添加引用
? ? 正常情况下，如果不需要给Sqlite数据库加密，则直接添加【Microsoft.Data.Sqlite】的包引用即可；但是如果是需要给Sqlite数据库加密，则需要移除【Microsoft.Data.Sqlite】包，然后在项目中添加【Microsoft.Data.Sqlite.Core】和【SQLitePCLRaw.bundle_e_sqlcipher】引用，如下图所示。
2.3、Sqlite数据库帮助类
这里Sqlite数据库帮助类的部分内容，如下所示：
/***
* Title："轻量数据库" 项目
* 主题：：Sqlite数据库帮助类【使用 Microsoft.Data.Sqlite 】
* Description：
* 功能：XXX
* Date：2022
* Version：0.1版本
* Author：Coffee
* Modify Recoder：
*/
using LiteDBHelper.Model;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
namespace LiteDBHelper
{
public class SqliteDBHelper
{
#region 基础参数
//数据库连接字符串
private SqliteConnectionStringBuilder _ConnStr;
//获取到数据库连接字符串
public string ConnStr { get { return _ConnStr.ConnectionString; } }
#endregion
#region 构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connnection">连接字符串</param>
public SqliteDBHelper(string connnection)
{
if (string.IsNullOrEmpty(connnection)) return;
_ConnStr.ConnectionString = connnection;
}
/// <summary>
/// 构造函数（基础不加密连接）
/// </summary>
/// <param name="sqliteFilePathAndName">sqlite数据库文件的路径和名称（比如：@"D:\\HalmEL\\2022-4-19.db"）</param>
/// <param name="sqliteOpenMode">sqlite数据库的打开模式</param>
/// <param name="sqliteCacheMode">sqlite的缓存模式</param>
/// <param name="defaultTimeout">默认超时时间（单位是：秒）</param>
/// <param name="isUseSharePool">是否使用共享连接池（默认true表示使用）</param>
public SqliteDBHelper(string sqliteFilePathAndName,SqliteOpenMode sqliteOpenMode, SqliteCacheMode sqliteCacheMode,
int defaultTimeout=30, bool isUseSharePool=true)
{
var connectionString = new SqliteConnectionStringBuilder()
{
DataSource=sqliteFilePathAndName,
Mode=sqliteOpenMode,
Cache=sqliteCacheMode,
//DefaultTimeout=defaultTimeout,
//Pooling= isUseSharePool
};
_ConnStr = connectionString;
}
/// <summary>
/// 构造函数（带加密连接）
/// </summary>
/// <param name="sqliteFilePathAndName">sqlite数据库文件的路径和名称（比如：@"D:\\HalmEL\\2022-4-19.db"）</param>
/// <param name="password">数据库密码</param>
/// <param name="sqliteOpenMode">sqlite数据库的打开模式</param>
/// <param name="sqliteCacheMode">sqlite的缓存模式</param>
/// <param name="defaultTimeout">默认超时时间（单位是：秒）</param>
/// <param name="isUseSharePool">是否使用共享连接池（默认true表示使用）</param>
public SqliteDBHelper(string sqliteFilePathAndName, string password, SqliteOpenMode sqliteOpenMode, SqliteCacheMode sqliteCacheMode,
int defaultTimeout = 30, bool isUseSharePool = true)
{
var connectionString = new SqliteConnectionStringBuilder()
{
DataSource = sqliteFilePathAndName,
Password = password,
Mode = sqliteOpenMode,
Cache = sqliteCacheMode,
//DefaultTimeout = defaultTimeout,
//Pooling = isUseSharePool
};
_ConnStr = connectionString;
}
#endregion
#region 创建Sqlite数据库、表及其字段
/// <summary>
/// 创建sqlite数据库
/// </summary>
///<param name = "sqliteFilePathAndName" > sqlite数据库文件的路径和名称（比如：@"D:\\HalmEL\\2022-4-19.db"）</param>
/// <returns></returns>
public ResultInfo CreateSqliteDataBase(string sqliteFilePathAndName)
{
ResultInfo resultInfo = new ResultInfo();
if (File.Exists(sqliteFilePathAndName))
{
resultInfo.ResultStatus = ResultStatus.Success;
resultInfo.Message = $"{sqliteFilePathAndName} 文件已经存在！";
return resultInfo;
}
try
{
//如果目录不存在，则创建目录
string folder = Path.GetDirectoryName(sqliteFilePathAndName);
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
if (!File.Exists(sqliteFilePathAndName))
{
using (SqliteConnection sqliteConnection = new SqliteConnection(_ConnStr.ConnectionString))
{
sqliteConnection.Open();
};
}
resultInfo.ResultStatus = ResultStatus.Success;
resultInfo.Message = $"{sqliteFilePathAndName} 文件创建成功！";
}
catch (Exception ex)
{
resultInfo.ResultStatus = ResultStatus.Error;
resultInfo.Message = $"{ex.Message}";
}
return resultInfo;
}
/// <summary>
/// 修改数据库密码
/// </summary>
/// <param name = "sqliteFilePathAndName" > sqlite数据库文件的路径和名称（比如：@"D:\\HalmEL\\2022-4-19.db"）</param>
/// <param name="oldPassword">旧密码</param>
/// <param name="newPassword">新密码</param>
/// <returns></returns>
public ResultInfo ModifySqliteDBPassword(string sqliteFilePathAndName, string oldPassword, string newPassword)
{
ResultInfo resultInfo = new ResultInfo();
if (string.IsNullOrEmpty(sqliteFilePathAndName) || string.IsNullOrEmpty(oldPassword) ||
string.IsNullOrEmpty(newPassword))
{
resultInfo.SetContent(ResultStatus.Error, $"内容为空", null);
return resultInfo;
}
string baseConnectionString = $"Data Source={sqliteFilePathAndName}";
var connectionString = new SqliteConnectionStringBuilder(baseConnectionString)
{
Mode = SqliteOpenMode.ReadWriteCreate,
Password = oldPassword
};
try
{
using (var connection = new SqliteConnection(connectionString.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT quote($newPassword);";
command.Parameters.AddWithValue("$newPassword", newPassword);
var quotedNewPassword = (string)command.ExecuteScalar();
command.CommandText = "PRAGMA rekey = " + quotedNewPassword;
command.Parameters.Clear();
command.ExecuteNonQuery();
resultInfo.SetContent(ResultStatus.Success, $"修改密码成功", null);
};
}
//重置当前连接密码为新密码
_ConnStr.Password = newPassword;
}
catch (Exception ex)
{
resultInfo.SetContent(ResultStatus.Error, $"{ex.Message}", null);
}
return resultInfo;
}
/// <summary>
/// 创建sqlite表（可自定义字段类型）
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="fieldList">字段列表</param>
/// <returns></returns>
public ResultInfo CreateSqliteTable(string tableName, List<SqliteFieldInfo> fieldList)
{
ResultInfo resultInfo = new ResultInfo();
if (string.IsNullOrEmpty(tableName) || fieldList == null || fieldList.Count < 1)
{
resultInfo.SetContent(ResultStatus.Error, "内容为空,请检查！", null);
return resultInfo;
}
SqliteConnection sqliteConnection = new SqliteConnection(_ConnStr.ConnectionString);
try
{
//创建表的命令
string cmdText1 = $"CREATE TABLE IF NOT EXISTS {tableName} ( \n";
string cmdText2 = string.Empty;
//给表添加字段
if (fieldList != null && fieldList.Count >= 1)
{
int fieldCount = fieldList.Count;
SqliteFieldInfo fieldInfo = new SqliteFieldInfo();
for (int i = 0; i < fieldCount; i++)
{
fieldInfo = fieldList[i];
if (i<fieldCount-1)
{
cmdText2 += AddField(fieldInfo)+",\n";
}
if (i==fieldCount-1)
{
cmdText2 += AddField(fieldInfo)+"\n";
}
}
cmdText2 += $");";
}
if (sqliteConnection.State!=ConnectionState.Open)
{
sqliteConnection.Open();
using (SqliteCommand cmd = new SqliteCommand())
{
cmd.Connection = sqliteConnection;
cmd.CommandText = cmdText1 + cmdText2;
cmd.ExecuteNonQuery();
}
}
resultInfo.SetContent(ResultStatus.Success, $"创建：{tableName} 表成功", null);
}
catch (Exception ex)
{
resultInfo.SetContent(ResultStatus.Error, $"{ex.Message}", null);
}
finally
{
//关闭连接
sqliteConnection.Close();
}
return resultInfo;
}
#endregion
#region 私有方法
/// <summary>
/// 添加字段
/// </summary>
/// <param name="fieldInfo">字段信息</param>
/// <returns>返回添加字段结果</returns>
private string AddField(SqliteFieldInfo fieldInfo)
{
string str = null;
if (fieldInfo == null) return null;
//设置指定类型的字段到表中
str = $" {fieldInfo.Name}";
if (fieldInfo.Length > 0)
{
str += $" {fieldInfo.DataType}({fieldInfo.Length})";
}
else
{
str+= $" {fieldInfo.DataType}";
}
if (fieldInfo.IsNotEmpty)
{
str += $" NOT NULL";
}
if (fieldInfo.IsPrimaryKey)
{
str += $" PRIMARY KEY";
}
if (fieldInfo.IsAutoIncrement)
{
str += $" AUTOINCREMENT";
}
return str;
}
#endregion
}//Class_end
}
