StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Share connection

Share connection

Chan — Wed, 11 Feb 2009 08:55:51 GMT

Hi,
Is it possible to share connection for all / certain BOs to connect to DB?

Thank you

RE: Share connection

Dustin Taylor — Wed, 11 Feb 2009 08:55:51 GMT

You changed it by executing a SQL command, correct? You have to actually create a sql command object and execute it for that to take effect, like so:

[codesnippet]Dim cmd As New SqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED")
cmd.ExecuteNonQuery()[/codesnippet]

RE: Share connection

Chan — Tue, 10 Feb 2009 19:52:28 GMT

Hi,
I have tried all IsolateLevel enum value while BusinessLayer.TransactionBegin, but no one help.
Any ideas?

Thank you

RE: Share connection

Dustin Taylor — Tue, 10 Feb 2009 09:02:48 GMT

Sorry, had a little typo on that reply. For the first option (forcing SQL to run the query even when in the middle of a transaction), you should run the following command after the query to return SQL to it's default settings:

[codesnippet]SET TRANSACTION ISOLATION LEVEL READ COMMITTED[/codesnippet]

RE: Share connection

Dustin Taylor — Tue, 10 Feb 2009 09:00:21 GMT

Ah, now I get it :).

By default SQL server will not let you read an uncommited transaction from a table. So when you are in the middle of a transaction, any queries to that table will block and wait for the transaction to commit before going through. This is SQL server functionality (not strataframe), and is working as designed, but there are two ways around it depending on your needs:

1) If you want to go ahead and run the query which will include the uncompleted transaction, you can set the isolation level of SQL to READ UNCOMMITED before running the query, and the set it back to READ COMMITED after the query is run. This will run your query without blocking, but [i][b]will[/b][/i] include the incomplete transaction records.

So execute the following SQL command before running your query:

[codesnippet]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED[/codesnippet]

And when finished, execute this one:

[codesnippet]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED[/codesnippet]

2) The second option is to leave the isolation level alone, but set the timeout on the query command to a longer value so that it will sit and wait for the transaction to complete before executing. This will include the [i][b]completed[/b][/i] transaction records, but could make the query appear to "hang" from an end user's perspective while it is waiting for the transaction to be completed (you could, of course, show a marquee thermometer or some such as part of the UI to let the user know it is processing.)

For this, you would just set the timeout parameter of your SQL command to a large value before executing it:

[codesnippet]'-- Create the command
Dim cmd As SqlCommand = New SqlCommand("dbo.YourStoredProcedureOrQuery")
'-- Increase the command timeout to 5 minutes
cmd.CommandTimeout = 300[/codesnippet]

RE: Share connection

Chan — Mon, 09 Feb 2009 21:32:32 GMT

Hi.
thank you for reply,
what I meant is connection object, the reason I ask this is because I have problem when query datab within transaction if that is any record saved to same table.
for example, transaction began, bo a save data to table a, bo b try to query data of table a via new instance of bo a, error timeout occurred.

any advise?
thank you

RE: Share connection

Dustin Taylor — Mon, 09 Feb 2009 11:43:17 GMT

I'm not sure if I understand your question completely, because your BOs shoud already be sharing the connection information.

When you set up your project you will specify a default DbDataSourceItem in the SetDataSources() method of AppMain.vb (program.cs in C#). The DbDataSourceItem is "default" when it has an empty DataSourceKey, which means that any business object will use that data source connection unless a different DataSourceKey if specified.

You can set up more than one database connection by adding a second DbDataSourceItem in SetDataSources(), you just have to give a unique string as the DataSourceKey for the other DbDataSourceItems.

[codesnippet]ConnectionManager.AddRequiredDataSourceItem("YourDataSourceKey", "SQL Connection", _
DataSourceTypeOptions.SqlServer, "MyDatabase", "This connection is used by MyApplication.")[/codesnippet]

Is that what were asking, or did I miss the point entirely? :)

RE: Share connection

Greg McGuffey — Mon, 09 Feb 2009 11:31:56 GMT

I'm assuming your talking about connection pooling and not just sharing a data source. ADO does this automatically. The connections do time out at around 5 min (this varies apparently quite a bit) if unused. They also close when you close the application. This means that calling Close() on an open method doesn't actually close the connection. The connection remains open within the connection pool. This is turned on by default, so you'd have to figure out how to turn it off if you didn't want it. Try googling ADO connection pool for the details.