sql table udt, vs 2008, csharp

Question

Hi,

I am trying to run a SQL stored proc from Visual Studio 2008 which takes a table-valued UDT parameter as an input. I added this parameter in Visual Studio 2008 but when I run this program it gets an "ArgumentException - Specified type isn't registered on target server." So I googled this problem and think I need to create a new class in Visual Studio 2008 matching the properties of this table type from SQL Server 2008. But I can't find any examples on the internet involving table UDT's. And I tried all of the scalar UDT examples, but I wasn't sure how to modify these to make it table-based.
I also read I may need to create an assembly, although I don't know if this is required only for importing a type into SQL Server 2008 or if it can also be used to import a type into Visual Studio. BTW, I do not see any types listed from the Server Explorer in VS 2008, although I do see the database and its SP's. I tried refreshing the database, but the types were still not showing. What I need to do is simple. I have a table UDT like so:

CREATE TYPE [dbo].[parseInputFile] AS TABLE(
[NumCols] [int] IDENTITY(1,1) NOT NULL,
[strRow] [varchar](500) NOT NULL,
PRIMARY KEY CLUSTERED

And the code snippet from my C# code is:

    public static int AppendData(string[] myStringArray)
    {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "dbo.uspAppendTableFromInput";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            SqlParameter param = cmd.Parameters.Add("@InputFileParam", SqlDbType.Udt);
            param.UdtTypeName = "AdventureWorks.dbo.parseInputFile";
            param.Value = myStringArray;
            conn.Open();
            RowsAffected = cmd.ExecuteNonQuery();

RowsAffected show zero rows affected after running this. What am I missing? Can u point me to a website demonstrating a simple example of this. Or else give me a tip? I have also tried 'param.UdtTypeName="parseInputFile";' for the above snippet, but this returned the same error. BTW, "myStringArray" is essentially a table. It is a series of strings. e.g. myStringArray[0] = "Hello|Bob|How|Do", myStringArray[1] = "I|Am|Fine|And", etc.

Answer 1

Basically, you got your setup almost right - halfway through :-)

Check out those introductory articles to TVP's - table-valued parameters- which is what I GUESS you're trying to use, right?

• SQL Team: SQL Server 2008 Table Valued Parameters
• ADO.NET team blog: Introducting Table-Valued Parameters Part 1

Basically what you need to do is this:

• create a user-defined table type - you have that already
• create a stored procedure which takes on of those table types as a READONLY input parameter (not sure if you got this - haven't seen your code yet)
• to call from .NET; you need to create a DataTable instance and re-create the same structure (in terms of fields and their types - shown in the SQL Team article)
• fill that DataTable
• create a SqlConnection and SqlCommand (of CommandType = Stored Proc) in .NET which defines that table-type parameters as of type "SqlDbType.Structured" in .NET, and sets its values to the DataTable created above
• call that stored proc (packaged in the SqlCommand) from .NET

Does that help at all? I'd strongly recommend the SQL Team article - very informative!

Marc