C# calling stored procedure passing UDT parameter

[bmahf]

Hi all,

I am looking to pass a small table as a parameter to a stored procedure. This is the first time that I'm trying to do this, and it's not working. Here's what I'm doing.

I am first testing by sending a simple one-column table of strings. I have created a user-defined type in SQL Server 2008 called StringList as follows:

create TYPE [dbo].[StringList] AS TABLE(
[StringEntry] VARCHAR(MAX) NULL
)

I then set that as one of the parameters to my stored procedure as follows:

CREATE PROCEDURE [dbo].[spMyProc]
(
@pStringList as StringList READONLY
)
AS
BEGIN
...
END

Then I went to my C# code and added a method that creates a DataTable with the single column StringEntry:

public DataTable CreateStringList(string[] stringList)
{
DataTable stringListTable = new DataTable();
stringListTable.Columns.Add("StringEntry", typeof(String));
if (stringList != null)
{
foreach (string s in stringList)
{
DataRow dr = stringListTable.NewRow();
dr["StringEntry"] = s;
stringListTable.Rows.Add(dr);
}
}
return stringListTable;
}

And then I call that from my C# method that does the DB access:

public bool TestStringList(string[] list)
DataTable listTable = CreateStringList(list);
try
{
// I create a command as a transaction connection to call my stored procedure
SqlCommand incom = new SqlCommand();
incom.Connection = new SqlConnection(connString);
incom.Transaction = incom.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
incom.CommandText = "spMyProc";
incom.CommandType = CommandType.StoredProcedure;

// I create one parameter as a UDT passing the type name as was created in the DB.
SqlParameter param1 = new SqlParameter("@pStringList", SqlDbType.Udt, listTable.Rows.Count);
param3.UdtTypeName = "StringList";
param3.Value = studentList;

// I add the parameter and execute.
incom.Parameters.Add(param1);
incom.ExecuteNonQuery();

transaction.Commit();
}
catch (Exception ex)
{
Rollback(transaction, ex);
return false;
}
return true;
}

When I run my program, and I step passed the ExecuteNonQuery() call, I get the error message:

UdtTypeName property must be set for UDT parameters.

Does anyone have an idea what I'm doing wrong here?
Thanks...

[bmahf]

Ok, so I made a mistake, and actually corrected that mistake in sending my post. Then I just saw my mistake in my C# code and made the correction there too, so please disregard the error message I posted. The actual error message that goes with the code shown is this:

Specified type is not registered on the target server.System.Data.DataTable

How do I go about registering this user-defined type? When I looked online, I didn't really see anyone talking about registering their user-defined type.

[bmahf]

Ok, answered my own question. Turns out that SqlDbType.Udt is for non-table structured data. I changed my query to SqlDbType.Structured, and the error went away.

[bmahf]

Oh, and I also had to remove the setting of param1.UdtTypeName (which I again see is incorrect in my original post, there shouldn't be a param3, but all should be param1). This is because SqlParameter.UdtTypeName is only set for SqlDbType.Udt, not for SqlDbType.Structured.