OK, as the title suggests I am designing a data access layer for a survey framework I am currently working on.
We all are familiar with the layered architecture notion, we try to achieve separation between the layers in a way that presentation layer can be hooked to any business layer, in the same way business layer can be wired to any Data access layer regardless of its implementation as long as it maintains the same interface (Same methods).
Now, after building the database using SQL Server, I am building the DAL using a DataSet (*.xsd) file and in this file I create the methods for each table adapter and the corresponding stored procedures in the database.
After working for a little while with the data set visual designer in Visual Studio I have noticed that I am aiming at providing a very flexible API that provides all the possible queries for the user in the form of methods. For example, I want to provide the user with methods that performs retrieval operations on the tables using any possible filter or with not filter, I also want the user to be able to delete rows using any column she/he wants as filter, also updating all/individual fields using any column he wants as a filter.
The way I have accomplished this primarily is by creating a method for every possible query whether it is DDL or DML. Now, when I think that I might made a mistake in a certain method or that I want to check the methods to make sure I did not miss anything while fast typing it seems like a pain because I have ended up with a ton of methods.
So, my question is: Is there another way for designing the data access layer so that it can be easy to refactor the methods and create them?
I hope I did not elaborate too much but I wanted to put you in the picture so I can get the correct answer, thanks in advance
Well, you could use an ORM tool to provider a good data access layer. I mean it because with an ORM tool you will have support to most of populars databases as SQL Server, Oracle, MySQL, PostgreSql, etc.
Depending of wichi ORM tool you use, you do not have to write SQL statments, which means you will be less sensitive to error an query.
I recommend you to check a tool called NHibernate. With this ORM you can write queries using Linq and another one (more specific for NHibernate) called QueryOver. You will have a lot of flexibily to write dynamics queries.
With an ORM tool you could implement a Repository Pattern and create methods and queries to get working data access.
So, when you use something like this, you will have the benefits of the Visual Studio like Refecting, because Linq, and QueryOver is strongly typed. But you will have HQL too, it like Sql Statment.
Check this article: Why I don't use DataSets