示例#1
0
        public static async Task <Dictionary <int, Dictionary <string, object> > > GetColumns(BitcornContext dbContext, string[] columns, int[] primaryKeys, UserReflectionContext context = null)
        {
            if (context == null)
            {
                if (_StandardReflectionModel == null)
                {
                    _StandardReflectionModel = new UserReflectionContext(UserModel);
                }

                _StandardReflectionModel.CacheReflection();
                context = _StandardReflectionModel;
            }
            else
            {
                context.CacheReflection();
            }

            //CacheReflection();
            if (columns == null)
            {
                var output = new Dictionary <int, Dictionary <string, object> >();
                for (int i = 0; i < primaryKeys.Length; i++)
                {
                    output.Add(primaryKeys[i], new Dictionary <string, object>());
                }

                return(output);
            }
            string[] validColumns;
            if (columns.Length == 1 && columns[0] == "*")
            {
                validColumns = context.TableColumns.SelectMany(u => u.Value).Distinct().ToArray();
            }
            else
            {
                var    list      = columns.Where(c => context.ColumnToTable.ContainsKey(c.ToLower())).Distinct().ToList();
                string userIdKey = nameof(User.UserId).ToLower();
                if (!list.Select(c => c.ToLower()).Contains(userIdKey))
                {
                    list.Add(userIdKey);
                }
                validColumns = list.ToArray();
            }
            if (validColumns.Length > 0)
            {
                string sql = GenerateSql(context, validColumns, primaryKeys);
                return(await RawSqlQuery(dbContext, sql));
            }
            return(null);
        }
示例#2
0
        static string GenerateSql(UserReflectionContext context, string[] columns, int[] primaryKeyValues)
        {
            StringBuilder sql          = new StringBuilder();
            List <string> uniqueTables = new List <string>();

            sql.Append("SELECT ");
            for (int i = 0; i < columns.Length; i++)
            {
                if (i > 0)
                {
                    sql.Append(',');
                }
                string column = columns[i];
                string table  = context.ColumnToTable[column.ToLower()].Name;
                if (!uniqueTables.Contains(table))
                {
                    uniqueTables.Add(table);
                }
                sql.Append('[');
                sql.Append(table);
                sql.Append("].[");
                sql.Append(column);
                sql.Append("] ");
            }
            var firstTable       = uniqueTables[0];
            var selectFirstTable = "[" + firstTable + "]";

            sql.Append(" FROM ");
            sql.Append(selectFirstTable);
            sql.Append(' ');
            string primaryKeyName = nameof(User.UserId);

            if (uniqueTables.Count > 1)
            {
                for (int i = 1; i < uniqueTables.Count; i++)
                {
                    sql.Append(" JOIN ");
                    string selfSelect = "[" + uniqueTables[i] + "]";

                    sql.Append(selfSelect);
                    sql.Append(" on ");
                    sql.Append(selectFirstTable);
                    sql.Append('.');
                    sql.Append(primaryKeyName);
                    sql.Append('=');
                    sql.Append(selfSelect);
                    sql.Append('.');
                    sql.Append(primaryKeyName);
                    sql.Append(' ');
                }
            }
            sql.Append(" Where ");
            sql.Append(selectFirstTable);
            sql.Append('.');
            sql.Append(primaryKeyName);
            if (primaryKeyValues.Length == 1)
            {
                sql.Append("=");
                sql.Append(primaryKeyValues[0]);
            }
            else
            {
                sql.Append(" in (");
                sql.Append(string.Join(',', primaryKeyValues));
                sql.Append(")");
            }
            return(sql.ToString());
        }