How do I Query an existing SQL Database in Orchard?

Topics: Writing modules
Oct 11, 2011 at 7:10 PM

I have a project where I need to build pages in Orchard. These pages need to have fields that the admin can fill out i.e. productID, CatergoryID, columns ect. and then publish the page. The productID, catergoryID fields will pull data from an existing database, and return the results on the published page. Do I need to write some custom module for this? There are not many tutorials on how to build custom modules in Orchard that query databases. Can I just create an MVC app that does the querying and then import it into Orchard CMS? Or does Orchard have some built in functionality to query other databases other than the database built by Orchard?


Any help is appreciated.



Oct 11, 2011 at 7:15 PM

You can perfectly well query other databases. Orchard is just ASP.NET MVC, so you can do this the same way you would in MVC. The main difference is that you have to opt out of Orchard's ambient transaction by surrounding your data access with a using(new TransactionScope(TransactionScopeOption.Suppress))

Oct 11, 2011 at 7:48 PM

Thanks for getting back.

Can I accomplish this by creating a new content type with all the required fields i.e. productID, CatergoryID, and get into the code behind for that newly created content type. And use Linq to Sql to pull data related to those fields? If there are any specific documentation or videos on how to write Sql queries inside Orchard, I would appreciate it.

I'm not sure I understand: "Orchard's ambient transaction by surrounding your data access with a using(new TransactionScope(TransactionScopeOption.Suppress))" Do I put this in the code behind of the content type I am using?





Oct 11, 2011 at 7:51 PM

You could do that yes but no we don't have documentation addressing that specific scenario.

I'm a little nervous when I hear you talk about code behind. This is ASP.NET MVC, not webforms, so you would do all that stuff in drivers, controllers, that sort of thing, not in code behind because there is no such thing.

Oct 11, 2011 at 7:57 PM

I understand it's MVC vs Webforms. When I say code behind I mean, controllers, drivers, models ect. I have created a custom content type. It's called Landing Page. I have opened my Orchard solution in VS 2010. I cannont seem to locate the new content type or it's controllers, models, ect inside any of the folders in the solution. I would like to locate the this content type in order to write my custom sql code. Where do these files reside?



Oct 11, 2011 at 8:14 PM

The types that you create from the admin do not generate code. If you want to create a content type through code, you'll have to do exactly that, without using tha admin.

Oct 11, 2011 at 8:59 PM

Any Documentation on how to create a custom content type through code? Orchard Project doesn't seems to have much in the way of tutorials. I watched the Fundamentals video on Pluralsight. Not much information, really.

Oct 11, 2011 at 9:09 PM

Oct 11, 2011 at 9:33 PM

Thanks. I will follow this walk through. But just for reference, where do I place this line of code you spoke about?:


using(new TransactionScope(TransactionScopeOption.Suppress))"

Oct 11, 2011 at 9:39 PM

Around your custom data access code.

Oct 12, 2011 at 1:59 PM

Thanks for your help. It's becoming a lot more clear to me now. Another question about MVC/Orchard. Which file is my custom data access code typically written in? (Best Practice) Is it in my Models class? Migrations class? or perhaps a Controller class?



Oct 12, 2011 at 3:25 PM

Also after creating the  Products.cs inside the Models folder and adding the correct code, In the walkthrough I am instructed to to the following so it will be added to the project:


////  In order for the application to pick up the new file, you need to add it to the module's project file. 
Open the SimpleCommerce.csproj file and look for "assemblyinfo.cs". After that line (What Line??), add the following:
<Compile Include="Models\Product.cs" />   /////////

If I understand these instructions it is telling me to add this line of code to the AssemblyInfo.cs class.
 Yet the line of code appears it should be written in some kind of config. file. 
I go ahead and add this line of code to AssemblyInfo.cs and it just gives me errors.


Oct 12, 2011 at 6:57 PM

Your custom data access code should be in a service class that has this unique responsibility. Also make an interface for it, and inject that dependency where needed. Model classes should be relatively dumb data structures that do not handle data access but only include logic and data that makes sense at the model level. The controller should orchestrate things, getting instructions through the route and request, getting relevant data for the current request and hand that over to the view. You might want to get a book on MVC to get a better explanation than what I can write here in two lines.

The instructions are telling you to add this line in the *csproj* file, *after* the line that has assemblyinfo. Definitely not *in* the assemblyinfo file.

Oct 12, 2011 at 7:06 PM

Thanks for your help. I resolved the assemblyinfo.cs issue.

So basically I will have to write my custom data access code in a service class? Like a WCF service class? Or Is it an MVC specific service class. I have books on both MVC and WCF. Which one do you think would address this issue the best?



Oct 12, 2011 at 8:36 PM

Well, it's not that you have to, it's that it's good practice. This would be just a class that has this single responsibility. I don't have a specific book to recommend for that specific issue. It's just that you seemed to be confused about some of the basic concepts so I wanted to recommend some reading so that those best practices come more naturally. I heard good things about Brad Wilson's latest MVC book, and knowing the guy, I'm pretty sure it's a solid read.

Oct 13, 2011 at 2:22 PM

I just noticed a new version of Orchard was released on Oct. 3rd. V.1.3.9 Where can I find what version I am currently running? I don't want to go through the update process if I don't have to. Is the version number in a config file somewhere?



Oct 13, 2011 at 4:19 PM

I'm following the tutorial on building a module in order to create a custom content type. In the Migration.cs file I am suppose to add a method called 

public int UpdateFrom1
It takes the argument ProductPart. But my module has multiple parts i.e. ProductPart, SeminarPart, WebinarPart.
 When I add these as arguments in the update method I get an exception saying no overlaoded method exist. 
How can I pass multiple parts in the UpdatFrom1 method to make sure all parts get Updated.

Similarly when "Putting it all Together into a Content Type" I use a method: 
ContentDefinitionManager.AlterTypeDefinition("Product", cfg => cfg

How can I make this method accept multiple parts as arguments so I can make sure they all get updated?


Oct 13, 2011 at 7:32 PM

The version number can be seen on the bottom right of the dashboard page (don't worry if only the first two numbers correspond, that is normal).

You do not pass parameters into a migration method. You don't need parameters. Notice how the existing migration methods have none and still upgrade more than one thing? Well, you can just do that same thing.

Oct 17, 2011 at 3:02 PM

Hi Bertrand


I have successfully created a module with 3 different parts. ProductPart, ACWebinarsPart, and SeminarsPart. Everything installed correctly and when I try to create a new content Type. I am only offered the ProductsPart to add to my new content type. The other two ( ACWebinarsPart, and SeminarsPart) are not on the list of adding parts. Did I go about this wrong? Does Each part need to be it's own module? Or can I have one module with 3 different parts to choose from when adding a new content type. I want to give the user the most control over adding these types.

Oct 17, 2011 at 5:31 PM

You can have as many parts as you want per module or per feature. Make sure you made them attachable, and that everything they need is marked with the correct OrchardFeature attributes.

Oct 17, 2011 at 7:20 PM

Hi Bertrand,


At the beginning of this thread I asked how I could query an external database. I have changed the database structure to include the tables from the external database and they now exist in the Orchard database. These tables are already populated with data.

I have Product tables with ID columns. I want to query and retrieve data with standard SQL queries or Linq to Sql queries.

So when the Admin enters a Product ID on the CMS Admin side, the page gets published and collects all the necessary data from the tables and publishes the content to the page.

My question is,  what would be the "Orchard Way" of querying these tables that already have data and are inside the Orchard DB? Would I use Linq to sql inside a particular driver? Would I create a controller that inherits from Product Model or a using statment for the Product Model?


When I have a fresh install of Orchard it is very easy to fill up the database with data. But I'm not sure how to query existing data.


Any thoughts




Oct 17, 2011 at 7:29 PM

The easiest and most "orchardy" way of doing it now that you have the data in the Orchard DB would be to have record classes and repositories. This would be almost identical to what you do wtih a content part, except without the part itself.

Oct 17, 2011 at 7:38 PM

Thank you so much for your help.

So your saying I would create record classes inside my Models folder? (i.e. ProductsRecord.cs) And each record class would represent a table in my DB? And inside the record classes is where I would write my linq to sql queries? I'm not sure what purpose the repositories serve. Is there a specific peice of documentation you can direct me to so I can accomplish what I am trying to do? I have read a lot of documentation and have found nothing on writing queries against the Orchard DB. Only storing data in the Orchard DB.

I'll take another Look at how to create a content part again in the meantime.


Thanks so much



Oct 17, 2011 at 7:45 PM

I would not write queries in the record class, no, I would do that from a service class of sorts that would have an injected dependency on IRepository<T> where T is your record type. And it would not be linq to sql, it would use nHibernate through IRepository.

Oct 17, 2011 at 8:22 PM

man, i'm just lost. I'm not familiar with nHibernate. I'm used to writing old fashioned sql queries. or linq to sql queries. I wish there was some documentation on how to query my database with nHibernate and IRepositories....

Oct 17, 2011 at 8:24 PM

I don't have any unfortunately. I'm writing a module currently that does something similar, so if you can wait a few days I'll have code for you.

Oct 17, 2011 at 8:34 PM

That would be amazing. All I need to test my module is to select 1 ID from 1 table in the database. after i figure that out i can replicate the code to make all kinds of queries. I'll check out nHibernate website for any more clues.


Thanks again



Oct 18, 2011 at 4:29 PM

Would you mind taking a minute and looking at this Code? To see if I am on the right track?



namespace TopicCodes.Models
    public class TopicCodeModel
        public string TopicCode { get; set; }



namespace TopicCodes.Models
    public class TopicCodeObjectRecord : ContentPartRecord
        public virtual string TopicCode { get; set; }

        public class TopicCodeObjectPart : ContentPart
        public string TopicCode
            get { return Record.TopicCode; }
            set { Record.TopicCode = value; }


TopicCodeRetrievalService.cs (in a Contracts Folder in the Module)

namespace TopicCodes.Contracts.Services
    public interface ITopicCodeRetrievalService
        : IDependency
        List GetTopicCodeFor(TopicCodeObjectPart part);



namespace TopicCodes.Handlers
    public class TopicCodeObjectRecordHandler : ContentHandler
        public TopicCodeObjectRecordHandler(IRepository repository)

TopicCodeRetrievalService.cs (inside Services Folder in Module)


namespace TopicCodes.Services
    public class TopicCodeRetrievalService
        : ITopicCodeRetrievalService

        protected List RetrieveTopicCodeFor(TopicCodeObjectPart part)

            if (!string.IsNullOrEmpty(part.TopicCode))
                // Fetch data

                var topicCode = from //Database?
                             select new TopicCodeModel()
                                    ///Query Statements??
                topicCodeList = topicCode.Take(part.Count).ToList();

            // Return
            return topicCodeList;


I have based this on a module which Queries Twitter API. But I am not Querying outside the Database. I am Querying inside the Orchard database, the Topics_Table


Thanks for your help



Oct 18, 2011 at 5:37 PM
Edited Oct 18, 2011 at 5:38 PM

I suppose the question really is on the retrieval service, right? Your best bet here is probably to get a dependency on IContentManager or IRepository<TopicCodeObjectRecord> and query from that. Or, if that is still too high level for what you need to do, get an ISessionLocator and call For on it, this will get you access to the nHibernate session, from which you can do arbitrary queries.

Oct 18, 2011 at 7:50 PM

I will look into that. Thanks.


On another subject. I have been building and rebuilding Modules following the walkthroughs on the Orchard Project site. I keep running into an error everytime I write a module using the code verbatim from the walkthrough. It happens in Migrations.cs file.

I have a using staement at the top

using Products.Models;

Everytime I compile the module or run the website, i get an exception saying namespace Products.Models cannont be found, are you missing this or that? Yet every walkthrough on building modules has this using statement in it's code.


Oct 18, 2011 at 8:00 PM

Fixed. I am building this module in WebMatrix. For some reason the Model.cs file does not get included into Visual Studio when opening the Module in Visual Studio. I add the file manually and then build the Module in VS. The error goes away. I dunno.



Oct 20, 2011 at 9:56 PM

Is it still possible to embedd aspx pages into Orchard in version 1.3? I got the Idea from this thread you had on Stackoverflow:


Does this still work? It would be nice to be able to incorporate server controls and user controls into Orchard.

Oct 20, 2011 at 10:11 PM

It should still work, but I would very strongly advise against it. Especially to incorporate Webforms server controls. That would be a horrendous thing to do.

Oct 20, 2011 at 10:20 PM

Alright. Thanks for the tip. Any luck on that Module you were building to query tables in the Orchard database? I'm dying to see some code on how this works in MVC/Orchard.


" I'm writing a module currently that does something similar, so if you can wait a few days I'll have code for you." --bertrandleroy

Oct 20, 2011 at 10:25 PM

Still working on it.

Oct 20, 2011 at 11:29 PM

You're the man!

Oct 21, 2011 at 4:01 PM

Is there no DATETIME datatype for Orchard ? Im setting a field in a module with {get; set;} and intellisense is not giving me datetime as a datatype?

Oct 21, 2011 at 4:05 PM

Errrr, this has nothing to do with Orchard. Do you have a using System directive on top of your file?

Oct 21, 2011 at 5:17 PM
That was it. I need to watch my cutting and pasting.



On Fri, Oct 21, 2011 at 11:05 AM, bertrandleroy <> wrote:

From: bertrandleroy

Errrr, this has nothing to do with Orchard. Do you have a using System directive on top of your file?

Read the full discussion online.

To add a post to this discussion, reply to this email (

To start a new discussion for this project, email

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at

Oct 21, 2011 at 9:38 PM

I want to alter a table in the Orchard database. The table was created for a module I made. I set the column to data type datetime. But now I want to change it to a string datatype. I tried to change it manually in SQL Studio Management but was not able to.

Anyway I decided to alter the data type in this column using the Migrations file and the Updatefrom method. Can you take a look at this and tell me if this is the correct code to use:


 public int UpdateFrom3() {
            SchemaBuilder.AlterTable("SeminarTopicWidget", table => table
                .AlterColumn("CreatedDtTm" System.Action<Orchard.Data.Migration.Schema.AlterColumnCommand> column =  string ));
            return 4;




Thanks so much. I appreciate your help.

Oct 21, 2011 at 10:06 PM

This doesn't look like it would even compile.

Oct 21, 2011 at 11:26 PM

Well this is not the complete migration.cs code. I just posted the new Update method. Am i on the right track?? I'm just following the intellisenes from Webmatrix to see how I can alter the column datatype. What would be the correct code?

Oct 21, 2011 at 11:28 PM

Right, but the code you posted won't compile anywhere, I'm pretty sure. It just doesn't make any sense in any context. Did you try to look for similar code in existing modules?

Oct 21, 2011 at 11:36 PM

My module, which queries custom non-content data stored within the Orchard database, can be found here:

It does queries in plain SQL, HQL and Linq to nHib. Look for the service class in particular (which is a little too big for my liking, I have to say, but this is work in progress).

Oct 22, 2011 at 1:34 AM

You are a God.

Oct 22, 2011 at 1:39 AM

I'm going into the office tomorrow. I can't wait to try this out. Thank you so much. BTW, Orchard is fantastic so far. It's just what Microsoft needed to give to the world. And you and your team are doing amazing things. Thank you.

Oct 22, 2011 at 1:40 AM

Ironic for an atheist ;) Glad I could help.

Oct 22, 2011 at 1:56 AM

Ha! I am too. But you create the Orchard Universe. And for that, I thank you.

Oct 30, 2011 at 8:51 PM

I still have to implement the code you sent me into my module. So I can query my custom tables. I got sidetracked by another client but I will be working on my module tomorrow.

On another subject, Whats the best way to import the Orchard class library into Visual Studio as a reference?  Everytime I try to build a module in VS I get all kinds of red squiggly errors

because the namespaces and classes are not recognized. I can't tell which errors are real and which ones are just there because I am not referencing the Orchard namespaces.

I hope this makes sense.

Oct 30, 2011 at 8:58 PM

I just added a reference to the all the DLL's from the bin folder in my Orchard install. To my module project in VS. That seemed to do the trick.

Oct 31, 2011 at 4:42 PM

Is Orchard using the Entity FrameWork by any chance? I am following an MVC 3 tutorial on how to query a db in MVC. But they imply they are using Entity Framework. Or can I implement Entity Framework specifically for my Module. I think it 

might make my limited knowledge of nHibernate and services easier to query my db tables by using this tutorial I got from Pluarlsight.





Oct 31, 2011 at 4:50 PM

Using NuGet, I installed The EntityFramework DLL into my Orchard bin, to write custom data access code in my Orchard module. Im not sure if this will conflict with nHibernate or what. But I'm going to follow the tutorial and see what happens. 

Oct 31, 2011 at 8:19 PM

If that's new code, I would really steer clear of that. You're going to have two databases, you won't be able to make your work compatible with multi-tenancy, etc. The only reasonable reason to use a different data stack would be to integrate legacy data, but that doesn't seem to be the case here.

Oct 31, 2011 at 8:32 PM

Ok. Thanks for all your help. I apologize for this constant back and forth. I will try to find my way through the code you sent me. You are correct though. There is a lot of stuff going on in that Localization Service class. And I'm just trying to figure out what I need and what I don't need. 

I definitely see the SQL query section of code that will be usefull. I just need to figure out how to customize the Service for my Module.