Building an Agile SQL Data Access Layer

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

namespace InMemoryDal.SqlData
{
	public class SqlOrderDAL: IOrderDAL
	{
        private const string _insertSqlString =
            "INSERT INTO Orders (Id, IdCustomer, OrderDate) VALUES (@Id, @IdCustomer, @OrderDate)";

        private const string _updateSqlString =
            "UPDATE Orders SET IdCustomer = @IdCustomer, OrderDate = @OrderDate WHERE (Id  = @Id)";

        private const string _deleteSqlString =
            "DELETE FROM Orders WHERE (Id = @Id )";

        private const string _selectSqlString =
            "SELECT Id, IdCustomer, OrderDate " +
            "FROM Orders " +
            "WHERE 1 = 1 "; // "AND (Id = @Id) AND (IdCustomer = @IdCustomer) AND (OrderDate = @OrderDate)";

        private const string _checkExistenceSqlString =
            "SELECT count(*) FROM Orders WHERE (Id = @Id)";

		private SqlConnection _sqlConnection;
        private SqlTransaction _sqlTransaction;
		
        /// <summary>
        /// Constructor is internal since we don't want client code 
        /// to instantiate the class directly.
        /// The session class will.
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="sqlTransaction"></param>
        internal SqlOrderDAL(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
        {
            _sqlConnection = sqlConnection;
            _sqlTransaction = sqlTransaction;
        }

        private static void parametersCommand(SqlConnection connection, SqlTransaction transaction, Order order, SqlCommand sqlComm)
        {
            sqlComm.Connection = connection;
            sqlComm.Transaction = transaction;
            sqlComm.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = order.Id;
            sqlComm.Parameters.Add("@IdCustomer", SqlDbType.UniqueIdentifier).Value = order.Customer.Id;
            sqlComm.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = order.OrderDate;
        }

        public List<Order> Search(Guid id, Guid idCustomer, DateTime? orderDate, OrderDataAdapterHandler orderDataAdapter)
        {
            List<Order> lro = new List<Order>();

            StringBuilder sb = new StringBuilder(_selectSqlString);

            if (id != Guid.Empty)
            {
                sb.Append(" AND (Id = @Id)");
            }
            if (idCustomer != Guid.Empty)
            {
                sb.Append(" AND (IdCustomer = @IdCustomer)");
            }
            if (orderDate.HasValue)
            {
                sb.Append(" AND (OrderDate = @OrderDate)");
            }

            SqlCommand sqlCmd = new SqlCommand(sb.ToString(), _sqlConnection);

            try
            {
                //sqlCmd.Parameters.Clear();
                if (id != Guid.Empty)
                {
                    sqlCmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = id;
                }
                if (idCustomer != Guid.Empty)
                {
                    sqlCmd.Parameters.Add("@IdCustomer", SqlDbType.UniqueIdentifier).Value = idCustomer;
                }
                if (orderDate.HasValue)
                {
                    sqlCmd.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = orderDate;
                }

                using (SqlDataReader dr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        Order ro = orderDataAdapter(
                            DBGet.Guid(dr, "Id"),
                            DBGet.Guid(dr, "IdCustomer"),
                            DBGet.DateTime(dr, "OrderDate")
                            );
                        lro.Add(ro);
                    }
                }
            }
            catch (Exception ex)
            {
                ApplicationException exp = new ApplicationException("An error occurred performing the search query on Orders", ex);
                throw exp;
            }
            return lro;
        }

		public void Save(Order order)
		{
			SqlCommand sqlComm = new SqlCommand();

			int righe = 0;

			sqlComm = CheckExistenceCommand(_sqlConnection, _sqlTransaction, order.Id);
			righe = (int)sqlComm.ExecuteScalar();

			// non presente, comando Insert
			if (righe == 0)
			{
                sqlComm = InsertCommand(_sqlConnection, _sqlTransaction, order);
				sqlComm.ExecuteNonQuery();
			}
			else
			// presente, comando Update
			{
				sqlComm = UpdateCommand(_sqlConnection, _sqlTransaction, order);
				sqlComm.ExecuteNonQuery();
			}		
		}

		public void Delete(Order order)
		{
			SqlCommand sqlComm = new SqlCommand();

			sqlComm = DeleteCommand(_sqlConnection, _sqlTransaction, order);
			sqlComm.ExecuteNonQuery();
		}

        public SqlCommand CheckExistenceCommand(SqlConnection connection, SqlTransaction transaction, Guid guid)
		{
			SqlCommand sqlComm = new SqlCommand(_checkExistenceSqlString);
			sqlComm.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = guid;
			sqlComm.Connection = connection;
			sqlComm.Transaction = transaction;

			return sqlComm;
		}

		private SqlCommand InsertCommand(SqlConnection connection, SqlTransaction transaction, Order entity)
		{
			SqlCommand sqlComm = new SqlCommand(_insertSqlString);
			parametersCommand(connection, transaction, entity, sqlComm);
			return sqlComm;
		}

		private SqlCommand UpdateCommand(SqlConnection connection, SqlTransaction transaction, Order entity)
		{
			SqlCommand sqlComm = new SqlCommand(_updateSqlString);
			parametersCommand(connection, transaction, entity, sqlComm);
			return sqlComm;
		}

		private SqlCommand DeleteCommand(SqlConnection connection, SqlTransaction transaction, Order entity)
		{
			SqlCommand sqlComm = new SqlCommand(_deleteSqlString);
			sqlComm.Connection = connection;
			sqlComm.Transaction = transaction;
			sqlComm.Parameters.Add("@Id ", SqlDbType.UniqueIdentifier).Value = entity.Id;
			return sqlComm;
		}
	}
}