BreezeJS and Stored Procedures

Question

I am new to BreezeJS and would like to know if there are any examples on how to use Breeze with an SQL Stored Procedure?

We have some pretty complex queries and want to be able to call them via an SP. Also, how can we tell Breeze that a column returned from a SP is the Key? We don't want to use Views, because we need to pass variables to the SP query each time we call it.

Thanks.

bob

Answer 1

Ok, the basic idea would be to use Breeze's EntityQuery.withParameters method to pass parameters to a server side method that calls your stored proc and returns an IEnumerable. ( i.e. the result of the stored proc).

If you want to treat this result as collection of Breeze entities then you will either need to shape the results into an existing entity type that Breeze knows about from Metadata OR manually create and add a new EntityType on the client that matches the shape that you want to return.

You may want to look at the EntityQuery.toType method to force breeze to convert your returned data into a specific EntityType or you might alternately want to use a "jsonResultsAdapter" to do the same thing.

Any data that is returned from a query and is converted into an Breeze EntityType is automatically wrapped according the "modelLibrary" in use, i.e. Knockout, Angular, Backbone etc.

If breeze is not able to construct entities out of the returned data then it will still be returned but without any special processing to wrap the result.

Hope this helps!

Answer 2

A sample to access Sql Stored Procedures from Breeze; the store procedure (GoStoCde) has been imported by EF.

Breeze Controller :

 [HttpGet]
 public object GetCdes(long jprod, int jqte, long jorder)
 {
   //output params
   var owrk = new System.Data.Objects.ObjectParameter("wkres", typeof(string));
   owrk.Value = "";
   var oeror = new System.Data.Objects.ObjectParameter("ceror", typeof(int));
   oeror.Value = 0;
   //invoke stored procedure
   var envocde = _contextProvider.Context.GoStoCde(jprod, jqte, jorder, owrk, oeror);
   //stored procedure results
   var cdeResult = new {
    dwork = owrk.Value,
    deror = oeror.Value,
   };
   return new { cdeResult };
 }

Datacontext :

 function reqLnecde(iprod, iqte, iorder, vxeror) {
   logger.log("commande en cours...");
   var query = new EntityQuery.from("GetCdes")
        .withParameters({ jprod: iprod, jqte: iqte, jorder: iorder });
        return manager
            .executeQuery(query)
                .then(querySucceeded)
                .fail(cqueryFailed);
        function querySucceeded(data) {
                //stored procedure results
        vxeror(data.results[0]);
                //stored procedure object member value  
                keror = vxeror().cdeResult.deror;
        if (keror === 0) {
                    logger.log("commande done");
        } else {
            logger.log("article absent"); 
        }
        }
    function queryFailed(data) {
                logger.log("commande failed"); //server errors
       }
 }

If you prefer to return entity in lieu of object, code consequently and its must also work. Hope this helps!