0

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.

Improve this question
6
  • 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. Commented Feb 26, 2019 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)? Commented Feb 26, 2019 at 12:41
  • 1
    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. Commented Feb 26, 2019 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)? Commented Feb 26, 2019 at 13:28
  • 1
    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. Commented Feb 27, 2019 at 7:59

2 Answers 2

Reset to default
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.

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

7 Comments

I don't have DB type at all so I can't use it. That's the crucial part of the problem.
@Mike If you do not have table type then first you need to create it.
I found exactly the same solution, I mean using CREATE TYPE. This is the correct answer.
You do need to create the user-defined table type, but you don't have to specify the type name in the SqlParameter.
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.
|
2

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);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.