Pros n Cons - One class to hold all Data Access Method?

[aiax]

Rafey,

Our current structure has a single DAL assembly, but we correspond each
class in the assembly with a table or view in the database. For instance, a
database for an e-store might contain the following tables: Customers,
Orders, Inventory. We would have a class Customers, class Orders and class
Inventory. Each class handles updating, inserting, loading (populating
properties), retrieving (returning sqldatareader) with code that also joins
transactions using Microsoft Application Blocks for .NET. So our DAL is
directly related to the structure of the database. We use the business
logic layer (BLL) to tie everything together, and our BLL corresponds to the
presentation layer, not the DAL. So in the presentation layer, we may have
a page and class called ShoppingCart.aspx/ShoppingCart.aspx.vb. In BLL, we
would also have a class ShoppingCart.vb. The presentation layer would call
ShoppingCart.vb in BLL to do everything it needs to have processed, and
ShoppingCart.vb will instantiate DAL.Customers, DAL.Orders and DAL.Inventory
to put everything together and give it back as a single structure of
information to the presentation layer, relieving the amount of actual
business logic code that must exist in the presentation layer. This leave
presentation layer to concentrate on the actual "presentation" of the data
and interaction with the user. BLL is the go between for user to database,
putting everything together, and also BLL is where the majority of the code
is being stored. DAL simply interacts with the database.

I hope our scenario for construction helps you understand a bit better,

Aiax

 

[Kevin Spencer]

IMHO, this is not entirely extensible. I would suggest a further separation.
Put generic Data Access methods into a single class. Create business classes
that work with the specific database that your app is currently working
with. The business classes interact with the Data Access class to manage
their data. Then, later, if you need to write another app that works with a
different data source, you still have the generic Data Access class to
provide the lower-level functionality, and you merely build a different
business class(es) on top of that.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Kevin Spencer]

I have one further suggestion, which we are currently in the process of
implementing: Separate the presentation layer into 2 components: 1 for
presentation logic, and one for layout. This can be achieved by using
table-less layout and CSS. In other words, don't put any layout information
(in-line attributes, styles, tables for psotioning) in the HTML that your
app generates. Instead, let the CSS do that part. Later, if you need to
change anything about how the app looks, it's merely a matter of editing a
style sheet.

This is particularly useful due to the fact that Graphic Designers often
know almost nothing about programming, and programmers know almost nothing
about Graphic Design. This method enables the 2 to work almost entirely
independently, and should cut labor time/costs immensely.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Raymond Lewallen]

Excellent suggestion Kevin. We actually do that as well, but since his
original post was only asking about DAL, I wasn't going to go that far.
However, since i tend to ramble and went far enough to explain most of our
layers, it was erroneous of me to have excluded this information. Thanks
for supplying that information which I left out.

Also a very good point about the difference in the graphics designers and
programmers, which is one reason our large scale application has 12 separate
assemblies right now.

Raymond Lewallen (Aiax)

 

[TJS]

how does one derive /define a generic Data Access Layer ?

 

[Kevin Spencer]

It is an assembly of (in our case) static methods, which can perform various
operations on a Database without being specific about what database, table,
etc. that they are working with. For example, we have methods which return a
DataTable from a query, a DataTable from a Stored Procedure, a DataReader
from a query, a DataReader from a Stored Procedure, etc. It also includes
methods for opening and closing Connections, and other typical database
operations. Our methods that return disconnected data, such as DataTables
and DataaSets, open and close their own Connections, while methods that work
with DataReaders receive a reference to a Connection and Command, and return
the DataReader. The calling class must then close the Connection by passing
it to another static method which receives a reference to the Connection and
closes it.

Our business classes employ these methods to work with the data they need,
and then manipulate it according to their functionality.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Charlie Smith]

Hello,

We are developing ASP.NET / VB.NET database application. We have one design
suggestion as stated below:

What are the Pros n Cons of having ONE and ONLY ONE class to hold all Data
Access Method? Or

rafey,

I do this in both small and large apps, but there is an issue that I
don't see mentioned here much. I do not mix sql code and .net code.
I was told many years ago to let the database do what it does best, so
I never allow a database to accept sql code. Everything is done with
stored procedures. That makes my DAL class very simple and my db code
very secure and effecient. I even do this with access database apps,
even though they aren't as effecient as sql server when executing
stored procedures.

I basically have one command object and parameters collection in my
DAL layer, and yes, I even put SELECT ALL FROM queries in stored
procedures. This puts the responsibility for data integrity both in
and out of the database on the database itself (which is where it
belongs).

A small bonus to this is that you can protect your business logic to
some degree by having it locked away in a database where it is
inaccessable without the proper credentials.

my $.02

Charlie

 

[rafey]

Hello,

We are developing ASP.NET / VB.NET database application. We have one design
suggestion as stated below:

What are the Pros n Cons of having ONE and ONLY ONE class to hold all Data
Access Method? Or

What are the advantages or disadvantages of following design:

ONE class that will contain ALL DAL (Data Access Layer) methods for complete
SQL Server Pubs database, for example.

That ONE class will contain Insert, Update, Delete and select method for all
tables in Pubs database. This class may have hundreds to Select from Pubs
database in various ways. We have following concerns for this design:

1- Resource Usage
2- Transaction
3- Code Reusability
4- Security

Please write if there are any advantages or disadvantages of this design.

Regards,
Rafey

 

[Jeremy]

We are developing ASP.NET / VB.NET database application.

See the Microsoft Patterns & Practices (http://msdn.microsoft.com/patterns -
data access section) or Design Patterns (book by GoF)

For future reference, ADO questions should be posted here:
microsoft.public.dotnet.framework.adonet

*Most people* would claim that a single class for data access is monolithic
and naive, but every approach has pros and cons, and those are based
directly on the future plans and intended usage of the application. This
question can easily turn into a fireball so I am just going to leave it at
that.

 

[Jeremy]

What are the Pros n Cons of having ONE and ONLY ONE class to hold all
Data

I do this in both small and large apps, but there is an issue that I
don't see mentioned here much. I do not mix sql code and .net code.
I was told many years ago to let the database do what it does best, so
I never allow a database to accept sql code. Everything is done with
stored procedures. That makes my DAL class very simple and my db code
very secure and effecient. I even do this with access database apps,
even though they aren't as effecient as sql server when executing
stored procedures.

Pros:
- Built-in Security
- Modular design (for SQL code anyway)
- No recompile for query mods

Cons:
- Monolithic & inflexable
- Heavy dependancy on data engine
- Non-modular design for data access components (the .NET side)
- Must re-sync SQL procs when queries change
- Negates some of the preformance advantages of using DataSets and Views

A small bonus to this is that you can protect your business logic to
some degree by having it locked away in a database where it is
inaccessable without the proper credentials.

This is true, but it also means that you can't effect logic from .NET code,
which causes a proliferation of stored procs for every little nuance in the
logic. Or, you get procs with 8 million parameters, which complicates the
query and the ability to understand what it does.

~
Jeremy

 

[Jeremy]

how does one derive /define a generic Data Access Layer ?

This question is too broad for this ng. See the following articles:

http://msdn.microsoft.com/architect...px?pull=/library/en-us/dnpatterns/html/Dp.asp

HTH,
Jeremy

 

[Sven Carstensen]

Hi Rafey,

Thank you all for clarifying things. But still I feel that not much is said
about this design regarding following:

1- Resource Usage if we use single class. Would it be costly in term of
memory foot print etc.

Class methods don't use memory except for the CPU instructions which you
need any way no matter if in one or in many classes.
One exception might be when you implement interfaces in your class which
might internally be implemented in terms of vtables like in C++. But that is
just an assumption.

If you have a lot of state for all the different connections or commands in
your one and only DAL class than the memory foot print will be higher than
necessary.

Stay with state less classes with static members and you will not have a big
memory footprint.

2- Transaction if 50 methods requires transaction, 50 supports transaction
and yet another 100 do not require transaction. Then will this design be
costly in terms of database locks, memory usage etc.

Database transactions do know nothing about classes and methods so they
don't care about your class design.

3- Code Reusability - The client of such class (WebForm or WinForm) having
large number of methods has any implication on code resuability. Is there
any code reusibility problem involved?

It might be difficult for the user of your class to find the correct method
which can be solved be proper method names. This of course would be an
argument for multiple classes as their names give a first hint which class,
e.g. get a product list -> use the Products class

Another point is development of the DAL class: when more than one person
wants to make changes one bis class can become a problem. With multiple
class you can more easily split the work.

4- Security - Are there any issues in this design?

Don't think so.

 

[Rafey]

Thank you all for clarifying things. But still I feel that not much is said
about this design regarding following:

1- Resource Usage if we use single class. Would it be costly in term of
memory foot print etc.

2- Transaction if 50 methods requires transaction, 50 supports transaction
and yet another 100 do not require transaction. Then will this design be
costly in terms of database locks, memory usage etc.

3- Code Reusability - The client of such class (WebForm or WinForm) having
large number of methods has any implication on code resuability. Is there
any code reusibility problem involved?

4- Security - Are there any issues in this design?

Just to clarify development environment further i.e. what we are using and
what we aren't. Here is a list:

A) We are using stored procedure, DB2 strored procedures that throws
exceptions. We are not using OUT parameter statusCode, errorCode etc.

B) We are using TypedDataSet and simple DataTable for binding with WebForm
UI controls.

C) We are using Win2K Pro, .NET 1.1.4322, VS.NET2003, DB2 7.2 and later DB2
5.1

D) We are using User Controls.

E) We are using Form Based Authentication

F) We will deploy application in two tiers one tier on Win2K Pro version
having DAL + DBHelper (for calling stored proc) + ASPX pages. And One tier
is AS400 DB2 database.

G) We are not using DataReaders as it keeps the connection open. Connection
theft problem. Our DBHelper class closes the connection and it is an
internal (C#) or Friend(VB.NET) class

H) OleDBCommand is passed between DAL and DBHelper (DBHelper is a Kind of MS
Data Block).

Keeping these things in mind I wish to know group words on this design.
Thanks in advance.

1- Resource Usage
2- Transaction
3- Code Reusability
4- Security

Agian thanks for the help.

Regards,
Rafey.

 

[Rafey]

Thanks Sven For your comments on design concerns.

1- Sven, I believe you are right, DAL class should be "almost stateless" in
order to avoid any large memory foot prints except:

A) It will have a ConnectionString private string variable and
B) Non of its methods will be Shared (VB.NET) / static(C#).

DAL will call DBHelper with private ConnectionString and OleDBCommand object
which needs to be executed. ConnectionString will be set once whenever a new
DAL object will be created and DAL method will be called.

Please write your suggestions if I could improve on this design of DAL.
_________________________________________________________________________

2- As far as I know you have to set class attributes in order to make it
transaction aware. For example, to make MyDALClass transaction aware, you
will do something like that

<Transaction(TransactionOption.Required)> Public Class MyDALClass
Inherits ServicedComponent

Public Sub Save(ByRef objClientDS As ClientDS)
End Sub

Public Sub DeleteClient(ByVal clientID As Integer)
End Sub

' Other Functions
Public Function SelectMailingAddresses() As AddressDS
End Function

Note that: Transaction(TransactionOption.Required) is NOT required for
function which merely selects the data for example,
SelectMailingAddresses().

TransactionOption.Required is required for DeleteClient() and Save()
functions. So, I am afraid that "Database transactions do know nothing about
classes and methods so they don't care about your class design.". I think
class design could effect transaction base resource usage

What I believe MyDALClass will always starts a transaction no matter which
function is called SelectMailingAddresses() or DeleteClient() or Save().
Correct me if I am wrong.

_________________________________________________________________________

3- I agree with you Sven, that it might be difficult for the user of your
class to find the correct method which can be solved be proper method names.
But following are few other concerns:

A) You can not -->selectively<-- extract classes for Client, Order, Payment
etc. for example for other projects to reuse as you will have one DAL. Copy
Paste is a different kind of reuse.

B) One-class-all-function model may drift the whole development mind frame
to procedural programming which usually don't care about reusability.
Chances are that people will start writing "same" function twice with
different names. But again that can be solved by proper method names. Proper
design delegation to developers and strong code review could solve this, but
still I can see what kind of mindframe this design will result.

C) DAL class VSS checkout by one developer could cause other one to stop.

_________________________________________________________________________

4- I am not very sure about security issues if we have One DAL class, but
let's analyze:

A) .NET code access security (CAS) system determines an assembly's
permissions to access protected resources and not the class. So this
security is on assembly level and not on class level. Correct me if I am
wrong, you have to do more attribute based programming if you want to
restrict DAL class functions. CAS could be used to further restrict DAL
class method calls but I am not very sure.

B) IIS security is related to authentication. Most common are Anonymous or
Windows Auth. Both of these two has nothing to do directly with DAL class
method call level security. Any authorized user could call any method of DAL
class or classes. Whatever the case would be.

C) ASP.NET security is either impersonation, which is again kind of Windows
Authentication. Again impersonation could be used within code or at
web.config level.

D) ASP.NET Form based authentication/authorization which is more fine
grained, could control authorization at function level. But again you have
to code IPrincipal/IIdentity in each function for role based security. I
believe it is same as CAS as mentioned in point A).

E) Friend/internal at code level could restrict DBHelper to be used only by
DAL. But again it has noting to do with security of DAL class or mclasses
itself.

F) NTFS could only control MyDAL.dll that is assembly. So NTFS could only
provide assembly level protection and can not protect method level
authorization.

G) Database connection string will be used in same way as if it is one DAL
class or multiple DAL classes. sa and empty password could be replaced with
less privileged userid and password. ConnectionString could be encrypted no
matter if it is one DAL class or multiple DAL classes. Password could be
stored with a salt. So I don't see any security that could will break if we
use one class or multiple.

H) IPSec and SSL based security has nothing to do with if we use one DAL
class or multiple DAL classes.

Please suggest if there is any other way ONE DAL class could compromise
security.

Regards,
Rafey

(e-mail address removed)