Architecture for Database Supported Applications

Question

I am looking for a reference implementation of the Unit of Work and Repository templates for MS SQL Server or regular ADO.NET. But all samples are built in an existing context, such as Linq2SQL or EF. In my opinion, these technologies themselves implement this model.

But how can I work with a "simple" SQL repository without any contexts and SaveChanges () methods? Correct way to use TransactionScope? For example, collecting all the SQL operations in a list of commands and then just executing them one by one within the Tx scope ... or is it too simple?

Why am I looking for this? I have the task of creating a data layer that can work with both the ancient Sybase database and SQL Server (possibly in combination with the POCO-based EF4 component)

To do this, my idea is to create an abstraction layer using the Repository and Unit of Work template and create different implementations for each technology.

Update: Last week I was on vacation. Sorry for delay. Today, for this, I created a basic picture of my architecture. [link] (s7.directupload.net/file/d/2570/whb7ulbs_jpg.htm). My idea is to create a simple ObjectContext, such as an EF ObjectContext, which exists in parallel with the EF Context and is used by my repository. This context collects ATOM Sql transactions as a stack and executes them in transactions in the Unit of Work part. A good idea? Bad idea? Is it hard to do? I look forward to your views on this.

Answer 1

I do not envy your task; maintaining multiple databases in your application will be a daunting task.

Here is an example of a Unit Of Work template using ASP.NET MVC and LightSpeed: link

Personally, I would use EF or NHibernate (I prefer EF); SQL Anywhere supports ADO.NET and Entity Framework, so (ideally) you did not need to support this database much.

Good luck

Answer 2

If you are just worried about the transaction scope, let me point you to the System.Transactions library and its TransactionScope . Great class. any transaction system running SQL or another transaction that is processed in the same thread that creates an instance of the transaction scope is automatically added to the transaction. Thus, if any part of the code fails and throws an exception, you can simply not call the scope.Complete () method and all operations in the transaction scope, rollback. very nice class.