Add collection as SQL parameter to SqlCommand without using (Table-Valued-Parameter) type

Question

I'm trying to use a table as a parameter in my SQL query, but I cannot get it to work without using the name of an existing type (Table Valued Parameter).

In MSSQL I can do this:

declare @mytable table (column1 int, column2 nvarchar(10))
insert into @mytable select 1, "test" UNION ALL select 2, "test2" UNION ALL [...]

select * from sometable inner join @mytable on sometable.id = @mytable.column1

To accomplish this in C#, I can (roughly) do the following:

SqlCommand cmd ...
var p = cmd.CreateParameter();
p.TypeName = "MyType";                  // <-- I dont't want to do this
p.SqlDbType = SqlDbType.Structured;
p.Value = myDataTable;
p.ParameterName = "table";
cmd.Parameters.Add(p);
cmd.CommandText = "select * from sometable inner join @mytable on ... ";

For this to work, I have to create the type "MyType" in the database: CREATE TYPE [MyType] AS TABLE (...);

My problem is that I need to explicitly specify the type of the table, even though in MSSQL I can do it inline (see previous example). Secondly, I need to explicitly define the type in the database for each possible collection type.

Is there any way to add a collection as a parameter to the SQL command without needing to declare its type in the database and using that type as the type name of the parameter?

Use a stored procedure and put code in SQL Server as a stored procedure. — jdweng
– jdweng, Commented Dec 10, 2018 at 13:39
Without a proc like @jdweng, suggested, you'll need to pass the collection as XML or JSON. — Dan Guzman
– Dan Guzman, Commented Dec 10, 2018 at 13:44
@jdweng (and Dan too) Thank you both for your quick replies; how would I go about doing that? I do not have the data in SQL server, so I have to supply it somehow.. And the only way of using stored procedures I can think of, uses the table valued parameter types.. — yaba
– yaba, Commented Dec 10, 2018 at 14:46
Use SQL Server Management Studio which comes with SQL Server. Create a Table for the data in SSMS Explorer. Then create a stored procedure using.SSMS. — jdweng
– jdweng, Commented Dec 10, 2018 at 15:15
@jdweng But then I could just as well create a table and fill it with data, and use that table in my query, right? The thing is, I'm trying to find a way to dynamically use a collection of objects I have in my program in a query, without the need to specify its structure beforehand. — yaba
– yaba, Commented Dec 10, 2018 at 15:23

yaba · Accepted Answer · 2018-12-11 09:22:36Z

0

I ended up using Json (as proposed by @DanGuzman), tested it, and it performed more than well with a relatively simple dataset of about 500 items (my use case).

Simple implementation example in C#:

var col = new object[] { new { Column1 = "Test", Column2 = 1 }, new { Column1 = "2nd row", Column2 = 2 }, ... };
IDbDataParameter p = command.CreateParameter();
p.DbType = DbType.String;
p.ParameterName = "mytable";
p.Value = JsonConvert.SerializeObject(col); // NewtonSoft 
command.Parameters.Add(p);
command.CommandText = "SELECT * FROM OPENJSON(@mytable) WITH (Column1 nvarchar(max), Column2 int)";

References:

answered Dec 11, 2018 at 9:22

yaba

9317 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Add collection as SQL parameter to SqlCommand without using (Table-Valued-Parameter) type

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related