.NET 跨資料庫交易處理二種方式

View Code

 public void ExecuteSqlTran(List<String> ListNews, List<String> ListNews1)
    {
string connectionString = ConfigurationManager.ConnectionStrings["News"].ToString();
string connectionString1 = ConfigurationManager.ConnectionStrings["News1"].ToString();
        SqlConnection sqlcon = new SqlConnection(connectionString);
        SqlConnection sqlcon1 = new SqlConnection(connectionString1);

        sqlcon.Open();
        SqlCommand sqlcmd = new SqlCommand();
        sqlcmd.Connection = sqlcon;
        SqlTransaction sqltr = sqlcon.BeginTransaction();
        sqlcmd.Transaction = sqltr;

        sqlcon1.Open();
        SqlCommand sqlcmd1 = new SqlCommand();
        sqlcmd1.Connection = sqlcon1;
        SqlTransaction sqltr1 = sqlcon1.BeginTransaction();
        sqlcmd1.Transaction = sqltr1;
try
        {
foreach (String str in ListNews)
            {
if (str.Trim().Length > 0)
                    sqlcmd.CommandText = str;
                sqlcmd.ExecuteNonQuery();
            }

foreach (String str1 in ListNews1)
            {
if (str1.Trim().Length > 0)
                    sqlcmd1.CommandText = str1;
                sqlcmd1.ExecuteNonQuery();
            }
            sqltr1.Commit();
            sqltr.Commit();

        }
catch (Exception)
        {
            sqltr1.Rollback();
            sqltr.Rollback();
        }

        sqlcon.Close();
        sqlcon1.Close();
    }

　　

View Code

 public void ExecuteSqlTran(List<String> ListNews)
    {
string connectionString = ConfigurationManager.ConnectionStrings["News"].ToString();
using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            sqlcon.Open();
using (SqlCommand sqlcmd = new SqlCommand())
            {
                sqlcmd.Connection = sqlcon;
                SqlTransaction sqltr = sqlcon.BeginTransaction();
                sqlcmd.Transaction = sqltr;
try
                {
foreach (String str in ListNews)
                    {
if (str.Trim().Length > 0)
                            sqlcmd.CommandText = str;
                        sqlcmd.ExecuteNonQuery();
                    }
                    sqltr.Commit();
                }
catch (Exception)
                {
                    sqltr.Rollback();
                    sqlcon.Close();
                }
            }
        }
    }

第一個是建立二個資料庫連接分別操作事務

第二個是建立一個串連事務

第一種方式測試

View Code

  List<String> NewsList = new List<string>();
        NewsList.Add("INSERT INTO [News] ([Title]) VALUES('這是測試')");
        NewsList.Add("INSERT INTO [News] ([Title]) VALUES('這是測試1')");
        NewsList.Add("INSERT INTO [News] ([Title]) VALUES('這是測試2')");
        NewsList.Add("INSERT INTO [News] ([Title]) VALUES('這是測試3')"); 
        List<String> NewsList1 = new List<string>();
        NewsList1.Add("INSERT INTO [News1] ([Title]) VALUES('這是測試')");
        NewsList1.Add("INSERT INTO [News1] ([Title]) VALUES(這是測試1')");
        NewsList1.Add("INSERT INTO [News1] ([Title]) VALUES(這是測試2')");
        NewsList1.Add("INSERT INTO [News1] ([Title]) VALUES('這是測試3')");
new DB().ExecuteSqlTran(NewsList, NewsList1);

　第二種試測試

View Code

   List<String> NewsList = new List<string>();
        NewsList.Add("INSERT INTO [News] ([Title]) VALUES('這是測試')");
        NewsList.Add("INSERT INTO [News1]..[News1] ([Title]) VALUES('這是測試1')");
new DB().ExecuteSqlTran(NewsList);

　　第二種方式注意上面的插入時候的資料庫和表  方法是 資料庫..表名

如果SQL執行失敗交易回復 其實就相當於在資料庫中執行了插入刪除操作