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

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.