0

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?

yaba
  • 829
  • 7
  • 11
  • 1
    Use a stored procedure and put code in SQL Server as a stored procedure. – jdweng Dec 10 '18 at 13:39
  • Without a proc like @jdweng, suggested, you'll need to pass the collection as XML or JSON. – Dan Guzman Dec 10 '18 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 Dec 10 '18 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 Dec 10 '18 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 Dec 10 '18 at 15:23
  • See following posting : https://stackoverflow.com/questions/24046680/how-to-create-a-table-before-using-sqlbulkcopy – jdweng Dec 10 '18 at 15:27

1 Answers1

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:

yaba
  • 829
  • 7
  • 11