Stored Procedure Table Valued Parameter

Hi,

I am looking for some guidance setting up and calling stored procedures with a table valued parameter in Sql Server 2008. I do have a working solution, but I was hoping there may some nicer ways of invoking these procedures.

My working solution is as follows:

var command = unitOfWork.Context.DataProviderObjectFactory.CreateCommand();
command.CommandText = "GetProductExtraInfo";
command.CommandType = CommandType.StoredProcedure;

 var metadata = new[] { new SqlMetaData("Id", SqlDbType.Int) };
 SqlDataRecord[] data = new SqlDataRecord[products.Count];

 for (int i = 0; i < products.Count; i++)
 {
    var dataRecord = new SqlDataRecord(metadata);
    dataRecord.SetValues(products[i].Id);
    data[i] = dataRecord;
 }

 command.Parameters.Add(new SqlParameter("@ProductIds", data) { TypeName = "IdTable", SqlDbType = SqlDbType.Structured });

 var result = unitOfWork.FindBySql<ProductInfo>(command);

My questions are:

1) I have been unable to "drag" the stored procedure onto the model. I wanted to do this so that it generated a GetProductExtraInfoResult type. When I attempt to, I get the following error (note that "IdTable" is my User-Defined Table Type.):

Exception while processing Server Explorer drag: System.Data.SqlClient.SqlException (0x80131904): Operand type clash: nvarchar is incompatible with IdTable

2) I read on an older forum post that you can use something like the below code, but I was unable to get it to work:

  var metadata = new[] { new SqlMetaData("Id", SqlDbType.Int) };
   SqlDataRecord[] data = new SqlDataRecord[products.Count];

   for (int i = 0; i < products.Count; i++)
   {
      var dataRecord = new SqlDataRecord(metadata);
      dataRecord.SetValues(products[i].Id);
      data[i] = dataRecord;
   }

  unitOfWork.Find<ProductInfo>(
        new ProcedureQuery("GetProductExtraInfo",
                new ProcedureParameter("ProductIds", data)))

I get the error message:

No mapping exists from object type Microsoft.SqlServer.Server.SqlDataRecord[] to a known managed provider native type.

3) Finally & more of a suggestion of how it could work, I'd love to be able to simply drag the stored procedure onto the design surface, and have it create a strongly typed method; generate a return type; and generate a type for the table valued parameter which I could populate and invoke as follows:

  GetProductExtraInfoResult result = unitOfWork.GetProductExtraInfo(new IdTable(new [] { "1", "2", "3" }));

Cheers & thanks,
-Brad

Hi Brad,

I believe for now you are going to need to manage this independently from the designer as the designer does not understand TVP's as you have already discovered. We do have a backlog item to look at this in the future but its not particularly high priority at this stage. Feel free to use the feature requests - http://www.mindscapehq.com/thinktank/product/9 - to generate some interest in this to bump it up the priority list though :)

In regards to your second error, make sure you are using SqlServer2008 for the data provider when setting up your context as the translation that is mentioned by Ivan in the earlier forum post only kicks in when you are using the 2008 provider, otherwise the object gets passed through verbatim and will generate the error you are seeing.

Jeremy