Access Stored Procedure With User Defined Data Type Using Entity Framework

During your professional career, while using Entity Framework, you might face situations where you need to insert multiple data at a time.

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

Now, a question raised in your mind is probably "Is it possible to access procedures in Entity Framework that are using custom table type?"

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 table type
  1. //Custom data type  
  2. -- ================================  
  3. -- Create User-defined Table Type  
  4. -- ================================  
  5. --DROP TYPE dbo.Tvp_Employee  
  6. CREATE TYPE dbo.Tvp_Employee AS TABLE  
  7. (  
  8.    Name varchar(50) NULL,  
  9.    Salary numeric(18,0) Null  
  10. )  
  11. GO  
Step 2

Create stored procedure using the custom data type you have created recently.
  1. //Create stored procedure  
  2. -- =============================================  
  3. -- Author: Mitesh Gadhiya  
  4. -- Create date: 15-jul-017  
  5. -- Description: Demo for Inserting data with Custom table Type  
  6. -- =============================================  
  7. --DROP PROC Proc_insertemployee  
  8. CREATE PROCEDURE Proc_insertemployee (@tbl_Employee TVP_EMPLOYEE readonly)  
  9. AS  
  10. BEGIN  
  11. BEGIN try  
  12. -- Insert statements for procedure here  
  13. INSERT INTO tbl_employee  
  14. (NAME,  
  15. salary)  
  16. SELECT NAME,  
  17. salary  
  18. FROM @tbl_Employee  
  19. END try  
  20. BEGIN catch  
  21. DECLARE @ErrorNumber INT  
  22. DECLARE @ErrorMessage VARCHAR(2000)  
  23. DECLARE @ErrorSeverity INT  
  24. DECLARE @ErrorState INT  
  25. SELECT @ErrorNumber = Error_number(),  
  26. @ErrorMessage = 'Error occured at time of inserting'  
  27. + Error_message(),  
  28. @Errorseverity = Error_severity(),  
  29. @ErrorState = Error_state()  
  30. RAISERROR (@Errormessage,@ErrorSeverity,@ErrorState)  
  31. END catch  
  32. END  
  33. 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 a new class to define Custom data type.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using EntityFrameworkExtras.EF6;  
  6. namespace EFCustomDatatype.Entity.CustomDataTypes {  
  7.     [UserDefinedTableType("Tvp_Employee")]  
  8.     public class Tvp_Employee {  
  9.         [UserDefinedTableTypeColumn(1)]  
  10.         public string Name {  
  11.             get;  
  12.             set;  
  13.         }  
  14.         [UserDefinedTableTypeColumn(2)]  
  15.         public decimal Salary {  
  16.             get;  
  17.             set;  
  18.         }  
  19.     }  
  20. }  
Step 5

Add a new class to define procedure that is using custom data type.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using EntityFrameworkExtras.EF6;  
  6. using System.Data;  
  7. namespace EFCustomDatatype.Entity.CustomDataTypes {  
  8.     [StoredProcedure("Proc_insertemployee")]  
  9.     public class Proc_insertemployee {  
  10.         [StoredProcedureParameter(SqlDbType.Udt, ParameterName = "tbl_Employee")]  
  11.         public List < tvp_employee > tbl_Employee {  
  12.             get;  
  13.             set;  
  14.         }  
  15.     }  
  16. }  
Step 6

Call procedure from Controller and insert data into database.



Code snippet for calling store procedure from application.
  1. using EFCustomDatatype.Entity.CustomDataTypes;  
  2. using EntityFrameworkExtras.EF6;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Data;  
  6. using System.Data.Entity;  
  7. using System.Linq;  
  8. using System.Web;  
  9. using System.Web.Mvc;  
  10. namespace EFCustomDatatype.Controllers {  
  11.     public class EFCustomDatatypeController: Controller {  
  12.         Entity.EFCustomDatatypeEntities objEFCustomDatatype;  
  13.         public EFCustomDatatypeController() {  
  14.             objEFCustomDatatype = new Entity.EFCustomDatatypeEntities();  
  15.         }  
  16.         // GET: EFCustomDatatype  
  17.         public ActionResult Index() {  
  18.             try {  
  19.                 var procedure = new Proc_insertemployee() {  
  20.                     tbl_Employee = GetEmpData()  
  21.                 };  
  22.                 objEFCustomDatatype.Database.ExecuteStoredProcedure(procedure);  
  23.             } catch (Exception) {  
  24.                 throw;  
  25.             }  
  26.             return View();  
  27.         }  
  28.         public List < tvp_employee > GetEmpData() {  
  29.             List < tvp_employee > lstEmp = new List < tvp_employee > ();  
  30.             for (int i = 0; i < 5; i++) {  
  31.                 lstEmp.Add(new Tvp_Employee {  
  32.                     Name = "Mitesh_" + i.ToString(),  
  33.                         Salary = (1000 * (i + 1))  
  34.                 });  
  35.             }  
  36.             return lstEmp;  
  37.         }  
  38.     }  
  39. }  
And we are done..!

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