How to pass datatable from c# class to MSSQL Server stored procedure using Entity Framework Core 5.0.1.

Question

I have a data import application written in .Net Framework to migrate to .Net Core. The application uses a c# class to pass a datatable as an input parameter to a MSSQL server stored procedure. The code in EF6 works fine and is pasted below.

    public void InsertExcelPrescriptions(int excelMasterId, DataTable dt)
    {

        var p1 = new SqlParameter { ParameterName = "IN_intExcelMasterId", Value = excelMasterId };
        var p2 = new SqlParameter { ParameterName = "IN_tabRecords"
            , Value = dt, SqlDbType = SqlDbType.Structured, TypeName = "dbo.HDS2_PrescriptionType" };

        string sql = string.Format("dbo.HDS2_spInsertPrescriptionsFromExcel @{0}, @{1}", p1.ParameterName, p2.ParameterName);

        var db = new Db();
        db.Database.ExecuteSqlCommand(sql, p1, p2);
        return;
    }

Where Db inherits from DbContext.

I have tried to emulate this behaviour in Entity Framework Core without success. I tried using FromSqlInterpolated but the code didn't understand the input parameter was a datatable rather than a string or numeric, etc.

I also tried to running a raw SQL command

 using (var command = new Db().Database.GetDbConnection().CreateCommand())
        {
           ....
        }

Although that didn't error, nothing was inserted in the MSSQL Server database table.

I just want to insert some rows into a database table by passing a datatable as a user defined table type to a stored procedure.

Answer 1

Hi WilliamsonStephen-8510,
In EntityFrameworkCore, there are two methods for executing Stored Procedures:

1. Query for records from a database - FromSqlRaw().
2. Execute a command (typically DML) on the database - ExecuteSqlRaw() or the awaitable ExecuteSqlRawAsync().
   The method takes two parameters: an SQL string and a series of the parameters to pass with this SQL statement.
   And here is a code example that Jimbo Jones provided, maybe helpful to you.
   Best Regards,
   Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try EF Core Power Tools, it maps stored procedures for you, also with TVP parameters

Answer 3

Hello @Williamson, Stephen

Here is an example using EF Core 5 inserting a new record into a table and returning the new primary key. Full source in the following GitHub repository.

Table definition

Stored procedure

Insert stored procedure in SQL-Server database containing the table above.

CREATE PROCEDURE [dbo].[uspInsertCategory]    
    @CategoryName NVARCHAR(15),   
    @Description ntext,   
    @Identity INT OUT   
AS   
BEGIN   
    -- SET NOCOUNT ON added to prevent extra result sets from   
    -- interfering with SELECT statements.   
    SET NOCOUNT ON;   
INSERT INTO dbo.Categories (CategoryName, Description) VALUES (@CategoryName, @Description);  
SET @Identity = SCOPE_IDENTITY()   
   
END   

Code

Code to insert a new record. Pay attention to the second parameter which after the command is finished if successful contains the new primary key where the parameter is setup as Output directions.

using System;  
using System.Collections.Generic;  
using System.Data;  
using System.Diagnostics;  
using System.Text;  
using Microsoft.Data.SqlClient;  
using Microsoft.EntityFrameworkCore;  
using StoredProcedureInsertNewCategory.Context;  
  
namespace StoredProcedureInsertNewCategory.Classes  
{  
    public class Operations  
    {  
        /// <summary>  
        /// Insert new category  
        /// </summary>  
        /// <param name="categoryName">Category name required</param>  
        /// <param name="description">Category description</param>  
        public static void InsertCategory(string categoryName, string description)  
        {  
            using var context = new NorthWindContext();  
            try  
            {  
                var parameters = new[] {  
                    new SqlParameter("@CategoryName", SqlDbType.NVarChar)  
                    {  
                        Direction = ParameterDirection.Input,   
                        Value = categoryName  
                    },  
                    new SqlParameter("@Description", SqlDbType.NText)  
                    {  
                        Direction = ParameterDirection.Input,   
                        Value = description  
                    },  
                    new SqlParameter("@Identity", SqlDbType.Int)  
                    {  
                        Direction = ParameterDirection.Output,   
                        Value = 0  
                    }  
  
                };  
                context.Database.ExecuteSqlRaw(  
                    "exec uspInsertCategory @CategoryName,@Description,@Identity out", parameters:   
                    parameters);  
  
                var newPrimaryKey = Convert.ToInt32(parameters[2].Value);  
                Debug.WriteLine(newPrimaryKey.ToString());  
  
  
            }  
            catch (Exception ex)  
            {  
                Debug.WriteLine(ex.Message);  
            }  
        }  
    }  
}  

This is the model for above

// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>  
using System;  
using System.Collections.Generic;  
using System.ComponentModel.DataAnnotations;  
  
#nullable disable  
  
namespace StoredProcedureInsertNewCategory.Models  
{  
    public partial class Category  
    {  
        /// <summary>  
        /// Primary key  
        /// </summary>  
        [Required]  
        public int CategoryId { get; set; }  
        public string CategoryName { get; set; }  
        public string Description { get; set; }  
        public byte[] Picture { get; set; }  
    }  
}  

Answer 4

Thanks all for the suggestions. I was unable to get a datatable to work directly with EF Core but I could use a SqlCommand which is available on Microsoft.EntityFrameworCore.SqlServer. The code below seems to work

        using (SqlConnection con = new SqlConnection("My connection string"))
        {
            using (SqlCommand cmd = new SqlCommand("dbo.HDS2_spInsertPrescriptionsFromExcel", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("IN_intExcelMasterId", SqlDbType.Int).Value = excelMasterId;
                cmd.Parameters.Add("IN_tabRecords", SqlDbType.Structured).Value = dt; // the datatable
                con.Open();
                cmd.ExecuteNonQuery();
            }
        }