DAL Question: SqlConnection vs SqlDataSource

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.

RE: DAL Question: SqlConnection vs SqlDataSource

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)


"John Kotuby" wrote:

> 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.
>
>
>

Re: DAL Question: SqlConnection vs SqlDataSource

Check my blog entry here:

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!1 39.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" wrote in message
news:uxokQ$1YIHA.4476@TK2MSFTNGP06.phx.gbl...
> 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.
>

Re: DAL Question: SqlConnection vs SqlDataSource

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?



"sloan" wrote in message
news:ODhipN2YIHA.484@TK2MSFTNGP06.phx.gbl...
>
> Check my blog entry here:
>
> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!1 39.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" wrote in message
> news:uxokQ$1YIHA.4476@TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>
>

Re: DAL Question: SqlConnection vs SqlDataSource

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.

"bruce barker" wrote in message
news:32D4EA66-3FAD-48F9-B464-93E9A49744B5@microsoft.com...
> 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)
>
>
> "John Kotuby" wrote:
>
>> 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.
>>
>>
>>

Re: DAL Question: SqlConnection vs SqlDataSource

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

Its listed there (The 2.0 Version)

Note:

I now do

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

instead of all the List 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.




"John Kotuby" wrote in message
news:%23yMTK63YIHA.4476@TK2MSFTNGP06.phx.gbl...
> 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?
>
>
>
> "sloan" wrote in message
> news:ODhipN2YIHA.484@TK2MSFTNGP06.phx.gbl...
>>
>> Check my blog entry here:
>>
>> http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!1 39.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" wrote in message
>> news:uxokQ$1YIHA.4476@TK2MSFTNGP06.phx.gbl...
>>> 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.
>>>
>>
>>
>
>

Re: DAL Question: SqlConnection vs SqlDataSource

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.pr ogramming/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)

Re: DAL Question: SqlConnection vs SqlDataSource

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" wrote in message
news:%236uMk14YIHA.208@TK2MSFTNGP02.phx.gbl...
> 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.pr ogramming/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)
>
>
>

Re: DAL Question: SqlConnection vs SqlDataSource

//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" wrote in message
news:e8ybMR7YIHA.5980@TK2MSFTNGP04.phx.gbl...
> 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" wrote in message
> news:%236uMk14YIHA.208@TK2MSFTNGP02.phx.gbl...
>> 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.pr ogramming/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)
>>
>>
>>
>

Re: DAL Question: SqlConnection vs SqlDataSource

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...

"sloan" wrote in message
news:%234$VNUCZIHA.600@TK2MSFTNGP02.phx.gbl...
> //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" wrote in message
> news:e8ybMR7YIHA.5980@TK2MSFTNGP04.phx.gbl...
>> 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" wrote in message
>> news:%236uMk14YIHA.208@TK2MSFTNGP02.phx.gbl...
>>> 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.pr ogramming/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)
>>>
>>>
>>>
>>
>
>