Future evidence of DAL

Question

We are at the beginning of a very long development project with several subprojects. Basically, each subproject will take several months. The code itself will be split into several C # projects, but the physical database will be used by all projects.

The problem is maintainability. If we add a column to the table or split the table into two smaller tables, we will have to go back and modify our C # DAL to support these changes. This is unacceptable, since we will constantly adapt the database to the needs of the company as a whole, and not only to the needs of one program. Constantly changing old code will be an endless task.

Our DB staff offered a different opinion. We execute all our CRUDs using stored procedures and use Linq in several tables to execute our SELECT statements. Then, if we restructure the database in a few years, we can simply provide the same stored procedures and views and not change the old code.

The question we have is that should ORM be used for something like this? EF seems a bit overkill (maybe not). Will there be something like SubSonic with this T4 template, can I use simpiler (and possibly faster) DAL?

Or maybe someone has an idea on how to make this whole process less painful? We would prefer not to add another layer to our application, but we also do not want to go back and change the code every time we make a db change.

Edit 1: Therefore, when I said: "I really don't want to add more layers." This is mainly because we already have several layers. We have Silverlight views, view-models, BLL-objects (through CSLA), then we have DAL and, finally, SQL tables themseleves.

Answer 1

C# DAL... not just the needs of a single program . The whole point of C # DAL as a separate assembly is that it can be reused for any type of .NET application. The main problem that you will encounter is that if the database changes, then the DAL must change (once), then all DAL-dependent applications must be redeployed with the new DAL. You also have a problem that DAL cannot be used by non-.NET applications.

So, how can you centralize the DAL so you don't have to redeploy it for each application? Think SOA. You can create a WCF service to contain DAL (and probably BLL). All your applications (if you use web services, even those that are not .NET), can use this service. When the database changes, you update your WCF service and deploy it once. Just make sure you are not making any changes! Create MyMethod2 if you need to add / change functionality.

Note. When you hear the n-level, it usually refers to the three-level, where each level is separate software and usually separate servers: presentation (UI), (your BLL / DAL), data (your SQL). There is merit for this architecture.

We'd rather not add another layer to our application . Good, so a three-tier approach might not be the best in your case.

neither do we want to go back and modify code everytime we make a db change Then what your database administrators have suggested is the only way.

However, consider this: modify the stored procedure in the same way as modifying code? This is basically the same. SQL stored procedures are often not versioned or tested, but they should be. SQL does not have a rich language like .NET. WCF can be easily scaled in a web farm. Once you identify these and other causes, it may be appropriate to use a three-tier / SOA approach.

It depends on the size of your project, staff skills, future growth, etc., and this is something you can determine.

Answer 2

I started using BLToolkit based on performance information from http://ormeter.net/

You can define your model in simple class files, add some methods with applicable attributes, and you have DAL. Divide the table into two parts, and you can save the original class file when creating new ones to support split tables. Just make sure you create a test project that uses each method to make sure that they all work with each version.

Class

  public class DirectoryListing { [PrimaryKey, Identity] public Int64 Id { get; set; } public Int64? OldId { get; set; } public Int32 CategoryId { get; set; } [Nullable] public String CompanyName { get; set; } } 

General selection or table function:

 [SqlQuery("SELECT * FROM Ajax_CategorySearch(@SearchString, @ResultCount)")] [Cache(MaxCacheTime = 10, IsWeak = false)] public abstract List<String> AjaxCategorySearch(String @SearchString, Int32 @ResultCount = 10); 

Or use the stored proc procedure:

 [ActionName("SelectById")] public abstract Model.DirectoryListing SelectById(Int64 @Id); 

This will call SP DirectoryListing_SelectById

Oh, and doing things in a more classic way is also easy.

  using (BIFDbManager db = new BIFDbManager()) { var output = db.SetCommand( "SQL GOES HERE", db.Parameter("@Id", 1)) .ExecuteList<DAL.Model.DirectoryListing>(); totalrecords = output.Count(); return output; } 

The final piece of the puzzle is the db manager, which also supports LINQ.

 public class BIFDbManager : DbManager { public BIFDbManager() : base("Connection string name") { } public Table<DirectoryListing> DirectoryListings { get { return GetTable<DirectoryListing>(); } } } 

Answer 3

How long can you work without a database? If this is a problem, enter it later. And yes, that probably means you're adding a layer.