Does anyone have some HTML Template code they would be willing to share that uses Custom/Execute to access a local stored procedure? I am working on a project where the best solution seems to be building my own version of TP_GET_WEB_CONTENT and calling that. If I had an example using Custom/Execute and passing a few parameters, that would get me over my current hurdle.
Clarke Weigle,
Here you go. This should be a more or less complete example for you to use. For my own ease of use, I am adapting from something we use in our database, so it might not match up to your local procedure, but hopefully this should give you what you need. This is something we use for a series of events we call our "PB&J series"; they are events geared at providing opportunities for kids 7 and under to experience live music performance in an interactive setting where they can enjoy, dance around and otherwise be kids, getting to interact directly with the musicians and sometimes try out the instruments. It is super cute. Anyway.
To make the code for this HTML template sample work, the following assumptions are in order (for LWP_CP_PBJ_PERF_NOS and all related, obviously substitute your own procedure, etc...):
Here then is the code for the actual HTML template. This is bare bones and just returns the performance ID number (SQL INT) and performance name/description (SQL VARCHAR(30)) for these PB&J events. While this is rather a simple example, it nevertheless hopefully shows what you would need to do to get a working custom procedure working in HTML.
@* Minimum necessary using statements for the below code; additional might be necessary depending on other needs *@ @using Newtonsoft.Json; @using Tessitura.Service.Client.Custom; @using Tessitura.Services.Common.Client.Utils; @* Declare the c# stuff for use *@ @functions { /* Parameters */ private ParameterValue newParameter(string name, string value) { return new ParameterValue { Name = name, Value = value, }; } /* Local procedure request */ private LocalProcedureRequest newLocalProcedureRequest(string procedureName, ParameterValues parameters) { return new LocalProcedureRequest { ProcedureName = procedureName, ParameterValues = parameters, }; } /* Individual return object */ public class PbjPerf { [JsonPropertyAttribute("perf_no")] public int perfNo; [JsonPropertyAttribute("perf_desc")] public string perfDesc; } /* List of returned objects (full return SQL table) */ public class PbjPerfs : List<PbjPerf> {}; } @{ /* Get procedure parameter inputs */ var orderNo = Convert.ToString(Model.OrderProductView.Id); var orderDt = Convert.ToString(Model.OrderProductView.OrderDateTime); /* Create request element */ LocalProcedureRequest request = newLocalProcedureRequest("LWP_CP_PBJ_PERF_NOS", new ParameterValues { newParameter("order_no", orderNo), newParameter("order_dt", orderDt) }); /* Make request and set response object */ List<PbjPerf> returnPbjPerfs = Model.RestClient.AtUrl("Custom/Execute") .WithContentType(ContentType.Json) .Post<LocalProcedureRequest, PbjPerfs>(request).ResponseObject; } @* Begin e-mail body *@ <body style="font-family: Arial, Helvetica, Sans-serif; font-size: 12px;"> @* Table because everyone loves tables *@ <table width="700" style="font-size: 12px; text-align:left; color: #000000; font-family:Arial, Helvetica, sans-serif;"> <tr> <td align="left" valign="top"> <table style="vertical-align: top; text-align:left; font-family: Arial, Helvetica, sans-serif; font-size: 12px;"> @* Headings *@ <tr> <th style="background-color: #000000; color: #ffffff; font-weight: bold; font-size: 12px; text-align: left; vertical-align: top"> Perf No </th> <th style="background-color: #000000; color: #ffffff; font-weight: bold; font-size: 12px; text-align: left; vertical-align: top"> Perf Desc </th> </tr> @* Cycle through each element in the list of objects *@ @foreach (var perf in returnPbjPerfs) { <tr> <td style="vertical-align: top;"> @* Performance number element *@ @perf.perfNo </td> <td style="vertical-align: top;"> @* Performance description element *@ @perf.perfDesc </td> </tr> } </table> </td> </tr> </table> </body>
Let me know if you have any questions. While they are not perfect, I have found HTML templates to be extremely flexible if you play with them a little bit. Best of luck!
John A. Moskal II
John, I am not making satisfactory progress on this. Can I get some additional time with you via email? It might be quicker to hire you to modify the cshtml file. I do want to gain knowledge about how to do this, but maybe now is not the time to do it myself.
Clarkecweigle [at] floridastudiotheatre.org
Just sent you an e-mail. I am sure we can make something happen.