更新一下,之前失误把前面一部分删掉了。
打开项目
VS菜单栏 -> 工具 -> 管理Nuget程序包 -> 选择浏览 -> 输入 System.Data.SQLite 搜索 -> 右侧点击安装。
封装可用的类
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Sql
{
/// <summary>
/// 数据库操作异常事件委托
/// </summary>
/// <param name="msg">错误信息</param>
public delegate void SqlExecuteQueryErrEventeHandle(string msg);
/// <summary>
/// SQLite数据库操作类
/// </summary>
public class SQLiteTools
{
/* SQLite数据库说明:
* 1、不支持删除字段操作;
* 2、不支持多线程同时操作;
*
*/
/// <summary>
/// 数据库操作对象
/// </summary>
SQLiteConnection conn;
/// <summary>
/// 数据库操作锁
/// </summary>
private static readonly object SequenceLock = new object();
/// <summary>
/// 数据库执行错误
/// </summary>
public event SqlExecuteQueryErrEventeHandle SqlExecuteNonQueryErrEvente;
#region 创建数据库连接和数据表
/// <summary>
/// 创建数据库连接,数据库不存在则创建
/// </summary>
/// <param name="DB_name">数据库路径名称</param>
/// <returns>null</returns>
public void CreateConnection(string DB_name)
{
string fileName = "Data Source = " + DB_name;
conn = new SQLiteConnection(fileName);
conn.Open();
}
/// <summary>
/// 判断指定数据表是否存在
/// </summary>
/// <param name="tbname">表格名称</param>
/// <returns>在返回True,否则返回False</returns>
public bool SelectTableExist(string tbname)
{
// select * from sqlite_master where type='table' and name='表格名称';
string sql = "select * from sqlite_master where type='table' and name = '" + tbname + "';";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
return true;
else
return false;
}
/// <summary>
/// 创建通用数据表,默认第一列为主键,名称:ID,类型:INTEGER, 自增
/// </summary>
/// <param name="tbname">数据表名称</param>
/// <returns>null</returns>
public void CreateSQLtable(string tbname)
{
// CREATE TABLE if not exists 表名 (ID INTEGER PRIMARY KEY AUTOINCREMENT);
string sql = "CREATE TABLE if not exists '" + tbname + "' (ID INTEGER PRIMARY KEY AUTOINCREMENT);";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
#endregion
#region 添加/修改表格数据
/// <summary>
/// 指定数据表添加列
/// </summary>
/// <param name="tbname">表名</param>
/// <param name="columnName">列名</param>
/// <param name="genre">添加列类型</param>
/// <returns>null</returns>
public void AddSQLtableColumn(string tbname, string columnName, string genre)
{
//# ALTER TABLE 表名 ADD 列名 列类型;
if (!SelectTableExist(tbname))
return; //指定表格名不存在则直接退出
List<string> ColumnNameList = GetSQLtableColumnName(tbname);
if (ColumnNameList.Contains(columnName))
return; //指定列名存在则直接退出
string sql = "ALTER TABLE " + tbname + " ADD " + columnName + " " + genre + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 指定数据表添加指定行数
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="rowNum">行数</param>
/// <returns>null</returns>
public void AddSQLtableRowNum(string tbname, int rowNum)
{
//# INSERT INTO 表名 (ID) VALUES (行);
if (!SelectTableExist(tbname) || rowNum < 1)
return; //指定表格名不存在或行数小于1则直接退出
int startRow = GetSQLtableRowNum(tbname);
for (int i = startRow; i < startRow + rowNum; i++)
{
string sql = "INSERT INTO " + tbname + " (ID) VALUES (" + i.ToString() + ");";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
}
/// <summary>
/// 指定数据表添加数据
/// </summary>
/// <param name="tbname">表名</param>
/// <param name="columnName">列名称列表(不包括ID列)</param>
/// <param name="rowVlue">行值列表(不包括ID行)</param>
public void AddSQLtaleRow(string tbname, string[] columnName, string[] rowVlue)
{
string sql = $"INSERT INTO {tbname} ({string.Join(",", columnName)}) VALUES ('{string.Join(",", rowVlue)}');";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 更新数据表指定位置的值
/// </summary>
/// <param name="tbname">数据表名称</param>
/// <param name="column">行数</param>
/// <param name="row">列数</param>
/// <param name="value">值</param>
/// <returns>null</returns>
public void SetSQLtableValue(string tbname, string column, int row, string value)
{
// UPDATE 表名 SET 列名=值 WHERE ID=行;
string sql = "UPDATE " + tbname + " SET " + column + "='" + value + "' WHERE ID=" + row.ToString() + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 已知某一列的值设置另一列对应的值
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="column0">设置列名称</param>
/// <param name="value0">设置列值</param>
/// <param name="column1">已知列名称</param>
/// <param name="value1">已知列值</param>
public void SetSQLtableValue(string tbname, string column0, string value0, string column1, string value1)
{
// UPDATE 表名 SET 列名=值 WHERE 列名=行;
string sql = $"UPDATE {tbname} SET {column0}='{value0}' WHERE {column1}='{value1}'";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 更新数据表指定行的所有值(不包含ID列)
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="columnName">表格列名称列表</param>
/// <param name="rowValue">行数据列表</param>
/// <param name="row">行号</param>
public void SetSQLtableRowValue(string tbname, string[] columnName, string[] rowValue, int row)
{
string keyVlue = columnName[0] + "='" + rowValue[0] + "'";
for (int i = 1; i < columnName.Length; i++)
{
keyVlue += ",";
keyVlue += columnName[i] + "='" + rowValue[i] + "'";
}
// UPDATE 表名 SET 列名=值 WHERE ID=行;
//string sql = "UPDATE " + tbname + " SET " + keyVlue + " WHERE ID=" + row.ToString() + ";";
string sql = $"UPDATE {tbname} SET {keyVlue} WHERE ID={row};";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 设置数据表指定列的所有值,(不能为ID列)
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="columnName">列名称</param>
/// <param name="Values">值列表</param>
public void SetSQLtableColumnValue(string tbname, string columnName, string[] Values)
{
}
#endregion
#region 获取表格数据
/// <summary>
/// 获取连接数据库的所有表格名称
/// </summary>
/// <returns>当前连接数据库内的所有表格名称</returns>
public List<string> GetSQLtableName()
{
List<string> tableName = new List<string> { };
string sql = "select name from sqlite_master where type='table' order by name;";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string name = reader[0].ToString();
tableName.Add(name);
}
return tableName;
}
/// <summary>
/// 获取指定表格总行数
/// </summary>
/// <param name="tbname">表格名称</param>
/// <returns>行数</returns>
public int GetSQLtableRowNum(string tbname)
{
string sql = "SELECT COUNT(*) FROM " + tbname + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
reader.Read();
return Convert.ToInt32(reader[0]);
}
/// <summary>
/// 获取指定表所有字段名称
/// </summary>
/// <param name="tbname">表格名称</param>
/// <returns>列名称列表</returns>
public List<string> GetSQLtableColumnName(string tbname)
{
List<string> columnNameList = new List<string> { };
string sql = @"PRAGMA table_info([" + tbname + @"])";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string columnName = reader[1].ToString();
if (columnName != "ID")
columnNameList.Add(columnName);
}
return columnNameList;
}
/// <summary>
/// 读取指定数据表的指定位置的数据
/// </summary>
/// <param name="tbname">数据表名称</param>
/// <param name="column">数据表列</param>
/// <param name="row">数据表行</param>
/// <returns>值</returns>
public string GetSQLtableValue(string tbname, string column, int row)
{
//#SELECT 列名 FROM 表名 WHERE ID = 行号;
string sql = "SELECT " + column + " FROM " + tbname + " WHERE ID=" + row.ToString() + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
reader.Read();
return reader[0].ToString();
}
/// <summary>
/// 读取数据表指定列的所有数据
/// </summary>
/// <param name="tbname">数据表名称</param>
/// <param name="column">列名称</param>
/// <returns>值列表</returns>
public List<string> GetSQLtableColumn(string tbname, string column)
{
//#SELECT 列名 FROM 表名;
List<string> columnValueList = new List<string> { };
// int rowNum = this.GetSQLtableRowNum(tbname);
string sql = "SELECT " + column + " FROM " + tbname + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
//reader.Read();
while (reader.Read())
{
string columnName = reader[0].ToString();
columnValueList.Add(columnName);
}
return columnValueList;
}
/// <summary>
/// 获取指定表格指定行所有数据
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="row">行数</param>
/// <returns>值列表(不包含ID)</returns>
public List<string> GetSQLtableRow(string tbname, int row)
{
//# SELECT * FROM 表名 WHERE ID=行数;
List<string> RowValueList = new List<string>();
int columnNum = GetSQLtableColumnName(tbname).Count;
string sql = "SELECT * FROM " + tbname + " WHERE ID=" + row.ToString() + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
//reader.Read();
while (reader.Read())
{
for (int i = 1; i <= columnNum; i++)
{
var result = reader[i].ToString();
RowValueList.Add(result);
}
}
return RowValueList;
}
/// <summary>
/// 已知某一列的值搜索另一列对应的值
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="column0">搜索值列名</param>
/// <param name="column1">已知值列名称</param>
/// <param name="value">已知值</param>
/// <returns></returns>
public List<string> GetSQLtableRow(string tbname, string column0, string column1, string value)
{
string result = "";
List<string> valueList = new List<string>();
string sql = $"SELECT {column0} FROM {tbname} WHERE {column1}='{value}';";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
result = reader[0].ToString();
valueList.Add(result);
}
return valueList;
}
/// <summary>
/// 获取表格某一列某个元素出现的次数
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="column">列名称</param>
/// <param name="value">值</param>
/// <returns></returns>
public int GetSQLtableData(string tbname, string column, string value)
{
int num = 0;
List<string> coValue = new List<string> { };
coValue = this.GetSQLtableColumn(tbname, column);
foreach (string str in coValue)
{
if (str == value)
{
num += 1;
}
}
return num;
}
/// <summary>
/// 读取指定表格的所有数据
/// </summary>
/// <param name="tbname"></param>
/// <returns></returns>
public List<List<string>> GetAllData(string tbname)
{
List<List<string>> resule = new List<List<string>>();
int columnNum = GetSQLtableColumnName(tbname).Count;
string sql = $"SELECT * FROM {tbname};";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
SQLiteDataReader reader = command.ExecuteReader();
//reader.Read();
while (reader.Read())
{
List<string> RowValueList = new List<string>();
for (int i = 1; i <= columnNum; i++)
{
var result = reader[i].ToString();
RowValueList.Add(result);
}
resule.Add(RowValueList);
}
return resule;
}
#endregion
#region 操作数据表
/// <summary>
/// 指定数据表删除指定列
/// SQLite不支持删除列操作,采用复制新表的方式
/// </summary>
/// <param name="tbname">表名</param>
/// <param name="columnName">列名</param>
/// <returns>null</returns>
public void DelSQLtableColumn(string tbname, string columnName)
{
//# ALTER TABLE 表名 drop 列名; #SQLite不支持
string tbName = tbname + "_0";
while (!SelectTableExist(tbName)) //防止零时表格名称已存在
{
tbName = tbName + "_0";
}
List<string> ColumnNameList = GetSQLtableColumnName(tbname);
if (ColumnNameList.Contains(columnName))
ColumnNameList.Remove(columnName);
else
return; //指定列名不存在则直接退出
ColumnNameList.Insert(0, "ID"); //获取到的列名称不包含ID
string strName = string.Join(",", ColumnNameList.ToArray());
//# create table 零时表名 as select 所有列名称 from 表名 where 1 = 1;
string sql = "create table " + tbName + " as select " + strName + " from " + tbname + " where 1 = 1";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
DelSQLtable(tbname);
RemoveSQLtableName(tbName, tbname);
}
/// <summary>
/// 指定数据表删除指定行数
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="startRow">开始行数</param>
/// <param name="rowNum">要删除总行数</param>
/// <returns>null</returns>
public void DelSQLtableRow(string tbname, int startRow, int rowNum = 1)
{
//# DELETE FROM 表名 WHERE ID = 行;
if (!this.SelectTableExist(tbname))
return;
for (int row = startRow; row < startRow + rowNum; row++)
{
string sql = "DELETE FROM " + tbname + " WHERE ID = '" + row.ToString() + "';";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
}
/// <summary>
/// 删除指定行数据
/// </summary>
/// <param name="tbname">表格名称</param>
/// <param name="column">已知列名称</param>
/// <param name="value">该列对应的值</param>
public void DelSQLtableRow(string tbname, string column, string value)
{
string sql = $"DELETE FROM {tbname} WHERE {column}='{value}';";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 重命名表格
/// </summary>
/// <param name="oldTbname">旧表格名称</param>
/// <param name="newTbname">新表格名称</param>
/// <returns>null</returns>
public void RemoveSQLtableName(string oldTbname, string newTbname)
{
// alter table 旧表格名称 rename to 新表格名称;
if (SelectTableExist(oldTbname) && !SelectTableExist(newTbname))
return;
string sql = "alter table " + oldTbname + " rename to " + newTbname + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
/// <summary>
/// 清空指定数据表
/// </summary>
/// <param name="tbname">表名</param>
/// <returns>null</returns>
public void ClearSQLtableRowValue(string tbname)
{
// DELETE FROM table_name WHERE[condition];
if (SelectTableExist(tbname))
{
string sql = "DELETE FROM " + tbname + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
}
/// <summary>
/// 删除指定表
/// </summary>
/// <param name="tbname">表格名称</param>
/// <returns>null</returns>
public void DelSQLtable(string tbname)
{
//# drop table record;
if (SelectTableExist(tbname))
{
string sql = "drop table " + tbname + ";";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLexecution(command, sql);
}
}
#endregion
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="command"></param>
/// <param name="sql"></param>
/// <returns></returns>
private bool SQLexecution(SQLiteCommand command, string sql)
{
/* SQLite语句统一位置执行,便于异常捕捉。
* SQLite数据库不支持多线程同时操作,必须加锁保证同一时刻只能一个线程进入
*/
Console.WriteLine($"执行SQL语句:{sql}");
bool mark = true;
lock(SequenceLock)
{
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
mark = false;
SqlExecuteNonQueryErrEvente?.Invoke(e.Message);
}
}
return mark;
}
}
}