Stored Procs in Razor templates

Jul 21, 2014 at 6:27 PM
Edited Jul 21, 2014 at 6:28 PM
Hi,

Is it possible to call Stored Procs (in a SQL database) in a Razor template?

If so an example would be great!

Cheers,

Ian
Aug 4, 2014 at 10:02 AM
Edited Aug 4, 2014 at 11:20 AM
Here an example of a simple storedprocedure 'dnn_plTasks_GetTasks'.
It returns a list of tasks and put it on screen.
I use here a new instance of the the dnn data provider class.

DataProvider.Instance().ExecuteReader(procedureName,commandParameters) to return a list from the database.
DataProvider.Instance().ExecuteScalar<T>(procedureName,commandParameters) to return a single value.
DataProvider.Instance().ExecuteNonQuery(procedureName,commandParameters) to return nothing (update , delete resp.).

these methods return a IDataReader-object. to hydrate this to our Task-object we can use the helperclass CBO(Custom Buisness Objects).
CBO has several methods and overloads.
FillCollection: return a collection
FillDictionary: return a dictionary
FillObject: return a single Object
FillQueryable: return a queryable for further use of linq
FillSortedList: a sorted List
@using DotNetNuke.Common.Utilities;
@using DotNetNuke.Data;


@functions{
    public class Task
    {
        public int TaskId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public bool IsComplete { get; set; }
    }

    public class TaskController
    {
        public IList<Task> GetTask()
        {
            return CBO.FillCollection<Task>(DataProvider.Instance().ExecuteReader("dnn_plTasks_GetTasks"));
        }
    }

}



@{
    TaskController controller = new TaskController();
    var tasks = controller.GetTask();
    var grid = new WebGrid(tasks);
}


@grid.GetHtml();
I'm pretty new to razor in dnn myself. However, the workflow seems related with the so called 'ASP WebPages'.
http://www.asp.net/web-pages

Therefore i guess you could also use the Data-classes in the webmatrix namespace.
http://stackoverflow.com/questions/3720113/webmatrix-and-stored-procedures
http://msdn.microsoft.com/de-de/library/webmatrix.data(v=vs.111).aspx

razor can be a bit messy if you try to solve all tasks in the cshtml-files. In the above example for instance, you could
get rid of the @functions section by creating own classfiles for the module in the app-code folder (in the editmode for your module in the dnn-backend, choose class from
the dropdown). DNN will then create the correct folderstructure in the dnn App-Code folder where you can put csharp code, which is shared by
all razor views in your module.


and of course you could create your own ClassLibrary(-Project) and build it into the dnn/bin folder. and use the classes in your razor-module.

maybe this answer helps a bit.

roxl