.NET: Writing DataAccess dll

Question

I would like to write all data relations processes (general functions regarding with DataAccess via .NET) in dll and I want to use it repeatedly. What kinds of functions should have in that dll? Some want to use Stored Procedures , Some with Statements. Can you all suggest me? Please guide me! Thanks all!

Answer 1

This is quite a big topic, but here's a start:

Overview of ADO.NET

For simple CRUD operations, I can sum up an easy way to do this, assuming you already know a bit about databases, and your database already exists.

1) Use the Dataset Designer which comes with the VS IDE. (There are other ways to go as well)

2) Drag/Drop tables that you want onto the surface. This will create a lot of generated code which will support your CRUD operations. You can customise them easily, whether you're using text statements or stored procs. The designer knows how to deal with the parameters and datatypes. You can also sample the data, just to make sure the statements are correct.

3) DataTables and Adapters are created for each table that you drag/dropped, with the correct types for the members. You can use them like so:

using (MyDataSet.MyDataTable sigtbl = new MyDataSet.MyDataTable ())
using (MyTableAdapter adtp = new MyTableAdapter())
            {
               adtp.Fill(sigtbl, TargetTime);
               //sigtbl now contains the data from your DB. Use LINQ if you want to subquery or whatever.
               adtp.Insert(newSig,NewTime) //Parameters depend on your insert.
            }

4) Encapsulate the DB logic into useful business functions, and reference the project from your other projects. Try to keep the DB stuff in its own layer.

Anyway, this is a pretty basic. You are bound to run into some issues as you try doing this for yourself, so make sure you have a good understanding of how the ADO.NET stuff works, and come back with more questions.

Other alternatives, just to mention: 1) Use the System.Data stuff if you don't want the overhead + complexity of generating typed classes for you. 2) Look at LINQtoSQL: LINQtoSQL

Answer 2

In addition to the previous suggestions you may want to take a look to the enterprise library's data access block: Microsoft Enterprise Library 5.0 The Data Access Application Block also to the ADO.NET Entity Framework

Answer 3

I would stay far, far away from DataTables, Adapters, Sets, and so forth. I've always found them to be quite clunky. I prefer to write repository classes, service layers, and so forth. I would recommend you do some reading on Domain-Driven Design (http://domaindrivendesign.org).

Answer 4

Microsoft Enterprise Library, using the Data Access Application Block should do what you need. It is a Framework for itself.

Another interesting Data Access library is the well known ORM NHibernate.

If you want to speed your DAL development, you ought to consider these two either individually or used together.

The EntLib DAAB will allow you to provide named connectionString into your application configuration file(s) so that you won't need to deploy a new build of your application upon connectionString change, only the config file. It is also common practice to manage and configure only one connectionString per configuration file, so if one only connection string changes, you only deploy this new configuration file for this connection, and you're done! EntLib allows you to manage your connection pool according to the best practices. Everything and all through quite simple XML configuration files.

As for NHibernate, it is an object/relational mapping tool which can handle the data access for you through the ISession API. All you need to concentrate on is developing your business class diagram. Once you're done, just write the XML mapping files by respect to your object model, then you're all set to persist your objects into the underlying datastore, whatever datastore you use. While using the SchemaExportTool, you may even don't bother writing the DDL scripts, as NHibernate can create your relational schema for you!

As I said, either one or both, these are DAL layer for themselves, and much much more! Don't bother wrapping them into another class library, unless you want to build a user-friendly tool for DAL. And they are 100% resusable! Isn't it nice?