Usually, one of the key point of using Entity Framework is to reduce the need to write SQL.

Sometimes, however, you want to use stored procedures, returning to write in SQL.

Maybe you are working with a legacy database or maybe you simple like this approach.

In this tutorial we will see how to implement a stored procedure, used for CRUD operations, and access the return value of the operation.

The database

We will refer to a very simple database structure.

<adsense></adsense>

Create the Stored Procedure

Open the connection to your database in Server Explorer and select New Query

Now, it’s time to create the stored procedure using SQL

CREATE PROCEDURE [ dbo ] . [ GetRobotDogsByLocation ]

-- Parameters

@Location nvarchar ( 100 )

AS

BEGIN

SET NOCOUNT ON ;



SELECT d . RobotDogId , d . Name , d . Armed , f . Location

FROM RobotDogs d

JOIN RobotFactories f ON d . RobotFactoryId = f . RobotFactoryId

WHERE f . Location LIKE @Location

END

Execute the query and the procedure will be created.

Import the procedure

Now, the stored procedure is in the database, and we want to use it in our context class.

Let’s consider two different methods, using the database first or the code first approach.

Database First

Open the edmx file and Right click -> Update Model from Database..

(If you don’t have an edmx file yet, you should create a ADO.NET object first)

Select Stored Procedures and Functions and click Finish:

Now, if you look in your context class:

You can see that a new method has been added:

public virtual ObjectResult GetRobotDogsByLocation ( string location )

{

var locationParameter = location != null ?

( "Location" , location ) :

( "Location" , typeof ( string ) ) ;



return ( ( IObjectContextAdapter ) this ) . ObjectContext . ExecuteFunction ( "GetRobotDogsByLocation" , locationParameter ) ;

} ObjectResult GetRobotDogsByLocationlocationlocationParameterlocation new ObjectParameter, location new ObjectParameterIObjectContextAdapter, locationParameter

You can also find a new file, GetRobotDogsByLocation_Result, which reflects the return type of the stored procedure (in this case we just returned the RobotDog fields).

This newly created stored procedure trembles to be used :

using ( RobotIndustryEntities context = ( ) )

{

var dogs = context . GetRobotDogsByLocation ( "Texas" ) ;

} RobotIndustryEntities context new RobotIndustryEntitiesdogscontext

Code first

When we call the stored procedure, we get a return value. We need a class that maps to that return value. In the database first approach that class is automatically generated (GetRobotDogsByLocation_Result).

In the code first approach we have to create a class for that purpouse.

public class RobotDogLocation

{

public int RobtDogID { get ; set ; }

public string Name { get ; set ; }

public bool Armed { get ; set ; }

public string Location { get ; set ; }

}

And we are ready to use the stored procedure!

using ( var context = ( ) )

{

var locationParameter = ( "@Location" , "Texas" ) ;

var dogs = context . Database . SqlQuery ( "GetRobotDogsByLocation" , locationParameter ) ;

} context new RobotContextlocationParameter new SqlParameterdogscontext, locationParameter

Return a scalar value

Not all the stored procedures may return an entity type.

For example, suppose that you want to return the number of robot dogs that are armed.

A simple stored procedure may be:

CREATE PROCEDURE [ dbo ] . [ GetArmedRobotDogs ]

AS

BEGIN

SET NOCOUNT ON ;



SELECT COUNT ( * )

FROM RobotDogs

WHERE Armed = 1

END

Repeat the previous steps to import this powerful procedure in your code.

Now, returning a scalar value (an int in this case) is really simple:

using ( var context = ( ) )

{

var armedDogs = context . Database . SqlQuery ( "GetArmedRobotDogs" ) ;

} context new RobotContextarmedDogscontext

The SqlQuery method takes a Type which states the return type of the query.

CRUD Operations

While in the previous procedures we only returned some data, we can also use them for other CRUD operations (Create, Read, Update, Delete).

Let’s make some examples.

Create procedure

CREATE PROCEDURE [ dbo ] . [ CreateRobotDog ]

-- Parameters

@Name VARCHAR ( 50 ) ,

@Armed bit ,

@RobotFactoryId INT

AS

BEGIN

SET NOCOUNT ON ;



INSERT INTO RobotDogs ( [ Name ] , [ Armed ] , [ RobotFactoryId ] )

VALUES ( @Name , @Armed , @RobotFactoryId )



SELECT SCOPE_IDENTITY ( ) AS RobotDogId

END

The SCOPE_IDENTITY function returns the last identity value that has been inserted. In this case it’s the RobotDogId, which is automatically generated.

This may be useful if we want to use the object inserted for further operations.

CREATE PROCEDURE [ dbo ] . [ UpdateRobotDog ]

-- Parameters

@RobotDogId INT ,

@Name VARCHAR ( 50 ) ,

@Armed bit ,

@RobotFactoryId INT

AS

BEGIN

SET NOCOUNT ON ;



UPDATE RobotDogs

SET Name = @Name , Armed = @Armed , RobotFactoryId = @RobotFactoryId

WHERE RobotDogId = @RobotDogId

END

For updating the fierce beast we need a way to identify it.

For that, we pass to the procedure the RobotDogId, which is the primary key for the RobotDogs table.

Delete procedure

CREATE PROCEDURE [ dbo ] . [ DeleteRobotDog ]

-- Parameters

@RobotDogId INT

AS

BEGIN

SET NOCOUNT ON ;



DELETE FROM RobotDogs

WHERE RobotDogId = @RobotDogId

END

We identify the dog to be removed passing the RobotDogId key.

Using the CRUD operations

Database First Approach

After importing the procedures (using the Update Model from Database as before), we are ready to use them with the Contexts methods, passing the desired parameters.

using ( var context = ( ) )

{

//This creates the new entity and returns the generated Id

var studentId = context . CreateRobotDog ( "Marbot" , true , 2 ) ;



//This deletes the entity.

context . DeleteRobotDog ( robotId ) ;

} context new RobotContextstudentIdcontextcontextrobotId

DeleteRobotDog return an int, which indicates the number of rows affected by the query.



Code First Approach

We don’t have the methods in our context class, but we can directly call the function, passing the parameters:

using ( var context = ( ) )

{

db . Database . SqlQuery ( "CreateRobotDog @Name, @Armed, @RobotFactoryId" ,

( "Name" , "Marbot" ) ,

( "Armed" , true ) ,

( "RobotFactoryId" , 2 ) ) ;

} context new RobotContextdb new SqlParameter new SqlParameter new SqlParameter

Conclusion

That was just a quick glance in the world of Stored Procedures.

Most of the times you would rather use something like LINQ to make your job easier, but sometimes you still have to use stored procedures.

In any case, they could be a powerful ally in the database management.