Pass list of values in sqlparameter, structured perhaps

Question

Is it possible to create something like this in C# code using SqlDbCommand and SqlParameter?

DECLARE @Users TABLE (ID INT)
INSERT INTO @Users
VALUES (10),(20),(30)

SELECT COUNT(*) FROM @Users

I tried something like this:
1) DataTable creator:

        private static DataTable CreateDataTable(IEnumerable<int> ids)
        {
        DataTable table = new DataTable();
        table.Columns.Add("ID", typeof(int));
        foreach (int id in ids)
        {
            table.Rows.Add(id);
        }
        return table;

2) Add SqlParameter:

 sqlParameters.Add(new SqlParameter()
                            {
                                ParameterName = $"@paramname",
                                SqlDbType = SqlDbType.Structured,
                                Value = table
                            });

3) Execute command (command is SELECT COUNT(*) FROM @Users), parameters is list of parameters from step 2:

using (SqlCommand cmd = new SqlCommand(command, connection))
                {
                    if (parameters != null)
                        cmd.Parameters.AddRange(parameters.ToArray());

                    SqlDataReader reader = cmd.ExecuteReader();

What I get is:

The table type parameter '@Users' must have a valid type name.

And I don't really have a real table so no type available, just want it to be:

DECLARE @Users TABLE (ID INT)

Is that doable? What I want to achieve is just pass list of values, in this case list of ints, obviously.

IN REGARD TO MARKED AS DUPLICATE:
Provided link doesn't solve the problem since it's not lack of typename problem but rather lack of typename to use. The problem is that I can't create any table and can't use any existing one to pass TypeName in SqlParameter.

ONE WORKING SOLUTION:

CREATE TYPE [dbo].[IntList] AS TABLE(
[Value] [int] NOT NULL
)

and then SqlParameter:

sqlParameters.Add(new SqlParameter()
                        {
                            ParameterName = $"@paramname",
                            SqlDbType = SqlDbType.Structured,
                            Value = table,
                            TypeName = "dbo.IntList"
                        });

Nevertheless, another step would be to use built-in type like GarethD suggested. I'm not sure if they are available in SQL Server 2016.

The answer linked should explain how to do this, but in summary, you **must** create your custom table types in the DB first, e.g. `CREATE TYPE dbo.ListOfInt AS TABLE (Value INT NOT NULL);`, then you can uses these in code. — GarethD, Feb 26 '19 at 12:02
So it's not possible at all to use it as in DECLARE statement in my example? Meaning I don't really have a db type at all, just declared TABEL (id int)? — Mike, Feb 26 '19 at 12:41
No it is not possible, you would need to create the table types, although once set up these are re-usable, so you would only need to set up the type once, then you will be good to go in the future. Most of my DBs have generic table types (`dbo.ListOfInt`, `dbo.ListOfString` etc) set up for exactly this purpose. Then these types can be re-used everywhere. — GarethD, Feb 26 '19 at 13:05
That's interesting and might be useful in my case. Do you know where to find them in SQL Server 2016 (if they are available)? — Mike, Feb 26 '19 at 13:28
Sorry, I wasn't very clear there, these generic table types are in most of my DBs because I created them, they are not built in. They are one of those things, a bit like a Calendar table that are very useful for many different things with little or no overhead, so may as well be created along with any database, just in case they are needed at some point in the future. — GarethD, Feb 27 '19 at 07:59
Ok, thanks for the info, I couldn't find it in Ms sql and it got me thinking. — Mike, Feb 27 '19 at 13:07

score 2 · Answer 1 · answered Feb 26 '19 at 11:50

You need to use table type parameter. first you need to create table type in SQL. then pass parameter from C#

Take Datatable as SP Pararmeter

@dt customdatatable READONLY

Write Following C# Code

DataTable dt = new DataTable();
dt.Columns.Add("customcolumnname");
DataRow dr = dt.NewRow();
dr["customcolumnname"] = "columnvalue";
dt.Rows.Add(dr);

SqlParameter[] parCollection = new SqlParameter[1];
parCollection[0] = new SqlParameter("@yourdt", dt);

Deepankshee Jain · Accepted Answer · 2019-02-26T13:10:40.787

2

You need to add TypeName in sqlParameter , the same name with you created your table type in DB.

 sqlParameters.Add(new SqlParameter()
                            {
                                ParameterName = $"@paramname",
                                SqlDbType = SqlDbType.Structured,
                                Value = table,
                                TypeName = "dbo.MyType";
                            });

If you do not have table type in database then first you need to create it in SQL

CREATE TYPE [dbo].[IntegerList] AS TABLE(
    [Data] [int] NOT NULL,  
)
GO

And then give that name in your code. That will work and you do need to create table in DB for that.

edited Feb 26 '19 at 13:10

answered Feb 26 '19 at 11:50

Deepankshee Jain

131
6

I don't have DB type at all so I can't use it. That's the crucial part of the problem. – Mike Feb 26 '19 at 12:42
@Mike If you do not have table type then first you need to create it. – Deepankshee Jain Feb 26 '19 at 13:02
I found exactly the same solution, I mean using CREATE TYPE. This is the correct answer. – Mike Feb 26 '19 at 13:37
You do need to create the user-defined table type, but you don't have to specify the type name in the SqlParameter. – Zohar Peled Feb 27 '19 at 09:10
I tried that. It doesn't work. If I don't specify the type name explicitly, I get the same error as when I had no user-defined table type at all. It has to be specified in SqlParameter. – Mike Feb 27 '19 at 13:06
@ZoharPeled: I think that only works (leaving out the type name) when SqlCommand.CommandType = CommandType.StoredProcedure is being used. When using CommandType.Text, I think the type name is required (?). – Moe Sisko Feb 28 '19 at 01:17
@MoeSisko Perhaps you're right, I don't remember ever using a table valued parameter with inline sql. I usually work only with stored procedures. – Zohar Peled Feb 28 '19 at 06:37

Pass list of values in sqlparameter, structured perhaps

2 Answers2