DAL Question: SqlConnection vs SqlDataSource

[John Kotuby]

Hi all,
I was reading a post regarding questions about how to properly separate the
Presentation layer from the BLL and the DAL (I don't know the acronym for
the presentation layer). Quite an interesting discussion it was. I started
to set up my ASP.NET 2.0 VB Web Application that way and then got bogged
down in details about SqlDataSource and strongly typed datasets. The project
involved the conversion of a very large existing application from ASP to
ASP.NET. Most of the Data Access code was written in ADO and very up-front,
as in built in directly with the presention layer. The ASP app was written
by another coder, long gone. I had to study and learn what was going on in a
relatively short time frame so I just dove in and started coding.

I have managed to separate the Data code from the Presentation code in a
very minimal way so far. My preference is to use disconnected DataSets and
DataTables with the SqlConnection and SqlDataAdapter. This is a preference
derived from 6 years of coding in VB6 and SQL 2000 on another (still-active)
large production application which also uses ADO and disconnected
RecordSets.

So far my Data Access 'separation' just involves running the SQL code in the
Code Behind and then using the results of the queries in the form of
DataTables. Below is an example of the beginning of a typical
"GetRecordSets" Method. Note that there are numerous tables populated. I
prefer to run all the queries in one swoop during Page Load.

Protected Sub GetRecordSets()
Dim MyConnection As New SqlConnection
Dim MyCommand As New SqlCommand
Dim MyAdapter As New SqlDataAdapter
Dim CardTable As New DataTable()
Dim SummaryTable As New DataTable()
Dim DeckTable As New DataTable()
Dim UsageTable As New DataTable()
Dim SegTable As New DataTable()
Dim CatTable As New DataTable()
Dim SelTable As New DataTable()
Dim ContactTable As New DataTable

I dispose of these objects at the end of each GetRecordSets() Method.

Now I am beginning to see the patterns of repetition and would like to start
moving most of this code to the DAL.
Is there a good tutorial or article that describes using the DAL with the
components I prefer to work with? Each of these tables require a separate
query. I can probably gang up all the queries in a single monstor SQL 2005
Stored Procedure and then pull the individual Table information from a
returned DataSet.

Thanks for any help.

 

[bruce barker]

unless you are using the MVC pattern, the seperation the dal provides is only
abstraction. asp.net pages tend to be written by binding directly to the dal
or dataset or datasource. this means the pages are tightly coupled to the
data layer.

what people commonly use a dal for is to include business and connection
logic in the dal, not the page. typed datasets are a pretty primative dal,
but better than nothing.

for you first foray into wrting a dal, first create a seperate library
project. in this project create typed datasets for you database. then create
a wrapper class to load and save the datasets. then add methods that reutrn a
datasets.

your dal should have methods like:

CustomerDataset dal.GetCustomer(int id)
CustomerDataset dal.SearchCustomer(string name)
dal.SaveCustomer(CustomerDataset ds)

add a reference to the library project to your web site, then bind against
datasets returned from the dal.


-- bruce (sqlwork.com)

 

[sloan]

Check my blog entry here:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

...

Where I have the
..DataSets.csproj

You can put in your DataTable definitions.

Your DAL (the .Data.csproj in my example) could return your DataTable
definitions.



...

You are at a VERY GOOD BREAKING POINT for learning some good new habits.
Don't waste it.


I would pick either the

DAAB 2.0 (Data Access Application Block)
or the
EnterpriseLibrary 3.1

projects to be your dll Helpers. And you'll avoid writing alot of redundant
code with SqlConnection objects.

Check the sample (downloadable code).....


Where I have

public CustomerOrderInfoDS CustomersGetAllDS()


You could have

public CustomerOrderInfoDataTable CustomerGetAllOrders()

something like that.


Don't fall trap to MS's RAPID development style..if you want to write well
maintainable code.

 

[John Kotuby]

Thanks sloan,

Your approach appears to be just what I was looking for.

The MS RAD approach looked to me like too much work for too little return.
Seemed like it would work well for quick and dirty small projects.

Would love to see the 2.0 version. Did you ever post it?

 

[John Kotuby]

Thanks Bruce,

The idea of a separate "helper" utility library sounds good. It should allow
me to avoid writing repetitive code.
I have created similar helper classes within the project itself for File IO
(log files, error logs) and data manipulation for formatting strings, dates,
currency, phone numbers, etc.

But I had not considered going the Library route. I hope that I can create
one that is flexible enough to be used in a number of situations.

 

[sloan]

http://sholliday.spaces.live.com/blog/

Its listed there (The 2.0 Version)

Note:

I now do

public class OrderCollection : List<BusinessObjects.Order>
{
//yep, that's it. the whole implementation
}

instead of all the List<BusinessObjects.Order> all over the place.

It'll make sense when you look at the code.


This won't matter to you, if you stick with a strictly strong
dataset/datatable implementation.



Also notice I didn't do the seperate csproj (projects/assemblies).

You ~can~ do this...and have good seperated code. The problem is that..you
tend to maybe break the rules.

I would still setup


BusinessLayer
DataLayer
DataSetsTables (.DataSets.csproj in my 1.1 example...name it what you'd
like..but its the code that floats among the other 3 layers
(dal,bll,prez)....and provides the "glue".

and (one of these)
Pres.Web.1
Pres.Winforms1


I reference the "bird's eye view" article at the 1.1 blog entry.
Find it. Bookmark it. Read it today. Read it tomorrow. Read it 1 week
from now. Read it 1 month from now.

Rereading that article will really help you, esp after you've struggled a
little bit more with DotNet code.

 

[sloan]

Once you get the hang of it...and you use the DAAB 2.0 or the
EnterpriseLibrary.Data (3.1)...you'll find the DAL code to be very very
clean.

Here is some EnterpriseLibrary usage, in a DAL I have.

My update procedure has barely any lines in it at all. Just the barest of
necessities.

Once you write clean code like this (and it actually works as well !!)..you
never want to go back to
hacky hacky,...declaring sqlconnection and sqlcommand objects.
Clean DAL code is the best DAL code of all !

(keep scrolling too, i have another comment after the code)


//start code

using System;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace GranadaCoder.Applications.BulkDataTransferExample.DAL
{

public class SqlServerPubsData
{

private readonly string PROC_TITLE_UPDATE = "dbo.uspTitleUpdate";

public SqlServerPubsData()
{
}


private Microsoft.Practices.EnterpriseLibrary.Data.Database GetDatabase()
{

Database db = DatabaseFactory.CreateDatabase(); // Gets the DEFAULT
instance
return db;
}


public int TitleInfoUpdate( DataSets.TitlesDS ds)
{
try
{
Database db = this.GetDatabase(); //
DBCommandWrapper dbWrap = db.GetStoredProcCommandWrapper
(this.PROC_TITLE_UPDATE);
dbWrap.AddInParameter( "@xml_doc", DbType.String, ds.GetXml());
dbWrap.AddOutParameter( "@numberRowsAffected", DbType.Int32, 0 );
db.ExecuteNonQuery(dbWrap);
return Convert.ToInt32 (dbWrap.GetParameterValue(
"@numberRowsAffected"));

}
finally
{
}


}



}
}


//end code


Also....you may not know this.

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/268be0e152d8b876

Look there. You can actually pass DataSet.GetXml() INTO a stored
procedure...and let tsql use it.
The url at google_groups I just gave shows the TSQL code for it.

Follow the MS KB's as well, and you can learn how to do BULK inserts and
updates.
I found that article a while back (2003?) and my post has a ...cleaned up
and nicer version of it.

Good luck. At least you started asking question before getting too deep
into the RAPID model.
::::wiping forehead with back of my hand::::::::: (ha ha)

 

[John Kotuby]

sloan,
I appreciate your effort on my behalf to get me started on the right course
to higher education.
It's amazing how sometimes good fortune seems to find one at just the right
time, provided you can keep your eyes and mind open.
There are too many people talking and too few listening.

I will study your code and the articles provided and make use of them.

I have read a good portion of the "bird's eye view" article and also
bookmarked it (as well as saving a DOC version for off-line reading), before
I had to get back to hacky hacky. We have a "dog and pony show" of my
program tomorrow to prospective clients, and I had to clean up the rough
edges.

I am hoping your method will stand the test of time and keep working while
other Microsoft fads come and go. I will be moving my codebase to VS 2008
soon. What I have read about LINQ so far is interesting and seems to offer
some similarity of purpose.

But before I can say that with ANY level of certainty I will try your code
examples. It's not often that one is offered solid working code examples
that are actually usable.

Thanks again... I will be expecting to receive an invoice next week for your
services ;-)

 

[sloan]

//Quote

Thanks again... I will be expecting to receive an invoice next week for
your services ;-)

//End Quote

The bill is already in the mail (ha ha).

...

I'm new to Linq myself.

Here is a free resource:
http://slickdeals.net/?pno=11351&lno=1&afsrc=1

However, I'm stuck in 2.0/3.0 land right now. Aka, no 3.5. So that
obviously affects my approach to things.

But LINQ takes a little time to get a hold of.


But based on what I've read so far, I' still pretty content where I'm at.
I'll get there one day as well.
And maybe reevaluate my whole approach.

But dude, when I came from VB6, I didn't get much help at all.
Then again, I didn't use newsgroups like these as much as I should have
either.

The most radical thing I learned was that "push xml down into tsql"
thing,...which really altered my approach to things.

Ok......happy hunting!

...

 

[John Kotuby]

sloan,

You're a good dude.
I don't see how some of the guys on this newsgroup seem to be here all the
time.
Between project deadlines, a Wife and 2 boys headed to college, and hobbies
like fishing and golfing (and watching American football), I just don't
seem to have the extra time.

That is precisely why newsgroups and guys like yourself, willing to share,
save me some time so I can actually have a life outside of work.

I hope to find more time to "give back" to the newsgroups when I have the
chance.
I suppose that the many conversations that I have participated in will in
some way help others, even if I don't answer directly.

BTW... I don't think that LINQ is quite ready for prime time yet, but very
soon...probably in the next 6 months.

You'll be seeing me on the newsgroup when I have had a chance to digest and
use all the info you have provided.

Have a great day...