/// <summary>
/// 返回分页条数sql
/// </summary>
/// <returns></returns>
public static int ToPageCountSql(DbParameter[] param, DbCommand cmd, string sql, ConfigModel config, ref string tempSql)
{
sql = string.Format("select count(0) from ({0})t", sql);
try
{
tempSql = ParameterToSql.ObjectParamToSql(param.ToList(), sql, config);
if (param != null)
{
cmd.Parameters.AddRange(param.ToArray());
}
var dt = BaseExecute.ToDataTable(cmd, sql.ToString());
return(int.Parse(dt.Rows[0][0].ToString()));
}
catch (Exception ex)
{
Task.Run(() =>
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(config, ex, "ToPageCountSql", sql);
}
else
{
DbLog.LogException(config.IsOutError, config.DbType, ex, "ToPageCountSql", sql);
}
});
return(0);
}
}
/// <summary>
/// 返回分页条数
/// </summary>
/// <returns></returns>
public static int ToPageCount(DataQuery item, DbCommand cmd, ref string sql, FilterType type)
{
try
{
var param = new List <DbParameter>();
sql = string.Format("select count(0) from {0}", item.Table[0]);
for (var i = 1; i < item.Predicate.Count; i++)
{
sql = string.Format("{2} {0} on {1}", item.Table[i], item.Predicate[i].Where, sql);
if (item.Predicate[i].Param.Count != 0)
{
param.AddRange(item.Predicate[i].Param);
}
}
if (!string.IsNullOrEmpty(item.Predicate[0].Where))
{
sql = string.Format("{1} where {0}", item.Predicate[0].Where, sql);
}
if (item.IsFilter)
{
BaseFilter.Filter(param.ToArray(), type, item.TableName, item.Config, ref sql);
}
if (item.Predicate[0].Param.Count != 0)
{
param.AddRange(item.Predicate[0].Param);
}
if (param.Count != 0)
{
cmd.Parameters.AddRange(param.ToArray());
}
var dt = BaseExecute.ToDataTable(cmd, sql.ToString());
return(int.Parse(dt.Rows[0][0].ToString()));
}
catch (Exception ex)
{
if (item.Config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(item.Config, ex, "ToPageCount", "");
}
else
{
DbLog.LogException(true, item.Config.DbType, ex, "ToPageCount", "");
}
return(0);
}
}
/// <summary>
/// 返回分页条数sql
/// </summary>
/// <returns></returns>
public static int ToPageCountSql(DbParameter[] param, DbCommand cmd, string sql, ConfigModel config, ref string tempSql, FilterType type, string tableName)
{
try
{
var table = new List <string>();
if (tableName != null)
{
table.Add(tableName);
}
sql = string.Format("select count(0) from ({0})t", sql);
if (param != null)
{
cmd.Parameters.AddRange(param.ToArray());
}
if (tableName != null)
{
BaseFilter.Filter(param, type, table, config, ref sql);
}
tempSql = ParameterToSql.ObjectParamToSql(param?.ToList(), sql, config);
var dt = BaseExecute.ToDataTable(cmd, sql.ToString());
return(int.Parse(dt.Rows[0][0].ToString()));
}
catch (Exception ex)
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(config, ex, "ToPageCountSql", sql);
}
else
{
DbLog.LogException(config.IsOutError, config.DbType, ex, "ToPageCountSql", sql);
}
return(0);
}
}
/// <summary>
/// model 转 update list sql
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="model">实体</param>
/// <param name="sql">sql</param>
/// <param name="oracleParam">参数</param>
/// <returns></returns>
public static OptionModel UpdateListToSql <T>(DbCommand cmd, List <T> list, ConfigModel config, Expression <Func <T, object> > field = null)
{
var result = new OptionModel();
result.IsCache = config.IsPropertyCache;
var where = PrimaryKey(config, cmd, typeof(T));
if (where.Count == 0)
{
result.Message = string.Format("{0}没有主键", typeof(T).Name);
result.IsSuccess = false;
return(result);
}
try
{
result.table = BaseExecute.ToDataTable <T>(cmd, config, where, field);
result.Sql = string.Format("update {0} set", typeof(T).Name);
var pInfo = PropertyCache.GetPropertyInfo <T>(config.IsPropertyCache);
if (field == null)
{
#region 属性
foreach (var item in pInfo)
{
if (where.Exists(a => a == item.Name))
{
continue;
}
result.Sql = string.Format("{2} {0}={1}{0},", item.Name, config.Flag, result.Sql);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item.Name;
temp.SourceColumn = item.Name;
result.Param.Add(temp);
}
#endregion
}
else
{
#region lambda
foreach (var item in (field.Body as NewExpression).Members)
{
if (where.Exists(a => a == item.Name))
{
continue;
}
result.Sql = string.Format("{2} {0}={1}{0},", item.Name, config.Flag, result.Sql);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item.Name;
temp.SourceColumn = item.Name;
result.Param.Add(temp);
}
#endregion
}
result.Sql = result.Sql.Substring(0, result.Sql.Length - 1);
var count = 1;
where.ForEach(a =>
{
if (count == 1)
{
result.Sql = string.Format("{2} where {0}={1}{0} ", a, config.Flag, result.Sql);
}
else
{
result.Sql = string.Format("{2} and {0}={1}{0} ", a, config.Flag, result.Sql);
}
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = a;
temp.SourceColumn = a;
result.Param.Add(temp);
count++;
});
result.IsSuccess = true;
list.ForEach(p =>
{
var row = result.table.NewRow();
where.ForEach(a =>
{
row[a] = BaseEmit.Get <T>(p, a);
});
if (field == null)
{
PropertyCache.GetPropertyInfo <T>().ForEach(a =>
{
row[a.Name] = BaseEmit.Get <T>(p, a.Name);
});
}
else
{
(field.Body as NewExpression).Members.ToList().ForEach(a =>
{
row[a.Name] = BaseEmit.Get <T>(p, a.Name);
});
}
result.table.Rows.Add(row);
});
return(result);
}
catch (Exception ex)
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException <T>(config, ex, "UpdateListToSql<T>", result.Sql);
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "UpdateListToSql<T>", result.Sql);
}
result.IsSuccess = false;
return(result);
}
}