/// <summary> /// 模拟ORACEL的Merge into数据库操作 /// 将ORACLE的Merge into分为三个步骤 /// 1. 得到需要merge到最终表的数据源,需要与最终表做一个外链接,并在数据集中取出最终表的KEY值 /// 2. 将得到的数据源根据最终表的ID是否有值来区分出insert与update的数据集 /// 3. 批量执行insert和update /// 使用该方法的人需要提供select(得到数据源),insert和update的SQL语句,并指定最终表 /// </summary> /// <param name="mergeIntoPara">执行merge into操作需要的参数</param> /// <param name="paras">在SQL中的实际参数</param> public void MergeInto <T>(MergrIntoParameter mergeIntoPara, params Object[] paras) { if (string.IsNullOrEmpty(mergeIntoPara.KeyColumn)) { mergeIntoPara.KeyColumn = "ID"; } List <T> sources = this.GetObjectsByXml <T>( mergeIntoPara.SqlFileName, mergeIntoPara.SelectSourceSqlName, paras); //如果得到的需要被mergeinto的数据源是空的,则结束mergeInto操作 if (sources == null || sources.Count <= 0) { return; } List <T> insertedSources = new List <T>(); List <T> updatedSources = new List <T>(); foreach (T source in sources) { int id = MB.Util.MyReflection.Instance.InvokePropertyForGet <int>(source, mergeIntoPara.KeyColumn); if (id > 0) { updatedSources.Add(source); } else { insertedSources.Add(source); } } using (TransactionScope scope = new TransactionScope()) { using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute()) { if (insertedSources.Count > 0) { int identityStartValue = MB.Orm.Persistence.EntityIdentityHelper.NewInstance.GetEntityIdentity(mergeIntoPara.TargetTableName, insertedSources.Count); insertedSources.ForEach(t => { MB.Util.MyReflection.Instance.InvokePropertyForSet(t, mergeIntoPara.KeyColumn, identityStartValue++); }); bulk.WriteToServer(mergeIntoPara.SqlFileName, mergeIntoPara.InsertSqlName, insertedSources); } if (updatedSources.Count > 0) { bulk.WriteToServer(mergeIntoPara.SqlFileName, mergeIntoPara.UpdateSqlName, updatedSources); } } scope.Complete(); } }
/// <summary> /// 显示传入transaction的 mergeInto, 功能与transactionscope的merge into相一致 /// </summary> public void MergeInto <T>(Database db, DbTransaction transaction, MergrIntoParameter mergeIntoPara, params Object[] paras) { if (string.IsNullOrEmpty(mergeIntoPara.KeyColumn)) { mergeIntoPara.KeyColumn = "ID"; } List <T> sources = null; if (db == null) { sources = this.GetObjectsByXml <T>(transaction, typeof(T), mergeIntoPara.SqlFileName, mergeIntoPara.SelectSourceSqlName, paras); } else { sources = this.GetObjectsByXml <T>(db, transaction, typeof(T), mergeIntoPara.SqlFileName, mergeIntoPara.SelectSourceSqlName, paras); } //如果得到的需要被mergeinto的数据源是空的,则结束mergeInto操作 if (sources == null || sources.Count <= 0) { return; } List <T> insertedSources = new List <T>(); List <T> updatedSources = new List <T>(); foreach (T source in sources) { int id = MB.Util.MyReflection.Instance.InvokePropertyForGet <int>(source, mergeIntoPara.KeyColumn); if (id > 0) { updatedSources.Add(source); } else { insertedSources.Add(source); } } MB.RuleBase.BulkCopy.IDbBulkExecute bulk = null; using (bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute(transaction)) { try { if (insertedSources.Count > 0) { int identityStartValue = MB.Orm.Persistence.EntityIdentityHelper.NewInstance.GetEntityIdentity(mergeIntoPara.TargetTableName, insertedSources.Count); insertedSources.ForEach(t => { MB.Util.MyReflection.Instance.InvokePropertyForSet(t, mergeIntoPara.KeyColumn, identityStartValue++); }); bulk.WriteToServer(mergeIntoPara.SqlFileName, mergeIntoPara.InsertSqlName, insertedSources); } if (updatedSources.Count > 0) { bulk.WriteToServer(mergeIntoPara.SqlFileName, mergeIntoPara.UpdateSqlName, updatedSources); } } catch (Exception ex) { throw new MB.RuleBase.Exceptions.DatabaseExecuteException("执行MergeInto 出错!", ex); } } }
/// <summary> /// 显示传入transaction的 mergeInto, 功能与transactionscope的merge into相一致 /// </summary> public void MergeInto <T>(DbTransaction transaction, MergrIntoParameter mergeIntoPara, params Object[] paras) { MergeInto <T>(null, transaction, mergeIntoPara, paras); }