Passing DataTable as table variable in SQL Server Ad-Hoc .NET Query

Question

I want to pass a table parameter to SQL Server 2008. Most of what I find on the Internet talks about doing this with a stored procedure, but is it possible to also do it with an Ad-Hoc query? With the code below, I get the error, "Column, parameter, or variable @idList. : Cannot find data type dbo.SiteType.". I think it might be executing the type definition as a totally separate query and 'forgetting' about it when the SELECT is initiated.

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
}
sqlCmd.CommandText = "CREATE TYPE [dbo].[SiteType] AS TABLE(ID uniqueidentifier);" + "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.AddWithValue("@idList", dt);
parameter.TypeName = "[dbo].[SiteType]";

UPDATE: I added 'dbo.SiteType' as a type to the database, and now using this code it works, but returns zero rows:

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
}
sqlCmd.CommandText = "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.Add("@idList", SqlDbType.Structured);
parameter.TypeName = "[dbo].[SiteType]";
parameter.Value = dt;

If you want this type why don't you create in your database? The way this is coded you would have to drop the type also or it would not be able to run a second time. Even better would be to just use a stored procedure. The way you have this your code does not have any separation from the data. Last but not least....don't use AddWithValue. It is not good for performance and can get datatypes wrong. blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Sean Lange
– Sean Lange, Commented Sep 4, 2015 at 13:38
After you have initialised the SqlParameter you need to tell ado that it is a TVP by adding the property parameter.SqlDbType = SqlDbType.Structured; — M.Ali
– M.Ali, Commented Sep 4, 2015 at 13:52
Thanks for your response. I know from what I posted it seems like an sproc is preferred, but I have other reasons why it is desirable to be ad-hoc (other queries on this table dynamically construct a parameter list and I would like to only have to maintain the select field list in one place - my API layer). I was not aware that creating a custom type was permanent. I will try defining it in the database instead. — user1054922
– user1054922, Commented Sep 4, 2015 at 13:58

user1054922 · Accepted Answer · 2015-09-04 14:37:53Z

0

Final, working code here (after creating the table data type in the server):

DataTable dt = new DataTable();
dt.Columns.Add("ID", Type.GetType("System.Guid"));

foreach (Guid guid in filter.FetchIDs)
{
    DataRow row = dt.NewRow();
    row["ID"] = guid;
    dt.Rows.Add(row);
}
sqlCmd.CommandText = "SELECT * FROM Site INNER JOIN @idList as FetchIDs on FetchIDs.ID=Site.ID;";
SqlParameter parameter = sqlCmd.Parameters.Add("@idList", SqlDbType.Structured);
parameter.TypeName = "[dbo].[SiteType]";
parameter.Value = dt;

answered Sep 4, 2015 at 14:37

user1054922

2,1852 gold badges25 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

Passing DataTable as table variable in SQL Server Ad-Hoc .NET Query

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