/// <summary>
/// 通用查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="_sqlwhere"></param>
/// <returns></returns>
public static ObservableCollection <T> ComSQLiteSelect <T>(string _sqlwhere)
{
try
{
var collection = new ObservableCollection <T>();
var model = Activator.CreateInstance <T>();
var modeltype = model.GetType();
var sql = "select * from " + modeltype.Name + " where 1=1 " + _sqlwhere;
var conn = new SQLiteConnection(SQLiteConn);
var ds = SqLiteHelper.ExecuteDataSet(conn, sql, null);
foreach (DataRow dr in ds.Tables[0].Rows)
{
model = Activator.CreateInstance <T>();
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
var propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
if (propertyInfo != null && dr[i] != DBNull.Value)
{
propertyInfo.SetValue(model, dr[i], null);
}
}
collection.Add(model);
}
return(collection);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// SQLite通用更新
/// </summary>
/// <param name="_model">实体</param>
/// <param name="_identity">主键</param>
/// <returns></returns>
public static int ComSQLiteUpdate(object _model, string _identity)
{
var conn = new SQLiteConnection(SQLiteConn);
var tbname = _model.GetType().Name;
var sql = new StringBuilder();
var sqlend = new StringBuilder();
var head = "UPDATE " + tbname + " set";
sqlend.Append("WHERE");
var cols = _model.GetType().GetProperties();
var _identityval = "";
foreach (var col in cols)
{
if (col.Module.Name == "ZNC.DataEntiry.dll") //仅实体类本身的属性，不包含父类属性
{
var val = col.GetValue(_model, null);
if (col.Name == _identity)
{
_identityval = val.ToString();
continue;
}
if (string.IsNullOrEmpty(val?.ToString()))
{
continue;
}
if (col.PropertyType == typeof(DateTime))
{
DateTime dt;
DateTime.TryParse(val.ToString(), out dt);
if (dt <= SqlDateTime.MinValue.Value)
{
continue;
}
}
sql.Append(" " + col.Name + " = '" + val + "',");
}
}
sqlend.Append(" " + _identity + "= '" + _identityval + "'");
var start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
var end = sqlend.ToString();
end = end.Substring(0, end.Length) + " ";
var realsql = head + start + end;
return(SqLiteHelper.ExecuteNonQuery(conn, realsql, null));
}
/// <summary>
/// SQLite通用删除
/// </summary>
/// <param name="_model">实体</param>
/// <param name="_identity">主键</param>
/// <returns></returns>
public static int ComSQLiteDelete(object _model, string _identity)
{
var cols = _model.GetType().GetProperties();
var tbname = _model.GetType().Name;
var _identityval = "";
foreach (var col in cols)
{
if (col.Module.Name == "ZNC.DataEntiry.dll") //仅实体类本身的属性，不包含父类属性
{
var val = col.GetValue(_model, null);
if (col.Name != _identity)
{
continue;
}
_identityval = val.ToString();
}
}
var sql = "DELETE FROM [" + tbname + "] WHERE [" + _identity + "] = " + _identityval;
var conn = new SQLiteConnection(SQLiteConn);
return(SqLiteHelper.ExecuteNonQuery(conn, sql, null));
}