internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning);
bool first = true;
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
// (c1, c2, c3, ...)
commandText.Append("(");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
parameters = translator.Parameters;
return commandText.ToString();
}
internal static string[] GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool isLocalProvider)
{
var commandTexts = new List<String>();
var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
var translator = new ExpressionTranslator(
commandText, tree,
null != tree.Returning, isLocalProvider);
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
var first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
throw ADP1.NotSupported("Default values not supported");
}
commandTexts.Add(commandText.ToString());
commandText.Length = 0;
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
if (!String.IsNullOrEmpty(commandText.ToString()))
{
commandTexts.Add(commandText.ToString());
}
parameters = translator.Parameters;
return commandTexts.ToArray();
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, "InsertFunction");
// insert [schemaName].[tableName]
commandText.Append("INSERT INTO ");
tree.Target.Expression.Accept(translator);
if (tree.SetClauses.Count > 0)
{
// (c1, c2, c3, ...)
commandText.Append("(");
bool first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
// values c1, c2, ...
first = true;
commandText.Append(" VALUES (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(");");
}
else // No columns specified. Insert an empty row containing default values by inserting null into the rowid
{
commandText.AppendLine(" DEFAULT VALUES;");
}
// generate returning sql
GenerateReturningSql(commandText, tree, translator, tree.Returning);
parameters = translator.Parameters;
return commandText.ToString();
}
protected virtual void VisitInsertCommandTree(DbInsertCommandTree insertTree)
{
EntityUtil.CheckArgumentNull(insertTree, "insertTree");
this.VisitExpressionBindingPre(insertTree.Target);
this.VisitModificationClauses(insertTree.SetClauses);
if (insertTree.Returning != null)
{
this.VisitExpression(insertTree.Returning);
}
this.VisitExpressionBindingPost(insertTree.Target);
}
private DbModificationCommandTree RebuildCommandTree(DbModificationCommandTree originalTree, Dictionary<DbSetClause, DbSetClause> clauseMappings)
{
if (clauseMappings.Count == 0)
{
return originalTree;
}
DbModificationCommandTree result;
Debug.Assert(originalTree.CommandTreeKind == DbCommandTreeKind.Insert || originalTree.CommandTreeKind == DbCommandTreeKind.Update, "Set clauses specified for a modification tree that is not an update or insert tree?");
if (originalTree.CommandTreeKind == DbCommandTreeKind.Insert)
{
DbInsertCommandTree insertTree = (DbInsertCommandTree)originalTree;
result = new DbInsertCommandTree(insertTree.MetadataWorkspace, insertTree.DataSpace,
insertTree.Target, ReplaceClauses(insertTree.SetClauses, clauseMappings).AsReadOnly(), insertTree.Returning);
}
else
{
DbUpdateCommandTree updateTree = (DbUpdateCommandTree)originalTree;
result = new DbUpdateCommandTree(updateTree.MetadataWorkspace, updateTree.DataSpace,
updateTree.Target, updateTree.Predicate, ReplaceClauses(updateTree.SetClauses, clauseMappings).AsReadOnly(), updateTree.Returning);
}
return result;
}
/// <summary>
/// Builds insert command.
/// </summary>
/// <param name="newRow">Row to insert.</param>
/// <param name="processor">Context for the table we're inserting into.</param>
/// <returns>Insert command.</returns>
internal UpdateCommand BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor)
{
// Bind the insert target
DbExpressionBinding target = GetTarget(processor);
// Create set clauses and returning parameter
Dictionary<int, string> outputIdentifiers;
DbExpression returning;
bool rowMustBeTouched = true; // for inserts, the row must always be touched
List<DbModificationClause> setClauses = new List<DbModificationClause>();
foreach (DbModificationClause clause in BuildSetClauses(target, newRow, null, processor, /* insertMode */ true, out outputIdentifiers,
out returning, ref rowMustBeTouched))
{
setClauses.Add(clause);
}
// Initialize DML command tree
DbInsertCommandTree commandTree =
new DbInsertCommandTree(m_translator.MetadataWorkspace, DataSpace.SSpace, target, setClauses.AsReadOnly(), returning);
// Create command
UpdateCommand command = new DynamicUpdateCommand(processor, m_translator, ModificationOperator.Insert, null, newRow, commandTree, outputIdentifiers);
return command;
}
protected virtual void VisitInsertCommandTree(DbInsertCommandTree insertTree)
{
//Contract.Requires(insertTree != null);
VisitExpressionBindingPre(insertTree.Target);
VisitModificationClauses(insertTree.SetClauses);
if (insertTree.Returning != null)
{
VisitExpression(insertTree.Returning);
}
VisitExpressionBindingPost(insertTree.Target);
}
/// <summary>
/// Determine whether we should use a generated values variable to return server generated values.
/// This is true when we're attempting to insert a row where the primary key is server generated
/// but is not an integer type (and therefore can't be used with scope_identity()). It is also true
/// where there is a compound server generated key.
/// </summary>
private static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion, ExpressionTranslator translator)
{
bool useGeneratedValuesVariable = false;
if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null)
{
// Figure out which columns have values
HashSet<EdmMember> columnsWithValues = new HashSet<EdmMember>(tree.SetClauses.Cast<DbSetClause>().Select(s => ((DbPropertyExpression)s.Property).Property));
// Only SQL Server 2005+ support an output clause for inserts
bool firstKeyFound = false;
foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers)
{
if (!columnsWithValues.Contains(keyMember))
{
if (firstKeyFound)
{
// compound server gen key
useGeneratedValuesVariable = true;
break;
}
else
{
firstKeyFound = true;
if (!IsValidScopeIdentityColumnType(keyMember.TypeUsage))
{
// unsupported type
useGeneratedValuesVariable = true;
break;
}
}
}
}
}
return useGeneratedValuesVariable;
}
internal static string GenerateInsertSql(DbInsertCommandTree tree, SqlVersion sqlVersion, out List<SqlParameter> parameters)
{
StringBuilder commandText = new StringBuilder(s_commandTextBuilderInitialCapacity);
ExpressionTranslator translator = new ExpressionTranslator(commandText, tree,
null != tree.Returning, sqlVersion);
bool useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion, translator);
EntityType tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType;
if (useGeneratedValuesVariable)
{
// manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)"
commandText
.Append("declare ")
.Append(s_generatedValuesVariableName)
.Append(" table(");
bool first = true;
foreach (EdmMember column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
string columnType = SqlGenerator.GenerateSqlForStoreType(sqlVersion, column.TypeUsage);
if (columnType == "rowversion" || columnType == "timestamp")
{
// rowversion and timestamp are intrinsically read-only. use binary to gather server generated
// values for these types.
columnType = "binary(8)";
}
commandText
.Append(GenerateMemberTSql(column))
.Append(" ")
.Append(columnType);
Facet collationFacet;
if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet))
{
string collation = collationFacet.Value as string;
if (!string.IsNullOrEmpty(collation))
{
commandText.Append(" collate ").Append(collation);
}
}
}
Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values");
commandText.AppendLine(")");
}
// insert [schemaName].[tableName]
commandText.Append("insert ");
tree.Target.Expression.Accept(translator);
if (0 < tree.SetClauses.Count)
{
// (c1, c2, c3, ...)
commandText.Append("(");
bool first = true;
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Property.Accept(translator);
}
commandText.AppendLine(")");
}
else
{
commandText.AppendLine();
}
if (useGeneratedValuesVariable)
{
// output inserted.id into @generated_values
commandText.Append("output ");
bool first = true;
foreach (EdmMember column in tableType.KeyMembers)
{
if (first)
{
first = false;
}
else
{
commandText.Append(", ");
}
commandText.Append("inserted.");
commandText.Append(GenerateMemberTSql(column));
}
commandText
.Append(" into ")
.AppendLine(s_generatedValuesVariableName);
}
if (0 < tree.SetClauses.Count)
{
// values c1, c2, ...
bool first = true;
commandText.Append("values (");
foreach (DbSetClause setClause in tree.SetClauses)
{
if (first) { first = false; }
else { commandText.Append(", "); }
setClause.Value.Accept(translator);
translator.RegisterMemberValue(setClause.Property, setClause.Value);
}
commandText.AppendLine(")");
}
else
{
// default values
commandText.AppendLine("default values");
}
// generate returning sql
GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable);
parameters = translator.Parameters;
return commandText.ToString();
}
public SqlInsertGenerator(DbInsertCommandTree commandTree)
{
_commandTree = commandTree;
}