sql-servernhibernatetable-valued-parameters

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


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.


Solution

  • 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.