Dapper - Table-Valued Parameter with string query

Question

I need to use Table Valued Parameter with string query

I have the following code

string query = "SELECT * FROM Tabla1 T1 INNER JOIN @listItems T2 ON T2.Id = T1.Id";
var results = sqlConnection.Query(query , new TableValuedParameter<string>("@listItems", "string_list_Type", valuesList));

the variable valueList is a List

The execution give me a error: "Could not find stored procedure"

Is posible usa table valued parameters without use procedure ?

thanks regards

What you have posted does not even compile. "The non-generic type 'Dapper.TableValuedParameter' cannot be used with type arguments" — Darek
– Darek, Commented Aug 21, 2015 at 17:13
Also, it seems that TableValuedParameter is an internal class so you can't create a new instance of it directly. — Darek
– Darek, Commented Aug 21, 2015 at 17:24
@Darek they probably used one of the generic TableValuedParameter implementations. I have something similar and run into the exact same error. — Tom Lint
– Tom Lint, Commented Dec 14, 2021 at 14:21

Darek · Accepted Answer · 2015-08-21 17:08:55Z

28

Without seeing full code sample it is hard to tell what the problem could be. Consider this, which executes perfectly fine:

First, TVP definition

CREATE TYPE [dbo].[TVPSTRING] AS TABLE(
    [VALUE] NVARCHAR(MAX) NULL
)

Then sample Dapper code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace CPTVP
{
    class Program
    {
        static void Main(string[] args)
        {
            var dt = new DataTable();
            dt.Columns.Add("StringsStringsEverywhere", typeof(string));
            foreach (int i in Enumerable.Range(0,10))
            {
                dt.Rows.Add(string.Format("{0:0000}", i));
            }

            using (var conn = new SqlConnection("Data Source=.;Initial Catalog=Scratch;Integrated Security=true;"))
            {
                Console.WriteLine(String.Join("\n", conn.Query<string>("SELECT * FROM @tvpstr", new {tvpstr=dt.AsTableValuedParameter("dbo.TVPSTRING")})));
            }
        }
    }
}

answered Aug 21, 2015 at 17:08

Darek

4,80534 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

galmok Over a year ago

What about a TVP with multiple columns?

Tom Lint · Accepted Answer · 2023-08-30 12:48:25Z

Assuming the OP used the same generic TableValuedParameter class I did, the solution lies in its implementation of the SqlMapper.IDynamicParameters AddParameters method, and the fact that it explicitly sets the CommandType of the IDbCommand to CommandType.StoredProcedure as shown below:

public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
    var sqlCommand = (SqlCommand)command;
    sqlCommand.CommandType = CommandType.StoredProcedure;
    var list = _values.Select(s =>
    {
        var sqlDataRecord = new SqlDataRecord(_tvpDefinition);
        _actionMapping(sqlDataRecord, s);
        return sqlDataRecord;
    }).ToList();

    if (list.Count == 0)
    {
        return;
    }

    var sqlParameter = sqlCommand.Parameters.Add(_name, SqlDbType.Structured);
    sqlParameter.Direction = ParameterDirection.Input;
    sqlParameter.TypeName = _typeName;
    sqlParameter.Value = list;
}

By removing the line that explicitly sets the CommandType, the TableValuedParameter class now works for normal queries, too.

Collectives™ on Stack Overflow

Dapper - Table-Valued Parameter with string query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related