ODBC Connection Problem

[orchardbot]

razvan_d7 created: https://orchard.codeplex.com/workitem/17291

Repository.cs crashes on public virtual T Get(int id) { return Session.Get(id); } after opening a OdbcConnection in a module. error message:While preparing SELECT contentite0_.Id as Id430, contentite0_.Data as Data430, contentite0_.ContentType_id as ContentT3_430 FROM Orchard_FrameworkContentItemRecord contentite0 WHERE contentite0_.Id=@p0 an error occurred

inner exception:{"The connection object can not be enlisted in transaction scope."}

[orchardbot]

@bleroy commented:

You may be asking too much. References to external content should be fine but they should be extremely loose references that consist in a simple identifier, not actual relations, and no shared transactions. This may also be that you didn't take over enough of the repository implementation. Not seeing your code, it's hard to tell.

[orchardbot]

razvan_d7 commented:

In the tech specs is been said that one can write modules and integrate them in the CMS like pure MVC apps and take full advantage of MVC and .NET features.Am i missing something?

[orchardbot]

@bleroy commented:

I don't know. Without seeing your code it is really hard to tell.

[orchardbot]

@bleroy commented:

See http://orchard.codeplex.com/Thread/View.aspx?ThreadId=244312 for more context and a simple repro. I think this should work but somehow the transaction gets mixed into that whereas it shouldn't.

[orchardbot]

JuzFun commented:

This is one of the most important stuff that needs to work for anyone who would like to integrate their 'serious' apps into orchard. For obvious reasons the app authors would want to retain the control on the choice of databases. In my case i am pretty familiar with my db schema and been optimizing it for performance, maintainability etc for a while. it would be impractical for me to abandon my db completely and embrace the Record repository model here. I think the nested transaction issues probably got to do with how nHibernate handles the transaction scope.. the way it is enlisting any new open connections to the existing transactions... not sure if there is a way to explicitly tell nHib not to worry about my own db connection (unfortunately am not a nHib pro)

If the impact is marked Low, not sure what kinda priority this issue would get. It is sad that I need to give up on my Orchard integration plans until there is a solution. Back to good old MVC? :(

[orchardbot]

@bleroy commented:

Don't worry, this is just the default: the bug has not been triaged yet (it is still proposed).

[orchardbot]

@bleroy commented:

Duplicate: http://orchard.codeplex.com/workitem/17308

[orchardbot]

@bleroy commented:

See http://orchard.codeplex.com/discussions/244312 (there is a repro there)

[orchardbot]

kamsar commented:

This behavior, and the workaround above, are rather lame when it comes to integrating with existing database-driven data sources. That said, I figured out how to do it pretty neatly with my existing NHibernate data access project:

Implement your own IConnectionProvider (http://www.dondevelopment.com/2011/02/23/building-a-custom-nhibernate-iconnectionprovider/) and override the GetConnection() method to suppress the TransactionScope as loudej outlines above. Then register it in your NHibernate config and you should be good to go. Of course it won't help you if you're using TransactionScope yourself...

My connection provider:

internal class NonAmbientConnectionProvider : DriverConnectionProvider { public override System.Data.IDbConnection GetConnection() { using (new TransactionScope(TransactionScopeOption.Suppress)) { return base.GetConnection(); } } }

[orchardbot]

@bleroy commented:

@kamsar: thanks for the creative workaround. My comment on your comment would be that we chose to optimize the most common scenario (using the Orchard database) at the cost of making it a little more difficult when people want to integrate extenal data. There is also the problem that the unit of work is larger than any piece of user code, so the transaction has got to be handled by the framework. At least that's how I understand it.

[orchardbot]

hediposm commented:

we can make the connection with another bank, in our case it was an excel spreadsheet using the above code,

using (new TransactionScope (TransactionScopeOption.Suppress)) { Connection.Open (); }

and add using System.Transactions; reference and on the respective module project.

[orchardbot]

@loudej closed and commented:

Fortunately there is a very simple workaround for this: suppress the ambient transaction scope at the moment in time when you are opening the connection to your database. This way the connection will open normally without enlisting in the ambient transaction. The following code sample illustrates this.

const string connectionString = @"Data Source=.\sqlexpress;Initial Catalog=tempdb;Integrated Security=True";

var connection = new SqlConnection(connectionString);

using (new TransactionScope(TransactionScopeOption.Suppress)) { connection.Open(); }

// the connection may now be used normally and closed at any time

connection.Close();