/// <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();
}
}
private void button2_Click(object sender, EventArgs e)
{
int count = int.Parse(textBox1.Text);
using (MB.Orm.Persistence.DatabaseConfigurationScope scope = new MB.Orm.Persistence.DatabaseConfigurationScope("SQL SERVER")) {
using (MB.Util.MethodTraceWithTime t = new MB.Util.MethodTraceWithTime("BLUCK", count)) {
IList lstData = null;
if (chkDataTable.Checked)
{
DataTable dt = getDataTable(count, 1);
lstData = dt.Select();
}
else
{
lstData = getListData(count, 1);
}
using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute()) {
bulk.WriteToServer("MbfsFucDtl", "AddObject", lstData);
}
MessageBox.Show(string.Format("总共执行的时间有{0} 毫秒", t.GetExecutedTimes()));
}
}
}
private void button4_Click(object sender, EventArgs e)
{
//string ORACLE_FACTORY_PROVIDER = "Oracle.DataAccess.Client.OracleClientFactory";
//Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase();
//if (db.DbProviderFactory.GetType().FullName == "Oracle.DataAccess.Client.OracleClientFactory")
// MessageBox.Show("OK");
List <string> vals = new List <string>();
vals.Add("AAAAAAAAAAAA");
vals.Add("BBBBBBBBBBBB");
vals.Add("CCCCCCCCCCCC");
vals.Add("DDDDDDDDDDDD");
// using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) {
using (var dbConnection = new MB.RuleBase.BulkCopy.SimulatedOracleHelper().CreateOracleConnection()) {
dbConnection.Open();
using (var tran = dbConnection.BeginTransaction()) {
using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute(tran)) {
bulk.WriteToServer("TestOracleTransaction", "InsertGlobalTemporaryl", vals);
// MB.RuleBase.Common.DatabaseExcuteByXmlHelper.NewInstance.ExecuteNonQuery("TestOracleTransaction", "InsertGlobalTemporaryl");
var data = MB.RuleBase.Common.DatabaseExcuteByXmlHelper.NewInstance.GetDataSetByXml(tran, "TestOracleTransaction", "GetFromGlobalTemporaryl");
MessageBox.Show(data.Tables[0].Rows.Count.ToString());
}
tran.Commit();
}
}
// }
}
private void button6_Click(object sender, EventArgs e)
{
int count = int.Parse(textBox1.Text);
List <MY_TABLE_MAINInfo> myEntitys = new List <MY_TABLE_MAINInfo>();
int beginID = 1;
for (int i = 0; i < 1000; i++)
{
myEntitys.Add(new MY_TABLE_MAINInfo()
{
ID = beginID++, NAME = "CCCC", CODE = "BB", ADDRESS = "PPP"
});
}
using (MB.Orm.Persistence.DatabaseConfigurationScope scope = new MB.Orm.Persistence.DatabaseConfigurationScope("MB.MBFS")) {
Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase();
using (MB.Util.MethodTraceWithTime t = new MB.Util.MethodTraceWithTime("BLUCK", count)) {
//using (SqlConnection cn = new SqlConnection(db.ConnectionString)) {
// cn.Open();
// using (SqlTransaction tran = cn.BeginTransaction()) {
// try {
using (TransactionScope scopeTran = new TransactionScope()) {
using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute()) {
bulk.WriteToServer("MY_TABLE_MAIN", "UpdateObject", myEntitys);
}
// string data = MB.RuleBase.Common.DatabaseExcuteByXmlHelper.NewInstance.ExecuteScalar<string>("MY_TABLE_MAIN", "SelectObject", 10);
scopeTran.Complete();
// throw new Exception("ERROR");
}
// tran.Commit();
// // throw new Exception();
// }
// catch {
// tran.Rollback();
// }
//}
MessageBox.Show(string.Format("总共执行的时间有{0} 毫秒", t.GetExecutedTimes()));
}
}
}
private void button4_Click(object sender, EventArgs e)
{
int count = int.Parse(textBox1.Text);
using (MB.Orm.Persistence.DatabaseConfigurationScope scope = new MB.Orm.Persistence.DatabaseConfigurationScope("SQL SERVER")) {
Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase();
using (MB.Util.MethodTraceWithTime t = new MB.Util.MethodTraceWithTime("BLUCK", count)) {
using (SqlConnection cn = new SqlConnection(db.ConnectionString)) {
cn.Open();
using (SqlTransaction tran = cn.BeginTransaction()) {
try {
IList lstData = null;
if (chkDataTable.Checked)
{
DataTable dt = getDataTable(count, 1);
lstData = dt.Select();
}
else
{
lstData = getListData(count, 1);
}
using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute(tran)) {
bulk.WriteToServer("MbfsFucDtl", "AddObject", lstData);
}
tran.Commit();
// throw new Exception();
}
catch {
tran.Rollback();
}
}
}
MessageBox.Show(string.Format("总共执行的时间有{0} 毫秒", t.GetExecutedTimes()));
}
}
}
private void button1_Click(object sender, EventArgs e)
{
int count = int.Parse(textBox1.Text);
using (MB.Util.MethodTraceWithTime t = new MB.Util.MethodTraceWithTime("BLUCK", count)) {
int idC = MB.Orm.Persistence.EntityIdentityHelper.NewInstance.GetEntityIdentity("MBFS_FUC_DTL", count);
IList lstData = null;
if (chkDataTable.Checked)
{
DataTable dt = getDataTable(count, idC);
lstData = dt.Select();
}
else
{
lstData = getListData(count, idC);
}
using (MB.RuleBase.BulkCopy.IDbBulkExecute bulk = MB.RuleBase.BulkCopy.DbBulkExecuteFactory.CreateDbBulkExecute()) {
bulk.WriteToServer("MbfsFucDtl", "AddObject", lstData);
}
MessageBox.Show(string.Format("总共执行的时间有{0} 毫秒", t.GetExecutedTimes()));
}
}
/// <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);
}
}
}