Entity Framework (DB First) EDMX not support data table as stored procedure parameter

Question

User46076047 posted

I realized that Entity Framework (DB First) EDMX does not support data table as stored procedure parameter.

Is EDMX is kind od "old technology" that i better to not use with? Is there any work around for supporting DataTable as SP Param?

Answer 1

User1120430333 posted

I realized that Entity Framework (DB First) EDMX does not support data table as stored procedure parameter.

Is EDMX is kind od "old technology" that i better to not use with? Is there any work around for supporting DataTable as SP Param?

You have it backwards. What is the Ford Model T is the datatable, and no modem ORM that I know about like the Entity Framework no matter what EF version deals with a datatable. The O in Object Relational Mapping  deals with custom objects.

You can also pass in a XML into as a parameter into a sproc too. 

https://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html

Hey, you XML serialize the datatable. :)

Answer 2

User1400794712 posted

Hi OmTechGuy,

Do you mean that you want to use datatable as parameter in stored procedure? It's feasible. It needs us to create a user defined table type object. Then using this object as parameter in stored procedure. I made a demo, please refer to it.

T-SQL

CREATE TABLE [dbo].[Users] (
    [UId]   INT            IDENTITY (1, 1) NOT NULL,
    [UName] NVARCHAR (MAX) NULL,
    [Email] NVARCHAR (30)  NOT NULL,
    CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED ([UId] ASC)
);
CREATE TYPE [dbo].[UsersTableType] AS TABLE(
    [UName] NVARCHAR (MAX) NULL,
    [Email] NVARCHAR (30)  NOT NULL
)

Stored procedure

CREATE PROCEDURE [dbo].[User_Insert]
 @param1 UsersTableType readonly
AS
BEGIN
 SET NOCOUNT ON;
    -- Insert statements for procedure here
 INSERT INTO Users(UName, Email)
  SELECT UName, Email
  FROM @param1
END

After db first:

public ActionResult Index()
{
    using (var DB = new TestEntities())
    {
        DataTable _DataTable = GenerateDataTable();
        SqlParameter Parameter = new SqlParameter("@param1", _DataTable);
        Parameter.TypeName = "dbo.UsersTableType";
        DB.Database.ExecuteSqlCommand("exec User_Insert @param1", Parameter);
    }
    return View();
}
private DataTable GenerateDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("UName");
    dt.Columns.Add("Email");
    dt.Rows.Add("Andy", "qwefe");
    dt.Rows.Add("Mary", "adsafsf");
    dt.Rows.Add("Jacj", "sdsa");

    return dt;
}

Best Regards,
Daisy

Answer 3

User46076047 posted

Thank you very much for the detailed answer.  i may use this method but once i use it i will not longer have one place for all the stored procedure of my app because i manage them now with the EDMX file. So some of the stored procedure will be part of the edmx file and some not.., do you have any idea how can i still manage that kind of SPs (with datatable params) using the edmx file?

Answer 4

User1120430333 posted

Thank you very much for the detailed answer.  i may use this method but once i use it i will not longer have one place for all the stored procedure of my app because i manage them now with the EDMX file. So some of the stored procedure will be part of the edmx file and some not.., do you have any idea how can i still manage that kind of SPs (with datatable params) using the edmx file?

You can use the EF backdoor and call the sproc using the datatable using traditional ADO.NET, SQL Command objects and a datareader However, how to do this is not straight forward on EF 6 to obtain the SQL connection from the EF connection, but it can be done.

https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/