Querying tables in the Orchard Database

Topics: Customizing Orchard
Oct 18, 2011 at 5:52 PM

I have added some custom tables to my Orchard Database. They are already populated with Data. They are tables copied from an existing non Orchard ecommerce website. I need to Query these tables (which exist in my Orchard DB)

and pull data based on ProductID and other Columns in the table. It seems like the most simple thing to do. I have queried tables in ASP.NET Websites for years. I am new to MVC/NHibernate/Orchard.

 

Does anyone have any examples or documentation or tutorials on how to simply query tables in the Orchard Database and then display the data on the front end/published page?

 

Thanks

 

Oceantrain

Coordinator
Oct 18, 2011 at 6:27 PM
Edited Oct 18, 2011 at 6:27 PM

If it's in the Orchard database, you can grab the nHibernate session by injecting a ISessionLocator and then doing using(var session = _sessionLocator.For(typeof(TheMainTypeForTheQueryButItDoesntReallyMatter))) { ... session.CreateSQLQuery(...) or session.CreateQuery(...) or for r in session.Linq<SomeRecordType> ... }

Oct 18, 2011 at 6:34 PM

Sounds easy enough to me. Now would I put this code in a controller that references my Models? or A Service Class?

Coordinator
Oct 18, 2011 at 6:39 PM

I would definitely put that in a service class: the controller should not be responsible for querying the database.

Oct 19, 2011 at 8:28 PM

Any idea on how to remove the Date/Time stamp that exists on every page I publish? I'm just using the built in Page Content Type.

Oct 19, 2011 at 9:04 PM

Solved. Just Comment out this line in the file Core/Common/Views/Parts.Common.Date.Edit.cshtml:

<fieldset class="createdutc-datetime">   
 @Html.LabelFor(m => DateEditor.CreatedDate, T("Created On"))    
<label class="forpicker" for="@Html.FieldIdFor(m => DateEditor.CreatedDate)">@T("Date")</label>    
@Html.EditorFor(m => DateEditor.CreatedDate)    <label class="forpicker" for="@Html.FieldIdFor(m => DateEditor.CreatedTime)">@T("Time")</label>   
 @Html.EditorFor(m => DateEditor.CreatedTime)</fieldset>

 

Coordinator
Oct 20, 2011 at 12:27 AM

No no no no, there is a much cleaner solution. Go to contents/content types, then edit the page content type, deploy the common part settings and uncheck "Show editor for creation date time".

Oct 20, 2011 at 3:39 PM

Ok. I went back and uncommented the previous lines of code. I followed your instructions. But I got lost at: "deploy the common part settings and uncheck "Show editor for creation date time""

 There is a Drop down arrow next to the Common part. Drop it down and it reveals 2 options. That worked.

Thanks so much!

Oceantrain

Nov 8, 2011 at 4:18 PM

I have created a module called Pryor.Seminars

I created a folder called Models

I gave all my classes in that folder the namespace : Pryor.Seminars.Models

I add a using statement to Migration.cs file : using Pryor.Seminars.Models;

I get a compile error saying the namespace (Models) cannot be found am I missing an assembly reference.

 

There are no spelling errors whatsoever.

 

What am I missing??

Nov 8, 2011 at 4:38 PM

Forgot to add them all to my csproj. file

Nov 8, 2011 at 4:48 PM

What namespace do I need to add for IDependency to work? Compiler error says IDependency cannot be found

Nov 8, 2011 at 4:58 PM

In Visual Studio, press Shift+Alt+F11 for a dropdown that gives you an option to automatically add the using statement.

Did you create your module with code generation or by hand? It's best to use code generation so you have all the right project references...

Nov 8, 2011 at 7:00 PM

I did create the module using codegen. I'm not getting any response from VS using that keyboard shorcut. Do you know off the top of your head which namespace IDependency comes from in Orchard?

Nov 8, 2011 at 7:43 PM

Just open up another module, hover over IDependency, and see where it's from. It'll be Orchard.Core or Orchard.Framework.

Have you tried cleaning and rebuilding the solution?

Nov 9, 2011 at 4:43 PM

Hi Bertrand, 

So, I created my service with a CreateSql query method by following the Orchard.Po code you showed me. I was wondering if I need to implement a controller for this to work with my view. Orchard doesn't seem to use the controller in a lot of modules I've seen. But some do. Is it necessary to use a controller in order to display my query in a view? Or can I bypass that and just wire my query straight in to a search textbox in the View?

 

Thanks.

 

Oceantrain

Nov 9, 2011 at 5:32 PM

I am also getting a runtime error saying it can't find the type Pryor_GeoZip.  

Pryor_GeoZip is the name of the table I am querying. Although you mentioned earlier it doesn't really matter what type it is. What is the correct parameter when calling

_sessionLocator.For(typeof( what goes here??)

 

My method:

    public SearchResultViewModel SearchSeminars(int zipcode)
         {
             var model = new SearchResultViewModel();
            using (var session = _sessionLocator.For(typeof(Pryor_GeoZip)))
            {
              var query = session.CreateSQLQuery(
                @"SELECT TopicCd FROM Pryor_GeoZip WHERE ZIPCode >= " + @zipcode);
                
               
            }
             return model;

         }

Nov 9, 2011 at 6:10 PM
Oceantrain wrote:

Hi Bertrand, 

So, I created my service with a CreateSql query method by following the Orchard.Po code you showed me. I was wondering if I need to implement a controller for this to work with my view. Orchard doesn't seem to use the controller in a lot of modules I've seen. But some do. Is it necessary to use a controller in order to display my query in a view? Or can I bypass that and just wire my query straight in to a search textbox in the View?

 

Anywhere Orchard has a controller, that controller is being used for some purpose. Which controllers do you think aren't being used?

The only situation where you can get away without a controller is if you're writing a content part, and in that case you need a Driver (which is a kind of controller anyway).

The way things should work in MVC is that the controller processes data, the view simply displays it. Doing any kind of querying in a view is very bad coupling.

Nov 9, 2011 at 7:05 PM

Thanks randompete. I know that when controllers are used in Orchard modules they serve a purpose. I was observing the modules that don't use controllers and like you said those are modules built as content parts only, and use a Driver instead. The module I am building has some content parts. But it also has other functionality that has nothing to do with those content parts. So you did answer my question then:

I should use a controller for my non-content part funtionality.

Thanks.

 

Coordinator
Nov 9, 2011 at 7:11 PM

The Pryor_GeoZip class should exist and should adhere to the Orchard conventions for records if you want Orchard to auto-map it for you.

Nov 9, 2011 at 7:15 PM

Thanks. Ok. I was a bit confused. the parameter should be my model class that represents the table I am querying?

Coordinator
Nov 9, 2011 at 7:52 PM

yes.

Nov 11, 2011 at 10:04 PM

Does anyone know how I can get Orchard/NHibernate to map to custom tables added to my database. I have custom tables in my database that are filled with data that needs to be queried. When I installed orchard on this database it dropped all the Orchard tables/records around my custom tables. But I'm not sure how to make Orchard/NHibernate "aware" of these tables. Is there any way you know of, that I can tell Orchard/NHibernate to map these tables? From what I've been reading about NHiberbate, it's a lot about mapping to tables. (but correct me if I am wrong.)

I think it might be easier to query them once NHibernate knows they exist. And I can query them by following the tutorials on the Orchard website. Sort of turning my custom tables into Orchard records by just telling NHibernate they exist somehow.

 

Thanks

 

Oceantrain

Coordinator
Nov 11, 2011 at 10:14 PM

The answer to your question is actually up there, right in this thread, so I'm a little confused about what you're really asking. Can you explain what you are trying to do and how the answers already given are not working?

Nov 11, 2011 at 11:06 PM

Well, I guess for starters I can't even get the code from the N-1 tutorial to compile. I've written it verbatim. It's telling me the DataMigration Method is not allowed in a class.

 Compiler Error Message: CS1519: Invalid token '(' in class, struct, or interface member declaration: line 21

////// The Method ///////////////

Line 19:             private readonly IRepository _topicsRepository;
Line 20: 
Line 21:             public PryorProductsDataMigration(IRepository topicsRepository) <<<----Line 21
Line 22:         {
Line 23:             _topicsRepository = topicsRepository;


//////////////// FULL SOURCE ////////////////

Line 1:    using System;
Line 2:    using System.Collections.Generic;
Line 3:    using System.Data;
Line 4:    using Pryor.Products.Models;
Line 5:    using Orchard.ContentManagement.Drivers;
Line 6:    using Orchard.ContentManagement.MetaData;
Line 7:    using Orchard.ContentManagement.MetaData.Builders;
Line 8:    using Orchard.Core.Contents.Extensions;
Line 9:    using Orchard.Data.Migration;
Line 10:   using Orchard.Data;
Line 11:   using NHibernate;
Line 12:   using FluentNHibernate;
Line 13:   
Line 14:   namespace Pryor.Products.DataMigrations
Line 15:   {
Line 16:   
Line 17:       public class Migrations : DataMigrationImpl
Line 18:       {
Line 19:               private readonly IRepository<TopicCodes> _topicsRepository;
Line 20:   
Line 21:               public PryorProductsDataMigration(IRepository<TopicCodes> topicsRepository)      <<<<<<---ERROR
Line 22:           {
Line 23:               _topicsRepository = topicsRepository;
Line 24:   
Line 25:               return topicsRepository;
Line 26:           }
Line 27:   
Line 28:           
Line 29:   
Line 30:           public int Create() 
Line 31:           {
Line 32:             
Line 33:   			// Creating table ProductRecord
Line 34:   			SchemaBuilder.CreateTable("ProductRecord", table => table
Line 35:   				.ContentPartRecord()
Line 36:   				.Column("Product", DbType.String)
Line 37:   				.Column("Price", DbType.Double)
Line 38:                   .Column("Type", DbType.String)
Line 39:                   .Column("TopicCodes_Id", DbType.String));
Line 40:   
Line 41:   
Line 42:   			 SchemaBuilder.CreateTable("TopicCodes",
Line 43:               table => table
Line 44:               .Column<int>("Id", column => column.PrimaryKey().Identity())
Line 45:               .Column<string>("TopicCode", column => column.WithLength(2))
Line 46:               
Line 47:           );
Line 48:               
Line 49:   
Line 50:               ContentDefinitionManager.AlterPartDefinition("ProductPart", builder => builder.Attachable();
Line 51:                   
Line 52:   
Line 53:               return 1;
Line 54:           }
Line 55:       }
Line 56:   }

Nov 11, 2011 at 11:14 PM

The constructor should be the name of your class, which in this case is Migrations, not PryorProductsDataMigration ;)

Nov 14, 2011 at 3:12 PM
Edited Nov 14, 2011 at 3:48 PM

fixed.

Nov 14, 2011 at 3:22 PM

@bertrandleroy

This code in your Orchard.PO Module, is this HQL? or Linq to SQl? trying to decipher the difference between the 2.

 

  public CultureDetailsViewModel GetCultureDetailsViewModel(string culture) {
            var model = new CultureDetailsViewModel {Culture = culture};
            using (var session = _sessionLocator.For(typeof(LocalizableStringRecord))) {
                var query = session.CreateQuery(@"
                    select s
                    from OrchardPo.Models.LocalizableStringRecord as s fetch all properties
                    order by s.Path");
                var currentPath = "";
                var group = default(CultureDetailsViewModel.TranslationGroupViewModel);
                foreach (LocalizableStringRecord s in query.Enumerable()) {
                    if (s.Path != currentPath) {
                        group = new CultureDetailsViewModel.TranslationGroupViewModel {
                                                                                          Path = String.Format(s.Path, culture)
                                                                                      };
                        model.Groups.Add(group);
                        currentPath = s.Path;
                    }
                    var translation = s.Translations
                        .Where(t => t.Culture == culture)
                        .FirstOrDefault();
                    if (group != null &&
                        ((s.Translations.Count(t => t.Culture.Equals("en-US", StringComparison.OrdinalIgnoreCase)) > 0) ||
                         (translation != null && !String.IsNullOrEmpty(translation.Value)))) {
                        group.Translations.Add(new CultureDetailsViewModel.TranslationViewModel {
                            Context = s.Context,
                            Key = s.StringKey,
                            OriginalString = s.OriginalLanguageString,
                            LocalString = translation != null ? translation.Value : null
                        });
                    }
                }
            }
            return model;
        }

Coordinator
Nov 15, 2011 at 1:51 AM

query is HQL.

Nov 22, 2011 at 7:38 PM

Ok. I guess my equestion about querying custom tables in the Orchard database wasn't very clear. My tables are in the same Database as the rest of the Orchard tables. But they are NOT mapped by Orchard. Can I still query these tables using Session Locator without being mapped? and if so, do I need to create model classes for them? 

Nov 23, 2011 at 5:51 PM

Bertrand I followed the conventions of writing my classes to adhere to Orchard conventions. My classes are still not being Auto Mapped

 

"The Pryor_GeoZip class should exist and should adhere to the Orchard conventions for records if you want Orchard to auto-map it for you."

 

I get an error saying the class you are referencing to is NOT mapped. Can you please tell me how to map to my existing tables?

Coordinator
Nov 23, 2011 at 6:09 PM

http://stackoverflow.com/questions/7840250/orchard-custom-module-model-being-picked-up-by-nhibernate-requiring-virtual/7856337#7856337

Nov 23, 2011 at 6:12 PM

bertrand

you said:

"The answer to your question is actually up there, right in this thread, so I'm a little confused about what you're really asking. Can you explain what you are trying to do and how the answers already given are not working?"

Where is the answer in this thread explaining how to map tables using Orchard? Is it using Isession Locator? Cause that ain't working. If I use Isession Locator don't the tables still need to be mapped beforehand?

Coordinator
Nov 23, 2011 at 6:19 PM

First answer. Yes. Yes it does, my OrchardPo module runs just fine here: http://157.55.161.64/localize. No they will be automatically mapped if you respect the conventions (see Sébastein's link), and if you use raw SQL through session locator, there is no need for any mapping.

Nov 23, 2011 at 7:37 PM

Thank you both for your response. Considering My Existing Tables do not have an ID column. which according to Orchard conventions I need to have "an Id property, with virtual accessors:". Obviously I don't need that ID property if the ID column doesn't even exist.

So that leaves me with querying my unmapped tables Using ISessionLocator and Raw Sql which is demonstrated in your OrchardPO module.

I've looked at your Orchard PO code. In the Service your are using Isession Locator to query mapped records.  Records you created in the Migrations.cs file (LocalizableStringRecord, TranslationRecord) .

So I guess my disconnect is there. 

I will try again to Use ISession Locator, using Raw SQL to Query my unmapped tables. It hasn't worked thus far. I get the error of referencing tables that are not mapped. But I'll go over my code and try it again.

 

Thanks

Oceantrain

Coordinator
Nov 23, 2011 at 7:58 PM

If you are using raw SQL, you are effectively doing a manual mapping at the query level, so if you keep to simple types for your columns you should be fine.

Nov 23, 2011 at 8:03 PM

Do I still need a model with get and set? Or do I just need to use a view model?

Nov 23, 2011 at 9:44 PM

Whats the best way to integrate my unmapped table properties into my Driver Results. As in this N-1Tutorial shown here:

        protected override DriverResult Display(
            AddressPart part,
            string displayType,
            dynamic shapeHelper) {

            return ContentShape("Parts_Address",
                () => shapeHelper.Parts_Address(
                   ContentPart: part,
                    Address: part.Address, <--part
                    City: part.City, <--part
                   Zip: part.Zip, <--part
                 StateCode: part.State.Code,  <<--unmapped property 
                 StateName: part.State.Name));  <<--unmapped property 

I still need to make these unmapped properties, PARTS, in my Module. I need to access the data in these properties through a drop down list on the Admin UI side.
In the tutorial the table is included as a part in this piece of code:

using Orchard.ContentManagement;

namespace RelationSample.Models {
    public class AddressPart : ContentPart<AddressPartRecord> {
        public string Address {
            get { return Record.Address; }
            set { Record.Address = value; }
        }
        public string City {
            get { return Record.City; }
            set { Record.City = value; }
        }
        public StateRecord State {
            get { return Record.StateRecord; }
            set { Record.StateRecord = value; }
        }
        public string Zip {
            get { return Record.Zip; }
            set { Record.Zip = value; }
        }
    }
}


If the tables I am querying are not Mapped and thus not Records, how do I attach them as Parts to my Module?

    public MyUnmappedTable Table{
            get { return Table.MyUnmappedTable; }
            set { Table.MyUnmappedTable= value; }
        }

???

This is where I run into the error saying this class I am referring to is not Mapped.

Oceantrain


Nov 23, 2011 at 9:45 PM

In your OrchardPO code, you are not using a driver you are using a controller.

Coordinator
Nov 23, 2011 at 11:46 PM

With raw SQL you are going to address the result's columns one by one, so you can have whatever object model you want behind it, or none at all. You could for example copy the data over onto your shape and hand that over to Orchard for display. just don't make the nhib objects live too long: grab the data and copy it where you need it.

It doesn't make a bit of a difference for querying that it is in a driver or a controller. Actually, if I'm not mistaken, the querying code is in neither, it's in a service class.

Nov 24, 2011 at 2:42 PM
Edited Nov 24, 2011 at 2:54 PM

Yes, the query will be in a service class. I'm just unsure how to reference the parts of my unmapped properties in the driver like they do in the N-1 tutorial. 

I'm guessing if I have a ViewModel I can just reference the properties in my unmapped tables just like in the tutorial:


    return ContentShape("Parts_Address",
                            () => shapeHelper.Parts_Address(
                                ContentPart: part,
                                Address: part.Address,
                                City: part.City,
                                Zip: part.Zip,
                                StateCode: part.State.Code,
                                StateName: part.State.Name));

My code would look something like this:

    return ContentShape("Parts_Webinar", <<-- This is the Part and Record I am adding to Orchard.

                            () => shapeHelper.Parts_Webinar(
                                ContentPart: part,
                                TopicCode: part.TopicCode <-- This is the column I will be querying in my service class to be displayed as a drop down list in my EditorView.
                               
 				));

And for the Editor DriverDispaly:
using WebinarViewModels;
       
 private EditWebinarViewModel BuildEditorViewModel(WebinarPart part) {
            var avm = new EditWebinarViewModel {
     
                TopicCode = _webinarService.GetTopicCode()<--This would be the call to my Method in my service class which queries the database based on the TopicCode column.
            };
            if (part.TopicCode != null) {
                wvm.TopicCodeCode = part.TopicCode;
               
            }
            return wvm;

Am I in the right ballpark? Does this look right to you?


Nov 26, 2011 at 5:20 PM

I'm confused what you mean by this statement:

" You could for example copy the data over onto your shape and hand that over to Orchard for display. just don't make the nhib objects live too long: grab the data and copy it where you need it."

 

I have no clue how to integrate my query results in to my Drivers. All the tutorials are using parts and properties currently mapped by Orchard. I don't understand what you mean "grab the data and copy it where you need it.

1. I need a dropdown list on the AdminEditor side to display a list of Topic Codes. Much like the State code shown in the tutorial. The Query is using an unmapped column in my databes pulling data with joins and inner joins.

2. The published DriverResult Display method will show ALL the Data that was requested in the query.

 

     protected override DriverResult Display(WebinarAudioPart part, WebinarService webinarService, string displayType, dynamic shapeHelper)

          {
                 return ContentShape("Parts_WebinarAudio",
                   () => shapeHelper.Parts_WebinarAudio
                       (ContentPart: part, Length: part.Length,
                       Date: part.Date,
                        GetTopicCodes())); <<-The Method in my Webinarservice class that queries my DB
                       
                       
                     
                        
                    
                        
          }

Coordinator
Nov 28, 2011 at 11:40 PM
Edited Nov 28, 2011 at 11:41 PM

Yeah, see that right there, that's wrong. The ContentShape is getting an anonymous delegate so it can execute its code later, if and when needed. That means that your call to GetTopicCodes will happen later, possibly out of transaction. That's wrong.

Get your codes first, out of the delegate, then put them on the shape. That's what I mean by grab the data and copy it where you need it.

Nov 28, 2011 at 11:51 PM
Edited Nov 28, 2011 at 11:52 PM

Actually it's better to execute the data access inside the delegate - that way it won't run if it doesn't have to (otherwise you can hit performance problems e.g. in content lists). What Bertrand means I think is that you shouldn't pass data access into the shape itself.

Let me just demonstrate with your code example:

 

protected override DriverResult Display(WebinarAudioPart part, WebinarService webinarService, string displayType, dynamic shapeHelper)
          {
                 return ContentShape("Parts_WebinarAudio",
                   () => {
                       var codes = GetTopicCodes().ToList();
                      return shapeHelper.Parts_WebinarAudio
                       (ContentPart: part, Length: part.Length,
                       Date: part.Date,
                        TopicCodes:codes);
                  });
                      
          }

You see there how it all runs inside the delegate, and that delegate will only run if the shape gets a placement match. So by shutting something off in placement, you avoid doing the work (this is true for all core parts and most third party modules).

 

Placement is checked and the delegate actually gets run almost immediately after the call returns, and its guaranteed to happen before your driver or the transaction scope dispose. *However* the danger then is that the View that renders the shape itself won't get run for some time afterwards, and by that stage the transaction scope might have expired.

So for that reason your original code would be dangerous. Why? Because GetTopicCodes() could be returning an IEnumerable or an IQueryable ... which means the data access might not have run yet ... so you're passing a live data object down into your view and it might try to enumerate after a dispose. In fact the most important bit of the version I've shown is ToList(), because that ensures the data access takes place at the right time.

Edit: Fixed code parenthesis

Coordinator
Nov 29, 2011 at 12:14 AM

Yes, better, thanks.

Nov 29, 2011 at 3:02 AM

Thanks for your help guys.