3 tier architecture ado.net application

I would like to develop a student management system with repository pattern using ado.net (no EF or MVC).

I decided to create the model (business objects) and interfaces as one project "StudentModel". Implementing the interfaces to fetch data from DB as one project "StudentImpl" and the third will be the web application which will call the implementations and populate the views.

My Model

public class Student
 {
   public int StudentID { get; set; }
   public string StudentName { get; set; }
 }

My Repository

public interface IRepository
{
    T Create(T entity);
    T Update(T entity);
    T Delete(T entity);
    IList FindAll();
}

My Implementation

public class StudentRepository:IRepository
{
    SqlConnection aConnection;
    public StudentRepository(string ConnectionString)
    {
         aConnection = new SqlConnection(ConnectionString);
    }

    public Student Create(Student entity)
    {
        throw new NotImplementedException();
    }

    public Student Update(Student entity)
    {
        throw new NotImplementedException();
    }

    public Student Delete(Student entity)
    {
        throw new NotImplementedException();
    }

    public IList FindAll()
    {
        IList Students = new List();
        using(aConnection)
        {
            SqlCommand cmd = new SqlCommand("SELECT studentID,StudentName FROM Student", aConnection);
            aConnection.Open();
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Student aStudent = new Student();
                aStudent.StudentID = Convert.ToInt32(rdr["StudentID"]);
                aStudent.StudentName = rdr["StudentName"].ToString();
                Students.Add(aStudent);
            }
            return Students;
        }
    }

And my code behind

```
string ConString = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
IRepository aStudent = new StudentRepository(ConSt

The only thing I'd say about your repository, is that FindAll shouldn't return an IList, but an IEnumerable. And you're probably missing a method like GetById that returns only a single record - right now the only way to fetch a single student is... to fetch them all and then filter in memory. The database backend is better at this, you will want LINQ-to-SQL to pick up the criteria and translate a WHERE clause on the server.

You're missing a unit of work. Each repository owns its own connection to the database, which means if you have a business requirement to Delete a Student and everything associated to it, you're going to have to do it with as many connections as you have tables affected.

One way to have a unit of work is to use Entity Framework (that would mootinate your repositories); another way is to roll your own: instead of having code-behind that depends on repositories, you have code-behind that depends on a unit-of-work:

string ConString = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
IUnitOfWork uow = new UnitOfWork(ConString);
IList Students = uow.Students.FindAll();

In other words, push the responsibility of owning the connection outside of your repositories - let the repositories depend on a connection instead, and let the unit of work pass it down to each repository. Doing this will enable deleting/inserting/updating data from multiple repositories within a transaction which can be rolled back if things go wrong halfway through.

Achieving the same thing with EF is simpler: EF already implements repositories and unit-of-work for you, and SaveChanges operates like a Commit - you can Rollback the changes by simply disposing the DbContext before SaveChanges is called.

StackExchange Code Review Q#51308, answer score: 7