public class SQLiteDB
{
private static readonly string sqliteConn = @"Data Source = {0}";//定义链接字符串
/// <summary>
/// 判断数据库是否存在
/// </summary>
/// <param name="dbFilePath">数据库文件路径</param>
/// <returns></returns>
/// <exception cref="AggregateException"></exception>
public static bool IsDatabaseExist(string dbFilePath)
{
if (string.IsNullOrEmpty(dbFilePath))
{
throw new AggregateException(nameof(dbFilePath));
}
if (!File.Exists(dbFilePath))
{
return false;
}
using (var connection = new SQLiteConnection(string.Format(sqliteConn, dbFilePath)))
{
try
{
connection.Open();
return true;
}
catch (Exception)
{
return false;
}
}
}
/// <summary>
/// 查询表中数据
/// </summary>
/// <param name="dbFilePath">数据库文件路径</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
/// <exception cref="AggregateException"></exception>
public static DataTable ExecutQuery(string dbFilePath, string sql)
{
if (string.IsNullOrEmpty(dbFilePath))
{
throw new AggregateException(nameof(dbFilePath));
}
if (string.IsNullOrEmpty(sql))
{
throw new AggregateException(nameof(sql));
}
using (var connection = new SQLiteConnection(string.Format(sqliteConn, dbFilePath)))//链接数据库
using (var command = new SQLiteCommand(sql, connection))//用sql语句进行查询
using (var adapter = new SQLiteDataAdapter(command))//返回查询结果
{
try
{
connection.Open();
var dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
finally
{
connection.Clone();
}
}
}
/// <summary>
/// 增删改操作
/// </summary>
/// <param name="dbFilePath"></param>
/// <param name="sql"></param>
/// <returns></returns>
/// <exception cref="AggregateException"></exception>
public static int ExecutNonQuery(string dbFilePath, string sql)
{
if (string.IsNullOrEmpty(dbFilePath))
{
throw new AggregateException(nameof(dbFilePath));
}
if (string.IsNullOrEmpty(sql))
{
throw new AggregateException(nameof(sql));
}
using (var connection = new SQLiteConnection(string.Format(sqliteConn, dbFilePath)))
using (var command = new SQLiteCommand(sql, connection))
{
try
{
connection.Open();
return command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
/// <summary>
/// 判断数据库中是否存在该表
/// </summary>
/// <param name="dbFilePath"></param>
/// <param name="tableName"></param>
/// <param name="tableDefinition"></param>
/// <exception cref="AggregateException"></exception>
public static void EnsureTableExist(string dbFilePath, string tableName, string tableDefinition)
{
if (!IsDatabaseExist(dbFilePath))
{
return;
}
if (string.IsNullOrEmpty(tableName))
{
throw new AggregateException(nameof(tableName));
}
if (string.IsNullOrEmpty(tableDefinition))
{
throw new AggregateException(nameof(tableDefinition));
}
try
{
var sql = $"select count(*) from sqlite_master where type='table' and name ='{tableName}' ";
int tableNum = (int)Convert.ToInt64(ExecutQuery(dbFilePath, sql).Rows[0][0]);
if (tableNum > 0)
{
return;
}
}
catch (Exception ex)
{
var createTableSql = $"create table if not exists {tableName}({tableDefinition})";
ExecutNonQuery(dbFilePath, createTableSql);
}
}
}
SQLite连接数据库帮助类
最新推荐文章于 2024-09-22 11:17:00 发布
该代码段定义了一个名为SQLiteDB的公共类,用于处理SQLite数据库的相关操作。包括判断数据库是否存在、执行查询、非查询SQL命令,以及确保表存在的功能。方法使用了静态和封装的概念,通过连接字符串与数据库交互,并处理异常。
摘要由CSDN通过智能技术生成