DataSet Question -- Simple Concept? Beginner to 3-Tier asp.net

[Rangy]

Hi,

I am trying to be a thorough coder and create an app in layers. I can
do it using the sqldatasource but I really want to try creating the app
with tiers....thus....

If I use VS2005 to create a typed data set, I see that I can allow VS
to automatically generate INSERT, UPDATE, DELTE, statements for the
dataset.

1. Since the INSERT, etc. commands are directed at the database, why
would you want to write custom INSERT, etc,. commands for the dataset?
I though that that was the whole point of using stored procedures? If
I want to use Northwind and return all products that cost over $1, I'd
create a stored procedure ReturnProductsOverOneDollar. So if you use a
stored procedure, would you put it in the DAL, or as a buisness rule in
the business layer? Ideally I'd have a datlist or similar control, and
then call the ReturnProductsOverOneDollar sp to populate it.

2. I would then this example, want to allow the user to add a product
using another stored procedure, AddProducts. AddProducts would
obviously have an INSERT statement in it. Does this INSERT statement
have to be defined in the DAL? Clearly I have to code as many stored
procedures as I need, but does this mean that I have to write an
INSERT, UPDATE, and DELETE statement in the DAL for every possible
occurrence?

3. If I use northwind for example, and make a TableAdapter based on the
products table (call it ProductTableAdapter), I see that it generates a
Fill() method and a Get() method which returns data based on my SELECT
statement. If you let the Table Adapter Wizard automatically generate
the INSERT, UPDATE, etc. statements, why don't you see them as methods
in the TableAdapter.

Thank you so much in advance for your help.
-David

 

[RobinS]

Answers below.

Robin S.
------------------------------------------------

Hi,

I am trying to be a thorough coder and create an app in layers. I can
do it using the sqldatasource but I really want to try creating the app
with tiers....thus....

If I use VS2005 to create a typed data set, I see that I can allow VS
to automatically generate INSERT, UPDATE, DELTE, statements for the
dataset.

1. Since the INSERT, etc. commands are directed at the database, why
would you want to write custom INSERT, etc,. commands for the dataset?
I though that that was the whole point of using stored procedures? If
I want to use Northwind and return all products that cost over $1, I'd
create a stored procedure ReturnProductsOverOneDollar. So if you use a
stored procedure, would you put it in the DAL, or as a buisness rule in
the business layer? Ideally I'd have a datlist or similar control, and
then call the ReturnProductsOverOneDollar sp to populate it.

You might have a different insert statement?

I'm using stored procedures in my Data Access Layer to write the info
from my Business layer. I'm not using strongly-typed datasets, I
have business objects. I have a class defined for Products. In that
class, I have methods for populating my class, saving the changes
to the class, etc. When I put them on a form, I set up a data
source pointing at the object (the class) instead of the
database, and then used that to bind my fields to the controls
on the form.

When the user hits <Save> (or whatever) on the main form, it
runs the Save method of the class, which passes the pertinent
info to the DAL.

2. I would then this example, want to allow the user to add a product
using another stored procedure, AddProducts. AddProducts would
obviously have an INSERT statement in it. Does this INSERT statement
have to be defined in the DAL? Clearly I have to code as many stored
procedures as I need, but does this mean that I have to write an
INSERT, UPDATE, and DELETE statement in the DAL for every possible
occurrence?

For me, this is in the DAL. I call it from the Product class,
providing the values of all the parameters. I have Insert,
Update, and Delete statements for each class. They are in
*one* stored procedure, and I pass in the action I want to take. I
also pass in the *name* of the stored procedure from the Product
class to the DAL. This is especially useful when reading a list
of products from my ProductList class, because I can ask for all
products, or all products with a filter on a specified field -- I
have a stored proc for each of those.

3. If I use northwind for example, and make a TableAdapter based on the
products table (call it ProductTableAdapter), I see that it generates a
Fill() method and a Get() method which returns data based on my SELECT
statement. If you let the Table Adapter Wizard automatically generate
the INSERT, UPDATE, etc. statements, why don't you see them as methods
in the TableAdapter.

Speaking in VB, once you have created a strongly typed data set
(using the dataset designer) and it creates all that code for you,
including the adapter stuff, I found that you have to include it
in your program as an Imports, like

'this has a namespace in the designer code;
' import it into your code so you can access
' the table adapters
Imports myProject.MyDataSetTableAdapters

and then you can access it like this:

Dim myAdapter As MyDataSetTableAdapter _
= New MyDataSetTableAdapter
myAdapter.Update(myDataSet.myTable)

Thank you so much in advance for your help.
-David

You're welcome. Hope this helps.

Robin S.

 

[Earl]

I don't write anything for ASP apps, so my way of doing things may or may
not be appropriate. However, just because you have a strongly typed dataset
does not mean you cannot still create an app in a layered fashion. Realize
of course that the dataset is disconnected. If you create a data layer to
interact with your stored procedures, you can simply send tables of
information back and forth to that layer and let it do all the work. In
fact, I pull tables in the data layer and then merge them with the strongly
typed dataset. Where this whole idea gets blurred is via the
tableadapters -- well, Microsoft, which is it, disconnected datasets or
datasets with tableadapters that are now "disconnected" but most decidedly
part of the data layer? Needless to say, I do not use the table adapters, I
immediately delete those when using the dataset designer, but create the
params with the normal data adapters, then move the params back to the data
layer.

 

[Cor Ligthert [MVP]]

Rangy,

As soon as you have related tables you will see that you are in trouble with
the basic concept, than you will have to create your own procedures.
Although you can use the created transact SQL code as well by that.

Cor