Using table-valued parameters in SQL Server

private static void BuildCollection(IEnumerable inputCollection, IList outputCollection, SqlMetaData[] metaData)
        {
            foreach (var i in inputCollection)
            {
                var rec = new SqlDataRecord(metaData);
                rec.SetValue(0, i);
                outputCollection.Add(rec);
            }
        }

private static void PrepareDataRecordParameter(SqlCommand command, string paramName, IEnumerable records, string typeName)
        {
            if (records.Any())
            {
                var listParam = new SqlParameter(paramName, SqlDbType.Structured);
                listParam.Direction = ParameterDirection.Input;
                listParam.TypeName = typeName;
                listParam.Value = records;

                command.Parameters.Add(listParam);
            }
        }

Use these methods as follow

IEnumerable orders = new List {1, 200, 234, 23}

IList drOrders = new List();

SqlMetaData[] IntegerTable =
{
    new SqlMetaData("Id", SqlDbType.Int)
};

BuildCollection(orders, drOrders , intIdTable);

var command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.CheckOrders";
 
PrepareDataRecordParameter(command, "@orders", drOrders, "dbo.IntIdTable");


Stored procedure looks like this:

CREATE PROCEDURE dbo.CheckOrders(
@orders [dbo].[IntegerTable] readonly
)
AS
...

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s