Generic Repository + Unit of Work + Business Entity+ Business Service + Data Access + Entity Framework Layer calling Stored Procedure

Question

I have such a long title to be able to hopefully get a broader audience as I am unsure how to proceed. I am trying to write a generic repository for executing stored procedures with the Unit of work and repository pattern.

I have 4 projects in my solution

Business Entity used for holding poco's to map out my entities (seperation of concerns)

Business Services these hold my interfaces and service contracts

 public interface ItblAnswerServices
    {
        tblAnswerEntity GettblAnswerByID(int tblAnswerID);
        IEnumerable<tblAnswerEntity> GetAlltblAnswers();
        int CreatetblAnswer(tblAnswerEntity tblAnswer);
        bool UpdatetblAnswer(int tblAnswerid, tblAnswerEntity tblAnswer);
        bool DeletetblAnswer(int tblAnswerid);
    }




/// <summary>
        /// Updates a tblAnswer
        /// </summary>
        /// <param name="tblAnswerId"></param>
        /// <param name="tblAnswerEntity"></param>
        /// <returns></returns>
        public bool UpdatetblAnswer(int tblAnswerID, BusinessEntites.tblAnswerEntity tblAnswerEntity)
        {
            var success = false;
            if (tblAnswerEntity != null)
            {
                using (var scope = new TransactionScope())
                {
                    var tblAnswer = _unitOfWork.tblAnswerRepository.GetByID(tblAnswerID);
                    if (tblAnswer != null)
                    {
                        tblAnswer.QuestionID = tblAnswerEntity.QuestionID;
                        tblAnswer.AnswerText = tblAnswerEntity.AnswerText;
                        tblAnswer.Correct = tblAnswerEntity.Correct;

                        _unitOfWork.tblAnswerRepository.Update(tblAnswer);
                        _unitOfWork.Save();
                        scope.Complete();
                        success = true;
                    }
                }
            }
            return success;
        }

DataAccess this holds my Generic Repository as well as my Unit of Work and my EF6 DB entity model connection (.edmx file)

in my generic repository I am trying to use

  public IEnumerable<T> ExecWithStoreProcedure<T>(string query)
        {
            return Context.Database.SqlQuery<T>(query);
        }

I would prefer not to have to list out all of my Stored Procedures (there are lots) but if I have to in the UnitOfWork I will. Is there a generic method that I could use here? My EF model does actually have all of the SP's in it to be called. I need this to bubble up to my Web API to be called by an HTTP verb.

score 0 · Answer 1 · edited May 23 '17 at 12:08

I'm not sure what is your problem with this approache but I will give you some general advice:

I assume you are using Schema first or Model first (you mention .edmx file). You can generate Stored procedures call with edmx designer, you can get information from here.
If you stick to custom generic call style on repository (based on your sample) then you have to write wrapper methods for SPs (similar to yours). You can speed up this work with a little T4 template magic (text template transformation tool). Read more about it here. In this case you have to access to the current DbContext instance and exec the stored procedure on that connection. Using of SlqCommand or DbCommand is strongly recommended!
You are using TransactionScope what will cover all your scenario from transaction point of view. You have nothing more to do with this topic. In some advanced scenario you have to enable MARS (multiple active result sets) and MSDTC service. Here is a discussion from SO.

If you provide more detail or clarify the problem then I can give you more specific help/answer.

Generic Repository + Unit of Work + Business Entity+ Business Service + Data Access + Entity Framework Layer calling Stored Procedure

1 Answers1