how to access sql database in new module

Topics: Writing modules
Dec 20, 2011 at 10:32 PM

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

Stack Trace Here:

 at System.Transactions.Transaction.EnlistVolatile(IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions)
   at NHibernate.Transaction.AdoNetWithDistrubtedTransactionFactory.EnlistInDistributedTransactionIfNeeded(ISessionImplementor session)
   at NHibernate.Impl.AbstractSessionImpl.EnlistInAmbientTransactionIfNeeded()
   at NHibernate.Impl.AbstractSessionImpl.CheckAndUpdateSessionStatus()
   at NHibernate.Impl.SessionImpl.FireLoad(LoadEvent event, LoadType loadType)
   at NHibernate.Impl.SessionImpl.Get(String entityName, Object id)
   at NHibernate.Impl.SessionImpl.Get(Type entityClass, Object id)
   at NHibernate.Impl.SessionImpl.Get[T](Object id)
   at Orchard.Data.Repository`1.Get(Int32 id) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\Data\Repository.cs:line 87
   at Orchard.Data.Repository`1.Orchard.Data.IRepository.Get(Int32 id) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\Data\Repository.cs:line 56
   at Orchard.ContentManagement.DefaultContentManager.Get(Int32 id, VersionOptions options) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\ContentManagement\DefaultContentManager.cs:line 130
   at Orchard.ContentManagement.DefaultContentManager.Get(Int32 id) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\ContentManagement\DefaultContentManager.cs:line 110
   at Orchard.ContentManagement.ContentGetExtensions.Get[T](IContentManager manager, Int32 id) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\ContentManagement\ContentExtensions.cs:line 146
   at Orchard.Core.Settings.Services.SiteService.GetSiteSettings() in H:\BT.CMS\BT.HacCms.12182011\src\Orchard.Web\Core\Settings\Services\SiteService.cs:line 45
   at Orchard.UI.PageTitle.PageTitleBuilder.GenerateTitle() in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\UI\PageTitle\PageTitleBuilder.cs:line 33
   at Orchard.Mvc.Html.LayoutExtensions.Title(HtmlHelper html, String[] titleParts) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\Mvc\Html\LayoutExtensions.cs:line 39
   at ASP._Page_Core_Shapes_Views_Document_cshtml.Execute() in h:\BT.CMS\BT.HacCms.12182011\src\Orchard.Web\Core\Shapes\Views\Document.cshtml:line 14
   at System.Web.WebPages.WebPageBase.ExecutePageHierarchy()
   at System.Web.Mvc.WebViewPage.ExecutePageHierarchy()
   at System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage)
   at System.Web.Mvc.RazorView.RenderView(ViewContext viewContext, TextWriter writer, Object instance)
   at System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext viewContext, TextWriter writer)
   at System.Web.Mvc.HtmlHelper.RenderPartialInternal(String partialViewName, ViewDataDictionary viewData, Object model, TextWriter writer, ViewEngineCollection viewEngineCollection)
   at System.Web.Mvc.Html.PartialExtensions.Partial(HtmlHelper htmlHelper, String partialViewName, Object model, ViewDataDictionary viewData)
   at System.Web.Mvc.Html.PartialExtensions.Partial(HtmlHelper htmlHelper, String partialViewName, Object model)
   at Orchard.DisplayManagement.Descriptors.ShapeTemplateStrategy.ShapeTemplateBindingStrategy.Render(ShapeDescriptor shapeDescriptor, DisplayContext displayContext, HarvestShapeInfo harvestShapeInfo, HarvestShapeHit harvestShapeHit) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Descriptors\ShapeTemplateStrategy\ShapeTemplateBindingStrategy.cs:line 133
   at Orchard.DisplayManagement.Descriptors.ShapeTemplateStrategy.ShapeTemplateBindingStrategy.<>c__DisplayClass26.<>c__DisplayClass28.b__15(DisplayContext displayContext) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Descriptors\ShapeTemplateStrategy\ShapeTemplateBindingStrategy.cs:line 117
   at Orchard.DisplayManagement.Descriptors.ShapeAlterationBuilder.<>c__DisplayClass3.<>c__DisplayClass5.b__2(DisplayContext displayContext) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Descriptors\ShapeAlterationBuilder.cs:line 55
   at Orchard.DisplayManagement.Implementation.DefaultDisplayManager.Process(ShapeBinding shapeBinding, IShape shape, DisplayContext context) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Implementation\DefaultDisplayManager.cs:line 176
   at Orchard.DisplayManagement.Implementation.DefaultDisplayManager.Execute(DisplayContext context) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Implementation\DefaultDisplayManager.cs:line 95
   at Orchard.DisplayManagement.Implementation.DisplayHelper.ShapeExecute(Object shape) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Implementation\DisplayHelper.cs:line 71
   at Orchard.DisplayManagement.Implementation.DisplayHelper.Invoke(String name, INamedEnumerable`1 parameters) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Implementation\DisplayHelper.cs:line 38
   at Orchard.DisplayManagement.Implementation.DisplayHelperFactory.DisplayHelperBehavior.InvokeMember(Func`1 proceed, Object target, String name, INamedEnumerable`1 args) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\DisplayManagement\Implementation\DisplayHelperFactory.cs:line 27
   at ClaySharp.ClayBehaviorCollection.<>c__DisplayClass14.<>c__DisplayClass16.b__13() in C:\Users\sebros\My Projects\Clay\src\ClaySharp\ClayBehaviorCollection.cs:line 29
   at ClaySharp.ClayBehaviorCollection.Execute(Func`1 proceed, Func`3 linker) in C:\Users\sebros\My Projects\Clay\src\ClaySharp\ClayBehaviorCollection.cs:line 13
   at ClaySharp.ClayBehaviorCollection.InvokeMember(Func`1 proceed, Object self, String name, INamedEnumerable`1 args) in C:\Users\sebros\My Projects\Clay\src\ClaySharp\ClayBehaviorCollection.cs:line 29
   at Orchard.Mvc.ViewEngines.ThemeAwareness.LayoutAwareViewEngine.<>c__DisplayClass7.b__5(ViewContext viewContext, TextWriter writer, IViewDataContainer viewDataContainer) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\Mvc\ViewEngines\ThemeAwareness\LayoutAwareViewEngine.cs:line 63
   at Orchard.Mvc.ViewEngines.ThemeAwareness.LayoutAwareViewEngine.LayoutView.Render(ViewContext viewContext, TextWriter writer) in H:\BT.CMS\BT.HacCms.12182011\src\Orchard\Mvc\ViewEngines\ThemeAwareness\LayoutAwareViewEngine.cs:line 90
   at System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext controllerContext, ActionResult actionResult)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass1c.b__19()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter filter, ResultExecutingContext preContext, Func`1 continuation)
CODE Snippets Here:
Controller Code for Index Method:
        IArtistRepository repo;

        public ArtistDataController() : this(new ArtistRepository())
        {
        }

        public ArtistDataController(IArtistRepository repository)
        {
            repo = repository;
        }


        public ActionResult Index()
        {
            var result = repo.GetAllArtists().ToList();

            return View(result);
        }

Interface:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BT.MVC.HAC.Models
{
    public interface IArtistRepository
    {
        IQueryable<Artist> GetAllArtists();
        void DeleteAnArtist(Artist artist);
        void AddArtist(Artist artist);
        Artist GetArtistById(int id);
        IQueryable<Artist> GetArtistsFromZip(string zip);
    }
}

Implementation:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace BT.MVC.HAC.Models
{
    public class ArtistRepository : IArtistRepository
    {
        Hac_CmsEntities db;

        public ArtistRepository()
        {
            db = new Hac_CmsEntities();
        }

        #region IArtistRepository Members

        public IQueryable<Artist> GetAllArtists()
        {
            var result = from p in db.Artists
                         select p;
            return result;

        }

        public void DeleteAnArtist(Artist artist)
        {
            db.DeleteObject(artist);
        }

        public void AddArtist(Artist artist)
        {
            db.Artists.AddObject(artist);
        }

        public Artist GetArtistById(int id)
        {
            var result = from p in GetAllArtists()
                         where p.ID == id
                         select p;
            return result.FirstOrDefault();
        }

        public IQueryable<Artist> GetArtistsFromZip(string zip)
        {
            var result = from p in GetAllArtists()
                         where p.Zip == zip
                         select p;
            return result;
        }

        public void Save()
        {
            db.SaveChanges();
        }

        #endregion
    }
}

Coordinator
Dec 20, 2011 at 10:46 PM

That stack trace seems entirely unrelated to the code you posted. Also, what's hac_cmsEntities?

Dec 21, 2011 at 2:56 AM

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

Dec 21, 2011 at 3:51 AM
Edited Dec 21, 2011 at 4:57 AM

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:

        public ActionResult Index()
        {
            List<Artist> results = new List<Artist>();
            TestModel myModel = new TestModel();

            try
            {
                var result = repo.GetAllArtists();
                results = result.ToList();
                myModel.Info = null;
                myModel.ControllerName = "HomeController";
                myModel.ActionName = "Index";
                myModel.RecordCount = results.Count;
                myModel.Info = null;
                return View("Page2",myModel);

            }
            catch (System.Data.EntityException entityException)
            {
                return View("Page4");
            }
            catch (Exception ex)
            {
                return View("Page3");
            }
        }

and my TestModel looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Mvc;

namespace ArtistInfoDB.Models
{
    public class TestModel
    {
        public HandleErrorInfo Info { get; set; }

        public string ControllerName { get; set; }

        public string ActionName { get; set; }

        public int RecordCount { get; set; }
    }
}

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.

Bob

Coordinator
Dec 21, 2011 at 4:58 AM

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

Dec 21, 2011 at 10:38 AM
Edited Dec 21, 2011 at 4:43 PM
bertrandleroy wrote:

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:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Orchard.Themes;
using ArtistInfoDB.Models;
using System.Web.Mvc;
using System.Transactions;


namespace ArtistInfoDB.Controllers
{
    [HandleError]
    [Themed]
    public class HomeController : Controller
    {
        IArtistRepository repo;

        public HomeController()
            : this(new ArtistRepository())
        {
        }

        public HomeController(IArtistRepository repository)
        {
            repo = repository;
        }


        public ActionResult Index()
        {
            List<Artist> results = new List<Artist>();
            TestModel myModel = new TestModel();
            using(new TransactionScope(TransactionScopeOption.Suppress))
            {
                try
                {
                    var result = repo.GetAllArtists();
                    results = result.ToList();
                    myModel.Info = null;
                    myModel.ControllerName = "HomeController";
                    myModel.ActionName = "Index";
                    myModel.RecordCount = results.Count;
                    myModel.Info = null;
                    return View("Page2",myModel);

                }
                catch (System.Data.EntityException entityException)
                {
                    return View("Page4");
                }
                catch (Exception ex)
                {
                    return View("Page3");
                }
            }
        }
    }
}

Thanks for all the help!

Bob

 

 

Dec 23, 2011 at 11:28 AM
randompete wrote:

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.

We use Microsoft EF at my day job so that's what I'm more used to.

I'll probably be asking more questions on this...

Thanks,

Bob

Dec 27, 2011 at 9:14 PM
Edited Dec 27, 2011 at 9:16 PM

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. 

Coordinator
Dec 27, 2011 at 10:19 PM

That's the price to pay for not having to think about the transaction the rest of the time (which for the immense majority of users is all of the time).

Dec 27, 2011 at 10:45 PM

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.