3
\$\begingroup\$

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.

\$\endgroup\$
3
\$\begingroup\$

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<Product> 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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.