How To Create Generic Data Access Object (DAO) CRUD Methods with LINQ to SQL

Question

I am new to LINQ to SQL and attempting to create a generic Data Access Object (DAO) for the basic Create, Read, Update, and Destroy (CRUD) methods so that I can reuse the code. I was successful in creating a generic method that will delete any entity by using the code below but, I was wondering if anyone knows how to create a generic method that will select any entity by a common Id field that exists on all tables.

    /// <summary>
    /// Generic method that deletes an entity of any type using LINQ
    /// </summary>
    /// <param name="entity"></param>
    /// <returns>bool indicating whether or not operation was successful</returns>
    public bool deleteEntity(Object entity)
    {
        try
        {
            DomainClassesDataContext db = new DomainClassesDataContext();
            db.GetTable(entity.GetType()).Attach(entity);
            db.GetTable(entity.GetType()).DeleteOnSubmit(entity);
            db.SubmitChanges();
            return true;
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.StackTrace);
            return false;
        }
    }

I am pretty sure that the same patter will work for update and insert and would like to have a generic method on the GenericDAO that will retrieve me any entity (i.e. Customer, Invoice, WorkOrder, etc...) based on the entities Id. Thanks in advance for the replies.

score 18 · Accepted Answer · edited May 23 '17 at 11:54

18

I think you are looking for Repository Pattern, the following is a simple implementation of it:

First you need to create an interface IRepository like this:

public interface IRepository<T> where T : class
{
    void Add(T entity);
    void Delete(T entity);
    void Update(T entity);
    IEnumerable<T> All();
    ...
}

Then:

public class Repository<T> : IRepository<T>
    where T : class, IEntity
{
    DataContext _db;
    public Repository()
    {
        _db = new DataContext("Database string connection");
        _db.DeferredLoadingEnabled = false;
    }
    public void Add(T entity)
    {
        if (!Exists(entity))
            GetTable.InsertOnSubmit(entity);
        else
            Update(entity);
        SaveAll();
    }
    public void Delete(T entity)
    {
        GetTable.DeleteOnSubmit(entity);
        SaveAll();
    }
    public void Update(T entity)
    {
        GetTable.Attach(entity, true);
        SaveAll();
    }
    System.Data.Linq.Table<T> GetTable
    {
        get { return _db.GetTable<T>(); }
    }
    public IEnumerable<T> All()
    {
        return GetTable;
    }
}

Then :

public class CustomerRepository : Repository<Customer>
{
    public ProductRepository()
        : base()
    {
    }
}

Then you can have something like:

Customer newCustomer = new Customer { FistName = "Foo", LastName = "Boo" };
_customerRepository.Add(newCustomer);

Where Customer is an entity mapped to your database which is defined in the .dbml. This is just a start, see the following for more details:

edited May 23 '17 at 11:54

Community

1
1

answered Jan 07 '12 at 09:09

Mahmoud Gamal

78,257
17
139
164

Thank you very much. This works perfectly. I have only used Hibernate with Java in the past and the framework was already in place. Now, I am the sole developer working on a project from scratch so this is the first time I have had to write my own data access layer outside of a team. I wish I had enough rep to vote this solution up. I am new to the community and will be sure to vote this up when my rep is higher. – Grasshopper Jan 07 '12 at 18:31
@AaronMajor Glade to help and welcome to stackoverflow, BTW: you can mark the answer as accepted if you found it helpful see this: http://stackoverflow.com/faq#howtoask – Mahmoud Gamal Jan 07 '12 at 19:17
@MahmoudGamal great answer. Is it mandatory to use an interface can't we just define a single class(Repository) and from controller's Action method Add or Update or Delete a record. Thanks – Zaker Jun 18 '15 at 07:00
1

@User - No it is not a mandatory, but the interface works as a generic repository, it has the advantages of the interface instead of the class directly, like if a particular repository requires additional operations, you can extend the generic repository interface. Generally, it depends on your design and requirements, also read more about classes and interfaces and when you should choose over the other. – Mahmoud Gamal Jun 18 '15 at 10:08
@MahmoudGamal Thanks for your answer and your help is appreciated. I do have one more doubt as per this link http://programmers.stackexchange.com/questions/180851/why-shouldnt-i-use-the-repository-pattern-with-entity-framework Entity Framework already implements a repository pattern then if this is the case can't I use my own repository so that I can reduce the coding. Requires your kind advise and thanks for your time and patience. – Zaker Jun 18 '15 at 10:12
Hi @MahmoudGamal Thanks for the answer. One thing bugging my mind, because of the GetTable() function will retrieve the whole table, isnt it getting slower as the data grows? Thanks. – user2936719 Jul 16 '16 at 01:44
I think Repository pattern usually meant that you're storing your objects in-memory, and not persisting them immediately (but rather in a Unit of Work). What you wrote IMO looks more like a generic DAO. – drizin Nov 16 '20 at 03:20
Although Vaughn Vernon (in the book Implementing Domain-Driven Design) makes a distinction between collection-based and persistence-based repositories (which if I understand correctly are exactly like a DAO) – drizin Nov 16 '20 at 03:32

How To Create Generic Data Access Object (DAO) CRUD Methods with LINQ to SQL

1 Answers1

Linked