Trying to understand how to abstract my data access layer

Question

I'm writing an application that is used to catalog files, and attribute those files with meta data. A photo album program would be a good comparison to what I'm trying to do.

I'm trying to abstract the interface between my program and the file system which stores the files, and the database which stores the meta data for the files. This is to allow for mocking and unit testing. In addition, I'd like to be able to create multiple implementations of my tool that can leverage different database formats, and or file system structures.

For example I've created an interface, IFileSystemAdaptor, which is used to read and write files and associated metafiles (thumbnails and attachments) to a file system. Actual implementations of IFileSystemAdaptor decide where files are stored and in what structure.

public interface IFileSystemAdaptor
{
    void WriteFileData(string fileName, Stream data);
    Stream ReadFileData(string filename);
    void DeleteFileData(string filename);
    void ClearAllData();
    void WriteMetaFileData(string filename, string path, Stream data);
    Stream ReadMetaFileData(string filename, string path, Stream data);
    void DeleteMetaFileData(string filename, string path);
    void ClearMetaFilesData(string filename);
}

So now I'm trying to do something similar with the connection to the database. I have a fairly complex structure of classes which I want to read and write to and from a database. I'd like to be able to have implementations that connect to a SQL Server database, and another implementation that uses a serverless database such as SQL Lite.

How can I abstract the data access layer between my classes and the database in a way which will support multiple database types? Also how can I allow for inheritance relationships in my classes to be reflected in the database? I'd prefer a database format following the "class table inheritance" pattern (See one of my previous questions).

Answer 1

I'm only addressing how to abstract the Data Access Layer to the extent of supporting multiple database providers. You can use the DbProviderFactory class from the .NET framework which uses the Factory pattern to provide abstraction of the underlying database components. You would need to configure a connection string value and the provider name (like System.Data.SqlClient, I think, for SQL Server). With the the provider name you can create a concrete factory class and then with the connection string you can create connection objects, from which you can create command objects, etc. This will allow you to code the Data Access Layer independent of the underlying database provider. Be aware that a parameterized query designed for SQL Server (for example) will use parameters named like @parametername, whereas other database engines will use a different format to indicate parameters. So, while the type of the database objects will be correct by using the factory, the text of the queries will need to be carefully considered if you intend to support different database engines.

Answer 2

Why re-invent the wheel? Use nhibernate

Everything is already done for you. You can keep all your models and it's easy to switch database engine.

I've myself written three different DAL's/ORMS with support for SQL Server, Mysql, postgresql and sqlite. But right now I'm switching to nhibernate instead. It's not worth the hassle to get everything exactly as you want it.

If you still want to do it yourself you have to remember that most databases have added their own "smart" features to SQL that you have to handle. You need to read up on the SQL92 standard and stick to those data types.

When inserting rows you need to handle retreival of the primary key differently for most database engines (some engines use generators to get a PK value BEFORE the insert the row while others have functions you use to retrieve the PK value AFTER the insert)

Paging is another thing that each db have their own implementation of. Sql servers is more like a hack.