Thursday, 3 October 2013

EF4 using TableValue type in Storeprocedure

The EF5 has been released with .Net framework 4.5 and now it’s supporting the user defined table valued types as parameter in stored procedure. In earlier versions of Entity framework it was not supported. For e.g. EF4 is widely being used with .net framework 4.0. And these types are not supported in that version. Even some people raise a User voice ticket for the same but none action has been taken and ticket was closed since this feature is shipped with .net 4.5.

For those who can not migrate to 4.5 and are looking for a work around how to work with these kinda stored procedures. I’ve created a work around solution for that. Let’s take some database test objects.

Table:

CREATE TABLE [dbo].[testTable](
[mID] [uniqueidentifier] NOT NULL,
[nID] [int] NOT NULL,
[Desc] [varchar](2000) NOT NULL,
PRIMARY KEY CLUSTERED
(
[mID] ASC,
[nID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

User Defined Table Type:
CREATE TYPE [dbo].[tbltypeTestTable] AS TABLE(
[mID] [uniqueidentifier] NULL,
[nID] [int] NULL,
[Desc] [varchar](2000) NULL
)

Multi Row Insert stored procedure:

CREATE PROCEDURE [dbo].[Insert_TableValuedTypeData]
@Paramtable dbo.[tbltypeTestTable] READONLY

AS
begin

merge dbo.testTable as target
using ( select mID, nID, [Desc]
from @Paramtable) as source
on ( target.mID = source.mID and target.nID = source.nID)
when matched then
update set target.[Desc] = source.[Desc]
when not matched then
insert values (source.mID,source.nID,source.[Desc]);


return @@error
end

Now if you try to generate the model from database this StoredProcedure will not be listed even if you use FunctionImport to call the stored procedure. Now to workaround I’ve created a custom class that executes this stored procedure as a Sql statement from DBContext object.

 

Extension method:
public static class DbContextExtension
{
// Extension method of DbContext object
public static void ExecuteStoreProcedure(this DbContext @this, string storeProcName, params object[] parameters)
{
string command = "EXEC " + storeProcName + " @Paramtable";

@this.Database.ExecuteSqlCommand(command, parameters);
}
}

Now we’ll look at the class that will prepare the data and pass it to this extension method to execute.

 

Callee:
public class mnEntity
{
public bool AddMultipleRecords()
{

Test_Entities testEntities = new Test_Entities();

var dt = new DataTable();
dt.Columns.Add("mID");
dt.Columns.Add("nID");
dt.Columns.Add("Desc");

dt.Rows.Add(Guid.NewGuid(), 1, "Test2");
dt.Rows.Add(Guid.NewGuid(), 2, "Test2");
dt.Rows.Add(Guid.NewGuid(), 3, "Test2");
dt.Rows.Add(Guid.NewGuid(), 4, "Test2");
dt.Rows.Add(Guid.NewGuid(), 5, "Test2");

var items = new SqlParameter("Paramtable", SqlDbType.Structured);
items.Value = dt;
items.TypeName = "dbo.tbltypeTestTable";

testEntities.ExecuteStoreProcedure("Insert_TableValuedTypeData", items);

return true;
}

Now let’s test this method if it work’s and call the SP with multiple values insertion:

 

Main:
static void Main(string[] args)
{
mnEntity entity = new mnEntity();
try
{
entity.AddMultipleRecords();
Console.WriteLine("Success!!!");
}
catch (Exception exception)
{
Console.WriteLine(exception);
}

Console.Read();
}

Output:

 

image

image

Voila!!!!!! It worked like charm.


If you have any comments/suggestions please feel free to drop.