Dapper - Table-Valued Parameter with string query

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

1 Answer

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")}))); } } } }