Is it possible to use `SqlDbType.Structured` to pass Table-Valued Parameters in NHibernate?

Viewed 11595

I want to pass a collection of ids to a stored procedure that will be mapped using NHibernate. This technique was introduced in Sql Server 2008 ( more info here => Table-Valued Parameters ). I just don't want to pass multiple ids within an nvarchar parameter and then chop its value on the SQL Server side.

4 Answers

For my case, my stored procedure needs to be called in the middle of an open transaction. If there is an open transaction, this code works because it is automatically reusing the existing transaction of the NHibernate session:

NHibernateSession.GetNamedQuery("SaveStoredProc")
    .SetInt64("spData", 500)
    .ExecuteUpdate();

However, for my new Stored Procedure, the parameter is not as simple as an Int64. It's a table-valued-parameter (User Defined Table Type) My problem is that I cannot find the proper Set function. I tried SetParameter("spData", tvpObj), but it's returning this error:

Could not determine a type for class: …

Anyways, after some trial and error, this approach below seems to work. The Enlist() function is the key in this approach. It basically tells the SQLCommand to use the existing transaction. Without it, there will be an error saying

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction…

using (SqlCommand cmd = NHibernateSession.Connection.CreateCommand() as SqlCommand)
{
    cmd.CommandText = "MyStoredProc";
    NHibernateSession.Transaction.Enlist(cmd); // Because there is a pending transaction
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@wiData", SqlDbType.Structured) { Value = wiSnSqlList });
    int affected = cmd.ExecuteNonQuery();
}

Since I am using the SqlParameter class with this approach, SqlDbType.Structured is available.

This is the function where wiSnList gets assigned:

private IEnumerable<SqlDataRecord> TransformWiSnListToSql(IList<SHWorkInstructionSnapshot> wiSnList)
{
    if (wiSnList == null)
    {
        yield break;
    }
    var schema = new[]
    {
        new SqlMetaData("OriginalId", SqlDbType.BigInt),           //0
        new SqlMetaData("ReportId", SqlDbType.BigInt),             //1
        new SqlMetaData("Description", SqlDbType.DateTime),        //2
    };

    SqlDataRecord row = new SqlDataRecord(schema);
    foreach (var wi in wiSnList)
    {
        row.SetSqlInt64(0, wi.OriginalId);
        row.SetSqlInt64(1, wi.ShiftHandoverReportId);
        if (wi.Description == null)
        {
            row.SetDBNull(2);
        }
        else
        {
            row.SetSqlString(2, wi.Description);
        }

        yield return row;
    }
}
Related