I have a custom business rule defined within a BO that uses a commandbase that is also defined within that same BO, much like Rocky's "ExistsCommand" defined in his Project class. Within my Command class, I'm doing a executescalar against the database in the dataportal_execute function. In this function I'm using the standard code to get a new SqlConnection but an exception is always thrown when I open the connection because it says the datareader is already in use!
This makes sense, the command object is being called within my custom business rule and is being called within the context of a Fetch operation which already has an open DataReader...
Is there some workaround for running nested sqlcommand operations within the context of the basic CRUD operations in the class?
Depending on where you are in the process you could:
1. Close the existing datareader.
2. Create a 2nd datareader.
Joe
I thought that is what this code did:
using (SqlConnection cn = new SqlConnection(Database.PTrackerConnection)) // Open a new connection to the db{
cn.Open(); //here is where the exception occurs...
I can't close it, it's being used elsewhere (in this case, the FETCH)...
I'm wondering if this is happening because I'm running this within the context of the collection classes transactional wrapper...
Just a theory...
If you're using Sql Server 2005 you can use the Multiple Recordset feature (MARS). Simply enable it in the connectionstring and your code will work.
Yeah, I can see that now...
I think the key is that there is an active transaction in force, which is blocking me from doing other database actions. I wondered if anyone else had ever encountered this from the context of a business object. While doing CRUD operations, a custom business rule needs to hit the database. If there is an active transaction, how am I supposed to do it?
When I remove the transaction attribute, everything works fine. This is OK when it comes to the fetch operations (the codesmith templates puts a transaction on everything, even the fetches), but I need to keep transaction support on the insert and update.
So, the workaround I need is to be able to do a database lookup that sidesteps the active transaction...
Anyway, I'm fully aware that I might not be posing the question in a meaningful way...
Copyright (c) Marimer LLC