Access stored procedure with user defined data type using Entity framework

During your professional career when you are using entity framework you might be facing situations where you need to insert multiple data at a time.

Well inserting multiple data we can achieve in SQL by creating custom table type and using it with stored procedure.

Now the common question raise in your mind that is it possible to access procedures in Entity framework those are using custom table type?

So answer is YES, It is possible using EntityFrameworkExtras.

Now lets have a look how can we implement.

Step-1. Create custom data type in your database

//Custom data type
-- ================================
-- Create User-defined Table Type
-- ================================
--DROP TYPE dbo.Tvp_Employee
CREATE TYPE dbo.Tvp_Employee AS TABLE
(
Name varchar(50) NULL,
Salary numeric(18,0) Null
)
GO


Step-2. Create Stored procedure using custom data type that you have created recently


























Stored procedure

//Create stored procedure
-- =============================================
-- Author: Mitesh Gadhiya
-- Create date: 15-jul-017
-- Description: Demo for Inserting data with Custom table Type
-- =============================================
--DROP PROC Proc_insertemployee
CREATE PROCEDURE Proc_insertemployee (@tbl_Employee TVP_EMPLOYEE readonly)
AS
BEGIN
BEGIN try
-- Insert statements for procedure here
INSERT INTO tbl_employee
(NAME,
salary)
SELECT NAME,
salary
FROM @tbl_Employee
END try

BEGIN catch
DECLARE @ErrorNumber INT
DECLARE @ErrorMessage VARCHAR(2000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

SELECT @ErrorNumber = Error_number(),
@ErrorMessage = 'Error occured at time of inserting'
+ Error_message(),
@Errorseverity = Error_severity(),
@ErrorState = Error_state()

RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)
END catch
END

go


Step-3. After creating procedure in your visual studio’s project add EntityFrameworkExtras using nuget package manager


























Step-4. After installing nuget package successfully add new class to define Custom data type




















Tvp_Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using EntityFrameworkExtras.EF6;
namespace EFCustomDatatype.Entity.CustomDataTypes
{
[UserDefinedTableType("Tvp_Employee")] public class Tvp_Employee
{
[UserDefinedTableTypeColumn(1)] public string Name { get; set; }

[UserDefinedTableTypeColumn(2)] public decimal Salary { get; set; }
}
}


Step-5. Add new class to define procedure that is using custom data type

















Proc_insertemployee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using EntityFrameworkExtras.EF6;
using System.Data;

namespace EFCustomDatatype.Entity.CustomDataTypes
{
[StoredProcedure("Proc_insertemployee")] public class Proc_insertemployee
{
[StoredProcedureParameter(SqlDbType.Udt, ParameterName = "tbl_Employee")] public List tbl_Employee { get; set; }
}
}


Step-6. Call procedure from controller and insert data into database






















EFCustomDatatypeController

using EFCustomDatatype.Entity.CustomDataTypes;
using EntityFrameworkExtras.EF6;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace EFCustomDatatype.Controllers
{
public class EFCustomDatatypeController : Controller
{
Entity.EFCustomDatatypeEntities objEFCustomDatatype;
public EFCustomDatatypeController()
{
objEFCustomDatatype = new Entity.EFCustomDatatypeEntities();
}
// GET: EFCustomDatatype
public ActionResult Index()
{
try
{
var procedure = new Proc_insertemployee()
{
tbl_Employee = GetEmpData()
};
objEFCustomDatatype.Database.ExecuteStoredProcedure(procedure);
}
catch (Exception)
{
throw;
}
return View();
}
public List GetEmpData()
{
List lstEmp = new List();

for (int i = 0; i < 5; i++)
{
lstEmp.Add(new Tvp_Employee { Name = "Mitesh_" + i.ToString(),
Salary = (1000 * (i + 1)) });
}
return lstEmp;
}
}
}

And we are done..!

Cheers…Hope this will be helpful to you..:)

Written by Mitesh Gadhiya