Hi,

What I'm trying to do is make a DAL which can retrieve multiple result sets whilst only opening a database connection once.

Usually, a DAL will have methods like 'GetProducts' and 'GetProductCategories' and so if you want both sets for a single web page then you have to call both methods and it'll go to the database once for each.

So my idea is to have generic retrieval code which is passed objects which describe the required queries. So one would end up with code like the following...

DAL dal1 = new DAL();
EntityCollection collection1, collection2;

QueryDescriptor qd1 = Queries.GetProducts();
QueryDescriptor qd2 = Queries.GetProductCategories();

dal1.AddQuery(qd1, ref collection1);
dal1.AddQuery(qd2, ref collection2);

dal1.Retrieve();

So a 'QueryDescriptor' has properties for 'RelationPredicateBucket' and 'SortClause' etc. and a function like 'Queries.GetProducts()' would prepare one of these objects with the required state.

Now I think this is quite cool except I want to be able to cache query results. But if a result is in the cache then there's no reason to prepare the 'QueryDescriptor' instance's 'RelationPredicateBucket' and other things because there's no need to go to the database.

So what I'm thinking of doing is sub-classing 'QueryDescriptor' for each and every query such that methods on the derived class create the 'RelationPredicateBucket' and 'SortClause' and so forth but only if the DAL doesn't find the result set in the cache. So the above code would look like this....

DAL dal1 = new DAL();
EntityCollection collection1, collection2;

dal1.AddQuery(new GetProducts(), ref collection1);
dal1.AddQuery(new GetProductCategories(), ref collection2);

dal1.Retrieve();

Another method on a class derived from 'QueryDescriptor' would return the cache key. The DAL reads this and does a cache check and then only if the result isn't in the cache does it call the other derived class methods and so create the objects for generating the SQL.

So the question is, from an OO point of view, is there something dodgy about having one class for every query that I need to do?

Also, is there a better way of identifying a query in order to check if its result is in the cache without actually creating the query description first?

Cheers,

Ian.

So the question is, from an OO point of view, is there something dodgy about having one class for every query that I need to do?

In my opinion this will end with too much code and classes to maintain!!!

Your main target was:

What I'm trying to do is make a DAL which can retrieve multiple result sets whilst only opening a database connection once.

You can easily achieve this by keeping the connection open through multiple fetches

DAL dal1 = new DAL(); 
EntityCollection collection1, collection2;

dal1.OpenConnection();

dal1.Retrieve(ref collection1);
dal1.Retrieve(ref collection2);

dal1.CloseConnection();

Yes I could do that but there's a draw back to it.

I want to keep the connection open for as small a time as possible so I figure its best to run the code for creating the RelationPredicateBuckets and SortClauses before the connection has been opened. The code you suggested would have each query preparing the description of the query whilst the connection is open.

In my opinion this will end with too much code and classes to maintain!!!

The amount of code to describe queries will be the same however one does this. Its just a matter of where its located. I could have a 'ProductsDB' class which has one method for creating each products related query or I could have a 'ProductsDB' namespace with one class for each query. Either way its the same code. The benifit of the latter is that the DAL can create the actual query description on demand - by calling methods on the QueryDescriptor instance past to it.

It's a trade-off.

You are probably right, although I think the time it takes to build the predicates and sort expressions is insignficant.

I also think the design I'm proposing is a better factoring than a typical DAL because the actual work done with the DataAccessAdapter only needs to be in one place and then even this could be factored into a generic DAL which could be coloured with an application specific DataAccessAdapter factory.

So all one's app needs to do is create a QueryDescriptor and pass it to the generic DAL.

Ian wrote:

I also think the design I'm proposing is a better factoring than a typical DAL because the actual work done with the DataAccessAdapter only needs to be in one place and then even this could be factored into a generic DAL which could be coloured with an application specific DataAccessAdapter factory.

So all one's app needs to do is create a QueryDescriptor and pass it to the generic DAL.

doesn't this sound like using native ADO.NET code but with LLBL syntax. Afterall, the most effecient way to acheive what you want is to interact directly with the data-readers ?? For me, the main reason for using business objects is to encapsulate everything related to this BO in its class. This way, code maintanibility is optimum and beleive me in big projects code maintainability is a big issue. For general retreival code (reteriving resultsets and field-sets) I use a DB class with shared methods that take sort-clauses and/or predicate filters as parameters. This is easy to do because of the way LLBL abstracts the description of these objects.

For general retreival code (reteriving resultsets and field-sets) I use a DB class with shared methods that take sort-clauses and/or predicate filters as parameters. This is easy to do because of the way LLBL abstracts the description of these objects.

Yes this is similar to what I'm suggesting except the parameters you refer to are encapsulated inside a QueryDescriptor instance.

doesn't this sound like using native ADO.NET code but with LLBL syntax.

I don't see it!

I'm saying that I can do this...

DAL dal1 = new DAL();
EntityCollection collection1;

dal1.AddQuery(new GetProductsQuery(), ref collection1);

dal1.Retrieve();

..instead of a more typical DAL styled call...

DAL dal1 = new DAL();
EntityCollection collection1 = dal1.GetProducts();

..which is to decouple the DataAccessAdapter usage from the query being executed. I could also add a special method which runs a single query so the code would look like this..

DAL dal1 = new DAL();
EntityCollection collection1= dal1.GetSingleQuery(new GetProductsQuery());

..which takes one back to one line of code for the actual retrieval. You could even encapsulate this inside another function to get back to a typical DAL method.

So if I want multiple result sets whilst only opening the connection once then I can do this...

DAL dal1 = new DAL();
EntityCollection collection1, collection2;

dal1.AddQuery(new GetSingleQuery(), ref collection1);
dal1.AddQuery(new GetProductCategoriesQuery(), ref collection2);

dal1.Retrieve();

So the code inside the DAL class is completely generic except for the application specific DataAccessAdapter which I could provide via a factory object assigned to a static/shared property on the DAL.

[Sorry if you've been trying to read this whilst I've been continuously editing it.]

Rather than a class for each query, would it be eaiser to create a static method in your entity for your query.

So, you could say something like:

CustomerCollection custs = CustomerEntity.GetByStateOfResidence("State");

BOb

If I did that how would I retrieve multiple result sets whilst only opening the DB connection once?

CustomerCollection custs1 = CustomerEntity.GetByStateOfResidence("State");
CustomerCollection custs2 = CustomerEntity.GetByCountryOfResidence("IRE");

Surely that's two trips to the database?

Ian wrote:

If I did that how would I retrieve multiple result sets whilst only opening the DB connection once?

CustomerCollection custs1 = CustomerEntity.GetByStateOfResidence("State");
CustomerCollection custs2 = CustomerEntity.GetByCountryOfResidence("IRE");

Surely that's two trips to the database?

Are you looking to get one trip to the DB or one connection, there is a difference. I don't know if there is a way to tell LLBLGen to batch the SQL commands.

But, there is a way to tell it to use the same connection. I think you can only do that with the Adapter code though.

BOb

If batching is unavailable then it would have to be one connection.

But if I use your syntax then the user of the DAL has to worry about passing Adapters or connections between DAL calls, don't they?

Also, there's alot of redundancy within each DAL method becaue each DAL method has its own copy of the code to program the DataAccessAdapter.

Ian wrote:

If batching is unavailable then it would have to be one connection.

But if I use your syntax then the user of the DAL has to worry about passing Adapters or connections between DAL calls, don't they?

Also, there's alot of redundancy within each DAL method becaue each DAL method has its own copy of the code to program the DataAccessAdapter.

Not if each of those methods uses an existing DataAccessAdapter. You chould have a helper class that returns a new DataAccessAdapter or an existing one.

BOb

Could you post some code? I don't see how static methods can share an adapter given that adapters are not thread safe.

Ian wrote:

Could you post some code? I don't see how static methods can share an adapter given that adapters are not thread safe.

Are you creating a windows or web form app? Are you using remoting?

What layer/tier are you instantiating your DataAccessAdapter in?

BOb

I'm making a web form app and I'm not using remoting.

I'm instantiating DataAccessAdapters in the data access layer.

Ian wrote:

I'm making a web form app and I'm not using remoting.

I'm instantiating DataAccessAdapters in the data access layer.

Since it is a web app you can cache the DataAccessAdapter in the HttpContext. This way it will be shared by all the data access that is done in a single request.

I created a project and used the pubs db. I generated the adapter code. Here are two methods I added to the AuthorsEntity class:

public static Sandbox.HelperClasses.EntityCollection GetAuthorsByState(string State)
{
   EntityCollection col = new EntityCollection(new AuthorsEntityFactory());
   DatabaseSpecific.DataAccessAdapter da = (DatabaseSpecific.DataAccessAdapter)System.Web.HttpContext.Current.Items["DataAccessAdapter"];
   if (da == null)
   {
      da = new Sandbox.DatabaseSpecific.DataAccessAdapter(true);
      System.Web.HttpContext.Current.Items["DataAccessAdapter"] = da;
   }
   RelationPredicateBucket b = new RelationPredicateBucket();
   IPredicateExpression filter = new PredicateExpression();
   filter.Add(PredicateFactory.CompareValue(AuthorsFieldIndex.State, ComparisonOperator.Equal, State));
   b.PredicateExpression.Add(filter);
   da.FetchEntityCollection(col, b);
   return col;
}

public static Sandbox.HelperClasses.EntityCollection GetAuthorsByZip(string Zip)
{
   EntityCollection col = new EntityCollection(new AuthorsEntityFactory());
   DatabaseSpecific.DataAccessAdapter da = (DatabaseSpecific.DataAccessAdapter)System.Web.HttpContext.Current.Items["DataAccessAdapter"];
   if (da == null)
   {
      da = new Sandbox.DatabaseSpecific.DataAccessAdapter(true);
      System.Web.HttpContext.Current.Items["DataAccessAdapter"] = da;
   }
   RelationPredicateBucket b = new RelationPredicateBucket();
   IPredicateExpression filter = new PredicateExpression();
   filter.Add(PredicateFactory.CompareValue(AuthorsFieldIndex.Zip, ComparisonOperator.Equal, Zip));
   b.PredicateExpression.Add(filter);
   da.FetchEntityCollection(col, b);
   return col;
}

In the web form I used the following code in page load:

Sandbox.HelperClasses.EntityCollection col = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByState("CA");
DataGrid1.DataSource = col;
DataGrid1.DataBind();

Sandbox.HelperClasses.EntityCollection col2 = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByZip("95128");
DataGrid2.DataSource = col2;
DataGrid2.DataBind();

you could also encapsulate the first 4 lines or so into a Helper class as a factory method to reduce getting a reference to the DataAccessAdapter object to one like of code.

The main down side is, this will tie the datalayer to a web app. You need to reference System.Web in your database generic project.

I think there is a similar way to store data in the thread context but I don't know how off hand. This would be better because it would allow you to use the same BL/DAL for Win apps and remoting servers as well as for Web Apps and Web Services.

BOb

That's cool thanks.

Two things though...

Firstly, each of the Helper functions repeat the generic DataAccessAdapter programming. i.e. da.FetchEntityCollection(col, b); so there's not a clear decoupling of the data access code and the query being executed.

In the code I posted, once the generic DAL has been written, it can be plugged into any app and then one only needs to create QueryDecriptors which can be passed as parameters to the generic DAL.

Secondly, I'm trying to not only reduce the number of DB connections being opended and closed per page request but also to keep the time that the DB connection is open to a minimum. Your code has, I believe, the connection opened the first time a fetch is done and then its basically left open indefinitely. I'm not sure, maybe until the next garbage collection.

Ian wrote:

Firstly, each of the Helper functions repeat the generic DataAccessAdapter programming. i.e. da.FetchEntityCollection(col, b); so there's not a clear decoupling of the data access code and the query being executed.

You could probably abstract that out. You should have the static methods return predicate epressions buckets and then pass those to the data access adapter. Which I think is what you were doing.

But, somewhere you have to create the DataAccessAdapter, your code is not clear in where or how you are doing that? I was trying to demonstrate how you could use the HttpContext to store the DataAccessObject so you could use the same connection for all your queries in a single request. Wasn't that your initial goal?

I think the main thing I was trying to say is that I wouldn't want to create a full class for each query. Adding static methods to the entities which abstract all the filter building and querying will make your UI code very simple. I'm not sure why you want to make the UI programmer go through the extra step of calling retrieve? I was trying to have the static method return the object graph the UI wants with as few steps and as little cohesion as possible.

Ian wrote:

Secondly, I'm trying to not only reduce the number of DB connections being opended and closed per page request but also to keep the time that the DB connection is open to a minimum. Your code has, I believe, the connection opened the first time a fetch is done and then its basically left open indefinitely. I'm not sure, maybe until the next garbage collection.

No, the connection is closed when the request ends. That is when the Context will close. With connection pooling the connections aren't actually "closed" but just returned to the pool. As long as you return all your connections (close) to the pool you should be ok.

BOb

But, somewhere you have to create the DataAccessAdapter, your code is not clear in where or how you are doing that?

It would be created in the Retrieve method. The connection would be opened and the queries, stored within an array list, would be iterated over and executed. Following this the connection would be closed.

I'm not sure why you want to make the UI programmer go through the extra step of calling retrieve?

Well I'd prefer not to.

I think the main thing I was trying to say is that I wouldn't want to create a full class for each query.

Me niether.

No, the connection is closed when the request ends.

This is the big draw back for me. I'm under the impression that one wants a connection returned to the pool as soon as possible so that another thread can use it. Holding on to it until the end of a request is holding on to it longer than necessary.

How about one uses the static methods that you're suggesting but give the UI programmer a little extra control. So there's a way of registering that a connection should be left open across data requests and then a way of telling the DAL to close it? Like this...

Sandbox.EntityClasses.AuthorsEntity.KeepConnectionOpen = true;

Sandbox.HelperClasses.EntityCollection col = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByState("CA");

Sandbox.HelperClasses.EntityCollection col2 = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByZip("95128");

Sandbox.EntityClasses.AuthorsEntity.CloseConnection();

DataGrid1.DataSource = col;
DataGrid1.DataBind();

DataGrid2.DataSource = col2;
DataGrid2.DataBind();

Although even if this were to be done, that still means that the filter building is being done whilst its open.

Having said that, perhaps its more important to make the DAL simple to program than trying to avoid every single last bit of code from running when the connection's open? As Walaa said,

I think the time it takes to build the predicates and sort expressions is insignficant.

Finally, how do you know that the connection is closed when the context is closed? Is Dispose called on all objects in the context collection at this point?

Cheers, Ian.

So how would you design your DAL if LLBLGen did support batching SQL commands?

Ian wrote:

No, the connection is closed when the request ends.

This is the big draw back for me. I'm under the impression that one wants a connection returned to the pool as soon as possible so that another thread can use it. Holding on to it until the end of a request is holding on to it longer than necessary.

You are taking about milliseconds here. After all, how long does it take your page request to run? I don't think it is a major concern.

Ian wrote:

How about one uses the static methods that you're suggesting but give the UI programmer a little extra control. So there's a way of registering that a connection should be left open across data requests and then a way of telling the DAL to close it? Like this...

Well, I guess you could do that. But if so, you might as well just have the UI instantiate the DataAccessAdapter itself and pass it to the static methods. So change your code above to:

DataAccessAdapter da = new DataAccessAdapter(true); // true = keep open connection

Sandbox.HelperClasses.EntityCollection col = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByState("CA", da);

Sandbox.HelperClasses.EntityCollection col2 = Sandbox.EntityClasses.AuthorsEntity.GetAuthorsByZip("95128", da);

da.CloseConnection();

DataGrid1.DataSource = col;
DataGrid1.DataBind();

DataGrid2.DataSource = col2;
DataGrid2.DataBind();

Ian wrote:

Although even if this were to be done, that still means that the filter building is being done whilst its open.

Yep, but your talking milliseconds again. Probably less time it would take to close the connection and open it again for the second query. Of course, you could rearange things so that all the filters are built first, put them in a collection, then run them all, but...

Ian wrote:

Having said that, perhaps its more important to make the DAL simple to program than trying to avoid every single last bit of code from running when the connection's open? As

Yea... it is definately a trade off. I think you should opt for the cleanest API you can. I think with connection polling the extra few milliseconds are not going to be significant. There are probably more bank for your buck performance optimization issues than this one.

Ian wrote:

Finally, how do you know that the connection is closed when the context is closed? Is Dispose called on all objects in the context collection at this point?

I can't speak for Fran's code, but the DatabaseAdapterBase does implement the dispose method. My assumption is the connection is closed here. Of course, you could instument the system to ensure that it does close. Even in the code above, where the CloseConneciton() is called. Calling that is probably only gonna close the connection milliseconds before the page is done and it would go out of scope and dispose anyway. Perhaps Fran could verify this.

BOb

What about my question as to how you'd design a DAL if LLBLGen supported batching?

Ian wrote:

What about my question as to how you'd design a DAL if LLBLGen supported batching?

Off the top of my head. I think a Helper class that contained a collection of requests that were then sent off to be retrieved. I would probalby have the static Query methods return a predicate expressions. So, the UI code could be something like:

EntityHelper.QueryRequests.Add( "Authors", Authors.GetByState(state) ); EntityHelper.QueryRequests.Add( "States", States.GetAll() ); EntityHelper.Execute(); EntityHelper.CloseConnection();

MyControl.DataSource = EntityHelper.QueryResults["Authors"]; MyControl.DataBind();

MyOtherControl.DataSource = EntityHelper.QueryResults["States"]; MyOtherControl.DataBind();

BOb

That looks vaguely familiar.

So if LLBLGen _did _support batching and it actually made a significant difference to an app's performance (maybe the fact that its not supported is because it doesn't) would you ask your UI programmers to use the more complicated syntax?

Cheers, I.

Ian wrote:

That looks vaguely familiar.

So if LLBLGen _did _support batching and it actually made a significant difference to an app's performance (maybe the fact that its not supported is because it doesn't) would you ask your UI programmers to use the more complicated syntax?

Cheers, I.

Yea. I don't know a way around it. At the "least" the UI code will have to know to do the following things:

1. Request the data/collections in a group.
2. Call some method to "do it". No other way the BL can know when to do the fetch.
3. Do all data binding after step two.

If the pattern above isn't followed there will be problems.

So, the real answer to your question is: I would have to decide if requiring the pattern was worth the performance benefit that "might" be gained.

BOb