Answers

• montoyam Posts: 568Questions: 136Answers: 5

  January 2020

  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 Posts: 61,665Questions: 1Answers: 10,096 Site admin

  January 2020

  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 Posts: 568Questions: 136Answers: 5

  January 2020

  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 Posts: 61,665Questions: 1Answers: 10,096 Site admin

  January 2020

  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 Posts: 568Questions: 136Answers: 5

  January 2020

  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 Posts: 61,665Questions: 1Answers: 10,096 Site admin

  January 2020

  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 Posts: 568Questions: 136Answers: 5

  January 2020

  but the datatable can use the stored proc datasource while the editor can use the 'simple' table, right?
• colin Posts: 15,142Questions: 1Answers: 2,586

  January 2020

  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 Posts: 568Questions: 136Answers: 5

  January 2020

  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 Posts: 568Questions: 136Answers: 5

  January 2020 edited January 2020

  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.