public void AppendKeyWord_should_case_based_on_property() { var sqlStringBuilder = new SqlStringBuilder(); sqlStringBuilder.AppendKeyword("select"); Assert.Equal("select", sqlStringBuilder.ToString()); sqlStringBuilder.UpperCaseKeywords = true; sqlStringBuilder.AppendKeyword(" from"); Assert.Equal("select FROM", sqlStringBuilder.ToString()); }
public void AppendKeyWord_should_case_based_on_property() { var sqlStringBuilder = new SqlStringBuilder(); sqlStringBuilder.AppendKeyword("select"); Assert.Equal("select", sqlStringBuilder.ToString()); sqlStringBuilder.UpperCaseKeywords = true; sqlStringBuilder.AppendKeyword(" from"); Assert.Equal("select FROM", sqlStringBuilder.ToString()); }
internal static string GenerateDeleteSql( DbDeleteCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool upperCaseKeywords = true, bool createParameters = true) { var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator( commandText, tree, false, sqlGenerator, createParameters: createParameters); // delete [schemaName].[tableName] commandText.AppendKeyword("delete "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // where c1 = ... AND c2 = ... commandText.AppendKeyword("where "); tree.Predicate.Accept(translator); parameters = translator.Parameters; return(commandText.ToString()); }
private string IntroduceRequiredLocalVariables( EntityType entityType, DbInsertCommandTree commandTree) { List <EdmProperty> list = entityType.KeyProperties.Where <EdmProperty>((Func <EdmProperty, bool>)(p => p.IsStoreGeneratedIdentity)).ToList <EdmProperty>(); SqlStringBuilder commandText = new SqlStringBuilder() { UpperCaseKeywords = true }; if (list.Any <EdmProperty>()) { foreach (EdmProperty edmProperty in list) { commandText.Append(commandText.Length == 0 ? "DECLARE " : ", "); commandText.Append("@"); commandText.Append(edmProperty.Name); commandText.Append(" "); commandText.Append(DmlSqlGenerator.GetVariableType(this._sqlGenerator, (EdmMember)edmProperty)); } commandText.AppendLine(); DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)commandTree, true, this._sqlGenerator, (ICollection <EdmProperty>)entityType.KeyProperties, true); DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, this._sqlGenerator.SqlVersion)); commandText.AppendLine(); commandText.AppendLine(); } return(commandText.ToString()); }
public void Visit_DbScanExpression_appends_SQL_if_defining_query_is_not_set() { var builder = new SqlStringBuilder(); var insertTree = new DbInsertCommandTree(); new DmlSqlGenerator.ExpressionTranslator( builder, insertTree, true, new SqlGenerator(SqlVersion.Sql10)) .Visit(CreateMockScanExpression(null).Object); Assert.Equal("[Kontainer].[Binky]", builder.ToString()); }
public void Visit_DbScanExpression_appends_SQL_if_defining_query_is_not_set() { var builder = new SqlStringBuilder(); var insertTree = new DbInsertCommandTree(); new DmlSqlGenerator.ExpressionTranslator( builder, insertTree, true, new SqlGenerator(SqlVersion.Sql10)) .Visit(CreateMockScanExpression(null).Object); Assert.Equal("[Kontainer].[Binky]", builder.ToString()); }
internal static string GenerateUpdateSql( DbUpdateCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true) { SqlStringBuilder commandText = new SqlStringBuilder(256) { UpperCaseKeywords = upperCaseKeywords }; DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, true); if (tree.SetClauses.Count == 0) { commandText.AppendKeyword("declare "); commandText.AppendLine("@p int"); } commandText.AppendKeyword("update "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); commandText.AppendLine(); bool flag = true; commandText.AppendKeyword("set "); foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag) { flag = false; } else { commandText.Append(", "); } setClause.Property.Accept((DbExpressionVisitor)translator); commandText.Append(" = "); setClause.Value.Accept((DbExpressionVisitor)translator); } if (flag) { commandText.Append("@p = 0"); } commandText.AppendLine(); commandText.AppendKeyword("where "); tree.Predicate.Accept((DbExpressionVisitor)translator); commandText.AppendLine(); if (generateReturningSql) { DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, (EntityType)null, translator, tree.Returning, false); } parameters = translator.Parameters; return(commandText.ToString()); }
private string IntroduceRequiredLocalVariables(EntityType entityType, DbInsertCommandTree commandTree) { DebugCheck.NotNull(entityType); DebugCheck.NotNull(commandTree); var storeGeneratedKeys = entityType .KeyProperties .Where(p => p.IsStoreGeneratedIdentity) .ToList(); var sql = new SqlStringBuilder { UpperCaseKeywords = true }; if (storeGeneratedKeys.Any()) { foreach (var keyProperty in storeGeneratedKeys) { sql.Append(sql.Length == 0 ? "DECLARE " : ", "); sql.Append("@"); sql.Append(keyProperty.Name); sql.Append(" "); sql.Append(DmlSqlGenerator.GetVariableType(_sqlGenerator, keyProperty)); } sql.AppendLine(); var translator = new DmlSqlGenerator.ExpressionTranslator( sql, commandTree, true, _sqlGenerator, entityType.KeyProperties); DmlSqlGenerator.GenerateReturningSql( sql, commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, _sqlGenerator.SqlVersion)); sql.AppendLine(); sql.AppendLine(); } return(sql.ToString()); }
internal static string GenerateDeleteSql( DbDeleteCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool upperCaseKeywords = true, bool createParameters = true) { SqlStringBuilder commandText = new SqlStringBuilder(256) { UpperCaseKeywords = upperCaseKeywords }; DmlSqlGenerator.ExpressionTranslator expressionTranslator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, false, sqlGenerator, (ICollection <EdmProperty>)null, createParameters); commandText.AppendKeyword("delete "); tree.Target.Expression.Accept((DbExpressionVisitor)expressionTranslator); commandText.AppendLine(); commandText.AppendKeyword("where "); tree.Predicate.Accept((DbExpressionVisitor)expressionTranslator); parameters = expressionTranslator.Parameters; return(commandText.ToString()); }
internal static string GenerateUpdateSql( DbUpdateCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true) { const string dummySetParameter = "@p"; var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlGenerator); if (tree.SetClauses.Count == 0) { commandText.AppendKeyword("declare "); commandText.AppendLine(dummySetParameter + " int"); } // update [schemaName].[tableName] commandText.AppendKeyword("update "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // set c1 = ..., c2 = ..., ... var first = true; commandText.AppendKeyword("set "); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); commandText.Append(" = "); setClause.Value.Accept(translator); } 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 // // We use the following pattern: // // update Foo // set @p = 0 // where ... commandText.Append(dummySetParameter + " = 0"); } commandText.AppendLine(); // where c1 = ..., c2 = ... commandText.AppendKeyword("where "); tree.Predicate.Accept(translator); commandText.AppendLine(); if (generateReturningSql) { GenerateReturningSql(commandText, tree, null, translator, tree.Returning, false); } 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 GenerateUpdateSql( DbUpdateCommandTree tree, SqlGenerator sqlGenerator, out List<SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true) { const string dummySetParameter = "@p"; var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator(commandText, tree, null != tree.Returning, sqlGenerator); if (tree.SetClauses.Count == 0) { commandText.AppendKeyword("declare "); commandText.AppendLine(dummySetParameter + " int"); } // update [schemaName].[tableName] commandText.AppendKeyword("update "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // set c1 = ..., c2 = ..., ... var first = true; commandText.AppendKeyword("set "); foreach (DbSetClause setClause in tree.SetClauses) { if (first) { first = false; } else { commandText.Append(", "); } setClause.Property.Accept(translator); commandText.Append(" = "); setClause.Value.Accept(translator); } 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 // // We use the following pattern: // // update SomeTable // set @p = 0 // where ... commandText.Append(dummySetParameter + " = 0"); } commandText.AppendLine(); // where c1 = ..., c2 = ... commandText.AppendKeyword("where "); tree.Predicate.Accept(translator); commandText.AppendLine(); if (generateReturningSql) { GenerateReturningSql(commandText, tree, null, translator, tree.Returning, false); } 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 GenerateDeleteSql( DbDeleteCommandTree tree, SqlGenerator sqlGenerator, out List<SqlParameter> parameters, bool upperCaseKeywords = true, bool createParameters = true) { var commandText = new SqlStringBuilder(CommandTextBuilderInitialCapacity) { UpperCaseKeywords = upperCaseKeywords }; var translator = new ExpressionTranslator( commandText, tree, false, sqlGenerator, createParameters: createParameters); // delete [schemaName].[tableName] commandText.AppendKeyword("delete "); tree.Target.Expression.Accept(translator); commandText.AppendLine(); // where c1 = ... AND c2 = ... commandText.AppendKeyword("where "); tree.Predicate.Accept(translator); parameters = translator.Parameters; return commandText.ToString(); }
private string IntroduceRequiredLocalVariables(EntityType entityType, DbInsertCommandTree commandTree) { DebugCheck.NotNull(entityType); DebugCheck.NotNull(commandTree); var storeGeneratedKeys = entityType .KeyProperties .Where(p => p.IsStoreGeneratedIdentity) .ToList(); var sql = new SqlStringBuilder { UpperCaseKeywords = true }; if (storeGeneratedKeys.Any()) { foreach (var keyProperty in storeGeneratedKeys) { sql.Append(sql.Length == 0 ? "DECLARE " : ", "); sql.Append("@"); sql.Append(keyProperty.Name); sql.Append(" "); sql.Append(DmlSqlGenerator.GetVariableType(_sqlGenerator, keyProperty)); } sql.AppendLine(); var translator = new DmlSqlGenerator.ExpressionTranslator( sql, commandTree, true, _sqlGenerator, entityType.KeyProperties); DmlSqlGenerator.GenerateReturningSql( sql, commandTree, entityType, translator, commandTree.Returning, DmlSqlGenerator.UseGeneratedValuesVariable(commandTree, _sqlGenerator.SqlVersion)); sql.AppendLine(); sql.AppendLine(); } return sql.ToString(); }
internal static string GenerateInsertSql( DbInsertCommandTree tree, SqlGenerator sqlGenerator, out List <SqlParameter> parameters, bool generateReturningSql = true, bool upperCaseKeywords = true, bool createParameters = true) { SqlStringBuilder commandText = new SqlStringBuilder(256) { UpperCaseKeywords = upperCaseKeywords }; DmlSqlGenerator.ExpressionTranslator translator = new DmlSqlGenerator.ExpressionTranslator(commandText, (DbModificationCommandTree)tree, null != tree.Returning, sqlGenerator, (ICollection <EdmProperty>)null, createParameters); bool useGeneratedValuesVariable = DmlSqlGenerator.UseGeneratedValuesVariable(tree, sqlGenerator.SqlVersion); EntityType elementType = (EntityType)((DbScanExpression)tree.Target.Expression).Target.ElementType; if (useGeneratedValuesVariable) { commandText.AppendKeyword("declare ").Append("@generated_keys").Append(" table("); bool flag = true; foreach (EdmMember keyMember in elementType.KeyMembers) { if (flag) { flag = false; } else { commandText.Append(", "); } commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)).Append(" ").Append(DmlSqlGenerator.GetVariableType(sqlGenerator, keyMember)); Facet facet; if (keyMember.TypeUsage.Facets.TryGetValue("Collation", false, out facet)) { string s = facet.Value as string; if (!string.IsNullOrEmpty(s)) { commandText.AppendKeyword(" collate ").Append(s); } } } commandText.AppendLine(")"); } commandText.AppendKeyword("insert "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); if (0 < tree.SetClauses.Count) { commandText.Append("("); bool flag = true; foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag) { flag = false; } else { commandText.Append(", "); } setClause.Property.Accept((DbExpressionVisitor)translator); } commandText.AppendLine(")"); } else { commandText.AppendLine(); } if (useGeneratedValuesVariable) { commandText.AppendKeyword("output "); bool flag = true; foreach (EdmMember keyMember in elementType.KeyMembers) { if (flag) { flag = false; } else { commandText.Append(", "); } commandText.Append("inserted."); commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)); } commandText.AppendKeyword(" into ").AppendLine("@generated_keys"); } if (0 < tree.SetClauses.Count) { bool flag = true; commandText.AppendKeyword("values ("); foreach (DbSetClause setClause in (IEnumerable <DbModificationClause>)tree.SetClauses) { if (flag) { flag = false; } else { commandText.Append(", "); } setClause.Value.Accept((DbExpressionVisitor)translator); translator.RegisterMemberValue(setClause.Property, setClause.Value); } commandText.AppendLine(")"); } else { commandText.AppendKeyword("default values"); commandText.AppendLine(); } if (generateReturningSql) { DmlSqlGenerator.GenerateReturningSql(commandText, (DbModificationCommandTree)tree, elementType, translator, tree.Returning, useGeneratedValuesVariable); } parameters = translator.Parameters; return(commandText.ToString()); }