none
Best way to handle exception in ADO.Net repository pattern RRS feed

  • Question

  • i have design small repository pattern for ado.net. now i could not manage to handle exception proper way. i want to push error to calling environment if any occur. if no error occur then result set will be push to calling environment.

    i have repository called AdoRepository which extend other repository classes like employee etc. we are calling employee repository function from mvc controller. so i want to push error to mvc controller from employee repository if any occur during data fetch, if no error occur then data will be sent to mvc controller. here is my full code. please have look and share the idea for best design. if possible paste rectified code here.

    Base repository

     public abstract class AdoRepository<T> where T : class
        {
            private SqlConnection _connection;
            public virtual void Status(bool IsError, string strErrMsg)
            {
    
            }
    
            public AdoRepository(string connectionString)
            {
                _connection = new SqlConnection(connectionString);
            }
    
            public virtual T PopulateRecord(SqlDataReader reader)
            {
                return null;
            }
    
            public virtual void GetDataCount(int count)
            {
    
            }
    
            protected IEnumerable<T> GetRecords(SqlCommand command)
            {
                var reader = (SqlDataReader) null;
                var list = new List<T>();
                try
                {
                    command.Connection = _connection;
                    _connection.Open();
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        list.Add(PopulateRecord(reader));
                    }
    
                    reader.NextResult();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            GetDataCount(Convert.ToInt32(reader["Count"].ToString()));
                        }
                    }
                    Status(false, "");
                }
                catch (Exception ex)
                {
                    Status(true, ex.Message);
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                    _connection.Close();
                    _connection.Dispose();
                }
    
                return list;
            }
    
            protected T GetRecord(SqlCommand command)
            {
                var reader = (SqlDataReader)null;
                T record = null;
    
                try
                {
                    command.Connection = _connection;
                    _connection.Open();
    
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        record = PopulateRecord(reader);
                        Status(false, "");
                        break;
                    }
                }
                catch (Exception ex)
                {
                    Status(true, ex.Message);
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                    _connection.Dispose();
                }
                return record;
            }
    
            protected IEnumerable<T> ExecuteStoredProc(SqlCommand command)
            {
                var reader = (SqlDataReader)null;
                var list = new List<T>();
                try
                {
                    command.Connection = _connection;
                    command.CommandType = CommandType.StoredProcedure;
                    _connection.Open();
                    reader = command.ExecuteReader();
    
                    while (reader.Read())
                    {
                        var record = PopulateRecord(reader);
                        if (record != null) list.Add(record);
                    }
                }
                finally
                {
                    // Always call Close when done reading.
                    reader.Close();
                    _connection.Close();
                    _connection.Dispose();
                }
                return list;
            }
        }
    
    StudentRepository which extend base AdoRepository
    -----------------------------------------------
    public class StudentRepository : AdoRepository<Student>
        {
            public int DataCounter { get; set; }
            public bool hasError { get; set; }
            public string ErrorMessage { get; set; }
    
            public StudentRepository(string connectionString)
                : base(connectionString)
            {
            }
    
            public IEnumerable<Student> GetAll()
            {
                // DBAs across the country are having strokes 
                //  over this next command!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents"))
                {
                    return GetRecords(command);
                }
            }
            public Student GetById(string id)
            {
                // PARAMETERIZED QUERIES!
                using (var command = new SqlCommand("SELECT ID, FirstName,LastName,IsActive,StateName,CityName FROM vwListStudents WHERE Id = @id"))
                {
                    command.Parameters.Add(new ObjectParameter("id", id));
                    return GetRecord(command);
                }
            }
    
            public IEnumerable<Student> GetStudents(int StartIndex, int EndIndex, string sortCol, string sortOrder)
            {
                string strSQL = "SELECT * FROM vwListStudents WHERE ID >=" + StartIndex + " AND ID <=" + EndIndex;
                strSQL += " ORDER BY " + sortCol + " " + sortOrder;
                strSQL += ";SELECT COUNT(*) AS Count FROM vwListStudents";
                var command = new SqlCommand(strSQL);
                return GetRecords(command);
            }
    
            public override Student PopulateRecord(SqlDataReader reader)
            {
                return new Student
                {
                    ID = Convert.ToInt32(reader["ID"].ToString()),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    IsActive = Convert.ToBoolean(reader["IsActive"]),
                    StateID = Convert.ToInt32(reader["StateID"].ToString()),
                    StateName = reader["StateName"].ToString(),
                    CityID = Convert.ToInt32(reader["CityID"].ToString()),
                    CityName = reader["CityName"].ToString()
                };
            }
    
            public override void GetDataCount(int count)
            {
                DataCounter = count;
            }
    
            public override void Status(bool IsError, string strErrMsg)
            {
                hasError = IsError;
                ErrorMessage = strErrMsg;
            }
    }

    calling StudentRepository from mvc controller like below way

     public class StudentController : Controller
        {
            private StudentRepository _data;
    
            public StudentController()
            {
                _data = new StudentRepository(System.Configuration.ConfigurationManager.ConnectionStrings["StudentDBContext"].ConnectionString);
            }
    
            // GET: Stuent
            public ActionResult List(StudentListViewModel oSVm)
            {
    
                StudentListViewModel SVm = new StudentListViewModel();
                SVm.SetUpParams(oSVm);
                SVm.Students = _data.GetStudents(SVm.StartIndex, SVm.EndIndex, SVm.sort, oSVm.sortdir).ToList();
                SVm.RowCount = _data.DataCounter;
    
                return View("ListStudents",SVm);
            }
        }
    Monday, January 4, 2016 8:32 AM

Answers

  • Hi Mou_kolkata,

    >>so i want to push error to mvc controller from employee repository if any occur during data fetch.

    You could try to throw exception to mvc controller instead of catching exception or ignoring the exception. You could override Status method as below.

    public override void Status(bool IsError, string strErrMsg)
    {            hasError = IsError;
                ErrorMessage = strErrMsg;
               If(IsError)
               {
                    throw new Exception(strErrMsg);       
             }
    }

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, January 5, 2016 8:09 AM
    Moderator