예제 #1
0
        /// <summary>
        /// 生成查询主键的SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        internal string GenerateSelectPkSql <T>(string where = "")
        {
            if (!string.IsNullOrEmpty(where))
            {
                if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = " WHERE " + where;
                }
            }
            var selectSql = new StringBuilder("SELECT ");
            List <FieldProperty> fpmap       = FieldProperty.GetFieldPropertys <T>();
            List <FieldProperty> tables      = FieldProperty.GetTables <T>();
            FieldProperty        masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));

            var ps = typeof(T).GetProperties();
            var pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);

            if (pk == null)
            {
                return(string.Empty);
            }
            selectSql.AppendFormat("{0}.{1}", masterTable.TableAlias, pk.Name);
            //selectSql.Append(pk.Name);
            selectSql.AppendLine();
            selectSql.AppendLine(" FROM ");
            selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
            selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);

            foreach (FieldProperty item in tables)
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
                            .Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
                            .Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
                }
                if (item.TableAlias == masterTable.TableAlias)
                {
                    continue;
                }
                selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
                selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
                                       item.TableAlias, item.RelateField);
                selectSql.AppendLine();
            }
            string strSql = selectSql + where;

            return(strSql);
        }
예제 #2
0
        /// <summary>
        ///     生成查询数据记录数的Sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <returns></returns>
        internal string GenerateCountSql <T>(string where = "")
        {
            if (!string.IsNullOrEmpty(where))
            {
                if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = " WHERE " + where;
                }
            }
            var selectSql = new StringBuilder("SELECT COUNT(1)  ");
            ICollection <FieldProperty> fpmap  = FieldProperty.GetFieldPropertys <T>();
            ICollection <FieldProperty> tables = FieldProperty.GetTables <T>();
            FieldProperty masterTable          = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));


            selectSql.AppendLine(" FROM ");
            string leftBrackets = "".PadLeft(tables.Count - 1, '(');

            selectSql.Append(" " + leftBrackets);
            selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);

            foreach (FieldProperty item in tables)
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
                            .Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".");
                }
                if (item.TableAlias == masterTable.TableAlias)
                {
                    continue;
                }
                selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
                selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
                                       item.TableAlias, item.RelateField);
                selectSql.AppendLine();
            }
            string strSql = selectSql + where;

            return(strSql);
        }
예제 #3
0
        //去除重复记录,保证表和主表关联字段的组合只有一个
        //得到的结果就是要关联的表
        private static List <FieldProperty> Distinct(List <FieldProperty> list)
        {
            var result = new FieldProperty[list.Count];

            list.CopyTo(result);

            List <FieldProperty> resultList = result.ToList();

            for (int i = 0; i < resultList.Count; i++)
            {
                for (int j = i + 1; j < resultList.Count; j++)
                {
                    if (resultList[i].TableName == resultList[j].TableName &&
                        resultList[i].MasterTableField == resultList[j].MasterTableField)
                    {
                        resultList.RemoveAt(j);
                        j--;
                    }
                }
            }
            return(resultList);
        }
예제 #4
0
        /// <summary>
        ///     生成查询语句
        /// </summary>
        /// <returns></returns>
        internal string GenerateSelectSql(Type type, string where = "")
        {
            if (!string.IsNullOrEmpty(where))
            {
                if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
                {
                    where = " WHERE " + where;
                }
            }
            var selectSql = new StringBuilder("SELECT ");
            List <FieldProperty> fpmap       = FieldProperty.GetFieldPropertys(type);
            List <FieldProperty> tables      = FieldProperty.GetTables(type);
            FieldProperty        masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));

            foreach (FieldProperty item in fpmap)
            {
                if (string.IsNullOrEmpty(item.FieldAlias))
                {
                    selectSql.AppendFormat("{0}.{1},", item.TableAlias, item.FieldName);
                }
                else
                {
                    selectSql.AppendFormat("{0}.{1} {2},", item.TableAlias, item.FieldName, item.FieldAlias);
                }
            }

            //增加扩展字段
            //Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            foreach (PropertyInfo info in propertyInfos)
            {
                ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
                if (extended != null)
                {
                    var extSql = extended.ExtendedSql;
                    foreach (FieldProperty item in tables)
                    {
                        extSql = extSql.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
                                 .Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
                                 .Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
                    }
                    selectSql.Append("(" + extSql + ") " + info.Name + ",");
                }
            }

            selectSql = selectSql.Remove(selectSql.Length - 1, 1);
            selectSql.AppendLine();
            selectSql.AppendLine(" FROM ");
            selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
            selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);

            foreach (FieldProperty item in tables)
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
                            .Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
                            .Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
                }
                if (item.TableAlias == masterTable.TableAlias)
                {
                    continue;
                }
                selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
                selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
                                       item.TableAlias, item.RelateField);
                selectSql.AppendLine();
            }
            string strSql = selectSql + where;

            return(strSql);
        }
예제 #5
0
        internal static List <FieldProperty> GetFieldPropertys(Type t)
        {
            var fplist = new List <FieldProperty>();
            TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t);

            PropertyInfo[] ps = t.GetProperties();

            #region 取出映射关系

            foreach (PropertyInfo item in ps)
            {
                var fp = new FieldProperty();
                var excludeFieldAttribute = (ExcludeFieldAttribute)Attribute.GetCustomAttribute(item, typeof(ExcludeFieldAttribute));
                if (excludeFieldAttribute != null)
                {
                    continue;
                }

                ExtendedAttribute extendedAttribute = ExtendedAttribute.GetAttribute(item);
                if (extendedAttribute != null)
                {
                    continue;
                }

                RefFieldAttribute fieldattr = RefFieldAttribute.GetAttribute(item);
                if (fieldattr != null)
                {
                    fp.TableName        = fieldattr.RefTableName;
                    fp.FieldName        = fieldattr.RefFieldName ?? item.Name;
                    fp.FieldAlias       = item.Name;
                    fp.PropertyName     = item.Name;
                    fp.MasterTableField = fieldattr.MasterTableField;
                    fp.RelateField      = fieldattr.RefTableKey;
                    fplist.Add(fp);
                }
                else
                {
                    fp.TableName    = tableInfo == null ? t.Name : tableInfo.TableName;
                    fp.FieldName    = item.Name;
                    fp.PropertyName = item.Name;
                    fplist.Add(fp);
                }
            }

            #endregion

            List <FieldProperty> tables = Distinct(fplist);
            for (int i = 0; i < tables.Count; i++)
            {
                string alias = "t" + i;
                foreach (FieldProperty item in fplist)
                {
                    if (item.TableName == tables[i].TableName && item.MasterTableField == tables[i].MasterTableField)
                    {
                        item.TableAlias = alias;
                        //if (string.IsNullOrEmpty(item.FieldAlias))
                        //    item.FieldAlias = alias + "_" + item.FieldName;
                    }
                }
            }
            return(fplist);
        }