Monday, April 9, 2012

How to use Entity Framework 3.5 with Stored Procedures

Entity Framework with .NET 3.5 did not actually reduce much pain which existed with ADO.NET 2.0. But you will appreciate the fact that we can easily sync the stored procedures and tables which we might have created using SQL Server 2005 or higher.

Just look at how simply you can link your stored procedure created.

Here's my sproc ValidateUser:

Now i am gonna import this sproc into my Entity Library. First step is to create the Entity class itself as shown in below steps:

 [Step 1]

[Step 2]

In step 2, you are seeing that the connection string is already set for the entity which includes the connection string for the database. Good news is - we don't need to create a connection string again because the entity will handle it for you. So lets continue.

[Step 3]

So now you are seeing the Table and the sproc available for you to be used. But we are not done yet as a little import thing is still left. So i check the table and the sproc and do a Finish click. Keep reading.

 My table is imported now but i need to still import the sproc which can be done like shown below.

[Step 1]

[Step 2]

Choose a name for your imported sproc. Also i would keep the "Returns a collection of" as None as the sproc is not returning any column. But i have kept an output parameter in the sproc which can be used as a return for the entity method which we have defined now, ValidateUser.


[Step 3]


[Step 4]

So you can see the imported sproc as a function imported now in the Model Browser. Now the sproc is ready to be used in our C# code.

This is one of the ways of using EF in .NET 3.5 and it looks pretty ugly because of the number of lines of code required to create the command parameters etc. But do not worry. This has been improved greatly in .NET 4 where you can use the imported function directly as a method which can be called in your code. All you need to do is to pass the parameters and expect the return type. EF with .NET 3.5 only does it if the return type is an entity like Users which we created. Otherwise just live with this approach for the time being if your development framework is still .NET 3.5.

PS: Looking forward to a better solution from fellow enthusiasts and developers.

No comments: