Stored procedure input (table) type

[RedRiverWeb]

Hi,

Is there a way to map the input (User-Defined Table) types to classes instead of DataTables?

Here is the test case:

CREATE TYPE dbo.MyType AS TABLE(
    ID int,
    Title nvarchar(20)
)
GO

CREATE PROCEDURE dbo.MyProc
    @UserId int
    , @MyData dbo.MyType readonly
AS
SET NOCOUNT ON;

SELECT 1

Here is the result:

public System.Collections.Generic.List<MyProc_Result> MyProc(
    int? userId, System.Data.DataTable myData)
{
    int procResult;
    return MyProc(userId, myData, out procResult);
}

public System.Collections.Generic.List<MyProc_Result> MyProc(
    int? userId, System.Data.DataTable myData, out int procResult)
{
    var userIdParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@UserId", SqlDbType = System.Data.SqlDbType.Int, Direction = System.Data.ParameterDirection.Input, Value = userId.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if (!userId.HasValue)
        userIdParam.Value = System.DBNull.Value;

    var myDataParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@MyData", SqlDbType = System.Data.SqlDbType.Structured, Direction = System.Data.ParameterDirection.Input, Value = myData, TypeName = "dbo.MyType" };
    if (myDataParam.Value == null)
        myDataParam.Value = System.DBNull.Value;

    var procResultParam = new System.Data.SqlClient.SqlParameter { ParameterName = "@procResult", SqlDbType = System.Data.SqlDbType.Int, Direction = System.Data.ParameterDirection.Output };
    var procResultData = Database.SqlQuery<MyProc_Result>("EXEC @procResult = [dbo].[MyProc] @UserId, @MyData", userIdParam, myDataParam, procResultParam).ToList();

    procResult = (int) procResultParam.Value;
    return procResultData;
}

[sjh37]

That's a nice idea. EF.Core is first on my list, so once that is out of the way, I can work on adding useful things like this.

[R-Norman]

Looking forward to this feature since I am using table types pretty heavily in a current project using reverse poco. For now, I use extensions for list<> of various types that creates matching typed DataTables for passing to the SPs. Still means syncing the definitions on any change but keeps it in one place.

[thomaskirk]

This enhancement would be very appreciated and useful. I hope to see it someday.