using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Transaction : System.Web.UI.Page
{

    protected void btnOK_Click(object sender, EventArgs e)
    {
        ConnectionStringSettings getString = WebConfigurationManager.ConnectionStrings["nwind"] as ConnectionStringSettings;
        if (getString != null)
        {
            string sSql = "insert into Categories(CategoryName) values('Livros')";
            string sSql1 = "insert into Products(ProductName) values('ASP.NET e C#')";
            using (SqlConnection conn = new SqlConnection(getString.ConnectionString))
            {
                SqlTransaction sqlTran = null;
                SqlCommand cmd = conn.CreateCommand();

                try
                {
                    conn.Open();
                    sqlTran = conn.BeginTransaction();
                    cmd.Transaction = sqlTran;

                    cmd.CommandText = sSql;
                    int i = cmd.ExecuteNonQuery();

                    cmd.CommandText = sSql1;
                    int x = cmd.ExecuteNonQuery();

                    if (i > 0 && x > 0)
                    {
                        sqlTran.Commit();
                        Label1.Text = "Registro inserido com sucesso.";
                    }
                }
                catch (SqlException)
                {
                    try
                    {
                        sqlTran.Rollback();
                        Label1.Text = "Erro ao inserir registro.";
                    }
                    catch (Exception)
                    {
                        Label1.Text = "Erro SQL.";
                    }
                }
                catch (InvalidOperationException)
                {
                    Label1.Text = "Operação inválida.";
                }
                catch (Exception)
                {
                    Label1.Text = "Erro ao executar código.";
                }
            }
        }
    }
}