/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="tree">查询语义</param> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context) { ISqlBuilder builder = this.CreateSqlBuilder(context);; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (tree.Entity != null) { // 如果没有Sequence列,使用 INSERT ALL INTO 语法,否则就一条一条逐行写入~~ // 批量 INSERT,自增列不会自动赋值 object entity = tree.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context); // 指定插入列 var members = typeRuntime.Members; if (tree.EntityColumns != null && tree.EntityColumns.Count > 0) { members = new MemberAccessorCollection(); for (int i = 0; i < tree.EntityColumns.Count; i++) { Expression curExpr = tree.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", tree.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; members[name] = typeRuntime.Members[name]; } } // 自增列参数 IDbDataParameter seqParameter = null; // 自增列标记 ColumnAttribute seqColumn = null; foreach (var item in members) { var m = item as FieldAccessorBase; if (m == null || !m.IsDbField) { continue; } columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type); columnsBuilder.Append(','); if (m == typeRuntime.Identity) { seqColumn = m.Column; if (tree.Bulk == null) { // 非批量INSERT,产生一个 OUTPUT 类型的参数 string parameterName = string.Format("{0}{1}{2}", this.ParameterPrefix, AppConst.PARAMETER_NAME_PRIFIX, context.Parameters.Count); seqParameter = this.DbProvider.CreateParameter(parameterName, -1, direction: ParameterDirection.Output); context.Parameters.Add(seqParameter); valuesBuilder.Append(seqParameter.ParameterName); valuesBuilder.Append(','); } else { valuesBuilder.AppendMember(((OracleColumnAttribute)m.Column).SEQName); valuesBuilder.Append(".NEXTVAL"); valuesBuilder.Append(','); } } else { var value = m.Invoke(entity); string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column); valuesBuilder.Append(sqlExpression); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (tree.Bulk == null) { // 非批量INSERT,产生一个 OUTPUT 类型的参数 if (seqParameter != null) { seqParameter.Direction = ParameterDirection.Output; seqParameter.DbType = DbType.Int64; builder.Append("SELECT "); builder.AppendMember(((OracleColumnAttribute)seqColumn).SEQName); builder.Append(".NEXTVAL INTO "); builder.Append(seqParameter.ParameterName); builder.Append(" FROM DUAL;"); builder.AppendNewLine(); //useSEQ = true; } builder.Append("INSERT "); } else { // 批量 INSERT if (!tree.Bulk.OnlyValue || seqColumn != null) { builder.Append("INSERT "); } // 如果有自增列则不使用 INSERT ALL INTO 语法 if (!tree.Bulk.OnlyValue && seqColumn == null) { builder.Append("ALL "); } } builder.Append("INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (tree.Bulk == null) { builder.Append(';'); } else { if (seqColumn != null) { if (tree.Bulk.IsEndPos) { builder.Append(";"); } else { builder.AppendNewLine(";"); } } else { builder.AppendNewLine(); if (tree.Bulk.IsEndPos) { builder.Append("SELECT 1 FROM DUAL;"); } } } } else if (tree.Select != null) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); var srcExpressionType = context.CurrentExpressionType; var srcIsOutermost = context.CurrentIsOutermost; context.CurrentExpressionType = DbExpressionType.Insert; context.CurrentIsOutermost = false; var cmd = this.TranslateSelectCommand(tree.Select, 0, false, context) as DbSelectCommand; context.CurrentExpressionType = srcExpressionType; context.CurrentIsOutermost = srcIsOutermost; int index = 0; foreach (var column in cmd.SelectedColumns) { builder.AppendMember(column.Name); if (index < cmd.SelectedColumns.Count - 1) { builder.Append(','); } index++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); builder.Append(';'); } var result = new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text); return(result); }
/// <summary> /// 创建 INSERT 命令 /// </summary> /// <param name="tree">查询语义</param> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context) { ISqlBuilder builder = this.CreateSqlBuilder(context); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (tree.Entity != null) { object entity = tree.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context); // 指定插入列 var members = typeRuntime.Members; if (tree.EntityColumns != null && tree.EntityColumns.Count > 0) { members = new MemberAccessorCollection(); for (int i = 0; i < tree.EntityColumns.Count; i++) { Expression curExpr = tree.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", tree.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; members[name] = typeRuntime.Members[name]; } } foreach (var item in members) { var m = item as FieldAccessorBase; if (m == null || !m.IsDbField) { continue; } if (m != typeRuntime.Identity) { columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type); columnsBuilder.Append(','); var value = m.Invoke(entity); string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column); valuesBuilder.Append(sqlExpression); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (tree.Bulk == null || !tree.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (tree.Bulk != null && !tree.Bulk.IsEndPos) { builder.Append(","); } if (tree.Bulk == null && typeRuntime.Identity != null) { builder.Append(';'); builder.AppendNewLine(); builder.Append("SELECT LAST_INSERT_ID()"); builder.AppendAs(AppConst.AUTO_INCREMENT_NAME); } } else if (tree.Select != null) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); var srcExpressionType = context.CurrentExpressionType; var srcIsOutermost = context.CurrentIsOutermost; context.CurrentExpressionType = DbExpressionType.Insert; context.CurrentIsOutermost = true; var cmd = this.TranslateSelectCommandImpl(tree.Select, 0, true, context) as DbSelectCommand; context.CurrentExpressionType = srcExpressionType; context.CurrentIsOutermost = srcIsOutermost; int index = 0; foreach (var column in cmd.SelectedColumns) { builder.AppendMember(column.NewName); if (index < cmd.SelectedColumns.Count - 1) { builder.Append(','); } index++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); } if (tree.Bulk == null || tree.Bulk.IsEndPos) { builder.Append(';'); } return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text)); }
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="tree">查询语义</param> /// <param name="context">解析上下文</param> /// <returns></returns> protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context) { // 增删改不用 NOLOCK bool noLock = ((SqlServerDbContext)context.DbContext).NoLock; ((SqlServerDbContext)context.DbContext).NoLock = false; ISqlBuilder builder = this.CreateSqlBuilder(context); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (tree.Entity != null) { object entity = tree.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context); // 指定插入列 var members = typeRuntime.Members; if (tree.EntityColumns != null && tree.EntityColumns.Count > 0) { members = new MemberAccessorCollection(); for (int i = 0; i < tree.EntityColumns.Count; i++) { Expression expression = tree.EntityColumns[i]; if (expression.NodeType == ExpressionType.Lambda) { expression = (expression as LambdaExpression).Body.ReduceUnary(); } if (expression.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("ERR {0}: Only support MemberAccess expression.", tree.EntityColumns[i]); } var member = expression as MemberExpression; string name = member.Member.Name; members[name] = typeRuntime.Members[name]; } } foreach (var item in members) { var m = item as FieldAccessorBase; if (m == null || !m.IsDbField) { continue; } // 忽略行版本号列 if (m.Column != null && m.Column.DbType is SqlDbType && (SqlDbType)m.Column.DbType == SqlDbType.Timestamp) { continue; } if (item != typeRuntime.Identity) { columnsBuilder.AppendMember(null, item.Member, typeRuntime.Type); columnsBuilder.Append(','); var value = item.Invoke(entity); string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column); valuesBuilder.Append(sqlExpression); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (tree.Bulk == null || !tree.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); if (tree.Bulk != null && typeRuntime.Identity != null) { builder.AppendNewLine(); builder.Append("OUTPUT INSERTED."); string memberName = TypeUtils.GetFieldName(typeRuntime.Identity.Member, typeRuntime.Type); builder.AppendMember(memberName); builder.AppendAs(AppConst.AUTO_INCREMENT_NAME); } builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (tree.Bulk != null && !tree.Bulk.IsEndPos) { builder.Append(","); } if (tree.Bulk == null && typeRuntime.Identity != null) { builder.AppendNewLine(); builder.Append("SELECT SCOPE_IDENTITY()"); builder.AppendAs(AppConst.AUTO_INCREMENT_NAME); } } else if (tree.Select != null) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); var srcDbExpressionType = context.CurrentExpressionType; var srcIsOutermost = context.CurrentIsOutermost; context.CurrentExpressionType = DbExpressionType.Insert; context.CurrentIsOutermost = true; var cmd = this.TranslateSelectCommand(tree.Select, 0, true, context) as DbSelectCommand; context.CurrentExpressionType = srcDbExpressionType; context.CurrentIsOutermost = srcIsOutermost; int index = 0; foreach (ColumnDescriptor column in cmd.SelectedColumns) { builder.AppendMember(column.NewName); if (index < cmd.SelectedColumns.Count - 1) { builder.Append(','); } index++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); } ((SqlServerDbContext)context.DbContext).NoLock = noLock; return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text)); }