Data Access Layer?

[TheCivic]

Hi everyone,

I was sondering if someone can point me in the right direction to the following :

We currently have a web application in old asp that is used by our clients.
Each client has there own database for there data.

Now i am trying to rewrite the application so it is Object Orientated.

I have an object that loads its data from sql using a stored procedure, what happens when i have to load more then 1 object into a collection? I cant reuse the stored procedure, unless i start changing the input variables it accepts, which means i have to have another stored procedure that loads multiple items. Is this the correct way of doing it? I dont really want duplicate code in the database, as at the moment we have over 70 databases! So that is a lot of stored procedures.

What about creating the SQL dynamically? I know that i would still repeat the code, but it would only be in the base and collection class. Would this be the better approach?

If anyone has any material that they think i need to read i would be willing to read it, if you can send me a link.

Cheers
Darren

 

[BrianB]

Does the sproc return the data for a single object or for more than one?

Is it , for example,
p_GetCustomerOrders(@CustomerID) or p_GetOrder(@OrderID)

It is trivial to iterate through data rows and create a new object for each row.

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com

 

[TheCivic]

I have setup the objects in 2 different classes, 1 is a single object called ob1 and the other is a collection class, which holds more then 1 ob1 class.

' sinle object defined as
Dim a as new ob1(ID)

' collection object defined as
SQL = "ID = 12 or id = 13 or id = 14"
dim a as new ob1collection(SQL)

 

[BrianB]

OK. And you want to avoid making 2 db trips?

Is there something holding the collection together, like an id of a parent object?

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com

 

[TheCivic]

BrianB,

This is my first try at single objects and collections, and i am wanting to know the following.

For the single object i have a stored procedure that accepts an ID, to get all the data.

Now for the collection, the SQL is the same apart from the fact that i need to search for multiple IDs.

Should i change the single object stored procedure, to accept a string of IDs?
Or should i dynamically create the SQL, then i dont need to bother, setting input parameters on stored procedures.

What is the best solution for this? I really only want 1 SQL string i.e. Select a,b,c,d,e from table1 + WHERE

You mentioned before, whether i have something holding the collection together, like an id of a parent object? What do you mean by this? Can you give an example?

Cheers

 

[BrianB]

What are the objects you want to retreive? If they are a series of order items, which are owned by another object, say an order, it is trial to ask to all items for order id 876.

If not, then you can use any criteria you want and build a collection from the dataset.

mycollectiontype col = new mycollectiontype();

foreach (DataRow dr in mydataset.Tables[0].Rows){
mycollectiontype.Add(buildobjectfordatarow(dr));
}

buildobjectfordatarow is a function that takes a row and returns an instance of the object.

myobjecttype buildobjectfordatarow(DataRow dr){
myobjecttype obj = new myobjecttype();

obj.MyField = (System.String)dr["myField"];
return obj;
}

Something like that

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com