Using the 'OUT' parameter in stored procedures in EF4/Silverlight

The Admin 7/22/2010 3:59:00 PM

This had me foxed this evening...

I had a stored procedure that performed some wacky positioning on a document stored in a database.  The detail is not important, except to say that I have a stored procedure that does wacky stuff on some rows, but then returns the 'result' as the row that has just been added.

What I needed was for the row to be returned as the result set, but also a return parameter with the ID of the just-inserted record.

All over the net bods are saying that EF4 doesn't support output params and this is simply not the case!  Believe me...

Below I show the stored procedure and the silverlight code for reference in case anyone else needs a solution.   Hope it helps!

Guff follows...

 

In my DomainService I have the following:

 

public IQueryable<docsection> InsertDocsectionAtPosition( int insertAtPosition, bool insertAbove, int documentunique, int sectionid)    System.Data.Objects.ObjectParameter x = new System.Data.Objects.ObjectParameter("DocSectionID", typeof(int));     var ids = this.ObjectContext.spInsertDocsection(insertAtPosition, insertAbove, documentunique, x);     return this.ObjectContext.docsections.Where( ds => ds.docsection_id == sectionid);

}

 

 

 

{

 

 

    sectionid = ids.FirstOrDefault().docsection_id;

 

 

In my Silverlight app I call the stored procedure using the following code:

 

{

 

private void button_manual_Click(object sender, RoutedEventArgs e)docsection selectedsection = docsectionsRadGridView.SelectedItem as docsection;

 

 

 

 

 

 

 

loadop_docsection.Completed +=

 

 

{

 

 

{

 

{

 

}

 

DomainServiceDocuments ourdocsections = documentDomainDataSource.DomainContext as DomainServiceDocuments;int resultid = -1;LoadOperation<docsection> loadop_docsection = ourdocsections.Load(ourdocsections.InsertDocsectionAtPositionQuery((int)selectedsection.docsection_position, false, selectedsection.document_unique, resultid));new EventHandler(loadop_docsection_Completed);}void loadop_docsection_Completed(object sender, EventArgs e)LoadOperation<docsection> loadop_docsections = sender as LoadOperation<docsection>;if (!loadop_docsections.HasError)foreach (docsection update_ds in loadop_docsections.Entities)MessageBox.Show("added docsection " + update_ds.docsection_position.ToString());

else MessageBox.Show(loadop_docsections.Error.ToString());

}

}

 

};

 

{

 

}

And the stored procedure that does all the work:

USE

[esg5]

GO

/****** Object: StoredProcedure [dbo].[InsertSection] Script Date: 07/22/2010 23:02:29 ******/

SET

GO

SET

GO

ANSI_NULLS ON QUOTED_IDENTIFIER ON

-- =============================================

-- Author:

-- Create date:

-- Description:

-- =============================================

ALTER

PROCEDURE [dbo].[InsertSection] (

 

-- Add the parameters for the stored procedure here

@InsertAtPosition

int,

@InsertAbove

bit,

@DocumentUnique

int,

@DocSectionID

int OUTPUT

)

BEGIN

AS

 

 

 

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

 

-- interfering with SELECT statements.

 

declare @DocSectionParent int;

 

declare @DocSectionLayout int;

 

 

SET NOCOUNT ON;

 

SET FMTONLY OFF

 

 

SET @DocSectionParent = (SELECT TOP 1 docsection_parent from DocSection where ((docsection_position=@InsertAtPosition) and (document_unique=@DocumentUnique)));

 

SET @DocSectionLayout = (SELECT TOP 1 docsection_layout from DocSection where ((docsection_position=@InsertAtPosition) and (document_unique=@DocumentUnique)));

 

 

BEGIN TRAN;

 

 

IF (@InsertAbove = 0)

 

 

begin UPDATE DocSection SET docsection_position=docsection_position+1 where ((docsection_position>@InsertAtPosition) and (document_unique=@DocumentUnique));

 

 

INSERT INTO DocSection (document_unique, docsection_layout, docsection_parent, docsection_position, docsection_text) VALUES (@DocumentUnique, @DocSectionLayout, @DocSectionParent, @InsertAtPosition+1, 'INserted below Edit your section here... xxx'); end

 

ELSE

 

BEGIN

 

UPDATE DocSection SET docsection_position=docsection_position+1 where ((docsection_position>=@InsertAtPosition) and (document_unique=@DocumentUnique));

 

 

INSERT INTO DocSection (document_unique, docsection_layout, docsection_parent, docsection_position, docsection_text) VALUES (@DocumentUnique, @DocSectionLayout, @DocSectionParent, @InsertAtPosition, 'Inserted above Edit your section here...'); END;

COMMIT

TRAN;

--get identity of newrecord

 

SET @DocSectionID = CONVERT(int, IDENT_CURRENT('DocSection'));

 

 

--WAITFOR DELAY '000:00:5';

 

--get INSERTED record

 

SELECT TOP 1 * FROM docsection where docsection_id = @DocSectionID;

--commit

END

 

 

 

 

 

Comments
No Comments

Leave a Comment