n-tier structure and how to use SqlDatabase

Topics: Data Access Application Block, General discussion
Jun 2, 2014 at 1:46 PM
Hi

Am about to update to v6 of the library and have some questions.

First a general design question. The code below shows the structure I'm using in my asp.net webforms solution. Is this a good design choice? If no, why and what should I change?

And the other question is about the new DAAB. In the SqlCustomerProvider class I show you two functions GetById1 and GetById2. In GetById1 is how I used to connect to the database in v5, and in GetById2 how I should do it now in v6 as I understand it. Both ways work, so whats the pros with the new way.

Thanks
namespace MyApp.UI
{
    public partial class CustomerPage : System.Web.UI.Page
    {
        private void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Customer c = Customer.GetById(1);
            }
        }
    }
}
namespace MyApp.Business
{
    public class Customer
    {
        private int _id;
        private string _name;
        private string _email;

        public int Id { get { return _id; } set { _id = value; } }
        public string Name { get { return _name; } set { _name = value; } }
        public string Email { get { return _email; } set { _email = value; } }

        public static Customer GetById(int id)
        {
            return Adapter.Customer.GetById(id);
        }
    }
}
namespace MyApp.Provider
{
    public static class Adapter
    {
        public static SqlCustomerProvider Customer
        {
            get
            {
                return new SqlCustomerProvider();
            }
        }
    }
}
namespace MyApp.Provider
{
    public class SqlCustomerProvider
    {
        // 1. Old practice
        public Customer GetById1(int id)
        {
            SqlDatabase db = new SqlDatabase(Utility.PathManager.GetDBConnectionString());
            DbCommand cmd = db.GetStoredProcCommand("dbo.Customer_GetById");
            db.AddInParameter(cmd, "@customerid", SqlDbType.Int, id);

            Customer c = null;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr.Read())
                {
                    c = new Customer();
                    c.Id = Convert.ToInt32(dr["Id"]);
                    c.Name = Convert.ToString(dr["Name"]);
                    c.Email = Convert.ToString(dr["Email"]);
                }
            }
            return c;
        }

        // 2. New practice
        private static DatabaseProviderFactory _factory = new DatabaseProviderFactory();
        private SqlDatabase db = _factory.Create("AppDB") as SqlDatabase;
        public Customer GetById2(int id)
        {
            DbCommand cmd = db.GetStoredProcCommand("dbo.Customer_GetById");
            db.AddInParameter(cmd, "@customerid", SqlDbType.Int, id);

            Customer c = null;

            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                if (dr.Read())
                {
                    c = new Customer();
                    c.Id = Convert.ToInt32(dr["Id"]);
                    c.Name = Convert.ToString(dr["Name"]);
                    c.Email = Convert.ToString(dr["Email"]);
                }
            }
            return c;
        }
    }
}
Jun 3, 2014 at 4:59 AM
One important aspect of n-tier development would be to ensure that each layer only has knowledge of the layer/tier immediately adjacent. i.e. the UI layer should not have any knowledge of the data layer. Your example seems to align with that.

Some comments:

  • The entity class (e.g. Customer) also has behavior exposed through static methods. You might want to move the behavior elsewhere and go with straight POCO classes.
  • Using static methods hurts testability if this is important to you

You only really need one DatabaseProviderFactory for your application so you could move that to a ProviderBase class.
Do you really need a SqlDatabase instance or could you leave the variable as a Database?
DbCommand implements IDisposable so it would be good form to call Dispose (although in this case it doesn't usually cause any harm not to call it).

I prefer the second approach especially if you change the SqlDatabase to Database then you can avoid instantiating an object directly and let the factory do this for you. You might want to move this to a base class to avoid code duplication (if that applies).

You could also consider using Dependency Injection to have the Database objects injected into the providers instead of having the providers responsible for creating the Database objects. You would probably want to do this for all classes so it could be a big change but it's something to think about.


~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Jun 3, 2014 at 2:34 PM
Hi Randy and thanks for your input.

I have moved the DatabaseProviderFactory to a ProviderBase class and also disposing the DbCommand by putting it within a using. So now it looks something like this:
    public class SqlProviderBase
    {
        private static DatabaseProviderFactory _factory = new DatabaseProviderFactory();
        protected SqlDatabase db = _factory.Create("AppDB") as SqlDatabase;
    }
    public class SqlCustomerProvider : SqlProviderBase
    {        
        public bool Create(string userid, string email)
        {
            bool r = false;
            using (DbCommand cmd = db.GetStoredProcCommand("dbo.Customer_Create"))
            {
                db.AddInParameter(cmd, "@userid", SqlDbType.UniqueIdentifier, Guid.Parse(userid));
                db.AddInParameter(cmd, "@email", SqlDbType.NVarChar, email);
                db.AddOutParameter(cmd, "@statuscode", SqlDbType.Int, 0);

                db.ExecuteNonQuery(cmd);
                r = Convert.ToInt32(db.GetParameterValue(cmd, "@statuscode")) == 100;
            }
            return r;
        }
    }
I also like the POCO approach so I have started to move the methods from the entity classes into a Manager namespace, shown below. So instead of calling Customer.GetById() I call it by Manager.Customer.GetById().
namespace MyApp.Manager
{
    public static class Customer
    {        
        public static Business.Customer GetById(int customerid)
        {
            return Adapter.Customer.GetById(customerid);
        }
    }
}