備忘錄_20160105(定位) 修改 回首頁

程式 2019-06-12 15:40:06 1560325206 100
C# sql transaction 範例

C# sql transaction 範例

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnRun_Click(object sender, EventArgs e)
        {
            bool booErr = false;
            string strErr = "";
            string strMsg = "";

            tbReport.Text = "";

            SqlConnection oConn = null;
            SqlCommand oCmd = null;
            SqlDataReader oDR = null;
            SqlTransaction oTrans = null;

            try
            {
                while (true)
                {
                    oConn = new SqlConnection("Data Source=192.168.1.15,1433; Initial Catalog=database_name; User ID=user_name; Password=user_password;");
                    oConn.Open();
                    oTrans = oConn.BeginTransaction();

                    oCmd = new SqlCommand("create table tb1(i int primary key,vc varchar(32) not null)", oConn, oTrans);
                    int iAffected = oCmd.ExecuteNonQuery();
                    if (iAffected != -1) { booErr = true; strErr += "建立資料表失敗!"; break; }
                    else { strMsg += "建立資料表成功!\r\n"; }

                    oCmd = new SqlCommand("insert into tb1(i,vc) values(1,'haha-first')", oConn, oTrans);
                    iAffected = oCmd.ExecuteNonQuery();
                    if (iAffected != 1) { booErr = true; strErr += "新增一筆資料失敗!i=1"; break; }
                    else { strMsg += "新增一筆資料成功!\r\n"; }

                    oCmd = new SqlCommand("insert into tb1(i,vc) values(2,'this is second chance')", oConn, oTrans);
                    iAffected = oCmd.ExecuteNonQuery();
                    if (iAffected != 1) { booErr = true; strErr += "新增一筆資料失敗!i=2"; break; }
                    else { strMsg += "新增另一筆資料成功!\r\n"; }
                    
                    oCmd = new SqlCommand("select i, vc from tb1", oConn, oTrans);
                    oDR = oCmd.ExecuteReader();
                    if (oDR.HasRows == false)
                    {
                        booErr = true;
                        strErr += "抱歉,讀取時發生意外,SQL沒有回傳任何資料!";
                        break;
                    }
                    else
                    {
                        StringBuilder oSB = new StringBuilder();
                        while (oDR.Read() == true)
                        {
                            oSB.AppendLine(oDR.GetValue(0).ToString() + "-" + oDR.GetString(1));
                        }
                        strMsg += oSB.ToString();
                    }

                    break;
                }

            }
            catch (Exception ex01)
            {
                booErr = true;
                strErr += "發生意外!" + ex01.ToString();
            }
            finally
            {
                if (oDR != null)
                {
                    try { oDR.Close(); }
                    catch (Exception ex02) { booErr = true; strErr += "關閉 DataReader 時發生意外!" + ex02.ToString(); }
                }

                if (booErr == false)
                {
                    try { oTrans.Commit(); }
                    catch (Exception ex03) { booErr = true; strErr += "準備完成交易時發生意外!(交易可能未完成!)" + ex03.ToString(); }
                }
                else
                {
                    try { oTrans.Rollback(); }
                    catch (Exception ex04) { booErr = true; strErr += "準備復原交易時發生意外!" + ex04.ToString(); }
                }

                if (oConn != null)
                {
                    try { oConn.Close(); }
                    catch (Exception ex05) { booErr = true; strErr += "準備關閉連線時發生意外!(交易可能未完成!)" + ex05.ToString(); }
                }
            }

            if (booErr == false)
            {
                tbReport.Text = "任務成功!\r\n" + strMsg;
            }
            else
            {
                tbReport.Text = "任務失敗!\r\n" + strErr + "\r\nmessage:\r\n" + strMsg;
            }
        }
    }
}