locked
Registering a table-valued UDT in C# RRS feed

  • 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.
    Saturday, June 13, 2009 7:19 PM

Answers

  • Table types and SQLCLR UDTs are different constructs, even though they are both declared in the database with CREATE TYPE syntax. So the table type is not SqlDbType.Udt, but SqlDbType.Structured.
     
    As far as a value goes, you can pass in a DataTable or a DataReader as input, or your own <List> of DbDataRecord. So you'd just change your code to:
     
     SqlParameter param = cmd.Parameters.Add("@InputFileParam", SqlDbType.Structured);
     param.UdtTypeName = "AdventureWorks.dbo.parseInputFile";
     
    DataTable dt = new DataTable();
    // initialize Data Table metadata (for the two columns) 
    // and populate it with the array members (using AddRow) in myStringArray here.
    // The DataTable should use the same data types as dbo.parseInputFile is expecting
     param.Value = dt;
     
    Hope this helps
    Bob Beauchemin
    SQLskills

    "Ryan D" wrote in message news:87e25d80-0cfc-498 a-a2cd-239f58ec0f6f...
    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)
            {
               &n bsp;    SqlConnection conn = new SqlConnection(connString);
           & nbsp;        SqlCommand cmd = conn.CreateCommand();
                     cmd.CommandText = "dbo.uspAppendTableFromInput";
          &nb sp;         cmd.CommandType = CommandType.StoredProcedure;
            ;         cmd.Connection = conn;
              &nbs p;     SqlParameter param = cmd.Parameters.Add("@InputFileParam", SqlDbType.Udt);
                     param.UdtTypeName = "AdventureWorks.dbo.parseInputFile";
         &nb sp;          param.Value = myStringArray;
             & nbsp;      conn.Open();
             &nb sp;      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.
    Saturday, June 13, 2009 7:42 PM