Need help using SQL 2008 User Defined Table type on stored procedure

I created a simple User Defined Table Type in SQL 2008 as follows:

/* Create a string array type. */
create type StringArrayType as table ( value varchar(10) );
go

So that I can pass a string array to my stored procedure. I need help on creating a Lightspeed mapping and using the created Lightspeed type to invoke my stored procedure. I have browsed through the manual which was helpful in guiding me create the ProcedureQuery and ProcedureParameters. However, I need more clarity on how exactly one uses a UDTT as I get the following exception with current code:

using (IUnitOfWork unitOfWork = _lbsContext.CreateUnitOfWork())
            { 
                var entityQuery = new ProcedureQuery("dbo.my_stored_proc",
                new ProcedureParameter("param1", 1),new ProcedureParameter("param2", 2),
                new ProcedureParameter("stringArrayInput", new[]{"1", "2"}));
                entities = unitOfWork.Find<MyEntity>(entityQuery);
        }

No mapping exists from object type System.String[] to a known managed provider native type.

TIA.

Guys, this is show stopping for me. Any advice on what to do? I tried

  var cmd = unitOfWork.Context.DataProviderObjectFactory.CreateCommand();
                cmd.CommandText = "exec dbo.my_stored_proc";

                cmd.Parameters.Add(new SqlParameter("@param", 1));
                cmd.Parameters.Add(new SqlParameter("@param", 2));
                cmd.Parameters.Add(new SqlParameter("@stringArrayInput", new[]{"1", "2"}) { SqlDbType = SqlDbType.Structured, TypeName = "dbo.StringArrayType" });
                entities  = unitOfWork.FindBySql<MyEntities>(cmd);

and got same exception.

Your FindBySql code is almost right, but you need to pass a proper structured data type rather than just an array of strings. Here's how to do that:

var metadata = new[] { new SqlMetaData("value", SqlDbType.NVarChar, 10) };  // the members of your SQL Server table type

var data1 = new SqlDataRecord(metadata);  // In reality you'd create a helper method for this
data1.SetValues("1");
var data2 = new SqlDataRecord(metadata);
data2.SetValues("2");
var data = new [] { data1, data2 };

cmd.Parameters.Add(new SqlParameter("@stringArrayInput", data) { SqlDbType = SqlDbType.Structured });

Note that this is not a LightSpeed issue -- this is just how SQL Server table type parameters work in ADO.NET.

For your convenience, we've added an enhancement to our SQL 2008 provider so that you can pass a collection of SqlDataRecords via a ProcedureParameter and it will work automatically:

// data is an array of SqlDataRecord as set up above
entities = unitOfWork.Find<MyEntity>(
  new ProcedureQuery("my_stored_proc",
    new ProcedureParameter("stringArrayInput", data));

This enhancement will be included in the next nightly build, available from about 1200 GMT. The FindBySql technique should work in current builds with the change outlined above. Let us know if you still run into problems!

Ivan, you are the man. Thanks again.

Ivan, you are the man. Thanks again.

Ivan, I realize this may not be a LightSpeed issue but why I really could use some help here:

Why would I be getting the following exception when executing the query through FindBySql?

Procedure or function 'dbo.my_stored_proc' expects parameter '@param', which was not supplied.

Even though I created and added this SqlParameter to the command's Parameters collection?

Thanks again.

Figured out that CommandText and parameters had to be modified as follows:

    cmd.CommandText = "exec dbo.my_stored_proc @param1, @param2, @stringArrayInput";

            cmd.Parameters.Add(new SqlParameter("param", 1));
            cmd.Parameters.Add(new SqlParameter("param", 2));
            .....
            entities  = unitOfWork.FindBySql<MyEntities>(cmd);

The problem is that you are setting a CommandText exec my_stored_proc and letting the CommandType default to Text.

Change the CommandText to my_stored_proc and set the CommandType to StoredProcedure and it should work.

No, I don't know why this makes a difference...!