public string BuildCommand(ConnectorBase connection)
{
if (this.QueryMode == QueryMode.ExecuteStoredProcedure || this.QueryMode == QueryMode.None) return string.Empty;
StringBuilder sb = new StringBuilder();
bool ownsConnection = false;
if (connection == null)
{
ownsConnection = true;
connection = ConnectorBase.NewInstance();
}
try
{
bool bFirst;
if (this.QueryMode != QueryMode.None)
{
switch (this.QueryMode)
{
case QueryMode.Select:
{
if (Offset > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSSQL &&
!connection.SupportsSelectPaging())
{
// Special case for Sql Server where in versions prior to 2012 there was no paging support
BuildSelectForMsSqlPaging(sb, connection);
}
else if (Offset > 0 && Limit > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
// Special case for Ms Access where paging is not supported so we do a complex emulation of LIMIT+OFFSET
BuildSelectForMsAccessLimitOffset(sb, connection);
}
else if (Offset > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
// Special case for Ms Access where paging is not supported so we do a complex emulation of OFFSET
BuildSelectForMsAccessOffset(sb, connection);
}
else
{
sb.Append(@" SELECT ");
if (IsDistinct) sb.Append(@"DISTINCT ");
if (Limit > 0 &&
(connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS ||
(connection.TYPE == ConnectorBase.SqlServiceType.MSSQL && !(connection.SupportsSelectPaging() && Offset > 0))
))
{
sb.Append(@"TOP " + Limit);
sb.Append(' ');
}
BuildSelectList(sb, connection);
sb.Append(@" FROM ");
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
}
else
{
sb.Append(@"(");
if (_FromExpression is dg.Sql.IPhrase)
{
sb.Append(((dg.Sql.IPhrase)_FromExpression).BuildPhrase(connection, this));
}
else sb.Append(_FromExpression);
sb.Append(@") ");
sb.Append(connection.WrapFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildGroupBy(sb, connection, false);
BuildHaving(sb, connection);
BuildOrderBy(sb, connection, false);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
if (Limit > 0)
{
sb.Append(@" LIMIT ");
sb.Append(Limit);
// OFFSET is not supported without LIMIT
if (Offset > 0)
{
sb.Append(@" OFFSET ");
sb.Append(Offset);
}
}
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
if (Limit > 0)
{
sb.Append(@" LIMIT ");
sb.Append(Limit);
}
if (Offset > 0)
{
sb.Append(@" OFFSET ");
sb.Append(Offset);
}
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
if (connection.SupportsSelectPaging() && Offset > 0)
{
// If we are in MsSql OFFSET/FETCH mode...
sb.Append(@" OFFSET ");
sb.Append(Offset);
sb.Append(@" ROWS");
if (Limit > 0)
{
sb.Append(@" FETCH NEXT ");
sb.Append(Limit);
sb.Append(@" ROWS ONLY");
}
}
}
// Done with select query
}
// Write out supported hints
switch (_QueryHint)
{
case QueryHint.ForUpdate:
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@" FOR UPDATE");
}
break;
case QueryHint.LockInSharedMode:
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@" LOCK IN SHARED MODE");
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@" FOR SHARE");
}
break;
}
}
break;
case QueryMode.Insert:
{
sb.Append(@"INSERT INTO ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
sb.Append(connection.WrapFieldName(ins.ColumnName));
}
if (InsertExpression != null)
{
sb.Append(@") ");
sb.Append(InsertExpression);
}
else
{
sb.Append(@") VALUES (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (ins.SecondType == ValueObjectType.Literal)
{
sb.Append(ins.Second);
}
else if (ins.SecondType == ValueObjectType.Value)
{
if (ins.Second is Query)
{
sb.Append('(');
sb.Append(((Query)ins.Second).BuildCommand(connection));
sb.Append(')');
}
else
{
Query.PrepareColumnValue(Schema.Columns.Find(ins.ColumnName), ins.Second, sb, connection, this);
}
}
else if (ins.SecondType == ValueObjectType.ColumnName)
{
if (ins.SecondTableName != null)
{
sb.Append(connection.WrapFieldName(ins.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.WrapFieldName(ins.Second.ToString()));
}
}
sb.Append(@")");
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
}
}
break;
case QueryMode.Update:
{
bool hasJoins = _ListJoin != null && _ListJoin.Count > 0;
sb.Append(@"UPDATE ");
if (hasJoins &&
(connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS) &&
_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(connection.WrapFieldName(_FromExpressionTableAlias));
}
else
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
if (_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(' ');
sb.Append(connection.WrapFieldName(_FromExpressionTableAlias));
}
}
if (hasJoins)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
BuildJoin(sb, connection);
}
}
bFirst = true;
foreach (AssignmentColumn upd in _ListInsertUpdate)
{
if (bFirst)
{
sb.Append(@" SET ");
bFirst = false;
}
else sb.Append(',');
sb.Append(connection.WrapFieldName(upd.ColumnName));
sb.Append('=');
if (upd.SecondType == ValueObjectType.Literal)
{
sb.Append(upd.Second);
}
else if (upd.SecondType == ValueObjectType.Value)
{
Query.PrepareColumnValue(Schema.Columns.Find(upd.ColumnName), upd.Second, sb, connection, this);
}
else if (upd.SecondType == ValueObjectType.ColumnName)
{
if (upd.SecondTableName != null)
{
sb.Append(connection.WrapFieldName(upd.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.WrapFieldName(upd.Second.ToString()));
}
}
if (hasJoins)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
sb.Append(@" FROM ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
if (_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(' ');
sb.Append(connection.WrapFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
}
}
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildOrderBy(sb, connection, false);
}
break;
case QueryMode.InsertOrUpdate:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@"REPLACE INTO ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
sb.Append(connection.WrapFieldName(ins.ColumnName));
}
if (InsertExpression != null)
{
sb.Append(@") ");
sb.Append(InsertExpression);
}
else
{
sb.Append(@") VALUES (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (ins.SecondType == ValueObjectType.Literal)
{
sb.Append(ins.Second);
}
else if (ins.SecondType == ValueObjectType.Value)
{
if (ins.Second is Query)
{
sb.Append('(');
sb.Append(((Query)ins.Second).BuildCommand(connection));
sb.Append(')');
}
else
{
Query.PrepareColumnValue(Schema.Columns.Find(ins.ColumnName), ins.Second, sb, connection, this);
}
}
else if (ins.SecondType == ValueObjectType.ColumnName)
{
if (ins.SecondTableName != null)
{
sb.Append(connection.WrapFieldName(ins.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.WrapFieldName(ins.Second.ToString()));
}
}
sb.Append(@")");
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
}
}
else
{
_NeedTransaction = true;
if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
throw new NotImplementedException(@"This operation is not implemented for PostgreSQL due to database limitations.");
}
// MSSQL
QueryMode qm = this.QueryMode;
this.QueryMode = QueryMode.Update;
sb.Append(BuildCommand(connection));
sb.Append(@"; IF @@rowcount = 0 BEGIN ");
this.QueryMode = QueryMode.Insert;
sb.Append(BuildCommand(connection));
sb.Append(@"; END");
this.QueryMode = qm;
}
}
break;
case QueryMode.Delete:
{
sb.Append(@"DELETE");
if (_ListJoin != null && _ListJoin.Count > 0)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
}
sb.Append(@" FROM ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildOrderBy(sb, connection, false);
}
break;
case QueryMode.CreateTable:
{
sb.Append(@"CREATE TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append('(');
int iPrimaryKeys = 0;
bool bSep = false;
foreach (TableSchema.Column col in Schema.Columns)
{
if (col.IsPrimaryKey) iPrimaryKeys++;
if (bSep) sb.Append(@", "); else bSep = true;
BuildColumnProperties(sb, connection, col, false);
}
if (iPrimaryKeys > 0)
{
if (bSep) sb.Append(@", ");
sb.AppendFormat(@"CONSTRAINT {0} PRIMARY KEY(", connection.WrapFieldName(@"PK_" + _SchemaName));
bSep = false;
foreach (TableSchema.Column col in Schema.Columns)
{
if (!col.IsPrimaryKey) continue;
if (bSep) sb.Append(@", "); else bSep = true;
if (col.IsPrimaryKey) sb.Append(connection.WrapFieldName(col.Name));
}
bSep = true;
sb.Append(')');
}
sb.Append(')');
if (Schema.TableOptions != null && Schema.TableOptions.Count > 0)
{
foreach (KeyValuePair<string, string> option in Schema.TableOptions)
{
sb.Append(' ');
sb.Append(option.Key);
sb.Append('=');
sb.Append(option.Value);
}
}
}
break;
case QueryMode.CreateIndex:
BuildCreateIndex(sb, connection, _CreateIndexObject);
break;
case QueryMode.CreateIndexes:
{
if ((Schema.Indexes.Count + Schema.ForeignKeys.Count) > 1)
{
_NeedTransaction = true;
}
foreach (TableSchema.Index index in Schema.Indexes)
{
BuildCreateIndex(sb, connection, index);
sb.Append(@";");
}
foreach (TableSchema.ForeignKey foreignKey in Schema.ForeignKeys)
{
BuildCreateIndex(sb, connection, foreignKey);
sb.Append(@";");
}
}
break;
case QueryMode.AddColumn:
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" ADD ");
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL || connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"COLUMN ");
}
BuildColumnProperties(sb, connection, _AlterColumn, false);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
int idx = Schema.Columns.IndexOf(_AlterColumn);
if (idx == 0) sb.Append(@"FIRST ");
else sb.AppendFormat(@"AFTER {0} ", connection.WrapFieldName(Schema.Columns[idx - 1].Name));
}
}
break;
case QueryMode.ChangeColumn:
{
if (_AlterColumnOldName != null && _AlterColumnOldName.Length == 0) _AlterColumnOldName = null;
if (_AlterColumnOldName != null)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
sb.Append(@"EXEC sp_rename ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append('.');
sb.Append(connection.WrapFieldName(_AlterColumnOldName));
sb.Append(',');
sb.Append(connection.WrapFieldName(_AlterColumn.Name));
sb.Append(@",'COLUMN';");
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" RENAME COLUMN ");
sb.Append(connection.WrapFieldName(_AlterColumnOldName));
sb.Append(@" TO ");
sb.Append(connection.WrapFieldName(_AlterColumn.Name));
sb.Append(';');
}
}
if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
// Very limited syntax, will have to do this with several statements
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
string alterColumnStatement = @" ALTER COLUMN ";
alterColumnStatement += connection.WrapFieldName(_AlterColumn.Name);
sb.Append(alterColumnStatement);
sb.Append(@" TYPE ");
bool isTextField; // UNUSED HERE
BuildColumnPropertiesDataType(sb, connection, _AlterColumn, out isTextField);
sb.Append(',');
sb.Append(alterColumnStatement);
sb.Append(_AlterColumn.Nullable ? @" DROP NOT NULL;" : @" SET NOT NULL;");
sb.Append(alterColumnStatement);
sb.Append(@" SET DEFAULT ");
Query.PrepareColumnValue(_AlterColumn, _AlterColumn.Default, sb, connection, this);
sb.Append(';');
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(' ');
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.AppendFormat(@"CHANGE {0} ", connection.WrapFieldName(_AlterColumnOldName != null ? _AlterColumnOldName : _AlterColumn.Name));
}
else
{
sb.Append(@"ALTER COLUMN ");
}
BuildColumnProperties(sb, connection, _AlterColumn, connection.TYPE == ConnectorBase.SqlServiceType.MSSQL);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
int idx = Schema.Columns.IndexOf(_AlterColumn);
if (idx == 0) sb.Append(@"FIRST ");
else sb.AppendFormat(@"AFTER {0} ", connection.WrapFieldName(Schema.Columns[idx - 1].Name));
}
}
}
break;
case QueryMode.DropColumn:
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP COLUMN ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
break;
case QueryMode.DropForeignKey:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP FOREIGN KEY ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
}
break;
case QueryMode.DropIndex:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL || connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP INDEX ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.WrapFieldName(_DropColumnName));
}
}
break;
case QueryMode.DropTable:
{
sb.Append(@"DROP TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
}
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (ownsConnection && connection != null)
{
connection.Close();
connection.Dispose();
connection = null;
}
}
return sb.ToString();
}
