SqlConnection 交易機制

private static void ExecuteSqlTransaction(string connectionString) {
    using (SqlConnection connection = new SqlConnection(connectionString)) {
        connection.Open();
        SqlCommand sc = connection.CreateCommand();
        // 啟動交易機制
        SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted, "SampleTransaction");
        // 基本設定讓 sc 使用交易程序
        // 怪怪的.Net sc 既然來自 connection卻又要人指定回去...
        sc.Connection = connection;
        sc.Transaction = transaction;
        try
        {
            sc.CommandText = "insert into userlist (id,name)values(@id,@name)";
            sc.Parameters.AddWithValue("@id", 1);
            sc.Parameters.AddWithValue("@name", "shioulo");
            //方法二
            //sc.Parameters.Add("@id", SqlDbType.Int);
            //sc.Parameters.Add("@name", SqlDbType.NVarChar, 30);
            //sc.Parameters["@id"].Value = 1
            //sc.Parameters["@name"].Value = "shioulo"
 
            sc.ExecuteNonQuery();
            transaction.Commit(); //交易提交
            Console.WriteLine("儲存成功");
        } catch (Exception e) {
            try {
                transaction.Rollback(); //交易回復
            } catch (SqlException ex) {
                if (transaction.Connection != null)
                {
                    Console.WriteLine("例外:" + ex.GetType() + " 回復交易程序失敗.");
                }
            }
            Console.WriteLine("例外:" + e.GetType() + " 寫入程序失敗.");
        }
    }
}

Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        connection.Open()
 
        Dim sc As SqlCommand = connection.CreateCommand()
        Dim transaction As SqlTransaction
 
        ' 啟動交易機制.
        transaction = connection.BeginTransaction( _
          IsolationLevel.ReadCommitted, "SampleTransaction")
 
        ' 基本設定讓 sc 使用交易程序
        ' 怪怪的.Net sc 既然來自 connection卻又要人指定回去...
        sc.Connection = connection
        sc.Transaction = transaction
 
        Try
            sc.CommandText = "insert into userlist (id,name)values(@id,@name)"
            sc.Parameters.AddWithValue("@id", 1)
            sc.Parameters.AddWithValue("@name", "shioulo")
            '方法二
            'sc.Parameters.Add("@id", SqlDbType.Int);
            'sc.Parameters.Add("@name", SqlDbType.NVarChar, 30);
            'sc.Parameters["@id"].Value = 1
            'sc.Parameters["@name"].Value = "shioulo"

            sc.ExecuteNonQuery()
            transaction.Commit() '交易提交
            Console.WriteLine("寫入成功.")
        Catch e As Exception
            Try
                transaction.Rollback() '交易回復
            Catch ex As SqlException
                If Not transaction.Connection Is Nothing Then
                    Console.WriteLine("例外:" & ex.GetType().ToString() & " 回復交易程序失敗.")
                End If
            End Try
 
            Console.WriteLine("例外:" & e.GetType().ToString() & " 寫入程序失敗.")
        End Try
    End Using
End Sub