C# database interaction

What would be the most generic way to handle user-database in开发者_JAVA百科teraction in C#

I expect being able to easilly swap the database drivers (data providers).

I also think (not sure if it's possible) that it would be great if I could switch the data source from something DB-like (for example, plain MySQL database) to something absolutely another - like data serialized into xml or arbitrary binary file. I suspect the last case would require writing some Query-File interaction wrapper or something like that.

So - could someone share a way to achieve the described behavior?

I can definitely use LINQ for my queries and it does introduce some abstraction - but is it enough to make the data sources interchangeable?

If yes, then what should I actually do to bring this to life and how would my development pipeline look?

Thanks.

Here's a small Respository Pattern Walkthrough using the EF Framework:

someRepository.Find.Where(something => something.IsRed && something.IsBig)

Create a generic interface called 'IRepository' of type T containing all the methods for data access.

It could look like this:

interface IRepository<T> where T : class
{
    IEnumerable<T> FindAll(Expression<Func<T, bool>> exp);

    T FindSingle(Expression<Func<T, bool>> exp);

    // And many more!
}   

Create an abstract 'Repository' class implementing this interface:

class Repository<T> : IRepository<T> where T : class
{
    TestDataContext _dataContext = TestDataContext(); // Would be your EF Context

    public IEnumerable<T> FindAll(Expression<Func<T, bool>> exp)
    {
        _dataContext.GetTable<T>().Where<T>(exp);
    }

    public T FindSingle(Expression<Func<T, bool>> exp)
    {
        _dataContext.GetTable<T>().Single(exp);
    }

    // And many more!
}

We can now create an interface for the ModelClass table/objects which implements our 'IRepository' and a concrete class extending the abstract 'Repository' class and implementing the 'IModelClassInterface':

interface IModelClassRepository : IRepository<ModelClass>
{
}

And the matching repository to implement it:

class ModelClassRepository : Repository<ModelClass>, IModelClassRepository
{
}

I would suggest using this approach as it gives you a lot of flexibility as well as enough power to control all the tiny entities you have.

Calling those methods will be super easy that way:

ModelClassRepository _repo = new ModelClassRepository();
_repo.Find.Where(something => something.IsRed && something.IsBig)

Yes, it means that you have to do some work but it is hell easier for you to change the data source later on.

You can even switch the EF Framework with any dataprovider of your choice. Be it XML, db4o or plain old Txt.

You should look up the repository pattern.

Asp.net also makes use of what is known as the provider pattern to create swappable components such as the membership provider, sitemap provider, etc.

1 word.

NHibernate

NH3.0 supports LINQ, and its trival to swap out SQL Server for Posgres or MyFailSQL.

"like data serialized into xml or arbitrary binary file." not sure what this means tho.

In the .NET framework the de facto standard for this right now is Entity Framework. Take any criticisms you find online of EF with a grain of salt because recently EF version 4 was released which addressed a large number of concerns with the old version - check the dates on articles you read. Right now it is, in my opinion, the best database abstraction tool in the .NET space (that I have tried).

Beware LINQ to SQL, Microsoft is not likely to put many resources into improving the product because EF is now the flagship in this area.

Might I suggest that you look into LLBLGen Pro. We have been using their ORM and it blows away the competition. Their new version allows you to use their tool and generate out code for multiple frameworks (entity framework, nhibernate, linq-to-sql). But their framework seems to be the most flexible for many scenarios.

You can find out more info from Frans on his blog. He is the main LLBLGen guru and original creator of LLBLGen.

"What would be the most generic way to handle user-database interaction in C#" ?

The most generic way is ADO.NET ... which is just Blocks of SQL or calls to Stored Procedures in your DB, has it's advantages. However has many disadvantages as it is not easily abstracted to OOP doesn't have any intellisense ... EF is very cool as it addresses many of these shortcomings but if its new to you the syntax takes a little getting used to