locked
Interface based database access design RRS feed

  • Question

  • User-289010069 posted

    I would like to design a data access layer that will connect to SQL and Oracle databases.  I would like to encapsulate the data access logic and hide the data access layer behind an interface.  However, I'm not sure about how to implement and haven't found any examples.  Does anyone have an example of how to design a simple DAL that is encapsulated using interfaces?

    Thursday, April 1, 2021 1:20 PM

Answers

All replies

  • User475983607 posted

    I would like to design a data access layer that will connect to SQL and Oracle databases.  I would like to encapsulate the data access logic and hide the data access layer behind an interface.  However, I'm not sure about how to implement and haven't found any examples.  Does anyone have an example of how to design a simple DAL that is encapsulated using interfaces?

    This work has already been done in ADO.NET.  If you are looking for a solution where you can swap out the a data access layer, then the pattern is called a generic repository.  Keep in mind Entity Framework is a repository.

    Thursday, April 1, 2021 2:39 PM
  • User-289010069 posted

    Yes, I'm looking for a generic repository.  Thanks for the clarification.

    Thursday, April 1, 2021 2:57 PM
  • User303363814 posted

    Use Entity Framework.  You can swap out the provider at a later stage, if needed.

    My advice would be to watch out for provider specific types.  I used Entity Framework for a medium sized project using the Sql/Server provider.  After a couple of years I switched the provider to MySQL for some tables.  There were 2 problems that I ran into

    1. Having two underlying databases takes a bit of juggling to get references between tables in different databases right.  But if you only ever have one database then this would not be an issue
    2. I made heavy use of DateTimeOffset in the original database which MySql does not support.  Making a complex type to split the DateTimeOffset into separate DateTime and TimeSpan fields was a relatively easy fix

    Bottom line, I would never expect that changing providers would be no effort in a project of any decent complexity but a generic repository like Entity Framework makes life easy and clean.  Constructing your own generic repository is just reinventing the wheel - I could never claim that my repository would be as efficient or reliable as one created by a large, professional team like Microsoft.

    Creating your own generic repository uses a lot of time to solve a problem which has already been solved many times in the past.  Much better to use your keystrokes getting the pieces unique to your application right. My 2c/

    Thursday, April 1, 2021 10:52 PM
  • User-821857111 posted

    Search for Generic Repository and you will find loads of examples of generic interfaces that you can use e.g.

        public interface IGenericRepository<T> where T : class
        {
            IEnumerable<T> GetAll();
            T GetById(object id);
            void Insert(T obj);
            void Update(T obj);
            void Delete(object id);
            void Save();
        }

    But as others have pointed out, Entity Framework does this for you already. 

    Friday, April 2, 2021 5:59 AM
  • User-289010069 posted

    Yes, I've searched for a few examples.  Unfortunately, the examples are for Entity Framework and I have to implement in a .net 4.0 web forms application without EF.

    Friday, April 2, 2021 11:51 AM
  • User475983607 posted

    Again, ADO.NET already handles several data providers through a common interface as explained in the docs.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/data-providers

    Use the interface pattern in Mikesdotnetting's post.  There is no magic.  You still have to write code, the implementation, according to your application requirements.

    Friday, April 2, 2021 12:45 PM
  • User-821857111 posted

    I have to implement in a .net 4.0 web forms application without EF.
    You will have to write your own repository classes. They will be responsible for fetching data from your database and hydrating it into entities. Look at this series of articles to see how it can be done. The part you are interested in is the DAL layer: https://imar.spaanjaars.com/416/building-layered-web-applications-with-microsoft-aspnet-20-part-1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 3, 2021 7:28 PM
  • User303363814 posted

    I have to implement ... without EF.

    Why?  Can you explain why you cannot use EF? What about NHibernate? 

    Saturday, April 3, 2021 11:05 PM
  • User-1545767719 posted

    Yes, I've searched for a few examples.  Unfortunately, the examples are for Entity Framework and I have to implement in a .net 4.0 web forms application without EF.

    Are you talking about 3-tier architecture (although it is not an interface based)?

    If yes, search with key words such like "asp.net three tier". You will be able to find useful articles such as:

    Create and Implement 3-Tier Architecture in ASP.Net
    https://www.c-sharpcorner.com/UploadFile/4d9083/create-and-implement-3-tier-architecture-in-Asp-Net/

    and many others.

    Sunday, April 4, 2021 3:43 AM
  • User-289010069 posted

    Database Administrators won't allow EF.

    Tuesday, April 6, 2021 2:49 AM