ADO Database ASP.Net

  1. Home
  2. ASP.Net
  3. ADO Database
  4. Implicit Transactions using TransactionScope
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Transactions" %>
<%@ Import Namespace="System.Web.Configuration" %>

    void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
            int categoryID;
            string connectionString = WebConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;            
            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {                    
                  categoryID = InsertCategory(connection);
                }
                scope.Complete();
            }
            lblResult.Text= "Category is written successfully. Category ID= " + categoryID.ToString();
        }
        catch (Exception ex)
        {
            lblResult.Text= "Exception is : " + ex.Message;
        }
    }
    
    int InsertCategory(SqlConnection connection)
    {
        string sql = "Insert into Production.ProductCategory(Name," +
                        "rowguid, ModifiedDate) Values(@Name, @rowguid, @ModifiedDate); SELECT @@IDENTITY";
        connection.Open(); 
        SqlCommand command = new SqlCommand(sql, connection);
        command.CommandType = CommandType.Text;
        SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
        nameParam.Value = txtCategoryName.Text;
        command.Parameters.Add(nameParam);
        SqlParameter guidParam = new SqlParameter("@rowguid", SqlDbType.UniqueIdentifier);
        guidParam.Value = System.Guid.NewGuid();
        command.Parameters.Add(guidParam);
        SqlParameter modifieDateParam = new SqlParameter("@ModifiedDate", SqlDbType.DateTime);
        modifieDateParam.Value = System.DateTime.Now;
        command.Parameters.Add(modifieDateParam);
        int categoryID = Convert.ToInt32(command.ExecuteScalar());  
        return categoryID;      
    }



    Implicit Transactions using TransactionScope