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

Question

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.

Answer 1

My first, ad hoc, idea was to implement my own IType.

public class Sql2008Structured : IType {
    private static readonly SqlType[] x = new[] { new SqlType(DbType.Object) };
    public SqlType[] SqlTypes(NHibernate.Engine.IMapping mapping) {
        return x;
    }

    public bool IsCollectionType {
        get { return true; }
    }

    public int GetColumnSpan(NHibernate.Engine.IMapping mapping) {
        return 1;
    }

    public void NullSafeSet(DbCommand st, object value, int index, NHibernate.Engine.ISessionImplementor session) {
        var s = st as SqlCommand;
        if (s != null) {
            s.Parameters[index].SqlDbType = SqlDbType.Structured;
            s.Parameters[index].TypeName = "IntTable";
            s.Parameters[index].Value = value;
        }
        else {
            throw new NotImplementedException();
        }
    }

    #region IType Members...
    #region ICacheAssembler Members...
}

No more methods are implemented; a throw new NotImplementedException(); is in all the rest. Next, I created a simple extension for IQuery.

public static class StructuredExtensions {
    private static readonly Sql2008Structured structured = new Sql2008Structured();

    public static IQuery SetStructured(this IQuery query, string name, DataTable dt) {
        return query.SetParameter(name, dt, structured);
    }
}

Typical usage for me is

DataTable dt = ...;
ISession s = ...;
var l = s.CreateSQLQuery("EXEC some_sp @id = :id, @par1 = :par1")
            .SetStructured("id", dt)
            .SetParameter("par1", ...)
            .SetResultTransformer(Transformers.AliasToBean<SomeEntity>())
            .List<SomeEntity>();

Ok, but what is an "IntTable"? It's the name of SQL type created to pass table value arguments.

CREATE TYPE IntTable AS TABLE
(
    ID INT
);

And some_sp could be like

CREATE PROCEDURE some_sp
    @id IntTable READONLY,
    @par1 ...
AS
BEGIN
...
END

It only works with Sql Server 2008 of course and in this particular implementation with a single-column DataTable.

var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));

It's POC only, not a complete solution, but it works and might be useful when customized. If someone knows a better/shorter solution let us know.

Answer 2

A simpler solution than the accepted answer would be to use ADO.NET. NHibernate allows users to enlist IDbCommands into NHibernate transactions.

DataTable myIntsDataTable = new DataTable();
myIntsDataTable.Columns.Add("ID", typeof(int));

// ... Add rows to DataTable
ISession session = sessionFactory.GetSession();
using(ITransaction transaction = session.BeginTransaction())
{
    IDbCommand command = new SqlCommand("StoredProcedureName");
    command.Connection = session.Connection;
    command.CommandType = CommandType.StoredProcedure;
    var parameter = new SqlParameter();
    parameter.ParameterName = "IntTable";
    parameter.SqlDbType = SqlDbType.Structured;
    parameter.Value = myIntsDataTable;
    command.Parameters.Add(parameter);            
    session.Transaction.Enlist(command);
    command.ExecuteNonQuery();
}

Answer 3

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;
    }
}