sql-server - 是否可以使用 `SqlDbType.Structured`在NHibernate中传递表值参数？

Question

我想将一组ID传递给将使用NHibernate映射的存储过程。这项技术是在Sql Server 2008中引入的(此处的更多信息=> Table-Valued Parameters)。我只是不想在nvarchar参数中传递多个ID，然后在SQL Server端将其值切碎。

Answer 1

我的第一个临时想法是实现自己的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...
}

不再执行任何方法；剩下的全部是 throw new NotImplementedException();。接下来，我为 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);
    }
}

对我来说典型的用法是

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>();

好的，但是 "IntTable"是什么？它是创建用来传递表值参数的SQL类型的名称。

CREATE TYPE IntTable AS TABLE
(
    ID INT
);

和 some_sp可能像

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

当然，它仅适用于Sql Server 2008，并且在此特定实现中仅使用单列 DataTable。

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

它仅是POC，不是完整的解决方案，但它可以工作，并且在定制时可能有用。如果有人知道更好/更短的解决方案，请告诉我们。