Dals and Datatables. | Tech Off

yman

I am in search of clarity and confirmation on whether I am implementing my web applications properly.

Currently, I obviously use a three tier architecture: gui, business and data. My business layer returns entity classes which my gui layer consumes.
The question is, is it ok for the Dal layer to return DataTables which the business layer subsequently uses to populate corresponding entity classes with? I originally chose this pattern as I reasoned that it clearly seperates the layers, data and business. It also makes it easy for my web apps to be db independent as the Dal layer is normally implemented as a pluggable interface or derived class. Is this ok? Or should the Dal layer actually return the entity classes? If so would the business layer simply have wrappers around the direct Dal methods when entity classes need to be exposed, e.g "User[] GetUsers()".

JChung2006

I prefer entity classes to DataTables, because they are strongly typed. However, you can make your DataTables strongly typed as well with a little bit of effort with the XSD Designer in Visual Studio or using the command-line XSD.EXE tool in the Windows SDK.

Dr Herbie
Horses for courses

The first question you need to ask is: how likely is it that the datasource will change?

If the datasource is unlikely to change then typed datasets can be a quick way to generate a data entity (if you use partial classes, you can even add custom code without risk of trashing it by the IDE regenerating the class).

Even if the datasource is likely to change from one relational database to another, as long as all the database-dependent stuff is in the DAL then datasets are still not a problem.

However, if your datasource might change from database to webservice or XML files, then datasets might not be the best strategy.

Herbie

yman

Thank you for your comments. I'll look into using DataSets in the future.

What I was really looking for is whether it's /proper/ or acceptable to do it in the way in which I am currently doing it? This way has worked well for me in countless previous projects and I just want to affirm whether there is a technical or design reason why this is a no no.

Just to ensure you know how I am doing it:

A typical business entity retreive method in my code.


public static AuthUser GetUser(string userName)	

        
{
                
        DataTable dt = this.LoadedDal.GetUser(userName);
        if (dt.Rows.Count == 0)
                    
            return null;
 	

                
    return new AuthUser(
                            
                (int)dt.Rows[0][0],
			
                // etc
                            );
            
        
}

The corresponding Dal method:


public DataTable GetUser(string userName)
        
{ 
    // fire-up a sql connection, command and a data adapter and fill a datatable.
}

Rossj

I always cheat by putting my types in a separate DLL and having them share it so that only the types are passed between the layers ... YMMV but has been quite useful for me in the past.

ScanIAm
On a scale of 1 to 10, people are stupid.

Rossj wrote:
I always cheat by putting my types in a separate DLL and having them share it so that only the types are passed between the layers ... YMMV but has been quite useful for me in the past.

Ditto.

I was about to ask this very question, but it turns out I was doing something marginally correct Smiley

ScanIAm
On a scale of 1 to 10, people are stupid.

Is there any advantage to getting a result from SQL (in the data tier) using datatables instead of using a SqlDataReader and parsing through the data that way?

Tensor
Im in yr house upgrading yr family

I always try and do my reading from the db with datareaders if I'm going to be populating an object graph. Datatabes are just too heavy for the job for my liking. Just remember - drs are connected and should be handled with care.

yman

ScanIAm wrote:

Is there any advantage to getting a result from SQL (in the data tier) using datatables instead of using a SqlDataReader and parsing through the data that way?

hmm, probably not. I'll look into that in the future.

yman

Tensor wrote:

drs are connected and should be handled with care.

... i.e, you really need to wrap it all in a using() statement?

But as Dr Herbie mentioned briefly, isn't it more flexible to return DatatTables/DatataSets because in the future, for what ever reason, your Dal might not be talking to a database? It may be using a webservice for example. I know this is a rare situation and should probably be balanced up.

footballism
Another Paradigm Shift!

I prefer returning entity objects, although this requires a bit more coding, but you can actually use the ORM tools to automatically generate the entity classes and DALs for you.

Sheva

yman

footballism wrote:
I prefer returning entity objects, although this requires a bit more coding, but you can actually use the ORM tools to automatically generate the entity classes and DALs for you.

Sheva

I have never really looked into ORM bar playing with BLINQ and Codesmith one afternoon. Recommend any good ORM mappers?

JChung2006

nHibernate (http://www.nhibernate.org/) and Subsonic (http://www.codeplex.com/actionpack) are good ORM mappers.

Tensor
Im in yr house upgrading yr family

yman wrote:

Tensor wrote:
drs are connected and should be handled with care.

... i.e, you really need to wrap it all in a using() statement?

But as Dr Herbie mentioned briefly, isn't it more flexible to return DatatTables/DatataSets because in the future, for what ever reason, your Dal might not be talking to a database? It may be using a webservice for example. I know this is a rare situation and should probably be balanced up.

With a dr i'd put it in a try/finally and use close and dispose just to be sure (checking the state - ie clsoe if state not closed).

On the other point I'd turn it round and have the dal responsible for populating an object rather than passing a container object like a datatable. Then you DAL can be loading / updating from / to whatever source. Then you dotn have to worry about manually constucting datatables to send stuff back and forth.

yman

Tensor wrote:

With a dr i'd put it in a try/finally and use close and dispose just to be sure (checking the state - ie clsoe if state not closed).

The self-flagellation Gestapo application, aka FxCop told me off once for wrapping something in using() and calling "Close" on it as well Expressionless

My Mood:

Befood running FxCop:
"Ah, this project is going really welll. This is some nice leet code."

After running FxCop.
"God Damnit! F%##%! Why in the hell do I need to pass culture info when I am using DataTables and String.ToUpper(). I don't want to use a resource table; I like passing literals into my strings."

Tensor
Im in yr house upgrading yr family

Ahh FxCop. The good news is, you can turn rules off.

JChung2006

Via System.Diagnostics.CodeAnalysis.SuppressMessageAttribute, yep.

Use using instead of manually writing try-finally, Close, Dispose, etc., code.

SqlConnection connection; SqlCommand command; SqlDataReader reader; try { connection = new SqlConnection(...); command = new SqlCommand(...); reader = command.ExecuteReader(...); // code } finally { if (reader != null) { if (!reader.IsClosed()) { reader.Close(); } reader.Dispose(); } if (command != null) { command.Dispose(); } if (connection != null) { if (connection.State != ConnectionState.Closed) { connection.Close(); } connection.Dispose(); } }

vs.

using (SqlConnection connection = new SqlConnection(...)) using (SqlCommand command = new SqlCommand(...)) using (SqlDataReader reader = command.ExecuteDataReader(...)) { // code }

The first chunk of code looks like code written by someone used to writing C++ without auto_ptr.

yman

JChung2006 wrote:
using (SqlCommand command = new SqlCommand(...))

I always only wrapped SqlConnection in a using() statement. I thought using() calls dispose on all objects inside which implement Idisposable?

e.g,
using(SqlConnection conn = GetConnection()) {
    SqlCommand command = new SqlCommand("spBah", conn);
    // do work.
}

Using() calls dispose on conn, but does it call dispose on command?

Do I really need to be doing this?:

using(SqlConnection conn = GetConnection()) {
    using(SqlCommand command = new SqlCommand("spBah", conn)) {
          // do work
    }
}

Tensor
Im in yr house upgrading yr family

You need to explicitly dispose the command. It may be that a specific implementation of a connection disposes its commands (I dont know either way) but there is certainly no guarantee.

If you want to be sure explicitly dispose anything which implements IDisposable.

Soviut

yman wrote:

Tensor wrote:
drs are connected and should be handled with care.

... i.e, you really need to wrap it all in a using() statement?

But as Dr Herbie mentioned briefly, isn't it more flexible to return DatatTables/DatataSets because in the future, for what ever reason, your Dal might not be talking to a database? It may be using a webservice for example. I know this is a rare situation and should probably be balanced up.

The way I see it, have your DAL pass strongly typed entity objects. Then, if you switch to a webservice you simply write a new DAL that consumes the service. It will spit out the same entity objects as before so your business layer has no idea the DAL has even changed.

To make things even nicer, create an interface that both DALs can inherit from. Now you have your "contract" that both DALs must follow, guarenteeing that any and all DALs written for your application will produce the same entity classes for your business layer.

odujosh
Need Microsoft SUX now!

LINQ To SQL is your best bet. Get VS2008 Beta 1 and start working with it. You'll be better off that learning the old way.