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

  • Thread starter Thread starter Showjumper
  • Start date Start date
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...
 
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...
 
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...
Click to expand...
 
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...
Click to expand...

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