With respect to the data access layer, what do you all do?

S

Showjumper

Do you all create dedicated data access layers for each project or do you
create resuable ones that you can use over many projects. Also, in the data
access layer, do you all create specific functions with the sql statements
hardcode into each and every function or just make a generic function and
pass it different sql statments? Thanks...
 
H

Hermit Dave

well i write a set of classes to deal with specific data.
i.e. if you have users then there's a class like usersDB which just methods
to add/ update... validate...
if you have products then you have methods that deal in product related
info.
shopping cart would have methods relating to different things you can
perform on shopping cart with relation to db

no i do not hard code sql statements. But i try to make generic stored procs
for each table within the db
so product would have
product_add
product_update
product_select
product_select_all
product_select_active

i call the corresponding stored proc based on requirements. if its more than
one product and i need to show it in a tabular format
i return a disconnected dataset
if its just one product then i use SqlParameter to channel the data around.
For add / update i always end up using stored procs in conjugation with
SqlCommand and SqlParameters

I use stored procs cause its already compiled and has an execution plan at
hand.
SqlParameters so that i don't have to worry bout SQL hacks.. plus it takes
care of quotes " ' " for string based data types.

hope this helps...
 
S

Showjumper

Thanks, that gives me some ideas.
Hermit Dave said:
well i write a set of classes to deal with specific data.
i.e. if you have users then there's a class like usersDB which just methods
to add/ update... validate...
if you have products then you have methods that deal in product related
info.
shopping cart would have methods relating to different things you can
perform on shopping cart with relation to db

no i do not hard code sql statements. But i try to make generic stored procs
for each table within the db
so product would have
product_add
product_update
product_select
product_select_all
product_select_active

i call the corresponding stored proc based on requirements. if its more than
one product and i need to show it in a tabular format
i return a disconnected dataset
if its just one product then i use SqlParameter to channel the data around.
For add / update i always end up using stored procs in conjugation with
SqlCommand and SqlParameters

I use stored procs cause its already compiled and has an execution plan at
hand.
SqlParameters so that i don't have to worry bout SQL hacks.. plus it takes
care of quotes " ' " for string based data types.

hope this helps...
 
C

Craig Deelsnyder

Showjumper said:
Do you all create dedicated data access layers for each project or do you
create resuable ones that you can use over many projects. Also, in the data
access layer, do you all create specific functions with the sql statements
hardcode into each and every function or just make a generic function and
pass it different sql statments? Thanks...

I normally create a 2-level data access layer. The first layer is the
lowest level data access. It has usually a worker class that has
methods like SelectSQLData(sql), ExecSproc(...), etc. and perhaps a
factory class if I need to support multiple databases (it would know how
to work with more than one provider). This is something all apps would use.

Then I create another layer of classes on top of those, which are
specific to the application I'm working on. These classes take inputs
and save/retrieve data from the database using the classes above.

This way I have one layer that is app-agnostic and reusable, and another
that is built on top of this original layer for each app.

BTW, I normally try to use stored procedures where possible, although
sometimes you can't and so my first layer supports SQL queries as well.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top