CODEWITHSUNDEEP
c#asp.net-core.net-coredapper
Dave Matney
Dave Matney

Reputation: 97

Exe sproc with TVP and un-tabled parameters in Dapper?

I have a sproc (we'll call it usp_SaveQuestionAnswer), that inserts/updates an applicant's answer to any given question on an application, that's expecting parameters similar to the following:

@answeredQuestionGuid UNIQUEIDENTIFIER,
@questionGuid UNIQUEIDENTIFIER,
@applicationId INT,
@applicantID INT,
@answerData tvp_AnswerData READONLY

And tvp_AnswerData is defined as:

answerGuid UNIQUEIDENTIFIER,
answerTypeId INT,
answerValue VARCHAR(300),
answerBoolValue BIT

The sproc takes the parameters, and either returns an error, in the form of a string, that the data is invalid (wrong types, null where it shouldn't be, etc.), or returns nothing at all.

The obvious answer to how to do this (according to this) would be DynamicParameters, as .Net Core doesn't support DataTables -- build up a DynamicParameters object for tvp_AnswerData, and then a second DynamicParameters object (called dynamicParamsObject) for the entire list of parameters, passing the tvp_AnswerData DynamicParameters object into that, and then execute the sproc with the following:

var result = _db.Query
(
    "usp_SaveQuestionAnswer",
    dynamicParamsObject,
    commandType: CommandType.StoredProcedure
);

Unfortunately, DynamicParameters doesn't know how to handle a parameter of the DynamicParameters type, as far as I can tell.

Am I doing this wrong? Is there any way Dapper can handle passing TVP alongside other parameters to a sproc, besides writing out the sql command verbatim, or are my options either to have DB rewrite or to use something other than Dapper (such as a SqlCommand)?

Upvotes: 0

Views: 351

Answers (1)

Void Ray
Void Ray

Reputation: 10209

It's not pretty, but it's possible. Below is an example from a random snippet I have:

CREATE TYPE [dbo].[DeptType] AS TABLE(
    [DeptId] [int] NULL,
    [DeptName] [varchar](100) NULL
)
GO

CREATE PROCEDURE [dbo].[InsertDepartment]
@InsertDept_TVP DeptType READONLY
AS
INSERT INTO Department(DepartmentID,DepartmentName)
SELECT * FROM @InsertDept_TVP;
GO

[Test]
public void Test()
{
    var conn = new SqlConnection(@"Data Source=.\sqlexpress;Integrated Security=true; Initial Catalog=foo");
    conn.Open();

    var x = new DataTable();
    x.SetTypeName("DeptType");
    x.Columns.Add("DeptId", typeof(int));
    x.Columns.Add("DeptName", typeof(string));

    var row = x.NewRow();
    row["DeptId"] = 1;
    row["DeptName"] = "Foo";
    x.Rows.Add(row);

    var row2 = x.NewRow();
    row2["DeptId"] = 2;
    row2["DeptName"] = "Bar";
    x.Rows.Add(row2);

    conn.Execute("InsertDepartment", new { InsertDept_TVP = x.AsTableValuedParameter("DeptType") }, commandType: CommandType.StoredProcedure);
}

Upvotes: 2

Related Questions