/// <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>
/// model 转 insert sql
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="model">实体</param>
/// <param name="sql">sql</param>
/// <param name="oracleParam">参数</param>
/// <returns></returns>
public static OptionModel InsertToSql <T>(T model, ConfigModel config)
{
var sbName = new StringBuilder();
var sbValue = new StringBuilder();
var dynGet = new DynamicGet <T>();
var list = new List <MemberInfo>();
var result = new OptionModel();
try
{
sbName.AppendFormat("insert into {0} (", typeof(T).Name);
sbValue.Append(" values (");
PropertyCache.GetPropertyInfo <T>(config.IsPropertyCache).ForEach(p => {
if (!list.Exists(a => a.Name == p.Name))
{
sbName.AppendFormat("{0},", p.Name);
sbValue.AppendFormat("{1}{0},", p.Name, config.Flag);
var itemValue = dynGet.GetValue(model, p.Name, config.IsPropertyCache);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = p.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
}
});
result.Sql = string.Format("{0}) {1})", sbName.ToString().Substring(0, sbName.ToString().Length - 1)
, sbValue.ToString().Substring(0, sbValue.ToString().Length - 1));
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
Task.Run(() =>
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException <T>(config, ex, "InsertToSql<T>", result.Sql);
}
else
{
DbLog.LogException(config.IsOutError, config.DbType, ex, "InsertToSql<T>", result.Sql);
}
});
result.IsSuccess = false;
return(result);
}
}
/// <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>
/// query field 1个表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="field"></param>
/// <returns></returns>
public static FieldModel QueryField <T>(Expression <Func <T, bool> > predicate, Expression <Func <T, object> > field, ConfigModel config)
{
try
{
var result = new FieldModel();
var queryFields = new List <string>();
var i = 0;
if (field == null)
{
#region 无返回列
var list = PropertyCache.GetPropertyInfo <T>(config.IsPropertyCache);
list.ForEach(a =>
{
queryFields.Add(string.Format("{0}.{1}", predicate.Parameters[0].Name, a.Name));
result.AsName.Add(a.Name);
});
result.Field = string.Join(",", queryFields);
#endregion
}
else
{
#region 返回列
(field.Body as NewExpression).Arguments.ToList().ForEach(a =>
{
if (a is MethodCallExpression)
{
var methodName = "";
var ower = "";
var propertyName = GetPropertyMethod(a, out methodName, false, out ower);
if (string.Compare(methodName, "distinct", true) == 0)
{
queryFields.Add(string.Format("{2}{0} {3}.{1} ", methodName, propertyName, ower, predicate.Parameters[0].Name));
result.AsName.Add((a as MemberExpression).Member.Name);
}
else if (string.Compare(methodName, "sum", true) == 0)
{
if (config.DbType == DataDbType.SqlServer)
{
propertyName = string.Format("isnull({1}.{0},0)", propertyName, predicate.Parameters[0].Name);
}
if (config.DbType == DataDbType.MySql || config.DbType == DataDbType.SQLite)
{
propertyName = string.Format("ifnull({1}.{0},0)", propertyName, predicate.Parameters[0].Name);
}
if (config.DbType == DataDbType.Oracle)
{
propertyName = string.Format("nvl({1}.{0},0)", propertyName, predicate.Parameters[0].Name);
}
if (config.DbType == DataDbType.DB2)
{
propertyName = string.Format("coalesce({1}.{0},0)", propertyName, predicate.Parameters[0].Name);
}
queryFields.Add(string.Format("{0}({2}.{1})", methodName, propertyName, predicate.Parameters[0].Name));
result.AsName.Add((field.Body as NewExpression).Members[i].Name);
}
else
{
queryFields.Add(string.Format("{2}{0}({3}.{1})", methodName, propertyName, ower, predicate.Parameters[0].Name));
result.AsName.Add((field.Body as NewExpression).Members[i].Name);
}
}
else
{
queryFields.Add(string.Format("{0}.{1}", predicate.Parameters[0].Name, (a as MemberExpression).Member.Name));
result.AsName.Add((a as MemberExpression).Member.Name);
}
i++;
});
#endregion
}
result.Field = string.Join(",", queryFields);
return(result);
}
catch (Exception ex)
{
if (string.Compare(config.SqlErrorType, SqlErrorType.Db, true) == 0)
{
DbLogTable.LogException <T>(config, ex, "QueryField<T>", "");
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "QueryField<T>", "");
}
return(new FieldModel
{
Field = string.Format("{0}.*", predicate.Parameters[0].Name),
AsName = new List <string> {
predicate.Parameters[0].Name
}
});
}
}
/// <summary>
/// Lambda where
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="Param"></param>
/// <returns></returns>
public static VisitModel LambdaWhere <T>(Expression <Func <T, bool> > item, ConfigModel config)
{
var result = new VisitModel();
var strType = "";
int i = 0;
var leftList = new List <string>();
var rightList = new List <string>();
var typeList = new List <System.Type>();
var sb = new StringBuilder();
try
{
if (item == null)
{
return(result);
}
result.Where = RouteExpressionHandler(config, item.Body, ExpressionType.Goto, ref leftList, ref rightList, ref typeList, ref sb, ref strType, ref i);
result.Where = Remove(result.Where);
for (i = 0; i < leftList.Count; i++)
{
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = leftList[i] + i.ToString();
temp.Value = rightList[i];
if (typeList.Count >= i + 1 && typeList[i].Name == "DateTime")
{
if (config.DbType == DataDbType.Oracle)
{
temp.DbType = DbType.Date;
}
else
{
temp.DbType = DbType.DateTime;
}
temp.Value = rightList[i].ToDate();
}
result.Param.Add(temp);
}
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
if (string.Compare(config.SqlErrorType, SqlErrorType.Db, true) == 0)
{
DbLogTable.LogException <T>(config, ex, "LambdaWhere<T>", "");
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "LambdaWhere<T>", "");
}
result.IsSuccess = false;
return(result);
}
}
/// <summary>
/// 返回字符串列表
/// </summary>
/// <param name="path">文件名</param>
/// <param name="xmlNode">结点</param>
/// <returns></returns>
public static bool GetXmlList(string path, string xmlNode,
ref List <string> key, ref List <string> sql, ref Dictionary <string, object> db,
ref Dictionary <string, object> type, ref Dictionary <string, object> check,
ref Dictionary <string, object> param, ref Dictionary <string, object> name,
ref Dictionary <string, object> parameName, ConfigModel config, string xml = null)
{
try
{
var result = true;
var tempKey = "";
//变量
var xmlDoc = new XmlDocument();
//载入xml
if (config.IsEncrypt)
{
var temp = BaseSymmetric.DecodeGB2312(File.ReadAllText(path));
if (temp != "")
{
xmlDoc.LoadXml(temp);
}
else
{
xmlDoc.Load(path);
}
}
else if (!string.IsNullOrEmpty(xml))
{
xmlDoc.LoadXml(xml);
}
else
{
xmlDoc.Load(path);
}
//结点
var nodelList = xmlDoc.SelectNodes(xmlNode);
var list = new List <string>();
foreach (XmlNode item in nodelList)
{
foreach (XmlNode temp in item.ChildNodes)
{
var foreachCount = 1;
var i = 0;
if (temp is XmlElement)
{
var tempParam = new List <string>();
#region XmlElement
tempKey = temp.Attributes["id"].Value.ToLower();
//节点数
if (Array.Exists(key.ToArray(), element => element == tempKey))
{
result = false;
Task.Run(() => { BaseLog.SaveLog(string.Format("xml文件:{0},存在相同键:{1}", path, tempKey), "MapKeyExists"); });
}
key.Add(tempKey);
sql.Add(temp.ChildNodes.Count.ToString());
//name
if (temp.Attributes["name"] != null)
{
name.Add(string.Format("{0}.remark", tempKey), temp.Attributes["name"].Value);
}
foreach (XmlNode node in temp.ChildNodes)
{
#region XmlText
if (node is XmlText)
{
key.Add(string.Format("{0}.{1}", tempKey, i));
sql.Add(node.InnerText.Replace("<", "<").Replace(">", ">"));
}
#endregion
#region XmlElement 动态条件
if (node is XmlElement)
{
if (node.Attributes["prepend"] != null)
{
key.Add(string.Format("{0}.format.{1}", tempKey, i));
sql.Add(node.Attributes["prepend"].Value.ToLower());
}
//foreach
if (node.Name.ToLower() == "foreach")
{
//type
if (node.Attributes["type"] != null)
{
key.Add(string.Format("{0}.foreach.type.{1}", tempKey, foreachCount));
sql.Add(node.Attributes["type"].Value);
}
//result name
key.Add(string.Format("{0}.foreach.name.{1}", tempKey, foreachCount));
if (node.Attributes["name"] != null)
{
sql.Add(node.Attributes["name"].Value.ToLower());
}
else
{
sql.Add("data");
}
//field
if (node.Attributes["field"] != null)
{
key.Add(string.Format("{0}.foreach.field.{1}", tempKey, foreachCount));
sql.Add(node.Attributes["field"].Value.ToLower());
}
//sql
if (node.ChildNodes[0] is XmlText)
{
key.Add(string.Format("{0}.foreach.sql.{1}", tempKey, foreachCount));
sql.Add(node.ChildNodes[0].InnerText.Replace("<", "<").Replace(">", ">"));
}
foreachCount++;
}
foreach (XmlNode dyn in node.ChildNodes)
{
if (dyn is XmlText)
{
continue;
}
//check required
if (dyn.Attributes["required"] != null)
{
check.Add(string.Format("{0}.{1}.required", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["required"].Value.ToStr());
}
//check maxlength
if (dyn.Attributes["maxlength"] != null)
{
check.Add(string.Format("{0}.{1}.maxlength", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["maxlength"].Value.ToStr());
}
//check existsmap
if (dyn.Attributes["existsmap"] != null)
{
check.Add(string.Format("{0}.{1}.existsmap", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["existsmap"].Value.ToStr());
}
//check checkmap
if (dyn.Attributes["checkmap"] != null)
{
check.Add(string.Format("{0}.{1}.checkmap", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["checkmap"].Value.ToStr());
}
//check date
if (dyn.Attributes["date"] != null)
{
check.Add(string.Format("{0}.{1}.date", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["date"].Value.ToStr());
}
//参数
tempParam.Add(dyn.Attributes["property"].Value);
//param name
if (dyn.Attributes["name"] != null)
{
parameName.Add(string.Format("{0}.{1}.remark", tempKey, dyn.Attributes["property"].Value.ToLower()), dyn.Attributes["name"].Value);
}
if (dyn.Name.ToLower() == "ispropertyavailable")
{
//属性和值
key.Add(string.Format("{0}.{1}.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(string.Format("{0}{1}", dyn.Attributes["prepend"].Value.ToLower(), dyn.InnerText));
}
else if (dyn.Name.ToLower() != "choose")
{
//属性和值
key.Add(string.Format("{0}.{1}.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(string.Format("{0}{1}", dyn.Attributes["prepend"].Value.ToLower(), dyn.InnerText));
//条件类型
key.Add(string.Format("{0}.{1}.condition.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(dyn.Name);
//判断条件内容
if (dyn.Attributes["condition"] != null)
{
key.Add(string.Format("{0}.{1}.condition.value.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(dyn.Attributes["condition"].Value);
}
//比较条件值
if (dyn.Attributes["compareValue"] != null)
{
key.Add(string.Format("{0}.{1}.condition.value.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(dyn.Attributes["compareValue"].Value.ToLower());
}
}
else
{
//条件类型
key.Add(string.Format("{0}.{1}.condition.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(dyn.Name);
if (dyn is XmlElement)
{
var count = 0;
key.Add(string.Format("{0}.{1}.{2}", tempKey, dyn.Attributes["property"].Value.ToLower(), i));
sql.Add(dyn.ChildNodes.Count.ToStr());
foreach (XmlNode child in dyn.ChildNodes)
{
//条件
key.Add(string.Format("{0}.{1}.{2}.choose.condition.{3}", tempKey, dyn.Attributes["property"].Value.ToLower(), i, count));
sql.Add(child.Attributes["property"].Value);
//内容
key.Add(string.Format("{0}.{1}.{2}.choose.{3}", tempKey, dyn.Attributes["property"].Value.ToLower(), i, count));
sql.Add(string.Format("{0}{1}", child.Attributes["prepend"].Value.ToLower(), child.InnerText));
count++;
}
}
}
}
}
#endregion
i++;
}
//db
if (temp.Attributes["db"] != null)
{
db.Add(tempKey, temp.Attributes["db"].Value.ToStr());
}
//type
if (temp.Attributes["type"] != null)
{
type.Add(tempKey, temp.Attributes["type"].Value.ToStr());
}
//foreach count
key.Add(string.Format("{0}.foreach", tempKey));
sql.Add((foreachCount - 1).ToStr());
param.Add(tempKey, tempParam);
#endregion
}
else if (temp is XmlText)
{
#region XmlText
key.Add(string.Format("{0}.{1}", item.Attributes["id"].Value.ToLower(), i));
sql.Add(temp.InnerText.Replace("<", "<").Replace(">", ">"));
key.Add(item.Attributes["id"].Value.ToLower());
sql.Add("0");
#endregion
}
}
}
return(result);
}
catch (Exception ex)
{
Task.Run(() =>
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(config, ex, "InstanceMap", "GetXmlList");
}
else
{
DbLog.LogException(true, "InstanceMap", ex, "GetXmlList", "");
}
});
return(false);
}
}
/// <summary>
/// model 转 delete sql
/// </summary>
/// <param name="cmd"></param>
/// <param name="model"></param>
/// <param name="config"></param>
/// <returns></returns>
public static OptionModel DeleteToSql(DbCommand cmd, object model, ConfigModel config)
{
var result = new OptionModel();
result.IsCache = config.IsPropertyCache;
var where = PrimaryKey(config, cmd, model.GetType());
if (where.Count == 0)
{
result.Message = string.Format("{0}没有主键", model.GetType().Name);
result.IsSuccess = false;
return(result);
}
try
{
result.Sql = string.Format("delete {0} ", model.GetType().Name);
var count = 1;
foreach (var item in where)
{
var itemValue = BaseEmit.Get(model, item);
if (itemValue == null)
{
result.IsSuccess = false;
result.Message = string.Format("主键{0}值为空", item);
return(result);
}
if (count == 1)
{
result.Sql = string.Format("{2} where {0}={1}{0} ", item, config.Flag, result.Sql);
}
else
{
result.Sql = string.Format("{2} and {0}={1}{0} ", item, config.Flag, result.Sql);
}
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
count++;
}
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(config, ex, "UpdateToSql", result.Sql);
}
else
{
DbLog.LogException(config.IsOutError, config.DbType, ex, "DeleteToSql", result.Sql);
}
result.IsSuccess = false;
return(result);
}
}
/// <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);
}
}
/// <summary>
/// model 转 update sql
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="model">实体</param>
/// <param name="sql">sql</param>
/// <param name="oracleParam">参数</param>
/// <returns></returns>
public static OptionModel UpdateToSql <T>(T model, ConfigModel config, Expression <Func <T, object> > field = null, DbCommand cmd = null)
{
var result = new OptionModel();
result.IsCache = config.IsPropertyCache;
var where = PrimaryKey(config, cmd, typeof(T));
try
{
result.Sql = string.Format("update {0} set", typeof(T).Name);
var pInfo = PropertyCache.GetPropertyInfo <T>(config.IsPropertyCache);
if (field == null)
{
#region 属性
pInfo.ForEach(a =>
{
result.Sql = string.Format("{2} {0}={1}{0},", a.Name, config.Flag, result.Sql);
var itemValue = BaseEmit.Get <T>(model, a.Name);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = a.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
});
#endregion
}
else
{
#region lambda
(field.Body as NewExpression).Members.ToList().ForEach(a =>
{
result.Sql = string.Format("{2} {0}={1}{0},", a.Name, config.Flag, result.Sql);
var itemValue = BaseEmit.Get <T>(model, a.Name);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = a.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
});
#endregion
}
foreach (var item in where)
{
if (result.Param.Exists(a => a.ParameterName == item))
{
var itemValue = BaseEmit.Get <T>(model, item);
if (itemValue == null)
{
result.IsSuccess = false;
result.Message = string.Format("主键{0}值为空", item);
return(result);
}
}
}
result.Sql = result.Sql.Substring(0, result.Sql.Length - 1);
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException <T>(config, ex, "UpdateToSql<T>", result.Sql);
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "UpdateToSql<T>", result.Sql);
}
result.IsSuccess = false;
return(result);
}
}
/// <summary>
/// model 转 update sql
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="model">实体</param>
/// <param name="sql">sql</param>
/// <param name="oracleParam">参数</param>
/// <returns></returns>
public static OptionModel UpdateToSql <T>(T model, ConfigModel config, Expression <Func <T, object> > field = null)
{
var result = new OptionModel();
var dynGet = new DynamicGet <T>();
result.IsCache = config.IsPropertyCache;
try
{
result.Sql = string.Format("update {0} set", typeof(T).Name);
var pInfo = PropertyCache.GetPropertyInfo <T>(config.IsPropertyCache);
if (field == null)
{
#region 属性
pInfo.ForEach(a => {
result.Sql = string.Format("{2} {0}={1}{0},", a.Name, config.Flag, result.Sql);
var itemValue = dynGet.GetValue(model, a.Name, config.IsPropertyCache);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = a.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
});
#endregion
}
else
{
#region lambda
(field.Body as NewExpression).Members.ToList().ForEach(a => {
result.Sql = string.Format("{2} {0}={1}{0},", a.Name, config.Flag, result.Sql);
var itemValue = dynGet.GetValue(model, a.Name, config.IsPropertyCache);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = a.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
});
#endregion
}
result.Sql = result.Sql.Substring(0, result.Sql.Length - 1);
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
Task.Run(() =>
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException <T>(config, ex, "UpdateToSql<T>", result.Sql);
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "UpdateToSql<T>", result.Sql);
}
});
result.IsSuccess = false;
return(result);
}
}
/// <summary>
/// model 转 update sql
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="model">实体</param>
/// <param name="sql">sql</param>
/// <param name="oracleParam">参数</param>
/// <returns></returns>
public static OptionModel UpdateToSql <T>(DbCommand cmd, T model, ConfigModel config, Expression <Func <T, object> > field = null)
{
var result = new OptionModel();
var dynGet = new DynamicGet <T>();
result.IsCache = config.IsPropertyCache;
var where = PrimaryKey(config, cmd, typeof(T).Name);
if (where.Count == 0)
{
result.Message = string.Format("{0}没有主键", typeof(T).Name);
result.IsSuccess = false;
return(result);
}
try
{
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 itemValue = dynGet.GetValue(model, item.Name, config.IsPropertyCache);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
}
#endregion
}
else
{
#region lambda
var list = (field.Body as NewExpression).Members;
foreach (var item in list)
{
if (where.Exists(a => a == item.Name))
{
continue;
}
result.Sql = string.Format("{2} {0}={1}{0},", item.Name, config.Flag, result.Sql);
var itemValue = dynGet.GetValue(model, item.Name, config.IsPropertyCache);
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item.Name;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
}
#endregion
}
result.Sql = result.Sql.Substring(0, result.Sql.Length - 1);
var count = 1;
foreach (var item in where)
{
var itemValue = dynGet.GetValue(model, item, config.IsPropertyCache);
if (itemValue == null)
{
result.IsSuccess = false;
result.Message = string.Format("主键{0}值为空", item);
return(result);
}
if (count == 1)
{
result.Sql = string.Format("{2} where {0}={1}{0} ", item, config.Flag, result.Sql);
}
else
{
result.Sql = string.Format("{2} and {0}={1}{0} ", item, config.Flag, result.Sql);
}
var temp = DbProviderFactories.GetFactory(config).CreateParameter();
temp.ParameterName = item;
temp.Value = itemValue == null ? DBNull.Value : itemValue;
result.Param.Add(temp);
count++;
}
result.IsSuccess = true;
return(result);
}
catch (Exception ex)
{
Task.Run(() =>
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException <T>(config, ex, "UpdateToSql<T>", result.Sql);
}
else
{
DbLog.LogException <T>(config.IsOutError, config.DbType, ex, "UpdateToSql<T>", result.Sql);
}
});
result.IsSuccess = false;
return(result);
}
}
/// <summary>
/// 返回分页DataReader
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbType"></param>
/// <param name="cmd"></param>
/// <param name="pModel"></param>
/// <param name="where"></param>
/// <returns></returns>
public static DbDataReader ToPageDataReader(DataQuery item, DbCommand cmd, PageModel pModel, ref string sql, FilterType type)
{
try
{
var table = new StringBuilder();
var sb = new StringBuilder();
var param = new List <DbParameter>();
table.Append(item.Table[0]);
for (var i = 1; i < item.Predicate.Count; i++)
{
table.AppendFormat(" {0} on {1}", item.Table[i], item.Predicate[i].Where);
if (item.Predicate[i].Param.Count != 0)
{
param.AddRange(item.Predicate[i].Param);
}
}
if (item.Predicate[0].Param.Count != 0)
{
param.AddRange(item.Predicate[0].Param);
}
if (item.Config.DbType == DataDbType.SqlServer)
{
#region sqlserver
var orderByLenght = item.Predicate[0].Where.IndexOf("order by");
sb.AppendFormat(@"select top {0} * from (select row_number()over({5})temprownumber,*
from (select tempcolumn=0,{3} from {1} where {4})t)tt where temprownumber>={2}"
, pModel.PageSize
, table
, pModel.StarId - 1
, string.Join(",", item.Field)
, orderByLenght == -1 ? item.Predicate[0].Where : item.Predicate[0].Where.Substring(0, orderByLenght)
, orderByLenght == -1 ? "order by tempcolumn" : item.Predicate[0].Where.Substring(orderByLenght, item.Predicate[0].Where.Length - orderByLenght));
#endregion
}
else if (item.Config.DbType == DataDbType.Oracle)
{
#region oracle
sb = new StringBuilder();
if (item.Predicate.Count > 0)
{
sb.AppendFormat("select * from(select field.*,ROWNUM RN from(select {0} from {1} where {2}) field where rownum<={3}) where rn>={4}"
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where
, pModel.EndId
, pModel.StarId);
}
else
{
sb.AppendFormat(@"select * from {3}
where rowid in(select rid from
(select rownum rn,rid from
(select rowid rid from {3})
where rownum<={0}) where rn>{1}) and {4}"
, pModel.EndId.ToString()
, (pModel.StarId - 1).ToString()
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where);
}
#endregion
}
else if (item.Config.DbType == DataDbType.MySql)
{
#region MySql
sb.AppendFormat("select {2} from {3} where {4} limit {0}, {1}"
, pModel.StarId
, pModel.PageSize
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where);
#endregion
}
else if (item.Config.DbType == DataDbType.DB2)
{
#region DB2
var orderByLenght = item.Predicate[0].Where.IndexOf("order by");
sb.AppendFormat("select * from (select row_number() over ({5}) as row_number,{2} from {3} where {4}) a where a.row_number>{0} and row_number<{1} "
, pModel.StarId
, pModel.EndId
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where == "" ? "" : item.Predicate[0].Where
, orderByLenght == -1 ? "" : item.Predicate[0].Where.Substring(orderByLenght, item.Predicate[0].Where.Length - orderByLenght));
#endregion
}
else if (item.Config.DbType == DataDbType.SQLite)
{
#region sqlite
sb.AppendFormat("select {2} from {3} where {4} limit {1} offset {0}"
, pModel.StarId
, pModel.PageSize
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where);
#endregion
}
else if (item.Config.DbType == DataDbType.PostgreSql)
{
#region PostgreSql
sb.AppendFormat("select {2} from {3} where {4} limit {1} offset {0}"
, pModel.StarId
, pModel.PageSize
, string.Join(",", item.Field)
, table
, item.Predicate[0].Where);
#endregion
}
if (item.IsFilter)
{
BaseFilter.Filter(param, type, item.TableName, item.Config, sb);
}
if (param.Count != 0)
{
cmd.Parameters.AddRange(param.ToArray());
}
cmd.CommandText = sb.ToString();
sql = string.Format("count:{0},page:{1}", sql, ParameterToSql.ObjectParamToSql(param, sb.ToString(), item.Config));
return(cmd.ExecuteReader());
}
catch (Exception ex)
{
if (item.Config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(item.Config, ex, "ToPageDataReader", "");
}
else
{
DbLog.LogException(true, item.Config.DbType, ex, "ToPageDataReader", "");
}
return(null);
}
}
/// <summary>
/// 返回分页DataReader sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dbType"></param>
/// <param name="cmd"></param>
/// <param name="pModel"></param>
/// <param name="where"></param>
/// <returns></returns>
public static DbDataReader ToPageDataReaderSql(DbParameter[] param, DbCommand cmd, PageModel pModel, string sql, ConfigModel config, ref string tempSql, FilterType type, string tableName)
{
try
{
var table = new List <string>();
if (tableName != null)
{
table.Add(tableName);
}
if (config.DbType == DataDbType.Oracle)
{
sql = string.Format("select * from(select field.*,ROWNUM RN from({0}) field where rownum<={1}) where rn>={2}"
, sql, pModel.EndId, pModel.StarId);
}
if (config.DbType == DataDbType.SqlServer)
{
sql = string.Format(@"select top {1} * from (select row_number()over(order by tempcolumn)temprownumber,*
from(select tempcolumn = 0, * from ({0})t)tt)ttt where temprownumber >= {2}"
, sql, pModel.PageSize, pModel.StarId - 1);
}
if (config.DbType == DataDbType.MySql)
{
sql = string.Format("{0} limit {1},{2}", sql, pModel.StarId, pModel.PageSize);
}
if (config.DbType == DataDbType.DB2)
{
sql = string.Format("select * from(select field.*,ROWNUM RN from({0}) field where rownum<={1}) where rn>={2}"
, sql, pModel.EndId, pModel.StarId);
}
if (config.DbType == DataDbType.PostgreSql)
{
sql = string.Format("{0} limit {1} offset {2}", sql, pModel.PageSize, pModel.StarId);
}
if (config.DbType == DataDbType.SQLite)
{
sql = string.Format("{0} limit {1} offset {2}", sql, pModel.PageSize, pModel.StarId);
}
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);
cmd.CommandText = sql;
return(cmd.ExecuteReader());
}
catch (Exception ex)
{
if (config.SqlErrorType == SqlErrorType.Db)
{
DbLogTable.LogException(config, ex, "ToPageDataReaderSql", "");
}
else
{
DbLog.LogException(config.IsOutError, config.DbType, ex, "ToPageDataReaderSql", "");
}
return(null);
}
}