If mapping stored procedures to the Entity Model wasn’t obvious enough, lets continue our discussion of our support of RefCursors and working effectively with EF. I have talked about Ref Cursors before and as we now have our GA out in the wild, it’s time for an update on full our Ref Cursor support.
First, we’ve extended our support for Ref Cursors and EF– we previously we only had quasi-implicit only, now we’ve introduced explicit support. We’ve also settled on a SSDL representation that explicitly highlights Ref Cursors which is now our default and preferred approach. No manual user intervention is required and explicitly includes Ref Cursor parameters in both the generated SSDL which is then subsequently defined in the CSDL. First lets define a stored procedure, and a related Ref Cursor definition. [cc lang="sql" line_numbers="false"] CREATE OR REPLACE PACKAGE types AS type cursorType is ref cursor; END; [/cc] [cc lang="sql" line_numbers="false"] CREATE OR REPLACE PROCEDURE "CategoriesGet1" (CATEGORYID IN number, myCursor OUT types.cursorType, CATEGORYNAME IN CHAR) AS BEGIN open myCursor for SELECT * FROM "Categories" Where "Categories"."CategoryID" = CATEGORYID AND "Categories"."CategoryName" = CATEGORYNAME ORDER BY "Categories"."CategoryID"; END; [/cc] Generating a SSDL representation gives the following – notice ‘MYCURSOR’ is explicitly defined as a ‘Ref Cursor’. When you build an entity on this SProc, the type reference ‘ref cursor’ is carried top the CSDL layer and exposed to the application. [cc lang="xml" line_numbers="false"]
[/cc] For simplicity’s sake, we’ll map use ObjectServices to interact more directly with our generated model, and then retrieve the value. [cc lang="csharp" line_numbers="false"] ObjectParameter param = new ObjectParameter("MYCURSOR", System.DBNull.Value); var cq = Provider.context.GetCategories1(1, param, "Beverages"); [/cc] So why consider a non-explicit approach? As we’ll see in later postings in this series it is sometimes very important to build in model portability in to your EDM model. With EF it is relative easy to can safe guard your model and quasi-implicit ref cursors helps achieve this. As you become more familiar with EF, getting stuck in with the EDMX file is something you’ll become more comfortable with. In this occasion, there is no exception. Let dig in and manually alter SSDL definition as follows: To safe guard the sanctity of the CSDL, we make a number of adjustments to the SSDL layer, first modify the preamble in the EDMX file to include an additional namespace, ‘ddtekora’.
[cc lang="xml" line_numbers="false"]
Next, we remove ref cursor parameter and migrate it to an attribute in our function declaration taking care to match each parameter with the logical parameter name If there are more than one Ref Cursor parameters who want to implicitly support, they too should be removed and placed in the attribute list separated by commas.
[cc lang="xml" line_numbers="false"]
Our code above simplifies to the following [cc lang="csharp" line_numbers="false"] var cq = Provider.context.GetCategories1(1, "Beverages"); [/cc]
View all posts from Jonathan Bruce on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.Learn More
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.