public void GenerateMemberTSql_returns_the_quoted_member_name() { Assert.Equal("[Magic]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic").Object)); Assert.Equal("[Magic]]Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic]Unicorn").Object)); Assert.Equal("[[Magic]]]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("[Magic]").Object)); Assert.Equal("[Magic[Unicorn]", DmlSqlGenerator.GenerateMemberTSql(CreateMockMember("Magic[Unicorn").Object)); }
public string GenerateDelete(ICollection <DbDeleteCommandTree> commandTrees, string rowsAffectedParameter) { DebugCheck.NotNull(commandTrees); List <SqlParameter> _; var sql = new StringBuilder(); sql.AppendLine( DmlSqlGenerator.GenerateDeleteSql( commandTrees.First(), _sqlGenerator, out _)); sql.AppendLine(); foreach (var commandTree in commandTrees.Skip(1)) { sql.AppendLine( DmlSqlGenerator.GenerateDeleteSql( commandTree, _sqlGenerator, out _)); sql.AppendLine("AND @@ROWCOUNT > 0"); sql.AppendLine(); } AppendSetRowsAffected(sql, rowsAffectedParameter); return(sql.ToString().TrimEnd()); }
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 string GenerateUpdate( ICollection <DbUpdateCommandTree> commandTrees, string rowsAffectedParameter) { if (!commandTrees.Any <DbUpdateCommandTree>()) { return((string)null); } StringBuilder sql = new StringBuilder(); List <SqlParameter> parameters; sql.AppendLine(DmlSqlGenerator.GenerateUpdateSql(commandTrees.First <DbUpdateCommandTree>(), this._sqlGenerator, out parameters, false, true)); foreach (DbUpdateCommandTree tree in commandTrees.Skip <DbUpdateCommandTree>(1)) { sql.Append(DmlSqlGenerator.GenerateUpdateSql(tree, this._sqlGenerator, out parameters, false, true)); sql.AppendLine("AND @@ROWCOUNT > 0"); sql.AppendLine(); } List <DbUpdateCommandTree> list = commandTrees.Where <DbUpdateCommandTree>((Func <DbUpdateCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbUpdateCommandTree>(); if (list.Any <DbUpdateCommandTree>()) { DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator(); foreach (DbUpdateCommandTree updateCommandTree in list) { updateCommandTree.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator); updateCommandTree.Returning.Accept((DbExpressionVisitor)selectSqlGenerator); updateCommandTree.Predicate.Accept((DbExpressionVisitor)selectSqlGenerator); } sql.AppendLine(selectSqlGenerator.Sql); sql.AppendLine(); } DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter); return(sql.ToString().TrimEnd()); }
internal static bool UseGeneratedValuesVariable(DbInsertCommandTree tree, SqlVersion sqlVersion) { bool flag1 = false; if (sqlVersion > SqlVersion.Sql8 && tree.Returning != null) { HashSet <EdmMember> edmMemberSet = new HashSet <EdmMember>(tree.SetClauses.Cast <DbSetClause>().Select <DbSetClause, EdmMember>((Func <DbSetClause, EdmMember>)(s => ((DbPropertyExpression)s.Property).Property))); bool flag2 = false; foreach (EdmMember keyMember in ((DbScanExpression)tree.Target.Expression).Target.ElementType.KeyMembers) { if (!edmMemberSet.Contains(keyMember)) { if (flag2) { flag1 = true; break; } flag2 = true; if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage)) { flag1 = true; break; } } } } return(flag1); }
public string GenerateUpdate(ICollection <DbUpdateCommandTree> commandTrees, string rowsAffectedParameter) { DebugCheck.NotNull(commandTrees); if (!commandTrees.Any()) { return(null); } List <SqlParameter> _; var sql = new StringBuilder(); sql.AppendLine( DmlSqlGenerator.GenerateUpdateSql( commandTrees.First(), _sqlGenerator, out _, generateReturningSql: false)); foreach (var commandTree in commandTrees.Skip(1)) { sql.Append( DmlSqlGenerator.GenerateUpdateSql( commandTree, _sqlGenerator, out _, generateReturningSql: false)); sql.AppendLine("AND @@ROWCOUNT > 0"); sql.AppendLine(); } var returningCommandTrees = commandTrees .Where(ct => ct.Returning != null) .ToList(); if (returningCommandTrees.Any()) { var returningSelectSqlGenerator = new ReturningSelectSqlGenerator(); foreach (var commandTree in returningCommandTrees) { commandTree.Target.Expression.Accept(returningSelectSqlGenerator); commandTree.Returning.Accept(returningSelectSqlGenerator); commandTree.Predicate.Accept(returningSelectSqlGenerator); } sql.AppendLine(returningSelectSqlGenerator.Sql); sql.AppendLine(); } AppendSetRowsAffected(sql, rowsAffectedParameter); return(sql.ToString().TrimEnd()); }
public override void Visit(DbPropertyExpression expression) { Check.NotNull <DbPropertyExpression>(expression, nameof(expression)); if (!string.IsNullOrEmpty(this.PropertyAlias)) { this._commandText.Append(this.PropertyAlias); this._commandText.Append("."); } this._commandText.Append(DmlSqlGenerator.GenerateMemberTSql(expression.Property)); }
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()); }
public string GenerateDelete( ICollection <DbDeleteCommandTree> commandTrees, string rowsAffectedParameter) { StringBuilder sql = new StringBuilder(); List <SqlParameter> parameters; sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(commandTrees.First <DbDeleteCommandTree>(), this._sqlGenerator, out parameters, true, true)); sql.AppendLine(); foreach (DbDeleteCommandTree tree in commandTrees.Skip <DbDeleteCommandTree>(1)) { sql.AppendLine(DmlSqlGenerator.GenerateDeleteSql(tree, this._sqlGenerator, out parameters, true, true)); sql.AppendLine("AND @@ROWCOUNT > 0"); sql.AppendLine(); } DmlFunctionSqlGenerator.AppendSetRowsAffected(sql, rowsAffectedParameter); return(sql.ToString().TrimEnd()); }
public string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees) { StringBuilder stringBuilder = new StringBuilder(); DbInsertCommandTree insertCommandTree1 = commandTrees.First <DbInsertCommandTree>(); List <SqlParameter> parameters; stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(insertCommandTree1, this._sqlGenerator, out parameters, false, true, false)); stringBuilder.AppendLine(); EntityType elementType = (EntityType)((DbScanExpression)insertCommandTree1.Target.Expression).Target.ElementType; stringBuilder.Append(this.IntroduceRequiredLocalVariables(elementType, insertCommandTree1)); foreach (DbInsertCommandTree tree in commandTrees.Skip <DbInsertCommandTree>(1)) { stringBuilder.Append(DmlSqlGenerator.GenerateInsertSql(tree, this._sqlGenerator, out parameters, false, true, false)); stringBuilder.AppendLine(); } List <DbInsertCommandTree> list = commandTrees.Where <DbInsertCommandTree>((Func <DbInsertCommandTree, bool>)(ct => ct.Returning != null)).ToList <DbInsertCommandTree>(); if (list.Any <DbInsertCommandTree>()) { DmlFunctionSqlGenerator.ReturningSelectSqlGenerator selectSqlGenerator = new DmlFunctionSqlGenerator.ReturningSelectSqlGenerator(); foreach (DbInsertCommandTree insertCommandTree2 in list) { insertCommandTree2.Target.Expression.Accept((DbExpressionVisitor)selectSqlGenerator); insertCommandTree2.Returning.Accept((DbExpressionVisitor)selectSqlGenerator); } foreach (EdmProperty keyProperty1 in elementType.KeyProperties) { EdmProperty keyProperty = keyProperty1; DbExpression right = insertCommandTree1.SetClauses.Cast <DbSetClause>().Where <DbSetClause>((Func <DbSetClause, bool>)(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty)).Select <DbSetClause, DbExpression>((Func <DbSetClause, DbExpression>)(sc => sc.Value)).SingleOrDefault <DbExpression>() ?? (DbExpression)keyProperty.TypeUsage.Parameter(keyProperty.Name); insertCommandTree1.Target.Variable.Property(keyProperty).Equal(right).Accept((DbExpressionVisitor)selectSqlGenerator); } stringBuilder.Append(selectSqlGenerator.Sql); } return(stringBuilder.ToString().TrimEnd()); }
public string GenerateInsert(ICollection <DbInsertCommandTree> commandTrees) { DebugCheck.NotNull(commandTrees); var sql = new StringBuilder(); List <SqlParameter> _; var firstCommandTree = commandTrees.First(); sql.Append( DmlSqlGenerator.GenerateInsertSql( firstCommandTree, _sqlGenerator, out _, generateReturningSql: false, createParameters: false)); sql.AppendLine(); var firstTable = (EntityType)((DbScanExpression)firstCommandTree.Target.Expression).Target.ElementType; sql.Append(IntroduceRequiredLocalVariables(firstTable, firstCommandTree)); foreach (var commandTree in commandTrees.Skip(1)) { sql.Append( DmlSqlGenerator.GenerateInsertSql( commandTree, _sqlGenerator, out _, generateReturningSql: false, createParameters: false)); sql.AppendLine(); } var returningCommandTrees = commandTrees .Where(ct => ct.Returning != null) .ToList(); if (returningCommandTrees.Any()) { var returningSelectSqlGenerator = new ReturningSelectSqlGenerator(); foreach (var commandTree in returningCommandTrees) { commandTree.Target.Expression.Accept(returningSelectSqlGenerator); commandTree.Returning.Accept(returningSelectSqlGenerator); } foreach (var keyProperty in firstTable.KeyProperties) { var parameterReference = firstCommandTree .SetClauses .Cast <DbSetClause>() .Where(sc => ((DbPropertyExpression)sc.Property).Property == keyProperty) .Select(sc => sc.Value) .SingleOrDefault() ?? keyProperty.TypeUsage.Parameter(keyProperty.Name); firstCommandTree .Target .Variable .Property(keyProperty) .Equal(parameterReference) .Accept(returningSelectSqlGenerator); } sql.Append(returningSelectSqlGenerator.Sql); } return(sql.ToString().TrimEnd()); }
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()); }
internal static void GenerateReturningSql( SqlStringBuilder commandText, DbModificationCommandTree tree, EntityType tableType, DmlSqlGenerator.ExpressionTranslator translator, DbExpression returning, bool useGeneratedValuesVariable) { if (returning == null) { return; } commandText.AppendKeyword("select "); if (useGeneratedValuesVariable) { translator.PropertyAlias = "t"; } returning.Accept((DbExpressionVisitor)translator); if (useGeneratedValuesVariable) { translator.PropertyAlias = (string)null; } commandText.AppendLine(); if (useGeneratedValuesVariable) { commandText.AppendKeyword("from "); commandText.Append("@generated_keys"); commandText.AppendKeyword(" as "); commandText.Append("g"); commandText.AppendKeyword(" join "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); commandText.AppendKeyword(" as "); commandText.Append("t"); commandText.AppendKeyword(" on "); string keyword = string.Empty; foreach (EdmMember keyMember in tableType.KeyMembers) { commandText.AppendKeyword(keyword); keyword = " and "; commandText.Append("g."); string memberTsql = DmlSqlGenerator.GenerateMemberTSql(keyMember); commandText.Append(memberTsql); commandText.Append(" = t."); commandText.Append(memberTsql); } commandText.AppendLine(); commandText.AppendKeyword("where @@ROWCOUNT > 0"); } else { commandText.AppendKeyword("from "); tree.Target.Expression.Accept((DbExpressionVisitor)translator); commandText.AppendLine(); commandText.AppendKeyword("where @@ROWCOUNT > 0"); EntitySetBase target = ((DbScanExpression)tree.Target.Expression).Target; bool flag = false; foreach (EdmMember keyMember in target.ElementType.KeyMembers) { commandText.AppendKeyword(" and "); commandText.Append(DmlSqlGenerator.GenerateMemberTSql(keyMember)); commandText.Append(" = "); SqlParameter sqlParameter; if (translator.MemberValues.TryGetValue(keyMember, out sqlParameter)) { commandText.Append(sqlParameter.ParameterName); } else { if (flag) { throw new NotSupportedException(Strings.Update_NotSupportedServerGenKey((object)target.Name)); } if (!DmlSqlGenerator.IsValidScopeIdentityColumnType(keyMember.TypeUsage)) { throw new InvalidOperationException(Strings.Update_NotSupportedIdentityType((object)keyMember.Name, (object)keyMember.TypeUsage.ToString())); } commandText.Append("scope_identity()"); flag = true; } } } }