The stored Procedure I am using is one that creates a Taxonomical Key containing a root Node with an Arc emanating from the Root Node.

Here the part of the data model of interest…

Multiple Insert Stored Procedure

   1: ALTER PROCEDURE [dbo].[CreateKey]

   2:     @KeyText varchar(MAX),

   3:     @AuthorID int

   4:     -- When you create a Key the first Root node is created with a Root Arc and node numbered 0 

   5: AS

   6: BEGIN

   7:     -- SET NOCOUNT ON added to prevent extra result sets from 

   8:     -- interfering with SELECT statements. 

   9:     SET NOCOUNT ON;

  10:     declare @MaxKey int;

  11:     declare @MaxNodeID int;

  12:     declare @MaxArcId int;

  13:     EXEC @MaxArcId =  dbo.maxArc; -- MaxArc 

  14:     SELECT @MaxNodeID = 0; -- first node 

  15:     EXEC @MaxKey = dbo.maxKey; -- Next KeyID 

  16:     -- Next ArcIn is an Insert (should Lock table here if multiprocessing) 

  17:           Insert into TaxKey(TaxKeyID,TaxKeyName,TaxKeyAuthor) values(@MaxKey + 1, @KeyText,@AuthorID);

  18:           Insert into Arc(ArcID,ArcLabel) values(@MaxArcId + 1,'Root' );

  19:           Insert into Node(NodeID,ArcID,Terminal,IsRoot,TaxKey) values(0,@MaxArcId + 1,0,1,@MaxKey + 1);

  20:           EXEC @MaxKey = dbo.maxKey;

  21:     return @MaxKey;

  22: END

Now I know what you may be thinking. Why does he not use identity generation for the integer Keys, well I don’t like them, I’m a control freak and where you need to cross reference values while inserting in tables (like in Folio References in Accountancy – it’s best to do it yourself. There are a number of simple utility stored procedure that do the same thing here maxArc, maxNode, maxKey.

2. Now to Hook into the edmx

Open the edmx and right click on the stored procedure in the Store of the Model Viewer (should be on a panel on the right in VS2010) you want to import from. CreateKey in this case

Model Viewer

You then can then refine the functionality of the stored procedure using the Add Function Import button…. And some of these are for another day….

Clicking on OK creates an imported function in the Entity Container middle tier back end representation

Looking at the code generated this is what it gives

   1: public int CreateKey(global::System.String keyText, Nullable<global::System.Int32> authorID)

   2:         {

   3:             ObjectParameter keyTextParameter;

   4:             if (keyText != null)

   5:             {

   6:                 keyTextParameter = new ObjectParameter("KeyText", keyText);

   7:             }

   8:             else

   9:             {

  10:                 keyTextParameter = new ObjectParameter("KeyText", typeof(global::System.String));

  11:             }

  12:             ObjectParameter authorIDParameter;

  13:             if (authorID.HasValue)

  14:             {

  15:                 authorIDParameter = new ObjectParameter("AuthorID", authorID);

  16:             }

  17:             else

  18:             {

  19:                 authorIDParameter = new ObjectParameter("AuthorID", typeof(global::System.Int32));

  20:             }

  21:             return base.ExecuteFunction("CreateKey", keyTextParameter, authorIDParameter);

  22:         }

3. REBUILD the Project. This will update the ObjectContext with a reference to the Method just created.

4. Update the RIA Web service (front end of the middle tier) to access this function by adding a function to call the imported version of the stored procedure. DON’T Forget the Invoke attribute (yet another not so well documented attribute!!) or you will not be able to see the Create_Key function in the domain service from the client code.

   1: [Invoke]

   2:

   3: public int Create_Key(TaxKey K)

   4:         {

   5:             return this.ObjectContext.CreateKey(K.TaxKeyName, K.TaxKeyAuthor);

   6:

   7:         }

   8:

   9: // NOTE RIA FUNCTIONS ONLY Accept ONE ARGUMENT WHY?? INTERNAL IMPELEMTATION?

5. Do Another REBUILD so that the Silverlight (or other) Front End can see the newly created function Create_Key.

6. You can now call the function thus

   1: TaxFullDbDom ds= new TaxFullDbDom();

   2: ds.Create_Key(new TaxKey() { TaxKeyName = "Another Test key", TaxKeyID = 0, TaxKeyAuthor = 1 });

Have to supply a TaxKeyID even though it is created automatically on SQL server side because of the fact that all Domain Service functions have to see one argument which as it is a node Tax Key type has to be a valid node Tax Key created. This returns the newly created node number. Data sources and dependent controls using this newly created key would have to be refreshed

And That’s it for now.

Some afterthoughts: Could get the stored procedure to return the created Tax Key Record record and then bind it to the appropriate collection of you so wish on the client.

P.