Building an Agile SQL Data Access Layer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InMemoryDal.Interfaces;
using InMemoryDal.Entities;
using System.Data.SqlClient;
using System.Data;

namespace InMemoryDal.SqlData
{
    /// <summary>
    /// Manages session to persist data to Sql server
    /// </summary>
    public class SqlOrdersSession: InMemoryDal.Interfaces.IOrdersSession
    {
        private SqlConnection _sqlConnection;
        private SqlTransaction _sqlTransaction;

        private IOrderDAL _dbOrders;        
        //private IOrderItemDAL _dbOrderItems;
        //private ICustomerDAL _dbCustomers;
        //private IProductDAL _dbProducts;

        public SqlOrdersSession(string connectionString)
        {
            OpenConnection(connectionString);
        }

        private void OpenConnection(string connectionString)
        {
            _sqlConnection = new SqlConnection(connectionString);
            try
            {
                _sqlConnection.Open();
            }
            catch (Exception ex)
            {
                ApplicationException exp = new ApplicationException("Db connection error.", ex);
                throw exp;
            }
        }


        #region IOrdersSession Members

        public IDbTransaction BeginTransaction()
        {
            IDbTransaction st = _sqlConnection.BeginTransaction();
            _sqlTransaction = st as SqlTransaction;
            return st;
        }

        public InMemoryDal.Interfaces.ICustomerDAL DbCustomers
        {
            get { throw new NotImplementedException(); }
        }

        public void Save(InMemoryDal.Entities.Customer customer)
        {
            throw new NotImplementedException();
        }

        public void Delete(InMemoryDal.Entities.Customer customer)
        {
            throw new NotImplementedException();
        }

        public IOrderDAL DbOrders
        {
            get
            {
                if (_dbOrders == null)
                    _dbOrders = new SqlOrderDAL(_sqlConnection, _sqlTransaction);
                return _dbOrders as IOrderDAL;
            }
            private set { _dbOrders = value; }
        }

        public void Save(Order order)
        {
            (DbOrders as SqlOrderDAL).Save(order);

        }

        public void Delete(Order order)
        {
            (DbOrders as SqlOrderDAL).Delete(order);
        }

        public InMemoryDal.Interfaces.IOrderItemDAL DbOrderItems
        {
            get { throw new NotImplementedException(); }
        }

        public void Save(InMemoryDal.Entities.OrderItem orderItem)
        {
            throw new NotImplementedException();
        }

        public void Delete(InMemoryDal.Entities.OrderItem orderItem)
        {
            throw new NotImplementedException();
        }

        public InMemoryDal.Interfaces.IProductDAL DbProducts
        {
            get { throw new NotImplementedException(); }
        }

        public void Save(InMemoryDal.Entities.Product product)
        {
            throw new NotImplementedException();
        }

        public void Delete(InMemoryDal.Entities.Product product)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// Clean up transaction if not committed and sql connection
        /// </summary>
        public void Dispose()
        {
            if (_sqlTransaction != null && _sqlTransaction.Connection != null)
            {
                _sqlTransaction.Rollback();
            }
            if (_sqlConnection != null && _sqlConnection.State == ConnectionState.Open)
            {
                _sqlConnection.Close();
            }
        }

        #endregion
    }
}