Пример #1
0
        /// <summary>
        /// order by 1个表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="field"></param>
        /// <param name="config"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public static List <string> OrderBy <T, T1>(Expression <Func <T, T1, object> > field, ConfigModel config, bool isDesc = true)
        {
            try
            {
                var result = new List <string>();

                (field.Body as NewExpression).Arguments.ToList().ForEach(a =>
                {
                    var asName = ((a as MemberExpression).Expression as ParameterExpression).Name;
                    result.Add(string.Format("{0}.{1} {2}", asName, (a as MemberExpression).Member.Name, isDesc ? "desc" : "asc"));
                });

                return(result);
            }
            catch (Exception ex)
            {
                if (config.SqlErrorType == SqlErrorType.Db)
                {
                    DbLogTable.LogException <T>(config, ex, "OrderBy<T,T1>", "");
                }
                else
                {
                    DbLog.LogException(config.IsOutError, config.DbType, ex, "OrderBy<T,T1>", "");
                }

                return(new List <string>());
            }
        }
Пример #2
0
        /// <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);
            }
        }
Пример #3
0
        /// <summary>
        /// group by 1个表
        /// </summary>
        /// <param name="field"></param>
        /// <param name="isDesc"></param>
        /// <returns></returns>
        public static List <string> GroupBy <T>(Expression <Func <T, object> > field, ConfigModel config)
        {
            try
            {
                var result = new List <string>();

                (field.Body as NewExpression).Arguments.ToList().ForEach(a => {
                    var asName = ((a as MemberExpression).Expression as ParameterExpression).Name;
                    result.Add(string.Format("{0}.{1}", asName, (a as MemberExpression).Member.Name));
                });

                return(result);
            }
            catch (Exception ex)
            {
                Task.Run(() =>
                {
                    if (config.SqlErrorType == SqlErrorType.Db)
                    {
                        DbLogTable.LogException <T>(config, ex, "GroupBy<T>", "");
                    }
                    else
                    {
                        DbLog.LogException(config.IsOutError, config.DbType, ex, "GroupBy<T>", "");
                    }
                });

                return(new List <string>());
            }
        }
Пример #4
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);
            }
        }
Пример #5
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);
            }
        }
Пример #6
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);
            }
        }
Пример #7
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
                    }
                });
            }
        }
Пример #8
0
        /// <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);
            }
        }
Пример #9
0
        /// <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("&lt;", "<").Replace("&gt", ">"));
                                }
                                #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("&lt;", "<").Replace("&gt", ">"));
                                        }
                                        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("&lt;", "<").Replace("&gt", ">"));

                            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);
            }
        }
Пример #10
0
        /// <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);
            }
        }
Пример #11
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);
            }
        }
Пример #12
0
        /// <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);
            }
        }
Пример #13
0
        /// <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);
            }
        }
Пример #14
0
        /// <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);
            }
        }
Пример #15
0
        /// <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);
            }
        }
Пример #16
0
        /// <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);
            }
        }