ExamGuru IT/.Net Guru : Return Entire DataSet to Display Code? on ASPFriends.com 'aspngbeta' list

.NETGURU

Return Entire DataSet to Display Code?

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngbeta' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Strauss, Jon (VIP)

Hi,

I'm creating a 3-tier asp.net application and I've come across a potential problem. My first tier is aspx pages and csharp codebehind files. The second tier is a .NET assembly (dll) and the third tier is, of course, my database.

I'm trying to make the second tier as abstract as possible regarding representation of the data in the database. For instance, I create a class in the first tier and fill it's attributes with values from a user form and then call a CreateNewRecord method in my second tier that turns the class into parameters in a stored procedure call.

All of this seems to work fine. My question is regarding retrieval of records and how to deal with only returning a subset of records from my database? For instance, if I have a large table of employees and I'm only interested in seeing employees with the last name of "smith", then how do I handle this in my second tier? In other words, how do I allow the first tier code to specify a subset of records to be returned?

I thought about just always returning a DataSet object to the first tier that contained all table entries and then let the first tier code decide what to display, but that solution seemed like it might be a performance problem with large tables? I also thought about creating a query class that the first tier code could fill in and then call a second tier method (e.g. GetRecords(QueryClass)), but it seems like there would have to be a large amount of combinations that I would have to handle. Does anyone have any suggestions?

Thanks,
Jon

Reply to this message...

Scott Swigart

It seems like you could make a data tier component with an overloaded
function. Something like

Class dbEmployee

Public Overload Function Get() as DataSet
' Return all employees
End Function

Public Overload Function Get(LastName as String) as DataSet
' select * from employees where lastname = LastName
End Function

End Class

Scott Swigart
3 Leaf Solutions, LLC
Click here to reveal e-mail address
www.3leafsolutions.com

"Strauss, Jon" <Click here to reveal e-mail address> wrote in message
news:379303@aspngbeta...
[Original message clipped]

second tier is a .NET assembly (dll) and the third tier is, of course, my
database.
[Original message clipped]

in the first tier and fill it's attributes with values from a user form and
then call a CreateNewRecord method in my second tier that turns the class
into parameters in a stored procedure call.
[Original message clipped]

database? For instance, if I have a large table of employees and I'm only
interested in seeing employees with the last name of "smith", then how do I
handle this in my second tier? In other words, how do I allow the first
tier code to specify a subset of records to be returned?
[Original message clipped]

what to display, but that solution seemed like it might be a performance
problem with large tables? I also thought about creating a query class that
the first tier code could fill in and then call a second tier method (e.g.
GetRecords(QueryClass)), but it seems like there would have to be a large
amount of combinations that I would have to handle. Does anyone have any
suggestions?
[Original message clipped]

Reply to this message...

Bob Levittan (VIP)

Try creating properties in your db class to hold values for fields that you
might be filtering by. Then dynamically construct the where clause of your
query using those properties.

-----------------------------

Hi,

I'm creating a 3-tier asp.net application and I've come across a potential
problem. My first tier is aspx pages and csharp codebehind files. The
second tier is a .NET assembly (dll) and the third tier is, of course, my
database.

I'm trying to make the second tier as abstract as possible regarding
representation of the data in the database. For instance, I create a class
in the first tier and fill it's attributes with values from a user form and
then call a CreateNewRecord method in my second tier that turns the class
into parameters in a stored procedure call.

All of this seems to work fine. My question is regarding retrieval of
records and how to deal with only returning a subset of records from my
database? For instance, if I have a large table of employees and I'm only
interested in seeing employees with the last name of "smith", then how do I
handle this in my second tier? In other words, how do I allow the first
tier code to specify a subset of records to be returned?

I thought about just always returning a DataSet object to the first tier
that contained all table entries and then let the first tier code decide
what to display, but that solution seemed like it might be a performance
problem with large tables? I also thought about creating a query class that
the first tier code could fill in and then call a second tier method (e.g.
GetRecords(QueryClass)), but it seems like there would have to be a large
amount of combinations that I would have to handle. Does anyone have any
suggestions?

Thanks,
Jon

| [aspngbeta] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngbeta.asp = JOIN/QUIT

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

Reply to this message...

RATSEY-WOODROFFE, Jens, FM

I would disagree. By do this means that you're potentially sending a great
deal of redundant data across the network: imagine looking for a single,
rare, surname in a table of a million rows. You're sending a million rows
each time and then filtering?!

You're far better off creating that call as a stored procedure within the
database - remember that databases are designed for this sort of thing: the
selection of data through filtering. If you need to get employees with a
certain name, others probably do to in other applications.

By creating the stored procedure you are also adding another layer of
abstraction from your code - always a good thing. Never be afraid to add
more stored procedures and *always* try to limit the amount of data sent
across your network! Stored procedures also have the advantage of running
compiled and optimized on the database server - dynamically created SQL will
not.

So Jon - my answer to your question is, yes, create a second tier data class
- but as a stored procedure in your database with whatever parameters you
may need.

J.

-----Original Message-----
From:     Bob Levittan [mailto:Click here to reveal e-mail address]
Sent:    Tuesday, May 01, 2001 5:09 PM
To:    aspngbeta
Subject:    [aspngbeta] Re: Return Entire DataSet to Display Code?

Try creating properties in your db class to hold values for fields that you
might be filtering by. Then dynamically construct the where clause of your
query using those properties.

-----------------------------

Hi,

I'm creating a 3-tier asp.net application and I've come across a potential
problem. My first tier is aspx pages and csharp codebehind files. The
second tier is a .NET assembly (dll) and the third tier is, of course, my
database.

I'm trying to make the second tier as abstract as possible regarding
representation of the data in the database. For instance, I create a class
in the first tier and fill it's attributes with values from a user form and
then call a CreateNewRecord method in my second tier that turns the class
into parameters in a stored procedure call.

All of this seems to work fine. My question is regarding retrieval of
records and how to deal with only returning a subset of records from my
database? For instance, if I have a large table of employees and I'm only
interested in seeing employees with the last name of "smith", then how do I
handle this in my second tier? In other words, how do I allow the first
tier code to specify a subset of records to be returned?

I thought about just always returning a DataSet object to the first tier
that contained all table entries and then let the first tier code decide
what to display, but that solution seemed like it might be a performance
problem with large tables? I also thought about creating a query class that

the first tier code could fill in and then call a second tier method (e.g.
GetRecords(QueryClass)), but it seems like there would have to be a large
amount of combinations that I would have to handle. Does anyone have any
suggestions?

Thanks,
Jon

| [aspngbeta] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngbeta.asp = JOIN/QUIT

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

| [aspngbeta] member Click here to reveal e-mail address = YOUR ID
| http://www.asplists.com/asplists/aspngbeta.asp = JOIN/QUIT

********************************************************************
Visit our Internet site at http://www.rbsmarkets.com

This e-mail is intended only for the addressee named above.
As this e-mail may contain confidential or privileged information,
if you are not the named addressee, you are not authorised to
retain, read, copy or disseminate this message or any part of it.
********************************************************************

Reply to this message...

Thomas Tomiczek (VIP)

Just a short note... with ***

-----Original Message-----
From: RATSEY-WOODROFFE, Jens, FM [mailto:Click here to reveal e-mail address]

Sent: Mittwoch, 2. Mai 2001 13:33
To: aspngbeta
Subject: [aspngbeta] Re: Return Entire DataSet to Display Code?

<..>
across your network! Stored procedures also have the advantage of
running
compiled and optimized on the database server - dynamically created SQL
will
not.

*** Carefull here - this optimisation can be pretty bad. Basically this
means your access path is NOT re-evaluated, which means it might be -
WRONG. That's why SP's can have "WITH RECOMPILE", in which case, though,
you loose the performance advantage.

*** It's, though, a special case. I just wanted to have this mentioned
in this context.

*** Regards

*** Thomas Tomiczek
*** THONA Consulting Ltd.

Reply to this message...

Jon Strauss

Thanks for all of the comments. I think I may be able to use a combination
of all of them. I thought about using the idea of db class to hold values
for the fields, but it would limit me since I wouldn't be able to specify a
range or multiple conditions of the same field (lastname='smith' AND
lastname='jones'). However, if I made the class a little more complex, I
might be able to handle most possibilities. I'm probably making this more
difficult than it needs to be. Anyway, thanks again for the help.

Jon

"Strauss, Jon" <Click here to reveal e-mail address> wrote in message
news:379303@aspngbeta...
[Original message clipped]

second tier is a .NET assembly (dll) and the third tier is, of course, my
database.
[Original message clipped]

in the first tier and fill it's attributes with values from a user form and
then call a CreateNewRecord method in my second tier that turns the class
into parameters in a stored procedure call.
[Original message clipped]

database? For instance, if I have a large table of employees and I'm only
interested in seeing employees with the last name of "smith", then how do I
handle this in my second tier? In other words, how do I allow the first
tier code to specify a subset of records to be returned?
[Original message clipped]

what to display, but that solution seemed like it might be a performance
problem with large tables? I also thought about creating a query class that
the first tier code could fill in and then call a second tier method (e.g.
GetRecords(QueryClass)), but it seems like there would have to be a large
amount of combinations that I would have to handle. Does anyone have any
suggestions?
[Original message clipped]

Reply to this message...

System.Data.DataSet

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification