database classes in a separate DLL ?

[utilitaire]

How many of you split their website into 3 dll:

 

1) Visual part (aspx)

2) Engine (code-behind)

3) Database access

 

I already code all my webforms in code-behind. However, I haven't split the code-behind so that all database queries would be in a single classe (or group of classes). I've seen this approach somewhere on the web, but I'm not so sure I want to use it. Here's an example:

 

Instead of doing this in a typical code-behind page:

 

sqlcommand = "select * from user";

...

 

I could do something like this:

 

MyProjectDataBase mpdb = new MyProjectDataBase();

ArrayList a = mpdb.GetUsers();

 

 

What do you think of this approach? In this way, all queries are encapsulated in a single DLL. All IO access are separated from the engine part. The problem I see is that I have to create another method EVERY SINGLE TIME I want to create a query.

Edited July 29, 2005 by utilitaire

[PlausiblyDamp]

Using something like the Data Access Application Block from Microsoft's Patterns And Practices site can simplify your data access code anyway.

 

Creating additional methods to encapsulate your DB is only a little extra work for potentially much more maintainable code (plus overloaded versions of these methods can make calling with similar requirements far simpler).

Too be honest you shouldn't really be hard coding SQL into your application anyway - if your DB supports stored procedures then use them.

[utilitaire]

Using something like the Data Access Application Block from Microsoft's Patterns And Practices site can simplify your data access code anyway.

 

Creating additional methods to encapsulate your DB is only a little extra work for potentially much more maintainable code (plus overloaded versions of these methods can make calling with similar requirements far simpler).

Too be honest you shouldn't really be hard coding SQL into your application anyway - if your DB supports stored procedures then use them.

 

The problem is that I have a website with more than 300 asp pages and 300 or 400 SQL queries, and I'm not really reusing my queries. Maybe 30 SQL queries are reused in the entire site. The thing is, I have to keep all queries very optimized. I cannot accept to do things like «select * from ..." or stuff like that. Therefor, queries tend to be very different from page to page. I assume that people who work with database classes that encapsulate their queries dont care much about requesting unecessary data to the SQL server. Is this really a good practice? If I create a class that encapsulate my queries, will it be really helpfull since I cannot really reuse theses methods in other pages??? One method, one page. 300 pages, 300 methods. :confused:

[PlausiblyDamp]

Just because you encapsulate your data access doesn't mean you have to return unnecessary data.

Depending on the types of queries you are executing and what you are doing with the results i.e. are you doing lots of joins and returning a single DataTable or are you returning multiple tables, are you using Datasets or DataReaders, there may be ways to organise your database code to be more re-usable.

[utilitaire]

Just because you encapsulate your data access doesn't mean you have to return unnecessary data.

Depending on the types of queries you are executing and what you are doing with the results i.e. are you doing lots of joins and returning a single DataTable or are you returning multiple tables, are you using Datasets or DataReaders, there may be ways to organise your database code to be more re-usable.

 

Here's an example of method in a database class:

 

public DataTable GetUserInfo(string userid){
      return sqlobject.Read("select * from user where id =" + userid);
}

 

This method encapsulate all info about the user. In this way, I guess I can easily reuse this method in many pages, since it return all the data from the table. However, each time I call this method, It will read all the column in the table, even if I only want a few of them.

 

Here's a second method:

 

public DataTable GetUserPassword(string userid){
      return sqlobject.Read("select password from user where id =" + userid);
}

 

This method only returns the password of the user. This time, the query is more performant since it wont return unecessary data. However, I wont be able to reuse this method very often. It will be usefull only when I need a password. Therefor, is it really usefull??? If I only use this method one time in the whole web site, why would I like to encapsulate this query? If I have to create a different method for each every possible combinaison of INNER JOIN and LEFT JOIN and UNION and WHERE in my database, I will have to create 300 methods in this class.

 

Do you have a example of this kind of class? I'd be interested to see how you do that. thank you!

[Joe Mamma]

I believe you are missing/combining layers that should be seperated. . .

 

Presentation ([{Asp Page/Controls}|{Windows Forms/Controls})

User Interface (Binding Logic)

Business Objects (Object model abstracting the business at hand)

Data-Access (Utilizing ADO.NET/ADO/ODBC/BDE/XML/Legacy)

Data-Resources (Physical Data Storage)

 

Work on building the Business Objects that abstract what you want to do.

get this book. . .

http://www.apress.com/book/bookDisplay.html?bID=284

 

You can use various code generation techniques to generate the Data-Access layer.

get this book. . .

http://www.apress.com/book/bookDisplay.html?bID=212

[utilitaire]

I believe you are missing/combining layers that should be seperated. . .

 

Presentation ([{Asp Page/Controls}|{Windows Forms/Controls})

User Interface (Binding Logic)

Business Objects (Object model abstracting the business at hand)

Data-Access (Utilizing ADO.NET/ADO/ODBC/BDE/XML/Legacy)

Data-Resources (Physical Data Storage)

 

Work on building the Business Objects that abstract what you want to do.

get this book. . .

http://www.apress.com/book/bookDisplay.html?bID=284

 

You can use various code generation techniques to generate the Data-Access layer.

get this book. . .

http://www.apress.com/book/bookDisplay.html?bID=212

 

 

Ok But I think I saw an article somewhere about people who actually create a classe (databaseAccessLayer) that contains all access queries to external data. Here's an example:

 

public class myMedia{

public datatable getUserInfo(){}
public datatable getUserLogged(){}
public datatable getStats(){}
public datatable getPassword(){}
public datatable getVisitors(){}
public datatable getIPVisitor(){}

public void SetUserPassword(){}
public void SetUserCountry(){}
public void SetUserName(){}
}

 

This class can be used after that in the engine:

 

myMedia m = new myMedia();
m.SetUserName(userid);
m.SetUserCountry("Canada");

 

Is this a correct way to encapsulate the data access?

[Joe Mamma]

I think you are mixing the business layer with the data access layer. . . I doubt that in your media object has properties that are datatables. what you are doing is coupling your object to the data access.

 

Again, get lhotka's business object book.

[Joe Mamma]

myMedia m = new myMedia();

m.SetUserName(userid);

m.SetUserCountry("Canada");

So you are making a round trip to the database for Username and country???

What happens when you are processing a batch of 1000 via a webservice or remoting??? Awful noisy and definitely not scalable!

 

myMedia m = new myMedia(Id);

Datatable userinfo = m.getUserInfo();

Datatable userlogged = m.getUserLogged();

again very noisy - AND it requires knowledge of the underlying datatables. . .

what happens if the result definition change (new/removed field - redefined field, etc)

 

The code compiles but it is broken.

 

what you want is

to be able to do something like:

 

myMedia m = new myMedia()

m.UserName = userid;

m.UserCountry = "Canada";

m.ApplyChanges();

 

{or m.RollbackChanges() }

 

and, when

myMedia m = new myMedia(ID);

 

at that point you want the userinfo and all dependent data already loaded in the initial call to the data layer. No need to make the multiple calls.

 

Also, you want UserInfo and UserLogged published as typed properties. I mean, userinfo, is an object isnt it???

 

Get lhotka's Expert C# Business Objects!!!!

[bri189a]

Is there a site out there that has an excerpt from that book. Sounds like a good idea for a book, but some are written well, and some are not; and that is subjective to the reader - I'd like to see an excerpt if you know of one so I can evaluate my need for it. Thanks Joe.

[mhildner]

...I saw an article somewhere about people who actually create a classe (databaseAccessLayer) that contains all access queries to external data.

That's the general idea. Joe Mama is correct in that you're mixing up some terminology. Those methods exist in the business object, not the data access class.

 

Our business objects usually have a few basic methods we use from a template, such as:

public DataSet GetAll()
public DataSet GetByPK(int primaryKey)

More "get" methods are added and tweaked as needed.

 

Note that a DataSet is returned (although it's not limited to that). Because of all the code in your base business objects and ADO.NET, you get your DataSet, change/add/delete data if you want, then call something similar to myBusinessObject.Save(). There's no need to write any of your "Set" methods.

 

A couple posts ago you mentioned a business object method and a form method. Both those methods should reside in the business object (you might tweak 'em a bit first). As far as moving your multipled INNER JOIN etc. code, you might think about making them stored procedures or views and creating business objects from that.

 

I was going to go on about why all this is a good idea, but this post is too long already.

 

Adios,

Mike

[utilitaire]

Thank you all guys!

 

I already use many stored procedure (hundreds). What I want to know, is even if you use stored procedure in your code, do you actually put all those queries(stored procedure calls) into a separate class, or do you simply put those procedure calls directly in you logic code?

 

Stored procedure encapsulated in a class:

 

public class MyMedia{

public DataSet GetUsers(){
     return mySqlObject.RunQuery("exec ps_getUsers");
}

}

 

Stored procedure calls directly in the logic:

 

public void Page_Load(){

string user = Request.QueryString("userid");
DataSet da = mySqlObject.RunQuery("exec ps_getUsers " + user);
Response.Write(user);

}

 

Is there a benefit in putting the queries (procedure calls and so on) in a separate class? Until know, I've never done that. But before starting my new project (.net), I want to be sure I using the best methods. Thank you again!

[Joe Mamma]

oh definitely put your data access calls inside of the object that needs it.

 

MyMedia should encapsulate all the logic it needs to load up - it is the interface between the UI and the data objects.

 

But, I don't usually return ADO.Net objects from my Business objects.

 

Using Lhotkas CSLA,

 

my objects implement:

IEditableObject, ICloneable,

IDataErrorInfo, Serialization.ISerializationNotification

 

And my collections implement:

IBindingList

 

This makes them usable like any typed dataset with the added benefit that they are my object model and not my relational model.

 

You have to see it in action to see the payoff. The real payoff is in serialization. In .Net 1.x, datasets where only XML serializable (and stand alone data tables were not serializable at all)

[utilitaire]

Ok its interesting but I dont really understand what this call is actually used. :rolleyes: Maybe you have an article somewhere on the web that describes this kind of class, with the a complete example.

 

Thank you again!

[Joe Mamma]

Ok its interesting but I dont really understand what this call is actually used. :rolleyes: Maybe you have an article somewhere on the web that describes this kind of class, with the a complete example.

 

Thank you again!

http://www.lhotka.net/

http://www.msnusers.com/CSLANET/Documents/7264%5Fchap01.pdf

 

get his download for CSLA here,and I will post a simple example later in the day.