Using Stored Procedures

Topics: Customizing Orchard, Writing modules
May 16, 2012 at 8:44 AM

Hi,

I'm using Orchard as a CMS but I am wanting to run some custom stored procedures to an external data source (SQL Server).

What is the best way to do this within the MVC/Orchard framework?

 

Thanks.

Coordinator
May 17, 2012 at 8:10 AM

Just opt out of the ambient transaction.

May 17, 2012 at 2:00 PM

I see.

I have created a seperate project in my solution which holds all the normal stored procedure calls while opting out of the transaction.

Is this the correct way to do this, or is there a better practice?

Coordinator
May 17, 2012 at 9:23 PM

sounds good from what you're saying. By separate project you mean a module?

Aug 23, 2012 at 10:33 AM

How do you opt out of the ambient transaction?

Cheers.

Coordinator
Aug 27, 2012 at 7:39 AM

using
(var scope = new TransactionScope(TransactionScopeOption.Suppress))
Jan 31, 2013 at 6:55 AM
can you give a good guidance for work with stored procedure in orchard module, because i created my own module with orchard i need to insert huge data to db so am going to use storedprocedure for that but i dont know as how to work stored procedure in orchard.
Feb 4, 2013 at 8:19 PM
I'd be interested in seeing this as well.
Feb 4, 2013 at 9:26 PM
Inject ISessionLocator into your class, and use that to get an instance of the NHIbernate ISession. Using ISession, you can run stored procs or custom ad-hoc SQL queries directly to the DB. Doing this, you are likely to kill the db portability of Orchard (might not be an issue for some).
Feb 18, 2013 at 6:31 PM
Edited Feb 18, 2013 at 6:32 PM
TheMonarch wrote:
Inject ISessionLocator into your class, and use that to get an instance of the NHIbernate ISession. Using ISession, you can run stored procs or custom ad-hoc SQL queries directly to the DB. Doing this, you are likely to kill the db portability of Orchard (might not be an issue for some).
I've tried using (var scope = new TransactionScope(TransactionScopeOption.Suppress)) with Dapper ORM and can retreive data from other databases and tables outside of Orchard, but still trying to connect the returned list back into a Orchard Model/View for display... any help here?
Feb 18, 2013 at 6:48 PM
What part are you having trouble with? I'm not sure what you mean by connecting the returned list back into an Orchard Model/View. Post code you have so far if possible. Are you retrieving the data from a service class? What are you trying to do with it ultimately, display some non-Orchard data as part of a Content Part?
Feb 18, 2013 at 7:12 PM
Edited Feb 26, 2013 at 7:08 PM
Yes, I'm doing this within a service class and yes i wish to display the return of non-Orchard data as part of a Content Part... and I'd rather use NHibernate with a stored procedure but using ISessionLocator with NHIbernate ISession was taking me too long to figure out. So I'm using Dapper ORM to retrieve data and get the following casting error?

"{"Unable to cast object of type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]' to type 'CRC.ClientManager.Models.CallerPart'."}"
Feb 18, 2013 at 7:36 PM
What I've done is use a custom query via the nhib session to get the content item or content part Id's, and then use the Orchard API's, I think .GetMany() to pull all the Content Items by their Id's.

What are you trying to do, from a high level? From this code it doesn't look like you're displaying any non-Orchard data. It looks like you're querying for Orchard content parts using non-Orchard data, but returning only Orchard data. Or do you also need to set some properties on the CallerPart based on non-Orchard data?
Feb 18, 2013 at 8:17 PM
Edited Feb 23, 2013 at 12:39 AM
Highlevel description:
  1. Get data with query using storedprocedure of non-Orchard tables (no migrations, no crud, for display only)
  2. I've created a class to store and display this data as if it were a true Orchard Part once it's been retrieved
  3. I wish to work with this data as if it always were a true Orchard Part once it's been retrieved for display purposes (only)
  4. Finally, by using an Orchard model, view, etc to help make the data seem more seamless as if a true Orchard Part when displayed but receive the following casting error:
"{"Unable to cast object of type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]' to type 'CRC.ClientManager.Models.CallerPart'."}"
 public IEnumerable<CallerPart> GetCallersByClient(int clientId)
{
        var caller = new CallerPart();
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
        {

                // setting stored procedure and parameters
                IDbConnection connection = new SqlConnection("Data Source=ServerIp;Initial Catalog=Orchard16;Persist Security Info=True;User ID=User;Password=pass");
                connection.Open();

                const string storedProcedure = "[dbo].[pr_storedProcedureName]";
                var p = new DynamicParameters(new { client_id = clientId, call_status = "C", first_name_mask = "F", last_name_mask = "RAMOS" });
                var rows = connection.Query<CallerPart>(storedProcedure, p, commandType: CommandType.StoredProcedure);

                foreach (CallerPart c in rows)
                {
                    caller.FullName = c.FullName;
                    caller.PhoneNum = c.PhoneNum;
                    caller.FullAddress = c.FullAddress;
                    caller.Age = c.Age;
                    caller.Sex = c.Sex;
                    caller.StartTime = c.StartTime;
                    caller.CallTime = c.CallTime;
                    caller.SeqNum = c.SeqNum;
                    caller.CallOfType = c.CallOfType;
                    caller.Email = c.Email;
                }
                return _contentManager.List<CallerPart>();
        }
}
Feb 23, 2013 at 12:40 AM
Edited Feb 23, 2013 at 12:40 AM
We all might benefit from this?
Coordinator
Feb 23, 2013 at 1:10 AM
Well, you can't cast one of those rows onto a CallerPart. You'll have to do that mapping more manually. Also, a part makes no sense without a content item. You'll have to refactor that so that you get the items first, and then call you stored proc to set the values on the part on each of the items.
Feb 26, 2013 at 7:58 PM
Edited Feb 28, 2013 at 5:15 PM
Yes, thank you... Bertrand
I was incorrectly casting my return data onto an Orchard contentPart.
as in http://stackoverflow.com/questions/5636428/casting-a-generic-dictionary-containing-a-generic-dictionary

My solution is now working:
added a Controller with ActionResult Index()
added a Model for RecordName
added a service:
    public IEnumerable<RecordName> GetRecordNameByClient(int clientId)
    {
    // temporary exit from current Orchard NHibernate transaction
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress))
        {
            // temporary scope for new connection and setting stored procedure, parameters, and return RecordName list
            using (IDbConnection connection = new SqlConnection())
            {
                connection.Open();
                const string storedProcedure = "ProcedureName";
                var p = new DynamicParameters(new { Parm_id = ParmId, Parm_2 = "", Parm_3 = "", Parm_4 = "" });

                return connection.Query<RecordName>(storedProcedure, p, commandType: CommandType.StoredProcedure).ToList();
            }
        }
    }
CoachJames http://CrcData.net