public override ISQLBuilder SelectValues(IEnumerable <object> values, string whereClause, params object[] whereClauseFormatArgs) { if (!values.Any()) { throw new ArgumentException("values is empty", "values"); } Emit("SELECT "); foreach (var value in values.WithDescriptions()) { if (value.Index > 0) { Emit(", "); } if (value.Item == null) { Emit("NULL"); } else if (value.Item is ColumnValue) { var columnValue = value.Item as ColumnValue; Emit("{0} AS ", columnValue.Value, SQLBuilderCommand.ColumnName(columnValue.ColumnName)); } else { Literal(value.Item); } } Emit(" FROM RDB$DATABASE"); if (!string.IsNullOrEmpty(whereClause)) { Emit(" WHERE ").Emit(whereClause, whereClauseFormatArgs); } return(this); }
public static long GetMaxID(this IDAC dac, string tableName, string idColName) { return(dac.ExecuteScalar <long>( dac.QuickString( "SELECT MAX(T.ID) FROM (SELECT {0} ID FROM {1} WHERE ID IS NOT NULL UNION SELECT 0 ID ) T", SQLBuilderCommand.ColumnName(idColName), SQLBuilderCommand.TableName(tableName) ) )); }
public override ISQLBuilder VariableName(string variableName) { if (!_variableDeclarations.ContainsKey(variableName)) { throw new SoftwareException("A variable with name '{0}' has not been declared", variableName); } var variable = _variableDeclarations[variableName]; var variableID = variable.Item1; var variableStorageClass = variable.Item2; string column; switch (variableStorageClass) { case VariableStorageClass.Integer: column = "IntValue"; break; case VariableStorageClass.DateTime: column = "DateValue"; break; case VariableStorageClass.Text: column = "TextValue"; break; case VariableStorageClass.Uuid: column = "UuidValue"; break; case VariableStorageClass.Blob: column = "BlobValue"; break; default: throw new NotImplementedException("Unknown VariableStorageClass " + variableStorageClass.ToString()); } return (Emit( "(SELECT {0} FROM {1} WHERE {2} = {3})", SQLBuilderCommand.ColumnName(column), SQLBuilderCommand.TableName(VariableTableName, TableType.Temporary), SQLBuilderCommand.ColumnName("ID"), SQLBuilderCommand.Literal(variableID) )); }
public virtual ISQLBuilder CreateTable(TableSpecification tableSpecification) { bool singularyPrimaryKey = tableSpecification.PrimaryKey.Columns.Length == 1; bool hasDeclarationsAfterColumns = !singularyPrimaryKey; Emit("{0} ", FormatCreateTable(tableSpecification.Type)) .TableName(tableSpecification.Name, tableSpecification.Type) .Emit("(") .NewLine(); this.TabRight(); foreach (var column in tableSpecification.Columns.WithDescriptions()) { Emit( "{0} {1}{2}{3}{4}", SQLBuilderCommand.ColumnName(column.Item.Name), !string.IsNullOrEmpty(column.Item.DataType) ? column.Item.DataType : ConvertTypeToSQLType(column.Item.Type), singularyPrimaryKey && column.Item.Name == tableSpecification.PrimaryKey.Columns[0] ? " PRIMARY KEY" : string.Empty, !column.Item.Nullable ? " NOT NULL" : string.Empty, (column.Description.HasFlag(EnumeratedItemDescription.Last) && !hasDeclarationsAfterColumns) ? string.Empty : ", " ) .NewLine(); } if (!singularyPrimaryKey) { Emit("PRIMARY KEY("); foreach (var primaryKeyColumn in tableSpecification.PrimaryKey.Columns.WithDescriptions()) { if (primaryKeyColumn.Description != EnumeratedItemDescription.Last) { Emit(", "); } this.ColumnName(primaryKeyColumn.Item); } } this.Untab(); Emit(")"); Emit(FormatCreateTableEnd(tableSpecification.Type)); return(EndOfStatement(SQLStatementType.DDL)); }