/// <summary>
/// Initializes a new instance of the GroupByClause class.
/// </summary>
/// <param name="selectList">Select list from which the grouping columns are extracted.</param>
/// <remarks>Very often a GROUP BY clause contains all the columns in the select list.
/// This method adds all columns found in the given select list to the GROUP BY clause.
/// Other select list items such as aggregate functions are skipped.</remarks>
public GroupByClause(SelectItemCollection selectList)
{
SelectItemCollection columns = selectList.GetByType(SqlItemType.Column);
this.groupingColumns = new DbColumnCollection();
foreach (ISqlItem columnItem in columns)
this.groupingColumns.Add((IDbColumn)columnItem.Item);
}
/// <summary>
/// Creates a new SET expression.
/// </summary>
/// <param name="expressionType">Expression type.</param>
/// <param name="column">Column which is to be changed.</param>
/// <param name="sourceColumns">Columns required by the expression to compute the new value.
/// Required to properly render the FROM clause when columns from other tables are used.
/// <b>Null</b> if the expression doesn't use any columns.</param>
/// <param name="item">SQL item that computes/contains the new value.</param>
/// <param name="itemType">Item type.</param>
internal UpdateExpression(UpdateExpressionType expressionType, IDbColumn column, DbColumnCollection sourceColumns, object item, SqlItemType itemType)
: this(item, itemType)
{
this.ExpressionType = expressionType;
this.column = column;
if (sourceColumns == null || sourceColumns.Count == 0)
this.sourceColumns = new IDbColumn[0];
else
this.sourceColumns = sourceColumns.ToArray();
}
private static DbColumnCollection CreateColumns(IDbTable targetTable)
{
int ordinal = 0;
DbColumnCollection levelAndPk = new DbColumnCollection();
IDbColumnConfiguration config = new DbColumnConfiguration(CreateLevelColumnName(targetTable), DbType.Int32, typeof(int), false, ordinal,
false, 0, 1, false, false, false, "Level", int.MinValue, int.MaxValue, false, null);
levelAndPk.Add(new SealedDbColumn(config, targetTable));
ordinal = 1;
foreach (IDbColumn pkPart in targetTable.PrimaryKey)
{
config = new DbColumnConfiguration(pkPart.ColumnName, pkPart.DbType, pkPart.DataType, pkPart.IsNullable, ordinal,
pkPart.AutoIncrement, pkPart.DefaultValue, pkPart.MaxLengthIfText, pkPart.IsPrimaryKeyPart, pkPart.IsForeignKeyPart, pkPart.IsUniqueConstraintPart, pkPart.PropertyName, pkPart.MinValue,
pkPart.MaxValue, pkPart.IsAutoGenerated, pkPart.SequenceName, pkPart.ExtendedProperties);
levelAndPk.Add(new SealedDbColumn(config, targetTable));
ordinal++;
}
return levelAndPk;
}
/// <summary>
/// Creates a new update expression that copies the value from another column.
/// </summary>
/// <param name="targetColumn">Column which is to be changed.</param>
/// <param name="sourceColumn">Source column that contains the new value.
/// May belong to same or other table which is joined through <see cref="UpdateStatement.Where"/> property.</param>
/// <returns>Expression.</returns>
public static UpdateExpression OtherColumn(IDbColumn targetColumn, IDbColumn sourceColumn)
{
DbColumnCollection sourceCols = new DbColumnCollection();
sourceCols.Add(sourceColumn);
return new UpdateExpression(UpdateExpressionType.OtherColumn, targetColumn, sourceCols, sourceColumn, SqlItemType.Column);
}
/// <summary>
/// Creates a new select statements that retrieves rows and columns from the given table.
/// </summary>
/// <param name="fromTable">Initial table in the FROM clause.</param>
/// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param>
public SelectStatement(IDbTable fromTable, DbColumnCollection selectList)
: base(fromTable)
{
this.selectList.Add(selectList);
}
/// <summary>
/// The get db column collection.
/// </summary>
/// <param name="connectionString">
/// The connection string.
/// </param>
/// <param name="databaseName">
/// The database name.
/// </param>
/// <param name="tableName">
/// The table name.
/// </param>
/// <returns>
/// The <see cref="DbColumnCollection"/>.
/// </returns>
public DbColumnCollection GetDbColumnCollection(string connectionString, string databaseName, string tableName)
{
var columnCollection = new DbColumnCollection();
using (var sqlConnection = new SqlConnection(connectionString))
{
var serverConnection = new ServerConnection(sqlConnection);
var server = new Server(serverConnection);
var database = server.Databases.Cast<Database>().FirstOrDefault(d => d.Name == databaseName);
if (database != null && database.Tables.Count > 0)
{
var table = database.Tables.Cast<Table>().FirstOrDefault(t => t.Name == tableName);
if (table != null && table.Columns.Count > 0)
{
foreach (var column in table.Columns.Cast<Column>().AsQueryable())
{
var dbColumnItem = new DbColumn();
dbColumnItem.Name = column.Name;
dbColumnItem.Description = column.ExtendedProperties.Count > 0 ? column.ExtendedProperties["MS_Description"].Value.ToString() : string.Empty;
dbColumnItem.IsPrimaryKey = column.InPrimaryKey;
dbColumnItem.IsIdentityColumn = column.Identity;
dbColumnItem.ColumnType = column.DataType.SqlDataType.ToString();
dbColumnItem.AllowEmpty = column.Nullable;
dbColumnItem.DefaultValue = column.Default;
columnCollection.Add(dbColumnItem);
}
}
}
}
return columnCollection;
}
/// <summary>
/// Initializes a new instance of the DbTable class.
/// </summary>
/// <param name="name">Table name.</param>
/// <param name="alias">Object alias. If <b>null</b> then it will be equal to table name.</param>
/// <param name="columns">Table fields.</param>
/// <param name="primaryKey">Primary key fields.</param>
/// <param name="setPrefixedColumnAliases">Specifies whether columns' aliases are prefixed with a table alias.</param>
protected DbTable(string name, string alias, DbColumnCollection columns, string[] primaryKey, bool setPrefixedColumnAliases)
{
Init(name, alias, columns, primaryKey, setPrefixedColumnAliases);
}
/// <summary>
/// Initializes object. Inherited class may set members via Init method.
/// </summary>
/// <param name="name">Table name.</param>
/// <param name="alias">Object alias. If <b>null</b> then it will be equal to the table name.</param>
/// <param name="columns">Table fields.</param>
/// <param name="primaryKey">Primary key fields.</param>
/// <param name="setPrefixedColumnAliases">Specifies whether columns' aliases are prefixed with a table alias.</param>
private void Init(string name, string alias, DbColumnCollection columns, string[] primaryKey, bool setPrefixedColumnAliases)
{
this.tableName = name;
if (alias != null)
this.alias = alias;
else
this.alias = name;
this.columns = columns;
foreach (IDbColumn column in this.columns)
column.Table = this;
this.columnAliasesArePrefixed = setPrefixedColumnAliases;
// Commented code is no logner required. Columns now always inspect their parent table, ie. IDbColumn.IDbTable.ColumnAliasesArePrefixed property,
// to check if prefixed aliases are used.
//if (setPrefixedColumnAliases)
//{
// foreach (IDbColumn column in this.columns)
// column.SetPrefixedAlias();
//}
this.primaryKeyFields = primaryKey;
}
/// <summary>
/// Initializes a new instance of the GroupByClause class.
/// </summary>
/// <param name="groupingColumns">Grouping columns.</param>
public GroupByClause(DbColumnCollection groupingColumns)
{
this.groupingColumns = groupingColumns; ;
}
/// <summary>
/// Initializes a new instance of the GroupByClause class.
/// </summary>
public GroupByClause()
{
this.groupingColumns = new DbColumnCollection();
}
