SqlConnection Transaction Management Approach I am currently working on a SharePoint 2013 project with custom application pages development. On one of those pages, there are multiple insert / update to database table operations. Here are the sample code I am working with:

Product.cs

class Product
{
    public int ProductId { get; set; };
    public string ProductName { get; set; };
}


Here is the helper class which handle CRUD of products:

ProductFacade.cs

class ProductFacade
{
    public static Product Save(Product productObject)
    {
        Product product;
        using(SqlConnection conn = new SqlConnection(DatabaseHelper.ConnectionString)) // The connection string is retrieved from other helper class
        {
            SqlCommand cmd = new SqlCommand("SaveProduct", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();

            // Parameters setting and execute query operations here..
        }
        return product;
    }
}


In the application pages, I used the method like:

ApplicationPage.aspx.cs

public void SaveProducts()
{
    try
    {
        DataTable products = (DataTable)ViewState["Products"];
        if(products != null)
        {
            Product product;
            foreach(DataRow row in products.Rows)
            {
                product = new Product();
                product.ProductName = row["ProductName"].ToString();
                ProductFacade.Save(product);
            }
        }
    }
    catch(Exception ex)
    {
        LogHelper.WriteLog(Category.Unexpected, "Product Application Page", ex.Message + ", " + ex.StackTrace);
    }
}


All of the methods in that helper class work fine. But I am curious, how if I want to add transaction management, for example, if I want to add BeginTransaction, Commit, and Rollback operation on the above SaveProducts() method? Should I create and pass the SqlConnection object as a parameter to the ProductFacade.Save() method so it will be something like:

ProductFacade.cs

class ProductFacade
{
    public static Product Save(Product productObject, SqlConnection conn = null, SqlTransaction tran = null)
    {
        Product product;
        if(conn == null)
            conn = new SqlConnection(DatabaseHelper.ConnectionString);
        using(SqlCommand cmd = new SqlCommand("SaveProduct", conn))
        {
            if(tran != null)
                cmd.Transaction = tran;
            // The rest of the same code from before
        }
    }
}


And call the method with SqlConnection object passed like:

ApplicationPage.aspx.cs

public void SaveProducts()
{
    using(SqlConnection conn = new SqlConnection(DatabaseHelper.ConnectionString))
    {
        conn.Open();
        SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            DataTable products = (DataTable)ViewState["Products"];
            if(products != null)
            {
                Product product;
                foreach(DataRow row in products.Rows)
                {
                    product = new Product();
                    product.ProductName = row["ProductName"].ToString();
                    ProductFacade.Save(product, conn, transaction);
                }
            }
            transaction.Commit();
        }
        catch(Exception ex)
        {
            transaction.RollBack();
            LogHelper.WriteLog(Category.Unexpected, "Product Application Page", ex.Message + ", " + ex.StackTrace);
        }
    }
}


Really appreciate any thoughts on this.
Don't add a the parameters to the save method. It adds unnecessary complexity to the api and it makes it a leaky abstraction because it forces knowledge of the underlying data store onto consumers.

Add a SaveAll method to your repository (why is it called a Facade?)

public static Product SaveAll(IEnumerable&lt;Product&gt; products)
{
    using (var scope = new TransactionScope())
    using (var conn = new SqlConnection(DatabaseHelper.ConnectionString))
    {
        conn.Open();
        foreach (var product in products)
        {
            Save(product, conn);
        }
        scope.Complete();
    }
}

// Used by both public `Save` and public `SaveAll`.
private static Product Save(Product product, SqlConnection conn)
{
    using (var cmd = conn.CreateCommand("SaveProduct"))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // etc.
    }
}


I haven't written raw ADO.Net code for several years so I apologise if the above is massively incorrect. You should explore a product like Entity Framework (full ORM) or Dapper (more lightweight) to simplify a lot of this repetitive code.