private static string BuildSqlJoin(DbRow container, DbQuery.DbJoin join, List <SqlQueryParameter> sql_parameters)
        {
            if (join != null)
            {
                StringBuilder sb_join = new StringBuilder();

                if (!string.IsNullOrEmpty(join.Container))
                {
                    sb_join.AppendFormat("{0} JOIN {1} ON {2}", join.Type, join.Container, join.Condition);
                }
                else
                {
                    var parsed_join = new DbQueryToSql(container, join.Query);

                    sb_join.AppendFormat("{0} JOIN {1} ON {2}", join.Type, parsed_join.Query, join.Condition);

                    if (parsed_join.Parameters != null)
                    {
                        if (sql_parameters == null)
                        {
                            sql_parameters = new List <SqlQueryParameter>();
                        }

                        sql_parameters.AddRange(parsed_join.Parameters);
                    }
                }
                if (join.ChainedJoin != null)
                {
                    sb_join.Append(' ').Append(BuildSqlJoin(container, join.ChainedJoin, sql_parameters));
                }

                return(sb_join.ToString());
            }
            else
            {
                return(null);
            }
        }
Esempio n. 2
0
        public Type SelectSingle <Type>(DbQuery query, Func <DbRow, Type> construct_from_container) where Type : class
        {
            var sql_query = new DbQueryToSql(this, query);

            Type t = null;

            using (SqlDataReader reader = SqlQuery.Select(connection_string, sql_query.Query, CommandType.Text, CommandBehavior.CloseConnection, sql_query.Parameters))
            {
                if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        DbRow c = new DbRow(this);

                        c.LoadValuesWithoutRead(reader);

                        t = construct_from_container(c);
                    }
                }
            }

            return(t);
        }
Esempio n. 3
0
        //public static DbRow[] select_dt(DbRow container_schema, string select_statement, SqlQueryParameter[] extra_params)
        //{
        //    DataTable dt_results = SqlQuery.execute_sql(select_statement, CommandType.Text, extra_params);

        //    //return dt_results;

        //    if (dt_results.Rows.Count == 0) return null;

        //    List<DbRow> db_items = new List<DbRow>(dt_results.Rows.Count);

        //    foreach (DataRow dr_result in dt_results.Rows)
        //    {
        //        DbRow db_item = new DbRow(container_schema);

        //        db_item.LoadValues(dr_result);

        //        db_items.Add(db_item);
        //    }

        //    return db_items.ToArray();
        //}

        public static DbRow[] Select(DbRow container_schema, string select_statement, params SqlQueryParameter[] extra_params)
        {
            List <DbRow> rows = null;

            using (SqlDataReader reader = SqlQuery.Select(container_schema.connection_string, select_statement, CommandType.Text, CommandBehavior.CloseConnection, extra_params))
            {
                if (reader.HasRows)
                {
                    rows = new List <DbRow>();

                    while (reader.Read())
                    {
                        DbRow row = new DbRow(container_schema);

                        row.LoadValuesWithoutRead(reader);

                        rows.Add(row);
                    }
                }
            }

            return(rows != null?rows.ToArray() : null);
        }
Esempio n. 4
0
 public DbRow(DbRow copy_schema)
     : this(copy_schema, copy_schema.table_name)
 {
 }
Esempio n. 5
0
        public static DbRow[] Select(DbRow container_schema, string container_column_prefix, string extra_select, SqlQueryParameter[] extra_params, int top, string from_clause, string where_clause, string order_clause, int start_row, int end_row)
        {
            bool is_paged = start_row > 0 && end_row > 0;

            StringBuilder sb_columns = new StringBuilder();

            if (top > 0 && !is_paged)
            {
                sb_columns.AppendFormat("TOP {0} ", top);
            }

            if (container_schema != null)
            {
                bool first_time = true;

                foreach (string key in container_schema.properties.Keys)
                {
                    DbRowValue current_field = container_schema.properties[key];

                    if (current_field.is_managed)
                    {
                        if (!first_time)
                        {
                            sb_columns.Append(',');
                        }
                        else
                        {
                            first_time = false;
                        }

                        if (!string.IsNullOrEmpty(container_column_prefix))
                        {
                            sb_columns.Append(container_column_prefix);
                            sb_columns.Append('.');
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(current_field.table_name))
                            {
                                sb_columns.Append(current_field.table_name);
                                sb_columns.Append('.');
                            }
                            else if (!string.IsNullOrEmpty(container_schema.table_name))
                            {
                                sb_columns.Append(container_schema.table_name);
                                sb_columns.Append('.');
                            }

                            sb_columns.Append(key);
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(extra_select))
            {
                if (sb_columns.Length > 0)
                {
                    sb_columns.Append(',');
                }
                sb_columns.Append(extra_select);
            }

            if (!string.IsNullOrEmpty(order_clause))
            {
                order_clause = string.Format("ORDER BY {0}", order_clause);
            }

            string select_statement;

            if (is_paged)
            {
                select_statement = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS RowNumber, {1} FROM {2} WHERE {3}) AS PagedView WHERE RowNumber BETWEEN {4} AND {5}",
                                                 order_clause,
                                                 sb_columns.ToString(),
                                                 from_clause,
                                                 where_clause,
                                                 start_row,
                                                 end_row);
            }
            else
            {
                select_statement = string.Format("SELECT {0} FROM {1} WHERE {2} {3}", sb_columns.ToString(), from_clause, where_clause, order_clause);
            }

            return(Select(container_schema, select_statement, extra_params));
        }
Esempio n. 6
0
 public static DbRow[] Select(DbRow container_schema, string extra_select, int top, string from_clause, string where_clause, string order_clause)
 {
     return(Select(container_schema, null, extra_select, null, top, from_clause, where_clause, order_clause, -1, -1));
 }
        private void BuildSqlQuery(DbRow container, DbQuery query)
        {
            if (string.IsNullOrEmpty(query.Sql))
            {
                string select_columns = query.Columns;
                string where = query.Where;
                string from            = query.From;
                string container_alias = query.ContainerAlias;
                string group_by        = query.GroupBy;
                string order_by        = query.OrderBy;
                string top             = query.Top;

                int starting_row = query.StartingRow;
                int ending_row   = query.EndingRow;

                if (string.IsNullOrEmpty(select_columns))
                {
                    select_columns = DbRow.GetSelectColumns(container, container_alias);
                }
                if (string.IsNullOrEmpty(from))
                {
                    from = container.GetContainerName();
                }

                if (!string.IsNullOrEmpty(container_alias))
                {
                    from = string.Format("{0} {1}", from, container_alias);
                }

                if (!string.IsNullOrEmpty(where))
                {
                    where = string.Format("WHERE {0}", where);
                }
                if (!string.IsNullOrEmpty(group_by))
                {
                    group_by = string.Format("GROUP BY {0}", group_by);
                }
                if (!string.IsNullOrEmpty(order_by))
                {
                    order_by = string.Format("ORDER BY {0}", order_by);
                }

                List <SqlQueryParameter> sql_parameters = new List <SqlQueryParameter>();

                if (query.Parameters != null)
                {
                    sql_parameters.AddRange(query.Parameters);
                }

                string join = BuildSqlJoin(container, query.Join, sql_parameters);

                string select_statement;

                if (starting_row != 0 || ending_row != 0)
                {
                    if (starting_row < 1)
                    {
                        throw new Exception("Starting row must be greater than 0");
                    }
                    if (ending_row < starting_row)
                    {
                        throw new Exception("Ending row must be >= the starting row");
                    }

                    select_statement = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS RowNumber, {1} FROM {2} {3} {4}) AS PagedView WHERE RowNumber BETWEEN {5} AND {6}",
                                                     order_by,
                                                     select_columns,
                                                     from,
                                                     join,
                                                     where,
                                                     starting_row,
                                                     ending_row);
                }
                else
                {
                    select_statement = string.Format("SELECT {0} {1} FROM {2} {3} {4} {5} {6}",
                                                     top,
                                                     select_columns,
                                                     from,
                                                     join,
                                                     where,
                                                     group_by,
                                                     order_by);
                }

                if (!string.IsNullOrEmpty(query.Alias))
                {
                    select_statement = string.Format("({0}) AS {1}", select_statement, query.Alias);
                }

                Query = select_statement;

                if (sql_parameters != null)
                {
                    Parameters = sql_parameters.ToArray();
                }
            }
            else
            {
                Query      = query.Sql;
                Parameters = query.Parameters;
            }
        }
 public DbQueryToSql(DbRow container, DbQuery query)
 {
     BuildSqlQuery(container, query);
 }
Esempio n. 9
0
 public DbColumn(DbRow container, string field_id)
     : this(container, field_id, default(FieldType))
 {
 }
Esempio n. 10
0
 public DbColumn(DbRow container, string field_id, FieldType default_value)
 {
     this.field_id      = field_id;
     this.default_value = default_value;
     this.container     = container;
 }