In the pre-LINQ days, I used to use the classical 3-tiers architecture for designing ASP.NET web projects, the user interface (UI), the business logic layer (BLL) and the data access layer (DAL).
My DAL layer used to rely on Microsoft’s Data Access Application Block (DAAB) which abstracted the repetitive and boring ADO.NET implementations. There are some 3rd party tools such as SubSonic, which has some common features with LINQ, or NHibernate, however, I would rather use the enterprise library.
Let me quickly illustrate the way to solve a problem with the classical architecture. This is a simple business problem, a website that has many brands and each brand has an advertising campaign. To access the campaign stats, which are supplied by the campaign agency, we need to access the agency’s webservice by providing our brand credentials. We simply store these login credentials in our database -> retrieve login info of a brand -> call the webservice -> display the stats on a web page.
I did not choose a trivial problem like the Company-Employee one as I wanted a real-life problem which I had experimented myself.
A stored procedure that fetches a campiagn credential for a brand from the campaign table could look like this:
ALTER PROCEDURE dbo.GetCampaignPerBrand
@BrandID TINYINT,
@AdwordsEmail VARCHAR(50) OUTPUT,
@AdwordsPassword VARCHAR(50) OUTPUT,
@AdwordsDeveloperToken VARCHAR(50) OUTPUT,
@AdwordsApplicationToken VARCHAR(50) OUTPUT,
@AdwordsClientEmail VARCHAR(50) OUTPUT
AS
BEGIN
SELECT @AdwordsEmail = AdwordsEmail,
@AdwordsPassword = AdwordsPassword,
@AdwordsDeveloperToken = AdwordsDeveloperToken,
@AdwordsApplicationToken = AdwordsApplicationToken,
@AdwordsClientEmail = AdwordsClientEmail
FROM Campaign
WHERE BrandID = @BrandID
ENDAnd a class, in the DAL layer, that would consume this method would look like:
public static class CampaignDAL {
// More code ...
public static void GetCampaignPerBrand(byte brandID, out string adwordsEmail,
out string adwordsPassword, out string adwordsDeveloperToken,
out string adwordsApplicationToken, out string adwordsClientEmail) {
// Using Microsoft's DAAB from the Microsoft Enterprise Library
Database db = DatabaseFactory.CreateDatabase();
DbCommand command = db.GetStoredProcCommand("GetCampaignPerBrand");
db.AddInParameter(command, "BrandID", DbType.Byte, brandID);
db.AddOutParameter(command,
"AdwordsEmail", DbType.AnsiString, 50);
db.AddOutParameter(command,
"AdwordsPassword", DbType.AnsiString, 50);
db.AddOutParameter(command,
"AdwordsDeveloperToken", DbType.AnsiString, 50);
db.AddOutParameter(command,
"AdwordsApplicationToken", DbType.AnsiString, 50);
db.AddOutParameter(command,
"AdwordsClientEmail", DbType.AnsiString, 50);
db.ExecuteNonQuery(command);
object adwordsEmailObj =
db.GetParameterValue(command, "AdwordsEmail");
object adwordsPasswordObj =
db.GetParameterValue(command, "AdwordsPassword");
object adwordsDeveloperTokenObj =
db.GetParameterValue(command, "AdwordsDeveloperToken");
object adwordsApplicationTokenObj=
db.GetParameterValue(command, "AdwordsApplicationToken");
object adwordsClientEmailObj =
db.GetParameterValue(command, "AdwordsClientEmail");
adwordsEmail =
adwordsEmailObj == DBNull.Value ? null : (string) adwordsEmailObj;
adwordsPassword =
adwordsPasswordObj == DBNull.Value ? null : (string)adwordsPasswordObj;
adwordsDeveloperToken =
adwordsDeveloperTokenObj == DBNull.Value ? null : (string)adwordsDeveloperTokenObj;
adwordsApplicationToken =
adwordsApplicationTokenObj == DBNull.Value ? null : (string)adwordsApplicationTokenObj;
adwordsClientEmail =
adwordsClientEmailObj == DBNull.Value ? null : (string)adwordsClientEmailObj;
}
// More code ...
}The purists among us might argue that I should be using a Data Container (DC) to carry the data from the BLL to the DAL, yes, it is a good solution, but I am trying a simpler solution here. Now a method in the associated business logic layer might look like this:
public class CampaignBLL {
// More code..
public Stats[] GetStats() {
byte brandID = Brand.CurrentBrand.BrandID;
string adwordsEmail, adwordsPassword, adwordsDeveloperToken,
adwordsApplicationToken, adwordsClientEmail;
GetCampaignPerBrand(brandID, out adwordsEmail, out adwordsPassword,
out adwordsDeveloperToken, out adwordsApplicationToken, out adwordsClientEmail);
Stats[] stats = new Stats();
// Use the retrieved values in calling a
// webservice and do other stuff to fill the stats array
return stats;
}
// More code..
}The advantages of this approach is that I was pushing my SQL into stored procs which, as I was convincing myself, is giving me an extra performance in addition to semi-portability (as the T-SQL will need to be rewritten when taken to another RDBMS). I had created a CodeSmith template to generate code which saved me writing it as writing such code with more complex stored procedures is error-prone and most probably the errors will be run-time errors.
Disadvantages are too much code to achieve a simple task, no intellisense for the column names (there are third party solutions for this which cost money), no compile-time checking for mispellings of stored proc and column names, semi-portable Transact SQL and the code is splitted into multiple locations/projects.
When I started designing an architecture based on .NET 3.5 of a mid-sized project, I was struggling with the DAL layer. I had tried to force the use of the DAL layer with LINQ to find that I am complicating the architecture rather than simplifying it. If LINQ is dealing with the database transparently and returning my data containers (DC) directly in strongly-typed format then I don’t need to use code such as the one used above.
To DAL or not to DAL, this is the Question
Some evil thoughts started to go arround my head, why do I need to have the DAL layer with LINQ? I went back to the basics for the need of the DAL layer.
DAL Fact: DAL will hide the DB provider such as SQL Server or Oracle make transitions to another DB much simpler and theoratically you will only need to change the implementations of the DAL layer, or you might not even need to change it if you have your own abstraction design or using a library such as EntLib DAAB.
LINQ Fact: Same fact goes for LINQ. Additional LINQ implementations, other than the SQL Server one, are currently available or in development.
DAL Fact: DAL will abstract the DB layer for the BLL and transform the db data types into .NET ones.
LINQ Fact: LINQ takes this a step further and returns strongly-typed data containers rather than dummy DataSets.
First I was afraid, I was Petrified
Shrinking the DAL layer or removing it completely? Merging the DAL and the BLL, very bold indeed, but I did it! My architecture turned to be much simpler and easier to change, this is a code snippet from the new LINQ-based model:
public class CampaignBLL {
// More code..
public Stats[] GetStats() {
byte brandID = Brand.CurrentBrand.BrandID;
DataClassesDataContext db = new DataClassesDataContext();
// using lambda expressions to make my life even easier.
Campaign campaign = db.Campaigns.Single(c => c.BrandID == brandID);
Stats[] stats = new Stats();
// Use the retrieved object in calling a
// webservice and do other stuff to fill the stats array
return stats;
}
// More code...
}Where Did The Code Go?!
Let us go back to one of the most famous physics laws, yes, phyiscs, I didn’t mistype:
Conservation of Energy Law: Energy cannot be created or destroyed, but can change its form.
Same applies to code. Code cannot suddenly disapear without changing to another form. The generated code by LINQ to SQL has reduced the amount of code to be written. With LINQ, usually developers tend to write less stored procedures as the performance benefit is negligible when compared to that of cleaner code and better architecture (you guessed it, I am a “better architecture” against “higher performance” type of developers).
Someone might argue that the DAL still exists in the LINQ generated code. Well, yes, but what is important to me that I didn’t have to do it and I can hardly see it. My discussion is what I have to do to get the project going and now I have no concern in “outsourcing” my DAL layer.
Some developers try to keep their DAL layer by having their LINQ statements inside DAL with methods that has 2 or 3 lines. I believe they have the fear of change! Some fanatic developers took this further step by returning IQueriable objects. I wouldn’t have taken such approach as I know that other developers will be working on the project and it is not just me.
DAL Shrinking Advantages
In the classical DAL architecture, each change in the database structure requires searching the associated stored procedure then changing the related DAL method(s) then changing the related business method(s). I am assuming with all the mentioned steps that you didn’t mis a change.
With LINQ you automatically shift to less stored procedures. Now changing in the db will require a LINQ recompilation and changing in the business layer only. If you’ve missed anything, you will get a compile-time error.
The code is easier to read, shorter and still portable as LINQ is having more and more RDBMS providers added every day.
Finally
Even though the project that I have applied these principles to is a mid-size and the database operations are mostly reading. I am still wondering if this approach is going to work in enterprise projects or projects with intensive write and update operations.
In the next post I am going to illustrate how I used Codesmith’s PLINQO template and created a simple architecture that made using LINQ a breeze. The results of this architecture was a scalable project which took less development time than it usually takes. It is an end to end architecture with fully functional commercial project. I will keep you posted, you might need to revisit in the next few days or subscribe to my RSS feeds.
Leave me a comment if you have any suggestion and if you agree or disagree on my approach!