how to access sql database in new module

I am making progress. Figured how to make simple Hello World like module. But my second test was to try to open a sql database and then display a list of records from the table. This works fine in non-Orchard, but in Orchard, even on my development machine
I cannot access the data. Instead I get an exception in the Repository.cs method in Orchard.Data.Repository<T>

In my module I added an ADO.Net Entity Data Model and hooked it up to a single database table with several columns. In a regular MVC 3 project I just do something like this to read the data:

I create an interface named IArtistRepository.cs and a class to implement it named ArtistRespository.cs. Then I put logic like that shown in the controller code snippet. In the test MVC 3 app it works fine, that is I am able get a collection from the data
base in the controller's Index method.

If I use the same code in my Orchard Module then I get an exception when I try to invoke the GetAllArtists() method in the Contoller's Index method. From debugging I can see that I get an exception in the Repository.cs method in Orchard.Data.Repository<T>
and a stack trace that looks like the one shown below.

I decided to search the forum and although I do not see anything specific, I think I'm reading between the lines that you cannot access a sql database in a created module without somehow going through some Orchard layer - maybe it is Orchard.Data? I saw
an article
http://docs.orchardproject.net/Documentation/Writing-a-content-part that looks like it may be a place for me to start, but I didn't immediately see anything about being able to work with a database on my module. I like using the MS Entity Framework
and hope I can continue using it. So far I haven't found an example of how to do that. Thanks. Bob

Sorry to be confusing. It is difficult to figure out what information would be more useful to give. However, I have been playing with this some more and figured out that I CAN access the database in my module in a live webset, but not in the development
website.

I believe the problem stems from the fact that I'm using the same database when debugging that I use on the live webserver. I have modified the controller code to be as follows:

When I run the code on the live webserver it works and my page2 shows me that there are 127 records in the database which is correct.

When I run the code in the debugger I can put a breakpoint at the EntityException and this is the stack trace:

﻿ at System.Data.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
at System.Data.Objects.ObjectContext.EnsureConnection()
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ArtistInfoDB.Controllers.HomeController.Index()

The message and source is:

message: ﻿The underlying provider failed on EnlistTransaction.

source: System.Data.Entity

If I dig into the inner exception, I get this stack, message and source:

at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(Byte[] propagationToken)
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.EnlistTransaction(Transaction transaction)
at System.Data.SqlClient.SqlConnection.EnlistTransaction(Transaction transaction)
at System.Data.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)

message: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

Source: System.Transactions

I'll try to make the above change to either my server or the development machine depending on what I find when I search for this error.

Oh, you asked what the hac_CmsEntities was; it is the ADO.Net Entity Framework model I created from the database.

I have another question once I get past this, but I will create a new post to ask it in detail.

Thanks,

Bob

I have decided I'll find another way to do this on the development machine for now since it is more complicated than I want to get into to set up the MSDTC so I'll have to copy my database over onto the development machine. that is inconvenient - especially
when I want to test with the live data. I'll have to figure out how to set up the MSDTC on both machines. Maybe someone know how to do that. I will make a new post for that too.

You need to opt out of the Orchard transaction around your custom data access code.

OK. That sounds great. I'll try to figure out how to do that.

Thanks!

Bob

-- NOTE. I figured it out, but there was no where in this info that documented which namespace includes the TransactionScope. So, for anyone else who might need it, add the System.Transactions to your references then include a

using System.Transactions;

at the top of your program.

My new controller code now looks like this and works in both dev and live:

The convention in Orchard is to inject an IRepository<T> to perform data access thru NHibernate. Why do you need an ADO.NET model?

Hi Randompete,

Sorry, I missed your question earlier. I have to say I'm just trying to learn how to use Orchard mostly in my spare time. I have built a number of MVC 3 sites and have used the microsoft ADO.net Entity Framework. I did not realize that Orchard uses NHibernate;
Orchard is touted as MVC 3 like so I figured it would just use the Microsoft EF for database code. I guess I didn't read enough between the lines.

Today, I create a database table in SQL then I just use Visual Studio to create an Entity Model by dragging in the data base to the form. It generates all of the code I need to access the database. However, I'm trying now to learn the Orchard way.
I don't know how to do that with NHibernate, but in looking at the article referenced here
http://docs.orchardproject.net/Documentation/Creating-a-module-with-a-simple-text-editor I'm thinking maybe that all I need to do is create models for each of my tables where my model will inherit the ContentPartRecord. If I do this then it appears
I can do without the Microsoft EF.

I had to port an existing MVC site into an Orchard module, so I too had to opt out of the ambient transaction that Orchard creates for all web requests.

This is one of the things I have found awkward about Orchard. I'd like to have my site a little more decoupled from the CMS because I have a lot of existing data-driven functionality that I don't need the CMS for. I'd like to have been able to have my site
be the "master" and just incorporate Orchard as a piece of it rather than the other way around.

I also encountered a similar transaction problem where one of my custom entities had some lazy loaded properties that were accessed inside the Razor views. The data was lazy loaded using ADO.NET stored procedure calls. I had to edit the web.config to load
System.Transactions assembly for use from razor views and then wrap the razor view inside a @using(new TransactionScope(TransactionScopeOption.Suppress)) { ... } block.

@randompete: I think there might be a lot of users like me who have use cases that don't fit inside the pattern of using IRepository<T>. If I had to convert everything to fit inside Orchard's view of how data should be accessed I would not have ended
up using Orchard for this project as it would have taken way to long to retrofit existing code to work that way.

Right, mainly just wanted to post my experience with the lazy-loaded properties from my "legacy" code needing special steps to opt-out of the ambient transaction from Razor views. The case for the transaction opt-out from C# code (mainly Controllers) has
been discussed before but I hadn't seen anyone mention the analog for the problem within Razor views, so I wanted to document it here in case someone else has the same issue.