有key：
using var con = new SQLiteConnection(Connection);
CreateTableHasKey<模型名称>(con, 表名称);//创建表
private string GetColumnType(PropertyInfo property)
{
Type propertyType = property.PropertyType;
if (propertyType == typeof(int) || propertyType == typeof(int?))
{
return "INTEGER";
}
else if (propertyType == typeof(string) || propertyType == typeof(decimal) || propertyType == typeof(decimal?))
{
return "TEXT";
}
else if (propertyType == typeof(double) || propertyType == typeof(double?) || propertyType == typeof(float))
{
return "DOUBLE";
}
else if (propertyType == typeof(bool))
{
return "REAL";
}
else if (propertyType == typeof(byte[]))
{
return "BLOB";
}
else
{
throw new NotSupportedException("Column type not supported.");
}
}
/// <summary>
/// 新建表 有主键
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
public void CreateTableHasKey<T>(SQLiteConnection connection, string tableName)
{
try
{
var properties = typeof(T).GetProperties().Where(p => Attribute.IsDefined(p, typeof(IgnoreFieldAttribute)) == false);
var queryString = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
queryString.Append($"{tableName}");
queryString.Append(" (");
int i = 0;
foreach (var property in properties)
{
string columnName = property.Name;
string columnType = GetColumnType(property);
// 处理 ID 属性
if (columnName == "ID" && property.PropertyType == typeof(int)) // int
{
columnType += " PRIMARY KEY AUTOINCREMENT";
}
else if (columnName == "ID" && property.PropertyType == typeof(int?)) // int?
{
columnType += " PRIMARY KEY"; // 这里不再使用 AUTOINCREMENT，通常主键应该是自增的
}
else if (columnName == "Guid")
{
columnType += " PRIMARY KEY";
}
queryString.Append(columnName);
queryString.Append(' ');
queryString.Append(columnType);
columnName = null;
columnType = null;
if (i < properties.Count() - 1)
{
queryString.Append(", ");
}
i++;
}
queryString.Append(" )");
connection.Execute(queryString.ToString());
}
catch (Exception ex)
{
LogHelper.GetSingleObj().WriteLog(ex);
}
}没有key：
/// <summary>
/// 新建表 没主键
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
public void CreateTable<T>(SQLiteConnection connection, string tableName)
{
try
{
var properties = typeof(T).GetProperties().Where(p => Attribute.IsDefined(p, typeof(IgnoreFieldAttribute)) == false);
var queryString = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
queryString.Append($"{tableName}");
queryString.Append(" (");
int i = 0;
foreach (var property in properties)
{
string columnName = property.Name;
string columnType = GetColumnType(property);
queryString.Append(columnName);
queryString.Append(' ');
queryString.Append(columnType);
columnName = null;
columnType = null;
if (i < properties.Count() - 1)
{
queryString.Append(", ");
}
i++;
}
queryString.Append(')');
connection.Execute(queryString.ToString());
var columns = connection.Query($"PRAGMA table_info({tableName})");
// 检查返回结果中是否存在主键列
bool hasPrimaryKey = columns.Any(column => column.pk == 1);
if (hasPrimaryKey)
{
// 创建临时表
connection.Execute($"CREATE TABLE TempTable AS SELECT * FROM {tableName}");
// 删除原始表
connection.Execute($"DROP TABLE {tableName}");
// 重新创建原始表（不包含主键约束）
CreateTable<T>(connection, tableName);
// 将临时表的数据插入到重新创建的原始表中
connection.Execute($"INSERT INTO {tableName} SELECT * FROM TempTable");
// 删除临时表
connection.Execute("DROP TABLE TempTable");
}
columns = null;
}
catch (Exception ex)
{
LogHelper.GetSingleObj().WriteLog(ex);
}
}
private string GetColumnType(PropertyInfo property)
{
Type propertyType = property.PropertyType;
if (propertyType == typeof(int) || propertyType == typeof(int?))
{
return "INTEGER";
}
else if (propertyType == typeof(string) || propertyType == typeof(decimal) || propertyType == typeof(decimal?))
{
return "TEXT";
}
else if (propertyType == typeof(double) || propertyType == typeof(double?) || propertyType == typeof(float))
{
return "DOUBLE";
}
else if (propertyType == typeof(bool))
{
return "REAL";
}
else if (propertyType == typeof(byte[]))
{
return "BLOB";
}
else
{
throw new NotSupportedException("Column type not supported.");
}
}
PS:如果模型中有不需要加到数据库中的字段则加标记。如下代码所示：
public partial class UserInfoModel : ObservableObject
{
public UserInfoModel() { }
[ObservableProperty]
string _Guid;
/// <summary>
/// 用户名
/// </summary>
[ObservableProperty]
string _Name;
/// <summary>
/// 创建人
/// </summary>
[ObservableProperty]
string _Creater;
/// <summary>
/// 创建时间
/// </summary>
[ObservableProperty]
string _CreateTime;
/// <summary>
/// 备注
/// </summary>
[ObservableProperty]
string _Remark;
#region 本地字段
string _StateStr;
[IgnoreField]
public string StateStr { get => _StateStr; set => SetProperty(ref _StateStr, value); }
#endregion
}表中增加数据，可写成公用方法如下：
/// <summary>
/// 插入sql的公用方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName"></param>
/// <returns></returns>
private string GenerateInsertQuery<T>(string tableName)
{
// 检查属性是否标记为忽略字段
var propertyNames = typeof(T).GetProperties().Where(p => Attribute.IsDefined(p, typeof(IgnoreFieldAttribute)) == false).Select(p => p.Name);
var columnNames = string.Join(", ", propertyNames);
var parameterNames = string.Join(", ", propertyNames.Select(p => $"@{p}"));
var insertQuery = $"INSERT INTO {tableName} ({columnNames}) VALUES ({parameterNames})";
return insertQuery;
}
/// <summary>
/// 新增数据
/// </summary>
/// <returns></returns>
public void InsertTable(模型 model)
{
if (model == null) { return; }
try
{
using var connew = new SQLiteConnection(Connection);
if (connew.State == ConnectionState.Closed) { connew.Open(); }
var tran = connew.BeginTransaction(IsolationLevel.Serializable);//开启事务
try
{
connew.Execute("Delete from XXXTable where Guid=@InGuid;", new { InGuid = model.Guid }, transaction: tran);
connew.Execute(GenerateInsertQuery<模型名称>("XXXTable"), model, transaction: tran);
tran.Commit();
model = null;
}
catch (Exception)
{
tran.Rollback();
throw;
}
}
catch (Exception ex)
{
LogHelper.GetSingleObj().WriteLog(ex);
}
}这样模型中也可写表不用的字段，用来数据绑定等其他用途。还可以做成公用方法便于调用和利用。