internal static string GenerateInsertSql(DbInsertCommandTree tree, out List <DbParameter> parameters, bool generateParameters = true) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters); bool first = true; commandText.Append("INSERT INTO "); tree.Target.Expression.Accept(translator); if (tree.SetClauses.Any()) { // (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(")"); } else { commandText.AppendLine("DEFAULT VALUES"); } // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
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()); }
internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List <DbParameter> parameters, bool generateParameters = true) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters); bool first = true; commandText.Append("UPDATE "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // set c1 = ..., c2 = ..., ... commandText.Append("SET "); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); commandText.Append(" = "); setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } if (first) { // If first is still true, it indicates there were no set // clauses. Introduce a fake set clause so that: // - we acquire the appropriate locks // - server-gen columns (e.g. timestamp) get recomputed EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target; // hope this column isn't indexed to not waste power EdmMember someColumn = table.ElementType.Members.Last(x => !MetadataHelpers.IsStoreGenerated(x)); commandText.AppendFormat("{0} = {0}", GenerateMemberSql(someColumn)); } commandText.AppendLine(); // where c1 = ..., c2 = ... commandText.Append("WHERE "); tree.Predicate.Accept(translator); commandText.AppendLine(); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
internal static string GenerateUpdateSql(DbUpdateCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters); bool first = true; commandText.Append("UPDATE "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // set c1 = ..., c2 = ..., ... commandText.Append("SET "); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); commandText.Append(" = "); setClause.Value.Accept(translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } if (first) { // If first is still true, it indicates there were no set // clauses. Introduce a fake set clause so that: // - we acquire the appropriate locks // - server-gen columns (e.g. timestamp) get recomputed EntitySetBase table = ((DbScanExpression)tree.Target.Expression).Target; // hope this column isn't indexed to not waste power EdmMember someColumn = table.ElementType.Members.Last(x => !MetadataHelpers.IsStoreGenerated(x)); commandText.AppendFormat("{0} = {0}", GenerateMemberSql(someColumn)); } commandText.AppendLine(); // where c1 = ..., c2 = ... commandText.Append("WHERE "); tree.Predicate.Accept(translator); commandText.AppendLine(); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return commandText.ToString(); }
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); // insert [schemaName].[tableName] commandText.Append("insert into "); tree.Target.Expression.Accept(translator); // (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(")"); // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
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(); }
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, SqlVersion sqlVersion, out List<SqlParameter> parameters) { var commandText = new StringBuilder(s_commandTextBuilderInitialCapacity); var translator = new ExpressionTranslator( commandText, tree, null != tree.Returning, sqlVersion); var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlVersion); var 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("); var first = true; foreach (var column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } var 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)) { var 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("("); var 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 "); var first = true; foreach (var 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, ... var 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(); }
internal static string GenerateInsertSql(DbInsertCommandTree tree, out List<DbParameter> parameters, bool generateParameters = true) { StringBuilder commandText = new StringBuilder(CommandTextBuilderInitialCapacity); ExpressionTranslator translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, generateParameters); 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, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true, bool createParameters = true) { var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator( commandText, tree, null != tree.Returning, sqlGenerator, createParameters: createParameters); var useGeneratedValuesVariable = UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion); var tableType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType; if (useGeneratedValuesVariable) { // manufacture the variable, e.g. "declare @generated_values table(id uniqueidentifier)" commandText .AppendKeyword("declare ") .Append(GeneratedValuesVariableName) .Append(" table("); var first = true; foreach (var column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } commandText .Append(GenerateMemberTSql(column)) .Append(" ") .Append(GetVariableType(sqlGenerator, column)); Facet collationFacet; if (column.TypeUsage.Facets.TryGetValue(DbProviderManifest.CollationFacetName, false, out collationFacet)) { var collation = collationFacet.Value as string; if (!string.IsNullOrEmpty(collation)) { commandText.AppendKeyword(" collate ").Append(collation); } } } Debug.Assert(!first, "if useGeneratedValuesVariable is true, it implies some columns do not have values"); commandText.AppendLine(")"); } // insert [schemaName].[tableName] commandText.AppendKeyword("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(")"); } else { commandText.AppendLine(); } if (useGeneratedValuesVariable) { // output inserted.id into @generated_values commandText.AppendKeyword("output "); var first = true; foreach (var column in tableType.KeyMembers) { if (first) { first = false; } else { commandText.Append(", "); } commandText.Append("inserted."); commandText.Append(GenerateMemberTSql(column)); } commandText .AppendKeyword(" into ") .AppendLine(GeneratedValuesVariableName); } if (0 < tree.SetClauses.Count) { // values c1, c2, ... var first = true; commandText.AppendKeyword("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.AppendKeyword("default values"); commandText.AppendLine(); } if (generateReturningSql) { GenerateReturningSql(commandText, tree, tableType, translator, tree.Returning, useGeneratedValuesVariable); } 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, bool insertParametersValuesInSql = false) { var commandText = new StringBuilder(COMMANDTEXT_STRINGBUILDER_INITIALCAPACITY); var translator = new ExpressionTranslator(commandText, tree, tree.Returning != null, insertParametersValuesInSql); commandText.Append("insert into "); tree.Target.Expression.Accept(translator); // Actually is not possible to retrieve the last inserted guid from Access // We can understand if there is a guid checking the returning value of the insert // statement string guidAutogeneratedColumn = null; if (tree.Returning is DbNewInstanceExpression) { guidAutogeneratedColumn = GetGuidArgs(tree.Returning as DbNewInstanceExpression); } if (tree.SetClauses.Count != 0) { var first = true; // (c1, c2, c3, ...) commandText.Append("("); if (!string.IsNullOrEmpty(guidAutogeneratedColumn)) { commandText.Append(SQLiteProviderManifestHelper.QuoteIdentifier(guidAutogeneratedColumn)); first = false; } 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 ("); if (!string.IsNullOrEmpty(guidAutogeneratedColumn)) { _lastGuid = Guid.NewGuid(); commandText.Append(string.Format("{{{0}}}", _lastGuid)); first = false; } 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 { commandText.AppendLine(" default values;"); } // generate returning sql GenerateReturningSql(commandText, tree, translator, tree.Returning); parameters = translator.Parameters; return(commandText.ToString()); }
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()); }