/// <summary>
/// 根据实体类创建表
/// </summary>
/// <typeparam name="T"></typeparam>
public void CreateTable <T>()
{
Type type = typeof(T);
List <string> columnNames = new List <string>();
List <string> columnType = new List <string>();
bool isTableColumn = true;
foreach (var item in type.GetProperties())
{
isTableColumn = true;
object[] attrs = item.GetCustomAttributes(true);
foreach (var attr in attrs)
{
if (attr is PropertyNameAttribute)
{
PropertyNameAttribute attr2 = attr as PropertyNameAttribute;
if (attr2.IsTableColumn)
{
isTableColumn = false;
break;
}
}
}
if (isTableColumn)
{
columnNames.Add(item.Name);
columnType.Add(item.PropertyType.Name);
}
}
CreateTable(ExtendTableName.GetTableName(type), columnNames.ToArray(), columnType.ToArray());
}
/// <summary>
/// 删除表
/// </summary>
/// <typeparam name="T"></typeparam>
public void DropTable <T>()
{
Act(() =>
{
string sql = $"drop table [{ExtendTableName.GetTableName(typeof(T))}]";
sqLiteHelper.ExecuteQuery(sql);
});
}
/// <summary>
/// 数据库是否存在该表
/// </summary>
/// <typeparam name="T"></typeparam>
public void TableExist <T>()
{
Act(() =>
{
if (!sqLiteHelper.TableExist(ExtendTableName.GetTableName(typeof(T))))
{
CreateTable <T>();
}
});
}
/// <summary>
/// 根据条件删除数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where">WHERE 1=1 {where}</param>
/// <returns></returns>
public bool DeleteByWhere <T>(string where = null)
{
var type = typeof(T);
var sqlStr = string.Format($"DELETE FROM [{ExtendTableName.GetTableName(type)}] WHERE 1=1 {where}");
return(ExcuteSql <bool>(sqlStr, (c) =>
{
// c.Parameters.Add(new SQLiteParameter("@Id", id));
var result = c.ExecuteNonQuery();
return result > 0;
}));
}
/// <summary>
/// 根据Id删除数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public bool DeleteById <T>(int id)
{
var type = typeof(T);
var sqlStr = string.Format($"DELETE FROM [{ExtendTableName.GetTableName(type)}] WHERE (Id = @Id)");
return(ExcuteSql <bool>(sqlStr, (c) =>
{
c.Parameters.Add(new SQLiteParameter("@Id", id));
var result = c.ExecuteNonQuery();
return result > 0;
}));
}
/// <summary>
/// 更新
/// WHERE {whereStr}
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="whereStr"></param>
/// <returns></returns>
public bool Update <T>(T t, string whereStr)
{
var type = typeof(T);
var values = new List <string>();
//无法将类型为“System.Data.SqlClient.SQLiteParameter”的对象强制转换为类型“System.Data.SQLite.SQLiteParameter”。
var par = new List <SQLiteParameter>();
bool isTableColumn = true;
foreach (var item in type.GetProperties())
{
isTableColumn = true;
object[] objArr = item.GetCustomAttributes(true);
foreach (var item2 in objArr)
{
if (item2 is PropertyNameAttribute)
{
PropertyNameAttribute pro = item2 as PropertyNameAttribute;
if (pro.IsTableColumn)
{
isTableColumn = false;
break;
}
}
}
if (isTableColumn)
{
var value = item.GetValue(t, null);
if (item.Name.ToLower() != "id")
{
par.Add(new SQLiteParameter(item.Name, value));
values.Add(string.Format($"[{item.Name}] = @{item.Name}"));
}
}
}
var v = string.Join(",", values.ToArray());
var sql = string.Format($"UPDATE [{ExtendTableName.GetTableName(type)}] SET {v} WHERE {whereStr}");
return(ExcuteSql <bool>(sql, cmd =>
{
foreach (var parameter in par)
{
cmd.Parameters.Add(parameter);
}
var result = cmd.ExecuteNonQuery();
return result > 0;
}));
// return RunCmd(sql, par.ToArray());
}
public T QueryById <T>(int id)
{
Type type = typeof(T);
string columnStrings = string.Join(",", type.GetProperties().Select(x => $" [{x.Name}] "));
string sqlStr = string.Format($"SELECT {columnStrings} FROM [{ExtendTableName.GetTableName(type)}] Where Id=@Id");
return(ExcuteSql <T>(sqlStr, c =>
{
c.Parameters.Add(new SQLiteParameter("@Id", id));
var reader = c.ExecuteReader();
if (reader.Read())
{
return (CreateT <T>(reader));
}
return default(T);
}));
}
/// <summary>
/// 返回所有符合条件的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="whereStr">where 1=1 {whereStr}</param>
/// <returns></returns>
public List <T> QueryAll <T>(string whereStr = null)
{
Type type = typeof(T);
string sqlStr = string.Format($"SELECT * FROM [{ ExtendTableName.GetTableName(type)}] where 1=1 {whereStr}");
return(ExcuteSql <List <T> >(sqlStr, c =>
{
var list = new List <T>();
var reader = c.ExecuteReader();
while (reader.Read())
{
var item = (CreateT <T>(reader));
list.Add(item);
}
return list;
}));
}
private string GetSql <T>(T t, bool IsGetId, ref List <SQLiteParameter> par)
{
//插入实体时要判断一下时间的插入.还可以通过特性来判断一下数据的长度
var type = typeof(T);
var keys = new List <string>();
var values = new List <string>();
bool isTableColumn = true;
foreach (var item in type.GetProperties())
{
isTableColumn = true;
object[] objArr = item.GetCustomAttributes(true);
foreach (var obj in objArr)
{
if (obj is PropertyNameAttribute)
{
PropertyNameAttribute propertyNameAttribute = obj as PropertyNameAttribute;
if (propertyNameAttribute.IsTableColumn || propertyNameAttribute.IsPrimaryKey == 1)
{
isTableColumn = false;
break;
}
}
}
if (isTableColumn)
{
var value = item.GetValue(t, null);
keys.Add(item.Name);
values.Add("@" + item.Name);
par.Add(new SQLiteParameter("@" + item.Name, value));
}
}
var c = string.Join(",", keys.ToArray());
var v = string.Join(",", values.ToArray());
string sqlStr = string.Format($"INSERT INTO [{ExtendTableName.GetTableName(type)}] ({c}) " +
$"VALUES({v})");
if (IsGetId)
{
sqlStr += ";SELECT last_insert_rowid();";
}
return(sqlStr);
}
