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);
}
}
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);
}
//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);
}
public DbRow(DbRow copy_schema)
: this(copy_schema, copy_schema.table_name)
{
}
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));
}
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);
}
public DbColumn(DbRow container, string field_id)
: this(container, field_id, default(FieldType))
{
}
public DbColumn(DbRow container, string field_id, FieldType default_value)
{
this.field_id = field_id;
this.default_value = default_value;
this.container = container;
}