/// <summary>
/// 查询单条记录
/// </summary>
/// <param name="ExpWhere">lamda表达式</param>
/// <param name="SqlOrderBy">取值排序字段，如：Id Asc,CreateTime Desc</param>
/// <param name="UseReadonlySource">是否实用只读数据源，true-是，false-否</param>
/// <returns></returns>
public static T Get(Expression <Func <T, bool> > ExpWhere, string SqlOrderBy, bool UseReadonlySource = true)
{
ResolveExpress re = new ResolveExpress();
re.ResolveExpression(re, ExpWhere);
string SqlWhere = "1=1" + re.SqlWhere;
object[] SqlParams = re.SqlParams.ToArray();
string SqlText = SqlGenerator.SQLBUILDER_Get(DB.ORM_TabInfo, SqlWhere, SqlOrderBy);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, UseReadonlySource);
DataTable dt = db.ExecTextDataTable(SqlText, SqlParams);
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
DynamicBuilder <T> DyBuilder = DynamicBuilder <T> .CreateBuilder(dr);
T Model = DyBuilder.Build(dr);
ModelBase <T> TempModel = Model as ModelBase <T>;
TempModel.ModifiedColumns.Clear();
return(Model);
}
else
{
return(new T());
}
}
/// <summary>
/// 物理删除数据记录
/// </summary>
/// <param name="SqlWhere">条件语句，如：IsDel=0 AND Status=@Status AND Id>@MinId</param>
/// <param name="SqlParams">参数集合，如：{Status,MinId}</param>
/// <returns></returns>
public static int DbDel(string SqlWhere, params object[] SqlParams)
{
string SqlText = SqlGenerator.SQLBUILDER_DbDel(DB.ORM_TabInfo, SqlWhere);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
return(db.ExecTextNonQuery(SqlText, SqlParams));
}
/// <summary>
/// 动态模型更新
/// </summary>
/// <param name="DSet">动态更新列模型</param>
/// <param name="DWhere">动态更新条件模型</param>
/// <returns></returns>
public static int Update(dynamic DSet, dynamic DWhere)
{
List <object> SqlParams = new List <object>();
string SqlText = SqlGenerator.SQLBUILDER_Update(DB.ORM_TabInfo, DSet, DWhere, out SqlParams);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
return(db.ExecTextNonQuery(SqlText, SqlParams.ToArray()));
}
/// <summary>
/// 逻辑删除数据记录
/// </summary>
/// <param name="EWhere"></param>
/// <returns></returns>
public static int DbDel(ExpandoObject EWhere)
{
List <object> SqlParams = new List <object>();
string SqlText = SqlGenerator.SQLBUILDER_DbDel(DB.ORM_TabInfo, EWhere, out SqlParams);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
return(db.ExecTextNonQuery(SqlText, SqlParams));
}
/// <summary>
/// 更新数据模型
/// </summary>
/// <param name="Model">数据模型</param>
/// <returns></returns>
private static bool UpdateWhereNeed(ModelBase <T> Model)
{
string SqlText = SqlGenerator.SQLBUILDER_Update(DB.ORM_TabInfo, Model.ModifiedColumns);
List <object> SqlParams = new List <object>();
foreach (string PropName in DB.ORM_TabInfo.ORMColList)
{
if (DB.ORM_TabInfo.ORM_NoAddCols.Contains(PropName) || DB.ORM_TabInfo.ORM_AutoIncreaseColName == PropName || !Model.ModifiedColumns.Contains("[" + PropName + "]"))
{
continue;
}
PropertyInfo Prop = DB.ORM_TabInfo.ORM_TypePropDic[PropName];
object PropValue = Prop.GetValue(Model);
if (Prop.PropertyType == typeof(DateTime))
{
DateTime DT = Convert.ToDateTime(PropValue);
if (DT.Year < 1900)
{
SqlParams.Add(new DateTime(1900, 1, 1));
}
else
{
SqlParams.Add(PropValue);
}
}
else
{
SqlParams.Add(PropValue);
}
}
if (!string.IsNullOrEmpty(DB.ORM_TabInfo.ORM_AutoIncreaseColName) && DB.ORM_TabInfo.ORM_TypePropDic.ContainsKey(DB.ORM_TabInfo.ORM_AutoIncreaseColName))
{
SqlParams.Add(DB.ORM_TabInfo.ORM_TypePropDic[DB.ORM_TabInfo.ORM_AutoIncreaseColName].GetValue(Model));
}
else if (DB.ORM_TabInfo.ORM_PrimaryKeys.Count > 0)
{
foreach (string PrimaryKeyName in DB.ORM_TabInfo.ORM_PrimaryKeys)
{
PropertyInfo PrimaryKeyProp = DB.ORM_TabInfo.ORM_TypePropDic[PrimaryKeyName];
SqlParams.Add(PrimaryKeyProp.GetValue(Model));
}
}
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
try
{
return(db.ExecTextNonQuery(SqlText, SqlParams.ToArray()) >= 1);
}
catch
{
return(false);
}
}
/// <summary>
/// 模型更新，动态条件类型
/// </summary>
/// <param name="Model">数据模型</param>
/// <param name="DWhere">动态更新条件模型</param>
/// <returns></returns>
public static int Update(T Model, ExpandoObject EWhere)
{
if (EWhere == null)
{
return(Update(Model) ? 1 : 0);
}
List <object> SqlParams = new List <object>();
List <object> SqlWhereParams = new List <object>();
ModelBase <T> TempModel = Model as ModelBase <T>;
string SqlText = SqlGenerator.SQLBUILDER_Update(DB.ORM_TabInfo, TempModel.ModifiedColumns, EWhere, out SqlWhereParams);
foreach (string PropName in DB.ORM_TabInfo.ORMColList)
{
if (DB.ORM_TabInfo.ORM_NoAddCols.Contains(PropName) || DB.ORM_TabInfo.ORM_AutoIncreaseColName == PropName)
{
continue;
}
PropertyInfo Prop = DB.ORM_TabInfo.ORM_TypePropDic[PropName];
object PropValue = Prop.GetValue(Model);
if (Prop.PropertyType == typeof(DateTime))
{
DateTime DT = Convert.ToDateTime(PropValue);
if (DT.Year < 1900)
{
SqlParams.Add(new DateTime(1900, 1, 1));
}
else
{
SqlParams.Add(PropValue);
}
}
else
{
SqlParams.Add(PropValue);
}
}
if (SqlWhereParams != null)
{
SqlParams.AddRange(SqlWhereParams);
}
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
try
{
return(db.ExecTextNonQuery(SqlText, SqlParams.ToArray()));
}
catch
{
return(-1);
}
}
/// <summary>
/// 查询统计信息
/// </summary>
/// <param name="SqlWhere">条件语句，如：IsDel=0 AND Status=@Status AND Id>@MinId</param>
/// <param name="UseReadonlySource">是否实用只读数据源，true-是，false-否</param>
/// <param name="SqlParams">参数集合，如：{Status,MinId}</param>
/// <returns></returns>
public static long Count(string SqlWhere, bool UseReadonlySource = true, params object[] SqlParams)
{
string SqlText = SqlGenerator.SQLBUILDER_Count(DB.ORM_TabInfo, SqlWhere);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, UseReadonlySource);
object CountObj = db.ExecTextScalar(SqlText, SqlParams);
if (CountObj != null && CountObj != DBNull.Value)
{
long CountVal = Convert.ToInt64(CountObj);
return(CountVal);
}
else
{
return(-1);
}
}
/// <summary>
/// 查询分页记录
/// </summary>
/// <param name="DWhere">动态条件</param>
/// <param name="SqlOrderBy">取值排序字段，如：Id Asc,CreateTime Desc</param>
/// <param name="RecordCount">数据记录总数</param>
/// <param name="PageIndex">页码，默认1</param>
/// <param name="PageSize">页大小，默认20</param>
/// <param name="UseReadonlySource">是否实用只读数据源，true-是，false-否</param>
/// <returns></returns>
public static List <T> List(dynamic DWhere, string SqlOrderBy, out long RecordCount, int PageIndex = 1, int PageSize = 20, bool UseReadonlySource = true)
{
List <object> SqlParams = new List <object>();
string SqlText = SqlGenerator.SQLBUILDER_List(DB.ORM_TabInfo, DWhere, SqlOrderBy, PageIndex, PageSize, out SqlParams);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, UseReadonlySource);
List <object> TempParams = new List <object>();
if (SqlParams != null)
{
TempParams.AddRange(SqlParams);
}
TempParams.Add(PageIndex * PageSize - PageSize + 1);
TempParams.Add(PageIndex * PageSize);
DataSet ds = db.ExecTextDataSet(SqlText, TempParams.ToArray());
List <T> ReturnObjs = new List <T>();
RecordCount = -1;
if (ds != null && ds.Tables.Count == 2)
{
DataTable DataDT = ds.Tables[0];
DataTable CountDT = ds.Tables[1];
if (DataDT != null && DataDT.Rows.Count > 0)
{
foreach (DataRow dr in DataDT.Rows)
{
DynamicBuilder <T> DyBuilder = DynamicBuilder <T> .CreateBuilder(dr);
T Model = DyBuilder.Build(dr);
ModelBase <T> TempModel = Model as ModelBase <T>;
TempModel.ModifiedColumns.Clear();
ReturnObjs.Add(Model);
}
}
if (CountDT != null && CountDT.Rows.Count == 1)
{
long.TryParse(CountDT.Rows[0][0].ToString(), out RecordCount);
}
else
{
RecordCount = 0;
}
}
return(ReturnObjs);
}
/// <summary>
/// 更新数据记录
/// </summary>
/// <param name="SqlWhere">条件语句，如：IsDel=0 AND Status=@Status AND Id>@MinId</param>
/// <param name="SqlSet">设置语句，如：TrueName=@TrueName,Remark=@Remark</param>
/// <param name="SqlParams">参数集合，如：{Status,MinId}</param>
/// <returns></returns>
public static int Update(string SqlSet, string SqlWhere, params object[] SqlParams)
{
string SqlText = SqlGenerator.SQLBUILDER_Update(DB.ORM_TabInfo, SqlWhere, SqlSet);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
for (int i = 0; i < SqlParams.Length; i++)
{
object SqlParam = SqlParams[i];
if (SqlParam is DateTime)
{
if (((DateTime)SqlParam).Year < 1900)
{
SqlParams[i] = new DateTime(1900, 1, 1);
}
}
}
return(db.ExecTextNonQuery(SqlText, SqlParams));
}
/// <summary>
/// 保存数据记录
/// </summary>
/// <param name="Model"></param>
/// <returns></returns>
public static bool Add(T Model)
{
string SqlText = SqlGenerator.SQLBUILDER_Add(DB.ORM_TabInfo, false);
List <object> SqlParams = new List <object>();
foreach (string PropName in DB.ORM_TabInfo.ORMColList)
{
if (DB.ORM_TabInfo.ORM_NoAddCols.Contains(PropName) || DB.ORM_TabInfo.ORM_AutoIncreaseColName == PropName)
{
continue;
}
PropertyInfo Prop = DB.ORM_TabInfo.ORM_TypePropDic[PropName];
object PropValue = Prop.GetValue(Model);
if (Prop.PropertyType == typeof(DateTime))
{
DateTime DT = Convert.ToDateTime(PropValue);
if (DT.Year < 1900)
{
SqlParams.Add(new DateTime(1900, 1, 1));
}
else
{
SqlParams.Add(PropValue);
}
}
else
{
SqlParams.Add(PropValue);
}
}
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
try
{
return(db.ExecTextNonQuery(SqlText, SqlParams.ToArray()) == 1);
}
catch (Exception E)
{
Console.WriteLine(E.Message);
return(false);
}
}
/// <summary>
/// 查询单条记录
/// </summary>
/// <param name="SqlWhere">条件语句，如：IsDel=0 AND Status=@Status AND Id>@MinId</param>
/// <param name="SqlOrderBy">取值排序字段，如：Id Asc,CreateTime Desc</param>
/// <param name="UseReadonlySource">是否实用只读数据源，true-是，false-否</param>
/// <param name="SqlParams">参数集合，如：{Status,MinId}</param>
/// <returns></returns>
public static T Get(string SqlWhere, string SqlOrderBy, bool UseReadonlySource = true, params object[] SqlParams)
{
string SqlText = SqlGenerator.SQLBUILDER_Get(DB.ORM_TabInfo, SqlWhere, SqlOrderBy);
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, UseReadonlySource);
DataTable dt = db.ExecTextDataTable(SqlText, SqlParams);
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
DynamicBuilder <T> DyBuilder = DynamicBuilder <T> .CreateBuilder(dr);
T Model = DyBuilder.Build(dr);
ModelBase <T> TempModel = Model as ModelBase <T>;
TempModel.ModifiedColumns.Clear();
return(Model);
}
else
{
return(new T());
}
}
/// <summary>
/// 查询统计信息
/// </summary>
/// <param name="ExpWhere">lamda表达式</param>
/// <param name="UseReadonlySource">是否实用只读数据源，true-是，false-否</param>
/// <returns></returns>
public static long Count(Expression <Func <T, bool> > ExpWhere, bool UseReadonlySource = true)
{
ResolveExpress re = new Tool.ResolveExpress();
re.ResolveExpression(re, ExpWhere);
string SqlWhere = "1=1" + re.SqlWhere;
string SqlText = SqlGenerator.SQLBUILDER_Count(DB.ORM_TabInfo, SqlWhere);
object[] SqlParams = re.SqlParams.ToArray();
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, UseReadonlySource);
object CountObj = db.ExecTextScalar(SqlText, SqlParams);
if (CountObj != null && CountObj != DBNull.Value)
{
long CountVal = Convert.ToInt64(CountObj);
return(CountVal);
}
else
{
return(-1);
}
}
/// <summary>
/// 保存数据记录
/// </summary>
/// <param name="Model">实体</param>
/// <param name="NewId">如存在自增列，则为新自增列Id</param>
/// <returns></returns>
public static bool Add(T Model, out long NewId)
{
NewId = -1;
string SqlText = SqlGenerator.SQLBUILDER_Add(DB.ORM_TabInfo);
List <object> SqlParams = new List <object>();
foreach (string PropName in DB.ORM_TabInfo.ORMColList)
{
if (DB.ORM_TabInfo.ORM_NoAddCols.Contains(PropName) || DB.ORM_TabInfo.ORM_AutoIncreaseColName == PropName)
{
continue;
}
PropertyInfo Prop = DB.ORM_TabInfo.ORM_TypePropDic[PropName];
object PropValue = Prop.GetValue(Model);
if (Prop.PropertyType == typeof(DateTime))
{
DateTime DT = Convert.ToDateTime(PropValue);
if (DT.Year < 1900)
{
SqlParams.Add(new DateTime(1900, 1, 1));
}
else
{
SqlParams.Add(PropValue);
}
}
else
{
SqlParams.Add(PropValue);
}
}
Data.DBHelper db = new Data.DBHelper(DB.ORM_TabInfo.ORMConnectionMark, false);
object NewIdObj = null;
try
{
NewIdObj = db.ExecTextScalar(SqlText, SqlParams.ToArray());
if (NewIdObj != null && NewIdObj != DBNull.Value)
{
long.TryParse(NewIdObj.ToString(), out NewId);
if (!string.IsNullOrEmpty(DB.ORM_TabInfo.ORM_AutoIncreaseColName) && DB.ORM_TabInfo.ORM_TypePropDic.ContainsKey(DB.ORM_TabInfo.ORM_AutoIncreaseColName))
{
PropertyInfo AutoIncreaseProp = DB.ORM_TabInfo.ORM_TypePropDic[DB.ORM_TabInfo.ORM_AutoIncreaseColName];
if (AutoIncreaseProp.PropertyType == typeof(int))
{
DB.ORM_TabInfo.ORM_TypePropDic[DB.ORM_TabInfo.ORM_AutoIncreaseColName].SetValue(Model, Convert.ToInt32(NewId));
}
else if (AutoIncreaseProp.PropertyType == typeof(short))
{
DB.ORM_TabInfo.ORM_TypePropDic[DB.ORM_TabInfo.ORM_AutoIncreaseColName].SetValue(Model, Convert.ToInt16(NewId));
}
else
{
DB.ORM_TabInfo.ORM_TypePropDic[DB.ORM_TabInfo.ORM_AutoIncreaseColName].SetValue(Model, NewId);
}
}
}
return(true);
}
catch (Exception E)
{
Console.WriteLine(E.Message);
return(false);
}
}