sql sp or function as data source

montoyam

for asp.net projects, is it possible to use a stored procedure or table-valued function as a data source (and pass a parameter)?

montoyam

I have done further searching on the forums here and found the one:
https://datatables.net/forums/discussion/comment/119150/#Comment_119150

but I am not sure how to incorperate this into my project. Are there more examples of how to use db.sql ?

allan

Its certainly possible, but its not something that our .NET libraries for Editor provide the ability to do I'm afraid. They can only operate directly against tables (or read from VIEWs).

If you wanted to use stored procedures you'd need to query it using ADO.NET, or whatever connection you are using to query the db.

Allan

montoyam

If I get it figured out, would I be able to use the result in the left join of this controller instead of the view on line 17?

    public class FundOrgDeptGroup_HeaderController : ApiController
    {
        [Route("api/FundOrgDeptGroup_Header")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult FundOrgDeptGroup_Header()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "FundOrgDeptGroup_Header", "DeptGroupID")
                    .Model<FundOrgDeptGroup_HeaderModel>("FundOrgDeptGroup_Header")
                        .Field(new Field("vw_FundOrgFTE_DeptCounts.FTE_Sum"))
                        .Field(new Field("vw_FundOrgFTE_DeptCounts.Emp_Sum"))
                    .LeftJoin("vw_FundOrgFTE_DeptCounts", "vw_FundOrgFTE_DeptCounts.DeptGroupID", "=", "FundOrgDeptGroup_Header.DeptGroupID")
                    .Process(request)
                    .Data();
                return Json(response);
            }
        }
    }

allan

Do you mean you want to do the join based on the result from the stored procedure? The joins are limited in the Editor library to just column names I'm afraid, so that wouldn't work either. But if its just the read you want to do this one, then you could set up a VIEW from which Editor will read the data and then it can just write to the table as normal.

That works for cases where the read is perhaps a little more complex (i.e. needing a stored proc.) and the write is just a straight write to a single table.

Where it doesn't work is if you needed to run a write through a stored proc. as well.

Allan

montoyam

what about database.sql()? Is this used in the controller? I can not find any sample code on how to use database.sql().

I changed the stored procedure to a function so now I can use it in a select statement: select * from udf_FundOrgFTE_GetDeptCounts (AsOfCookie)

allan

The .Sql() method is used to execute a raw SQL statement - there is no processing done on the data, no use of a model or anything else. You could use that, but it wouldn't do any SQL building or writing to the db for you.

An example might be:

var result = db.sql("SELECT ... FROM ...");

Full documentation for it is here.

Allan

montoyam

but the datatable can use the stored proc datasource while the editor can use the 'simple' table, right?

colin

Yep, exactly. The DataTable only needs to pull the data across that it displays in the table, so they can have different server-side scripts.

Colin

montoyam

so currently my DataTables looks like this:

        var FundOrgHeaderTable = $('#FundOrgDeptGroup').DataTable({
            dom: 'Bfrtip',
            ajax: '/api/FundOrgDeptGroup_Header',
            columns: [
                { data: "FundOrgDeptGroup_Header.Department" },
                { data: "vw_FundOrgFTE_DeptCounts.FTE_Sum" },
                { data: "vw_FundOrgFTE_DeptCounts.Emp_Sum" }
            ],
            select: { style: 'single' },
            lengthChange: false,
            buttons: [
                { extend: 'create', editor: FundOrgDeptGroupEditor },
                { extend: 'edit', editor: FundOrgDeptGroupEditor },
                { extend: 'remove', editor: FundOrgDeptGroupEditor }
            ]
        });

where would I incorporate: result = db.sql("SELECT ... FROM ...");

montoyam

actually, I appear to have figured out how to use a function. I can grab a regular table as the "main" table, then get the data from the function (maybe a stored proc, didnt try that yet), in the leftjoin:

                    .LeftJoin("udf_FundOrgFTE_GetDeptCounts('" + AsOfCookie + "') as dc", "dc.GroupID", "=", "FundOrgDeptGroup_Header.DeptGroupID")

I'm thinking if the 'main' table needs to be a function as well, I can just use a dummy table (like 'dual') as the main and left join the real 'main' function where 1=1??? That will be my next experiment.