Dapper: inserting rows into table variables

Question

i have lately been using dapper and all in all had no problems with it except when using table variables.

For demonstration, I use a modified example from this thread.

This code works without problems:

int tally = connection.Execute(
    "create table #t(Name nvarchar(max), Age int)\n" +
    "insert #t (Name,Age) values(@Name, @Age)", new[]
    {
        new {Age = 1, Name = "sam"},
        new {Age = 2, Name = "bob"}
    });

But this doesn't:

int tally = connection.Execute(
    "create table @t(Name nvarchar(max), Age int)\n" +
    "insert @t (Name,Age) values(@Name, @Age)", new[]
    {
        new {Age = 1, Name = "sam"},
        new {Age = 2, Name = "bob"}
    });

The only change is the use of table variables instead temporary tables (@ instead #). Dapper (or anything other in the chain?) seems to somehow mix this up with parameters and returns "Must declare the table variable @t".

Is there anything wrong in my usage or is this a bug/missing feature in dapper?

Best regards, Kc

UPDATE:

Just to clarify: @t is not a parameter to be set by dapper. @t is declared as a local table that's only existent for the currently running query. In my opinion, dapper should not distinguish between any type of table, be it a "normal" one, local/global temporary table or table variable.

A little bit more background information:

What I really want to do is:

• Insert a list of Ids into an indexed table variable (or maybe temporary table if table variables don't work)
• JOIN this table against one of my persistent tables and return the result.

My intention is to defeat a performance issue when SELECTing from my tables with an WHERE IN (...) clause

Answer 1

If you're using SQL Server, then the syntax is incorrect. In SQL Server, table variables are not created the same way as regular tables. It should be like this:

DECLARE @t TABLE (
   -- table definition
);

Answer 2

You said:

My intention is to defeat a performance issue when SELECTing from my tables with an WHERE IN (...) clause

I'm also worried about performance. I have a huge table and I need to SELECT a small subset of it each time I query.

What seems to work for me is to create a DataTable in C# and pass it to Dapper's AsTableValuedParameter method. Then I would:

JOIN this table against one of my persistent tables and return the result.

e.g.,

First in my db I created a UDTT:

 CREATE TYPE [dbo].[IDs] AS TABLE(
    [ID] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

Then in my code create a DataTable:

        var dt = new DataTable();
        dt.Columns.Add(new DataColumn("Id", typeof(long)));

//i'm adding from a list passed as parameter; your example is via collection initializer
        foreach (var id in Ids)
        {
            dt.Rows.Add(id);
        }

Then write a query that joins the large table to my subset:

        using (IDbConnection sqlConnection = new SqlConnection(connectionString))
        {

            const string query = @"

SELECT *
FROM [dbo].[Candidate] c
JOIN @tvp t ON c.CandidateID = t.ID

";
            var candList = sqlConnection.Query<Candidate>(query, new { tvp = dt.AsTableValuedParameter("[dbo].[IDs]") }).ToList();

            return candList;
        }

Answer 3

You're using it wrong. Dapper and all other micro-Orms don't care about table variables as defined by the whatever sql flavour, they care only about sql parameters.

You should do this (assuming the table name doesn't come from user input - and why should it?!)

var tableName='my_table' ;
int tally = connection.Execute(
string.format("create table {0}(Name nvarchar(max), Age int)\n" +
"insert {0} (Name,Age) values(@Name, @Age)",tableName), new[]
{
    new {Age = 1, Name = "sam"},
    new {Age = 2, Name = "bob"}
});