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 a custom table type and using it with stored procedure.

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

So the answer is YES, It is possible using EntityFrameworkExtras.

Now let’s have a look at how we can implement it.

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..:)