BLL & DAL: How are they different?

[gnewsgroup]

If you're using Strong DataSets or a DataTable model, then

these get defined in their own assembly....and "float" outside of the
others.
They have also been called the "glue" assemblies.

If you get my code, you'll see that my DataSets project resides OUTSIDE of
the other 3 layers.
This is my glue assembly.

My blog has a bad URL, for the "birds eye" article.
Here is the correction.

http://msdn2.microsoft.com/en-us/library/ms978496.aspx

Read that article.
Go to lunch.
Come back and reread that article.
Bookmark it.
In a week, reread that article.

If you find this part of the article:
Deploying Business Entities

You can read...and that is where I'm saying the strong dataset (or now the
DataTable) will reside in its own assembly as a the "glue".
This is the idea presented in the first dotted (lineitem) in the section
labeled "Deploying Business Entities".

..

This is something where you're going to read 1 article and get it by just
reading.
You gotta try and code one up.  (Which I know you are doing, and tryign to
find help via this post).
I'm just saying ... you gotta work with it some...and you gotta do some
reading and rereading as you get better with it.

I still read that article about every 6 months.....

You're leaning toward the DataTable/DataSet model.  I've converted over to
custom business objects and custom collections.

Both are ok solutions.  The article above .... is the best article I've
discovered which outlines the pros and cons of the approches.
That's why its a good "bird's eye view" article.

Thank you. The MSDN article has been printed out and your sample code
has been downloaded for research. Will get back if I have questions.

 

[Mark Rae [MVP]]

What have you really gained here? The ability to swap out SqlClient for
OracleClient? I guess that's nice, but IMO doesn't go far enough.

Like I said in my first reply, this is a very simplistic explanation of BLL
and DAL...

 

[Mark Rae [MVP]]

Question: I suppose colParamaters should always be an array of string,
such that when we add more parameters to a stored procedure in the
RDBMS, we don't have to modify the code in the DAL, right?

Depends on the RDBMS but, for SQL Server, I use the SqlParameters
collection:
http://www.google.co.uk/search?sour...8&rlz=1T4GZEZ_en-GBGB252GB252&q=SqlParameters

BTW, do DAL mostly have static methods?

Depends... The Microsoft DAAB does, and mine does because I based it on the
Microsoft one...

 

[Scott Roberts]

Final million dollar question is how to pass infomration between BLL

and DAL and vice versa. This is profound question with two answers
in .net, Customized objects and typed dataset. Dissucssion of this
beyond scope of this post. Google for "Customized objects vs typed
dataset" and hope you will get input from experts.

Do you count LinqToSql under "customized objects"? I haven't gotten to play
with it as much as I would like, but it looks fairly promising as a "bridge"
between those of us in the "customized objects" camp and those in the
"datasets are easier" camp.

 

[radix]

Do you count LinqToSql under "customized objects"? I haven't gotten to play
with it as much as I would like, but it looks fairly promising as a "bridge"
between those of us in the "customized objects" camp and those in the
"datasets are easier" camp.

Yes. LINQ is very elegant way to avoid duplicate implementation in
DAL. If you are using .net 3.5, I think LINQ should be used.

 

[chike_oji]

Hello gnewsgroup,

The DAL is usually a wrapper for the stored procedures used in
accessing data
from the database. (That is if you are a stored proc person).

The BLL is contains the business logic and also acts as a wrapper for
accessing
the DAL layer in a 3 -tier architecture model. The presentation layer
then directly accesses
the BLL.

In the architectures above, it is adiceable to access only the layer
beneath it.
The advantages of multi-tier architecture is to allow for easy
maintenance. One layer can
be modified without breaking the other layers or needing to modify
them.

Your DAL might look like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
//using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace IConceptDBWebUpdate.DAL
{
public class StockDetailDB : DataLayerBase
{

#region GetStockDetails
public static DataTable GetStockDetails(DbTransaction tran,
object SD_SRN)
{
DataTable retVal = new DataTable();

Database db = DatabaseFactory.CreateDatabase();

DbCommand cmd =
db.GetStoredProcCommand("proc_GetStockDetails");

db.AddInParameter(cmd, "@SD_SRN_", DbType.Double, SD_SRN);


retVal = ExecuteDataSet(db, tran, cmd).Tables[0];

return retVal;

}
#endregion


while your BLL might look like this:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
//using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IConceptDBWebUpdate.DAL;

namespace IConceptDBWebUpdate.BLL
{
[System.ComponentModel.DataObject]
public class StockDetail
{
#region GetStockDetails
public static DataTable GetStockDetails(object SD_SRN)
{
return StockDetailDB.GetStockDetails(null, SD_SRN);

}
#endregion

In the code snippets above, I am using the Microsoft Enterprise Lib
DAAB.
You could download it free from the web.

I hope this helps.
Thanks.

Regards,

Chike.

 

[Mark Rae [MVP]]

The DAL is usually a wrapper for the stored procedures used in
accessing data from the database. (That is if you are a stored proc
person).

And what if you aren't a stored procedure person...?

Your DAL might look like this:

public static DataTable GetStockDetails(DbTransaction tran, object SD_SRN)

That's precisely what your DAL *shouldn't* look like!

The DAL should not contain any reference to specific business logic, in this
case stock details.

 

[Registered User]

I guess I am repeating the question I asked sloan: How can the BLL and
DAL be completely independent of each other? Suppose, in (I guess)
the BLL, I need such fields from my database:

The confusion is due to the lack of a clean interface between the
app-specific BLL and the theoretically generic DAL.

CustomerID, FirstName, LastName, Address.

Don't we have to write a method in the DAL that returns a DataTable
(or whatever) that contains such fields? And then some time later, we
decide to add a Phone field in the BLL, then don't we have to modify
the method in the DAL such that the Phone number is included?

This depends upon how the DAL actually retrieves the data. Such a
change might be transparent if stored procedures are used.

See my confusion now? Thanks.

I would define the BLL's desired interface with the DAL as
IBusinessData or such. The implementation of the interface would be an
adapter between the BLL and DAL or it could be a wrapper for the DAL.
Either way the BLL talks to an IBusinessData interface and not
directly to the DAL.

This lets the implementation be malleable based upon the DAL. If the
DAL changes, a new IBusinessData implementation can be created w/out
changing the BLL. The implementation will turn out to be pretty thin.

regards
A.G.

 

[gnewsgroup]

And what if you aren't a stored procedure person...?


That's precisely what your DAL *shouldn't* look like!

The DAL should not contain any reference to specific business logic, in this
case stock details.

So, the DAL should be abstract enough, so abstract such that one
cannot very well predict what the BLL is roughly doing by looking at
the DAL code only, right? Let's forget about the suggestivity of
stored procedure names which are used in the DAL.

Also, Mark, in your reply dated Jan 28, 12:13pm, you say:

Depends on the RDBMS but, for SQL Server, I use the SqlParameters
collection.

in response to my question:

Question: I suppose colParamaters should always be an array of string,
such that when we add more parameters to a stored procedure in the
RDBMS, we don't have to modify the code in the DAL, right?

I was wondering if you would have to make changes in your DAL if you
add or leave out a parameter in your stored procedure. After all, in
your DAL, I assume that you still have to contruct the SqlParameter
before you call the stored procedure, right?

Thanks.

 

[Mark Rae [MVP]]

So, the DAL should be abstract enough, so abstract such that one
cannot very well predict what the BLL is roughly doing by looking at
the DAL code only, right? Let's forget about the suggestivity of
stored procedure names which are used in the DAL.

IMO, yes. The DAL should be precisely as its name suggests: a layer for
accessing the database - no more, no less.

E.g. supposing you have two completely different projects, both of which use
SQL Server as your RDBMS. So long as your DAL is capable of interfacing with
SQL Server (either because that's the only RDBMS it supports or because it
uses a factory pattern), you should simply be able to drop your DAL into
both projects without modification. If you have to modify it even slightly,
then it's not a DAL, IMO...

Also, Mark, in your reply dated Jan 28, 12:13pm, you say:

Depends on the RDBMS but, for SQL Server, I use the SqlParameters
collection.

in response to my question:

Question: I suppose colParamaters should always be an array of string,
such that when we add more parameters to a stored procedure in the
RDBMS, we don't have to modify the code in the DAL, right?

I was wondering if you would have to make changes in your DAL if you
add or leave out a parameter in your stored procedure.

No you don't! And, at the risk of repeating myself, that's the WHOLE
POINT!!!

After all, in your DAL, I assume that you still have to contruct the
SqlParameter
before you call the stored procedure, right?

No!!! You construct the SqlParameters collection in your BLL and *PASS IT*
to your DAL... E.g.

// method in BLL ---------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

private void BindData()
{
SqlParameter objSqlParameter;
List<SqlParameter> lobjSqlParameters = new List<SqlParameter>();

objSqlParameter = new SqlParameter("@ClientID", SqlDbType.Int);
objSqlParameter.Value = Convert.ToInt32(ViewState["SiteID"]);
lobjSqlParameters.Add(objSqlParameter);

objSqlParameter = new SqlParameter("@OrderID", SqlDbType.Int);
objSqlParameter.Value = Convert.ToInt32(ViewState["OrderID"]);
lobjSqlParameters.Add(objSqlParameter);

// add as many more SqlParameter objects as required in the stored procedure

DataSet MyDS = MyDAL.GetDataSet("FetchOrder", lobjSqlParameters);
MyGridView.DataSource = MyDS;
MyGridView.DataBind();
}


// method in DAL -------------------------------

public static DataSet GetDataSet(string pstrSP, List<SqlParameter>
plstSqlParameters)
{
using (SqlConnection objSqlConnection = new
SqlConnection(mstrConnectionString))
{
objSqlConnection.Open();
using (SqlCommand objSqlCommand = new SqlCommand(pstrSP,
objSqlConnection))
{
objSqlCommand.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter objSqlParameter in plstSqlParameters)
{
objSqlCommand.Parameters.Add(objSqlParameter);
}
using (SqlDataAdapter objDA = new SqlDataAdapter(objSqlCommand))
{
using (DataSet objDataSet = new DataSet())
{
objDA.Fill(objDataSet);
objSqlConnection.Close();
return (objDataSet);
}
}
}
}
}


As I mentioned, the above is just the way I do things... It's not the only
way, nor am I claiming it's the *right* way...

 

[gnewsgroup]

So, the DAL should be abstract enough, so abstract such that one
cannot very well predict what the BLL is roughly doing by looking at
the DAL code only, right?  Let's forget about the suggestivity of
stored procedure names which are used in the DAL.

IMO, yes. The DAL should be precisely as its name suggests: a layer for
accessing the database - no more, no less.

E.g. supposing you have two completely different projects, both of which use
SQL Server as your RDBMS. So long as your DAL is capable of interfacing with
SQL Server (either because that's the only RDBMS it supports or because it
uses a factory pattern), you should simply be able to drop your DAL into
both projects without modification. If you have to modify it even slightly,
then it's not a DAL, IMO...

Also, Mark, in your reply dated Jan 28, 12:13pm, you say:

Depends on the RDBMS but, for SQL Server, I use the SqlParameters
collection.

in response to my question:

Question: I suppose colParamaters should always be an array of string,
such that when we add more parameters to a stored procedure in the
RDBMS, we don't have to modify the code in the DAL, right?

I was wondering if you would have to make changes in your DAL if you
add or leave out a parameter in your stored procedure.

No you don't! And, at the risk of repeating myself, that's the WHOLE
POINT!!!

After all, in your DAL, I assume that you still have to contruct the
SqlParameter
before you call the stored procedure, right?

No!!! You construct the SqlParameters collection in your BLL and *PASS IT*
to your DAL... E.g.

// method in BLL ---------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

private void BindData()
{
    SqlParameter objSqlParameter;
    List<SqlParameter> lobjSqlParameters = new List<SqlParameter>();

    objSqlParameter = new SqlParameter("@ClientID", SqlDbType.Int);
    objSqlParameter.Value = Convert.ToInt32(ViewState["SiteID"]);
    lobjSqlParameters.Add(objSqlParameter);

    objSqlParameter = new SqlParameter("@OrderID", SqlDbType.Int);
    objSqlParameter.Value = Convert.ToInt32(ViewState["OrderID"]);
    lobjSqlParameters.Add(objSqlParameter);

// add as many more SqlParameter objects as required in the stored procedure

    DataSet MyDS = MyDAL.GetDataSet("FetchOrder", lobjSqlParameters);
    MyGridView.DataSource = MyDS;
    MyGridView.DataBind();

}

// method in DAL -------------------------------

public static DataSet GetDataSet(string pstrSP, List<SqlParameter>
plstSqlParameters)
{
    using (SqlConnection objSqlConnection = new
SqlConnection(mstrConnectionString))
    {
        objSqlConnection.Open();
        using (SqlCommand objSqlCommand = new SqlCommand(pstrSP,
objSqlConnection))
        {
            objSqlCommand.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter objSqlParameter in plstSqlParameters)
            {
                objSqlCommand.Parameters.Add(objSqlParameter);
            }
            using (SqlDataAdapter objDA = new SqlDataAdapter(objSqlCommand))
            {
                using (DataSet objDataSet = new DataSet())
                {
                    objDA.Fill(objDataSet);
                    objSqlConnection.Close();
                    return (objDataSet);
                }
            }
        }
    }

}

As I mentioned, the above is just the way I do things... It's not the only
way, nor am I claiming it's the *right* way...

Thank you and I understand then that no changes need to be made in the
DAL after we make changes to the stored procedure, but, it seems that
the BLL still needs to know about the parameter change in the stored
procedure in order to construct the SqlParameter object and pass it to
the DAL, right? If not, how do we contruct the SqlParameter object?
Please deconfuse. Thanks.

 

[Mark Rae [MVP]]

Thank you and I understand then that no changes need to be made in the
DAL after we make changes to the stored procedure, but, it seems that
the BLL still needs to know about the parameter change in the stored
procedure in order to construct the SqlParameter object and pass it to
the DAL, right?

Of course it does!

You've modified your stored procedure because your business requirements
have changed, therefore you need to modify your BLL accordingly.

What *hasn't* changed is the *method* by which the BLL talks to the RDBMS,
i.e. the DAL...

 

[gnewsgroup]

Of course it does!

You've modified your stored procedure because your business requirements
have changed, therefore you need to modify your BLL accordingly.

What *hasn't* changed is the *method* by which the BLL talks to the RDBMS,
i.e. the DAL...

OK, thanks. I got an idea now.

 

[Scott Roberts]

Of course it does!

You've modified your stored procedure because your business requirements
have changed, therefore you need to modify your BLL accordingly.

(I'm sure you are aware of this, Mark, but for the benefit of "gnewsgroup" -
who makes a valid point):

This assumes that each stored proc fulfills only one business requirement.
If multiple business objects utilize the stored proc (or table/view/etc if
you don't use stored procs) then you may well be changing the DB because
*one* business requirement changed, but you will still (presumably) have to
change *all* of the BLL code that uses the stored proc. And how do you find
*all* of those places? What if they exist across multiple applications (web,
WinForms, web services, etc)?

There is still a relatively tight coupling between the BLL and the RDBMS
(even though there is a DAL in the middle). The only way to mitigate this
"problem" is with a DAL mapping scheme (or, my preference, an Object
Persistence Framework). Of course, this adds untold complexity to the DAL
and should not be embarked upon lightly. However, there are several 3rd
party DALs out there that do precisely this, and I hold out hope that
LinqToSql will be another viable option.

 

[gnewsgroup]

(I'm sure you are aware of this, Mark, but for the benefit of "gnewsgroup" -
who makes a valid point):

This assumes that each stored proc fulfills only one business requirement.
If multiple business objects utilize the stored proc (or table/view/etc if
you don't use stored procs) then you may well be changing the DB because
*one* business requirement changed, but you will still (presumably) have to
change *all* of the BLL code that uses the stored proc. And how do you find
*all* of those places? What if they exist across multiple applications (web,
WinForms, web services, etc)?

There is still a relatively tight coupling between the BLL and the RDBMS
(even though there is a DAL in the middle). The only way to mitigate this
"problem" is with a DAL mapping scheme (or, my preference, an Object
Persistence Framework). Of course, this adds untold complexity to the DAL
and should not be embarked upon lightly. However, there are several 3rd
party DALs out there that do precisely this, and I hold out hope that
LinqToSql will be another viable option.

Thanks for making it clearer. When I said "I got an idea", I was
thinking to myself "Oh, so the BLL isn't really independent from the
Data layer at least." Exactly like what you said, there is still a
coupling btwn the BLL and the RDBMS.

 

[sloan]

I think "maintenance" is the subject on the table.
And there are different ways to approach this.

...

I don't use the method being discussed.

I'll actually do something a little different.

I create and define all parameters inside the DAL layer.
Aka, if my stored procedure changes, I change the DAL code.

The way I communicate things back to the Biz layer is via my "Layout"
classes, defined in the DAL layer as well.

Again, keep in mind that I deal with IDataReader's mostly, and I populate
custom business objects and collections.

So if I need to add a column, I add a extra "column" to the Layout, and then
adjust the Biz layer code.

One goal I have is to be able to swap out RDBMS if I ever had to. Thus my
code never uses a concrete class like SqlParameter.

I also code up my DAL using the Factory Design Pattern, so that I could
switch out the RDBMS without touching the biz code.

Design goals are a part of this. I'm 99% sure I'll always be with Sql
Server, but I have the option to not use it if necessary.

...

There is another maintainence method...that I kinda describe here:
http://www.sqlservercentral.com/articles/Stored+Procedures/thezerotonparameterproblem/2283/

(Focus on getting the into into variable or temp tables, and NOT the super
duper WHERE clause)
(Aka, once you have info in variable or temp tables, you can do anything you
want to with it).

You can also see this approach here:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5491c0640ddf9680

I make the contract for my DAL usually accept a strong DataSet and then use
the ds.GetXml() method to push Xml into TSQL.
This way, when I need a change, I update the (strong) dataset, and never
have to touch the method signatures.

I only need to:
1. add a table and or column(s) to the strong dataset.
2. populate this extra table or extra column where its needed.
3. alter the tsql code to handle the small extra bit of xml.

Now, I like Sql Servers Xml's abilities. But I don't like Oracle's,
although I know it can work. Keep in mind, my last Oracle experience was 3
years ago.

But with this approach I can ship xml into the DAL, and then push the entire
thing into TSQL, or I could loop over the dataset table(s) if I had too.
(Say if my db was Access or something, there you don't have alot of BULK
abilities).


This is all food for thought. I like my approach (obviously), and once I
get people onto it, they see the wisdom in it from a maintenace standpoint.

...

I think the principals are important.....and I still like that MS article I
reference (another post) that gives you a bird's eye view,a nd lets you
choose a strategy.

...

But somewhere if you need a change, you have to change (some) code. If I
change my tsql , then I do most of my changes in the DAL.
But pick your poison for that one.

 

[Scott Roberts]

There is still a relatively tight coupling between the BLL and the RDBMS

Thanks for making it clearer. When I said "I got an idea", I was
thinking to myself "Oh, so the BLL isn't really independent from the
Data layer at least." Exactly like what you said, there is still a
coupling btwn the BLL and the RDBMS.

Well, the only way to completely avoid coupling the BLL and RDBMS is to make
your RDBMS so generic that it is essentially useless outside of your
application. I've considered doing just that in the past, but it seems a bit
extreme and I admit, I like to peek directly into the DB. Not to mention
that most reporting tools utilize direct DB access as well.

So there is almost always *some* coupling. The question is, how much? I
guess that ultimately depends on your situation.

 

[Mark Rae [MVP]]

I create and define all parameters inside the DAL layer.
Aka, if my stored procedure changes, I change the DAL code.

Horses for courses, really...

To my way of thinking, what you have there isn't a DAL or, at least, not
*only* a DAL because it has to change per app...

The way I do it means I have one and only one DAL which is currently being
used (completely unmodified) by over a dozen live web apps all of which are
totally different, and at least twice that number of desktop apps...

 

[sloan]

I don't have to change my DAL for any specific application.

I only change it if there is a TSQL change.

Obviously, if I break the DAL contract, the (N number of) biz layers will
suffer.

If the TSQL never changes, then the DAL doesn't change. And the (N number
of) biz objects using it will work as expected.

.............

But I build my DAL for total and multiple biz layer reuse.

Since my "rules" are:

DAL objects return:

DataSets (strong usually)
IDataReaders
Scalars
Voids/Nothings

then I'm gonna say my DAL objects are DAL only.

I don't see them getting much slimmer than that.

 

[Chris Shepherd]

After all, in your DAL, I assume that you still have to contruct the
SqlParameter
before you call the stored procedure, right?

No!!! You construct the SqlParameters collection in your BLL and *PASS
IT* to your DAL... E.g. [...]
As I mentioned, the above is just the way I do things... It's not the
only way, nor am I claiming it's the *right* way...

How do you manage shifts between database servers or even other storage mediums
(webservices, etc.) with this though?

IMO this is contrary to the purpose of abstraction as you are placing Data
Access implementation details inside your BLL. It's great if you only have one
place in your BLL where that data is accessed, and will only ever use one
backend (SQL) for data storage, but what about cases where you need a different
solution to retrieve your data -- say, a webservice?


Chris.