BreezeJS and stored procedures

Question

I'm new to BreezeJS and would like to know if there are examples of using Breeze with a SQL stored procedure?

We have quite complex queries and we want them to be able to be called through SP. Also, how can we tell Breeze that the column returned from SP is the key? We do not want to use Views, because we need to pass variables to the SP request every time we call it.

Thanks.

bean

Answer 1

Well, the main idea would be to use the Breeze EntityQuery.withParameters method to pass parameters to a server-side method that calls your saved proc and returns IEnumerable. (i.e., the result of the stored procedure).

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

You may want to take a look at the EntityQuery.toType method to get the wind to convert the returned data into a specific EntityType, or you can use the "jsonResultsAdapter" one at a time to do the same.

Any data that is returned from the request and converted to Breeze EntityType is automatically wrapped according to the modelLibrary used, i.e. knockout, angular, base, etc.

If the breeze cannot construct objects from the returned data, it will still be returned, but without any special processing to wrap the result.

Hope this helps!

Answer 2

Sample to access Breeze Sql stored procedures; storage procedure (GoStoCde) was 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 an object instead of an object, then the code and it should work as well. Hope this helps!

Answer 3

This is actually not an answer, just a few thoughts. I think the ability to return randomly generated data (read viewmodel) using a stored procedure using parameters would be a great way to undo something like dapper.net. After resubmitting the specified view model, you can use overloads to restore real objects from your view model and save the changes. The only problem I am facing is that you would need to easily and automatically restart sproc and send the data back to the client ... I would like to know if this makes sense to someone else and / or if someone I already did it. For this kind of scenerio, I would think that you need to disable the tracking functions provided by the breezes and / or write a fairly intelligent data service that can process presentation models so that javascript on the client knows when it adds / removes / updates parts of x, y, z viewmodel a, which you create jx, jy, jz objects (j for javascript) and send them back and save as you go (the reverse view of what was mentioned above in some way) Thoughts?