Generation of INSERT, UPDATE, DELETE statements for a given SELECT statement
Last modified:
The following code sample generates three DML statements for a given SELECT statement. The table which will be affected is determined by the presence of primary key fields for tables involved in the query: which set of PK fields is found first. It actively uses the information about query and database schema collected by Active Query Builder.
The DMLSqlGenerator class must be initialized with the QueryBuilder object holding the SELECT statement for which you want to generate DML statements. The SqlSyntaxOverrides contains the features which vary depending on the database server being used.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ActiveDatabaseSoftware.ActiveQueryBuilder;
using YourCompany.YourProjectName.Infrastructure.SqlSyntaxOvverides;
namespace YourCompany.YourProjectName.Infrastructure
{
public class DMLSqlGenerator
{
private readonly SQLBuilder _sqlBuilder;
private readonly MetadataObject _selected;
private readonly StatisticsOutputColumnList _ocs;
private readonly ISqlSyntaxOverride _syntaxOverride;
public SqlGenerator(QueryBuilder queryBuilder)
{
_sqlBuilder = queryBuilder.SQLContext.SQLBuilderExpressionForServer;
_ocs = queryBuilder.QueryStatistics.OutputColumns;
if (queryBuilder.Query.IsQueryWithUnions())
throw new Exception("Not editable query");
List mos = new List();
foreach (StatisticsOutputColumn oc in _ocs.Where(oc => oc.MetadataObject != null && !mos.Contains(oc.MetadataObject)))
mos.Add(oc.MetadataObject);
_selected = GetSelectedObject(mos);
if (_selected == null)
throw new Exception("Not editable query");
_syntaxOverride = SqlSyntaxOvverideFactory.Create(_selected.SQLContext.MetadataProvider.Connection);
}
public string GenerateSelectSql()
{
return string.Format("Select * from {0}{1}{2}", _selected.NameFull, Environment.NewLine, GenerateWhereSql("Old_"));
}
public string GenerateInsertSql()
{
StringBuilder insert = new StringBuilder();
insert.Append("INSERT INTO" + Environment.NewLine + " ");
insert.Append(_selected.NameFull);
insert.Append(Environment.NewLine);
bool writeSeparator = false;
foreach (StatisticsOutputColumn oc in _ocs)
{
MetadataField field = oc.MetadataField;
if (field == null || field.ReadOnly)
continue;
if (oc.MetadataObject == _selected)
{
insert.Append(" ");
if (writeSeparator)
insert.Append(',');
else
insert.Append("(");
insert.Append(field.GetNameSQL(_sqlBuilder));
insert.Append(Environment.NewLine);
writeSeparator = true;
}
}
insert.Append(" )" + Environment.NewLine + "VALUES" + Environment.NewLine);
writeSeparator = false;
foreach (StatisticsOutputColumn oc in _ocs)
{
MetadataField field = oc.MetadataField;
if (oc.MetadataObject == _selected && field != null && !field.ReadOnly)
{
insert.Append(' ');
insert.Append(writeSeparator ? ", " : " (");
insert.Append(_syntaxOverride.GetParamSymbol());
insert.Append(oc.FieldName);
insert.Append(Environment.NewLine);
writeSeparator = true;
}
}
insert.Append(" )");
return insert.ToString();
}
public string GenerateMassUpdateSql()
{
StringBuilder update = new StringBuilder();
update.Append("UPDATE" + Environment.NewLine);
update.Append(_selected.NameFull);
update.Append(Environment.NewLine + "SET" + Environment.NewLine);
bool writeSeparator = false;
foreach (MetadataField field in _selected.Items.Fields.Where(f => !f.ReadOnly))
{
if (!_syntaxOverride.IsEditableType(field.FieldType))
continue;
StatisticsOutputColumn oc = IsFieldSelected(_selected, field);
if (oc == null)
continue;
if (writeSeparator)
update.Append(',');
update.Append(' ');
update.Append(field.GetNameSQL(_sqlBuilder));
update.Append(" = " + _syntaxOverride.GetParamSymbol());
update.Append(oc.FieldName);
update.Append(Environment.NewLine);
writeSeparator = true;
}
return update.Append(GenerateWhereSql("Old_")).ToString();
}
public string GenerateSingleFieldUpdate()
{
StringBuilder update = new StringBuilder();
update.Append("UPDATE" + Environment.NewLine);
update.Append(_selected.NameFull);
update.Append(Environment.NewLine + "SET" + Environment.NewLine);
update.Append("__editingField = " + _syntaxOverride.GetParamSymbol() + "editingValue");
update.Append(Environment.NewLine);
return update.Append(GenerateWhereSql("Old_")).ToString();
}
public string GenerateDeleteSql()
{
StringBuilder delete = new StringBuilder();
delete.Append("DELETE FROM" + Environment.NewLine + " ");
delete.Append(_selected.NameFull);
delete.Append(Environment.NewLine);
return delete.Append(GenerateWhereSql()).ToString();
}
private string GenerateWhereSql(string prefix = "")
{
StringBuilder where = new StringBuilder();
where.Append("WHERE" + Environment.NewLine);
bool writeSeparator;
if (IsObjectHavePrimaryKey(_selected))
{
writeSeparator = false;
foreach (MetadataField field in _selected.Items.Fields)
{
if (!field.PrimaryKey)
continue;
StatisticsOutputColumn oc = IsFieldSelected(_selected, field);
where.Append(' ');
if (writeSeparator)
where.Append("AND ");
where.Append(field.GetNameSQL(_sqlBuilder));
where.Append(" = " + _syntaxOverride.GetParamSymbol() + prefix);
where.Append(oc.FieldName);
where.Append(Environment.NewLine);
writeSeparator = true;
}
}
else
{
writeSeparator = false;
foreach (StatisticsOutputColumn oc in _ocs)
{
if (oc.MetadataObject == _selected)
{
MetadataField field = oc.MetadataField;
if (!_syntaxOverride.IsEditableType(field.FieldType))
continue;
if (!SqlSyntaxOvverideFactory.Create(_selected.SQLContext.MetadataProvider.Connection).IsComparableField(field))
continue;
where.Append(' ');
if (writeSeparator)
where.Append("AND ");
where.Append(field.GetNameSQL(_sqlBuilder));
where.Append(" = " + _syntaxOverride.GetParamSymbol() + prefix);
where.Append(oc.FieldName);
where.Append(Environment.NewLine);
writeSeparator = true;
}
}
}
return where.ToString();
}
private MetadataObject GetSelectedObject(IEnumerable mos)
{
MetadataObject selected = null;
foreach (MetadataObject mo in mos)
{
selected = mo;
if (IsObjectHavePrimaryKey(mo))
{
if (mo.Items.Fields.Any(field => field.PrimaryKey && IsFieldSelected(mo, field) == null))
selected = null;
}
else
{
if (mo.Items.Fields.Any(field => IsFieldSelected(mo, field) == null))
selected = null;
}
if (selected != null)
break;
}
return selected;
}
private bool IsObjectHavePrimaryKey(MetadataObject metadataObject)
{
return metadataObject.Items.Fields.Any(field => field.PrimaryKey);
}
private StatisticsOutputColumn IsFieldSelected(MetadataObject metadata, MetadataField field)
{
return _ocs.FirstOrDefault(column => column.MetadataObject == metadata && column.MetadataField != null && _ocs.QueryPart.SQLContext.IsQualifiedNamesEqual(column.MetadataField.GetSQLQualifiedName(), field.GetSQLQualifiedName()));
}
}
}
using System.Data;
using ActiveDatabaseSoftware.ActiveQueryBuilder;
namespace YourCompanyYourProjectName.Infrastructure.SqlSyntaxOvverides
{
public interface ISqlSyntaxOverride
{
bool IsComparableField(MetadataField field);
void SetSpecifyDbType(IDbDataParameter parameter, DbType type, string fieldTypeName = "");
bool IsEditableType(DbType type);
string GetParamSymbol();
}
public class CommonSqlSyntaxOverride : ISqlSyntaxOverride
{
public bool IsComparableField(MetadataField field)
{
var commonNotComparableTypes = new System.Collections.Generic.HashSet();
var result = !commonNotComparableTypes.Contains(field.FieldTypeName);
return result;
}
public void SetSpecifyDbType(IDbDataParameter parameter, DbType type, string fieldTypename = "")
{
parameter.DbType = type;
}
public bool IsEditableType(DbType type)
{
var notEditableTypes = new System.Collections.Generic.HashSet
{
DbType.Binary,
DbType.Object
};
var result = !notEditableTypes.Contains(type);
return result;
}
public string GetParamSymbol()
{
return ":";
}
}
public class MsSqlSyntaxOverride : ISqlSyntaxOverride
{
public bool IsComparableField(MetadataField field)
{
var notComparableTypes = new System.Collections.Generic.HashSet
{
"xml"
};
return !notComparableTypes.Contains(field.FieldTypeName) && new CommonSqlSyntaxOverride().IsComparableField(field);
}
public void SetSpecifyDbType(IDbDataParameter parameter, DbType type, string fieldTypename = "")
{
switch (type)
{
case DbType.Time:
((SqlParameter)parameter).SqlDbType = SqlDbType.Time;
break;
case DbType.Date:
((SqlParameter)parameter).SqlDbType = SqlDbType.Date;
break;
default: new CommonSqlSyntaxOverride().SetSpecifyDbType(parameter, type);
break;
}
if (fieldTypename == "date")
((SqlParameter)parameter).SqlDbType = SqlDbType.Date;
}
public bool IsEditableType(DbType type)
{
return new CommonSqlSyntaxOverride().IsEditableType(type);
}
public string GetParamSymbol()
{
return "@";
}
}
}
This piece of code is taken from the FlySpeed SQL Query tool. It's performed when the tool creates a dataset and works with DevArt MyDAC TMyQuery object, but it can be easily adapted to any TQuery descendant.
procedure TForm1.OnSqlTaskCustomizeDataset(ASender:TObject; ADataset:TDataset);
function ObjectHasPrimaryKey(AMetadataObject:TacMetadataObject):Boolean;
var i:Integer;
mf:TacMetadataField;
begin
Result:=False;
for i:=0 to AMetadataObject.Fields.Count-1 do
begin
mf:=AMetadataObject.Fields[i];
Result:=mf.PrimaryKey;
if Result then Exit;
end;
end;
function IsFieldSelected(AOutputColumns:TacStatisticsOutputColumnsList; AObject:TacMetadataObject; AField:TacMetadataField):TacStatisticsOutputColumn;
var i:Integer;
begin
for i:=0 to AOutputColumns.Count-1 do
begin
Result:=AOutputColumns[i];
if (Result.MetadataObject=AObject)and(Result.MetadataField<>nil)and
(AOutputColumns.SQLContext.IsQualifiedNamesEqual(Result.MetadataField.Name,AField.Name))
then Exit;
end;
Result:=nil;
end;
function GetMetadataObjectFields(AMetadataObject: TacMetadataObject): string;
var i: integer;
list: TStringList;
begin
list := TStringList.Create;
try
for i:=0 to AMetadataObject.Fields.Count-1 do
list.Add('"'+AMetadataObject.Fields[i].NameStrNotQuoted+'"');
Result := list.CommaText;
finally
list.Free;
end;
end;
var myQ: TMyQuery;
sqlBuilder:TSQLBuilder;
ocs:TacStatisticsOutputColumnsList;
oc:TacStatisticsOutputColumn;
i:integer;
mos:TList;
mo:TacMetadataObject;
mf:TacMetadataField;
selected:TacMetadataObject;
j:integer;
where:TacSimpleStringBuilder;
update:TacSimpleStringBuilder;
delete:TacSimpleStringBuilder;
insert:TacSimpleStringBuilder;
writeSeparator:boolean;
begin
if (SqlTask.ReturnResults)and(ADataset is TMyQuery)
then begin
myQ:=TMyQuery(ADataset);
if not acQueryBuilder.Query.IsQueryWithUnions
then begin
ocs:=acQueryBuilder.QueryStatistics.OutputColumns;
sqlBuilder:=acQueryBuilder.SQLContext.SQLBuilderExpressionForServer;
mos:=TList.Create;
try
// collect all objects from output columns
// collect all primary key fields from output columns
for i:=0 to ocs.Count-1 do
begin
oc:=ocs[i];
mo:=oc.MetadataObject;
if (mo<>nil)and(mos.IndexOf(mo)=-1)
then mos.Add(oc.MetadataObject);
end;
// find first object with all primary keys selected
selected:=nil;
for i:=0 to mos.Count-1 do
begin
mo:=mos[i];
selected:=mo;
if ObjectHasPrimaryKey(mo)
then begin
// table have primary key
for j:=0 to mo.Fields.Count-1 do
begin
mf:=mo.Fields[j];
if (mf.PrimaryKey)and(IsFieldSelected(ocs,mo,mf)=nil)
then begin
selected:=nil;
Break;
end;
end;
end
else begin
// table have no primary key
for j:=0 to mo.Fields.Count-1 do
begin
mf:=mo.Fields[j];
if IsFieldSelected(ocs,mo,mf)=nil
then begin
selected:=nil;
Break;
end;
end;
end;
if selected<>nil then Break;
end;
// generate SQL
if (selected<>nil) and (selected.Fields.Count > 0)
then begin
where:=TacSimpleStringBuilder.Create;
update:=TacSimpleStringBuilder.Create;
delete:=TacSimpleStringBuilder.Create;
insert:=TacSimpleStringBuilder.Create;
try
// WHERE
where.Append('WHERE'#13#10);
if ObjectHasPrimaryKey(selected)
then begin
writeSeparator:=false;
for i:=0 to selected.Fields.Count-1 do
begin
mf:=selected.Fields[i];
if not mf.PrimaryKey then Continue;
oc:=IsFieldSelected(ocs,selected,mf);
Assert(oc<>nil);
where.Append(' ');
if writeSeparator then where.Append('AND ');
where.Append(mf.Name.SimpleSQL(sqlBuilder));
where.Append(' = :`Old_');
where.Append(oc.ColumnNameInQuery+'`');
where.Append(#13#10);
writeSeparator:=true;
end;
end
else begin
writeSeparator:=false;
for i:=0 to ocs.Count-1 do
begin
oc:=ocs[i];
if oc.MetadataObject=selected
then begin
mf:=oc.MetadataField;
Assert(mf<>nil,
'ObjectName="'+selected.FullNameStrNotQuoted+'"; OutputColumnName="'+oc.ColumnNameInQuery+
'"; Object Fields: '+GetMetadataObjectFields(oc.MetadataObject)+'.');
where.Append(' ');
if writeSeparator then where.Append('AND ');
where.Append(mf.Name.SimpleSQL(sqlBuilder));
where.Append(' = :`Old_');
where.Append(oc.ColumnNameInQuery+'`');
where.Append(#13#10);
writeSeparator:=true;
end;
end;
end;
// UPDATE
update.Append('UPDATE'#13#10' ');
update.Append(selected.FullName.SimpleSQL(sqlBuilder));
update.Append(#13#10'SET'#13#10);
writeSeparator:=false;
for i:=0 to selected.Fields.Count-1 do
begin
mf:=selected.Fields[i];
oc:=IsFieldSelected(ocs, selected, mf);
if oc<>nil
then begin
if writeSeparator then update.Append(',');
update.Append(' ');
update.Append(mf.Name.SimpleSQL(sqlBuilder));
update.Append(' = :');
update.Append('`'+oc.ColumnNameInQuery+'`');
update.Append(#13#10);
writeSeparator:=true;
end;
end;
update.Append(where.ToWideString);
// DELETE
delete.Append('DELETE FROM'#13#10' ');
delete.Append(selected.FullName.SimpleSQL(sqlBuilder));
delete.Append(#13#10);
delete.Append(where.ToWideString);
// INSERT
insert.Append('INSERT INTO'#13#10' ');
insert.Append(selected.FullName.SimpleSQL(sqlBuilder));
insert.Append(#13#10);
writeSeparator:=false;
for i:=0 to ocs.Count-1 do
begin
oc:=ocs[i];
mf:=oc.MetadataField;
if (oc.MetadataObject=selected)and(mf<>nil)
then begin
insert.Append(' ');
if writeSeparator then insert.Append(',') else insert.Append('(');
insert.Append(mf.Name.SimpleSQL(sqlBuilder));
insert.Append(#13#10);
writeSeparator:=true;
end;
end;
insert.Append(' )'#13#10'VALUES'#13#10);
writeSeparator:=false;
for i:=0 to ocs.Count-1 do
begin
oc:=ocs[i];
mf:=oc.MetadataField;
if (oc.MetadataObject=selected)and(mf<>nil)
then begin
insert.Append(' ');
if writeSeparator then insert.Append(',') else insert.Append('(');
insert.Append(':');
insert.Append('`'+oc.ColumnNameInQuery+'`');
insert.Append(#13#10);
writeSeparator:=true;
end;
end;
insert.Append(' )');
//q.ReadOnly:=false;
myQ.SQLInsert.Text:=insert.ToWideString;
myQ.SQLUpdate.Text:=update.ToWideString;
myQ.SQLDelete.Text:=delete.ToWideString;
finally
where.Free;
insert.Free;
delete.Free;
update.Free;
end;
end;
//else q.ReadOnly:=true;
finally
mos.Free;
end;
end;
end;
end;