Querying data from custom table and orchard table

Topics: Customizing Orchard, Writing modules
Aug 19, 2012 at 1:37 PM

I have an existing sql database that I have successfully integrated into my orchard site.  The tables and procs from this existing database live in the same sql database that orchard is using.  I am doing CRUD operations against these tables via an Orchard module which calls stored procedures.  I am using System.Transactions.TransactionScopeOption.Suppress in order to make my database calls outside of NHib.

My issue is that I need to display data on the same page in the UI from both an Orchard table and from one of my custom tables.  I can query the orchard table with the following sql in my proc, but this feels wrong, it feels like I am doing something I am not supposed to with Orchard, and that I will cause myself pain during future upgrades.  I only need to display data here, any updates to data both in my custom tables and orchard tables happen through the appropriate data layer (my custom procs vs orchard module w/NHib).  

Is this an appropriate way to handle this?  Any alternatives?  

 

select * 
	from orchard_Orchard_Users_UserPartRecord userRecord
	join orchard_Orchard_Framework_ContentItemVersionRecord contentItem
		on contentItem.ContentItemRecord_id = userRecord.id

 

 

 

 

 

Aug 21, 2012 at 3:44 PM

I asked that question a few weeks ago. It's possible to do it with Nhibernate but I chose to "cheat" and bypass NHib for this. 

Here is how I did it: 

  1. I created a skeleton class with fields for every column returned, or the ones I needed. 
  2. Then I have a DbBypass class that returns the an IEnumberable<>. Code and explanation at the bottom of the post.
  3. Create a View Model with single field IEnumberable<Name of Class from Step 1>. 
  4. Create a View that references the ViewModel and handles the display. 
  5. Add an ActionResult method inside the controller that will handle the request. Sample code below. 

DbBypass class:

 

public class DbBypass
    {
        private readonly string _connstring;
        public DbBypass(string connstring)
        {
            _connstring = connstring;
        }

        public IEnumerable<ExtendedLog> GetLogEntries()
        {
//Must suppress Orchard's transactions
            using (new TransactionScope(TransactionScopeOption.Suppress))
            {
                SqlConnection conn = new SqlConnection(_connstring);
                SqlDataAdapter da = new SqlDataAdapter("Select * From LogEntries", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                foreach (DataRow row in dt.Rows)
                {
      //Use the 'yield' keyword to allow for foreach iterations
                    yield return new ExtendedLog
                    {
                        Username = row["Username"].ToString(),
                        Email = row["Email"].ToString(),
                        Country = row["CountryCode"].ToString(),
                        AuditCode = row["AuditCode"].ToString(),
                        AuditData = row["AuditData"].ToString(),
                        TimeInitiated = Convert.ToDateTime(row["TimeInitiated"]),
                        SourceUrl = row["SourceUrl"].ToString(),
                        ReferrerUrl = row["ReferrerUrl"].ToString()
                    };
                }
            }
        }

 

Controller handling request:

private IShellSettingsManager _shellSettingsManager;

        public AdminController(IShellSettingsManager ShellSettingsManager )
        {
            _shellSettingsManager = ShellSettingsManager; //Used for grabbing Orchard's connectionstring.
        }



        [HttpGet]
        public ActionResult Index()
        {
            var _dbbypass = new DbBypass(_shellSettingsManager.LoadSettings().First().DataConnectionString);
            var _logEntries = _dbbypass.GetLogEntries();
            return View(new LogEntriesViewModel() { LogEntries = _logEntries });
        }

Developer
Aug 25, 2012 at 2:30 AM

Just being curious as to why you wouldn't use the IContentManager to query Orchard content tables?

e.g. replace this:

select * 
	from orchard_Orchard_Users_UserPartRecord userRecord
	join orchard_Orchard_Framework_ContentItemVersionRecord contentItem
		on contentItem.ContentItemRecord_id = userRecord.id


with this:

_contentManager.Query<UserPart>().List()
Sep 13, 2012 at 1:55 PM
sfmskywalker wrote:

Just being curious as to why you wouldn't use the IContentManager to query Orchard content tables?

with this:
_contentManager.Query<UserPart>().List()

Probably because I wasn't aware that I should be :).  Any examples on how to use this?  I've included this in my controller but _contentManager is null.. Digging through other modules to see if I can find an example... 

 

public class AdminController : Controller 
    {
        private readonly IOrchardServices _orchardServices;
        private readonly IContentManager _contentManager;


    public ActionResult ManageRosters()
    {
            _contentManager.Query().List();

            //Models.OrchardUser user = new Models.OrchardUser();
            //user.UserId = 1;
            //user.Role = "Instructor";

            return View();
     }
}

Sep 13, 2012 at 2:00 PM

nevermind..  just needed to add it to the constructor..

    public class AdminController : Controller 
    {
        private readonly IOrchardServices _orchardServices;
        private readonly IContentManager _contentManager;

        public AdminController(IOrchardServices orchardServices, IContentManager contentManager)
        {
            _orchardServices = orchardServices;
            _contentManager = contentManager;
        }