-1

I'm trying to develop a Data Access layer in .NET, C# to be specific, without the use on entity framework, just ADO.NET. In this case the data is coming from a database.

I'd like to keep the data processing logic away from the Data store querying logic. I'm trying to do this to do all my queries via a single method, that takes a SQLCommand, and manages the disposable objects related to the database. I'd like this method to return the data obtained from the query, I could return the Reader object but that'd imply me having to close and dispose of the reader and it's connection on the caller method, and that's exactly what I'm trying to avoid. The other alternative I've come up with is storing each row in a dictionary where the column name is the key, and returning a list of dictionaries back to the caller, this would allow me to close the connection, but I'm not really sure if this is a good way of accomplishing this. I'd like to know if there is a better (proper?) way of doing this.

user2921909
  • 83
  • 2
  • 7

1 Answers1

1

The two approaches you have currently are not pretty. The first approach of returning the database reader to the business layer keeps the data layer coupled to the business layer. As an example, think of a day where you decide to use a nosql database, this task would be difficult with the tightly coupled architecture.

The second approach would be difficult to manage.

One approach could be to create repositories on your data layer. For instance, if you have a employee table you could have a employee repository class with methods such as getEmployerById(int id), ect... This repository would return a strongly typed employee (class).

Consuming the employee repo in your business layer would be easy and if the day comes when you wish to switch your data layer to something like nosql then code in the business layer wont care what changes under the getEmployerById method as long as it still functions.

Joshua Leonard
  • 512
  • 5
  • 10
  • Hey @Joshua Leonard, thanks for the answer. My problem with what you suggest is the repository then would deal with the Reader object(I'm assuming), and then each repository would have to have this boiler plate code to deal with and clean up the disposable DB objects. I'm pretty much trying to route the interactions with the DB thru a single method that takes the SQLCommand object, executes the query and returns the result set(if there's one) as a non-DBObject of some sort, in this case my horrible list of dictionaries, that I can then process and create instances of the bussiness objs. – user2921909 Jan 27 '18 at 05:41