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

Question

我想将 id 集合传递给将使用 NHibernate 进行映射的存储过程。这项技术是在 Sql Server 2008 中引入的（更多信息请参见此处 => 表值参数）。我只是不想在 nvarchar 参数中传递多个 id，然后在 SQL Server 端截断其值。

Answer 1

您可以毫无麻烦地传递值集合。

示例：

var ids = new[] {1, 2, 3};
var query = session.CreateQuery("from Foo where id in (:ids)");
query.SetParameterList("ids", ids);

NHibernate 将为每个元素创建一个参数。

Answer 2

对于我的情况，需要在打开的事务中间调用我的存储过程。
如果存在打开的事务，则此代码可以工作，因为它会自动重用 NHibernate 会话的现有事务：

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

但是，对于我的新存储过程，参数并不像 Int64 那么简单。它是一个表值参数（用户定义的表类型）
我的问题是我找不到正确的 Set 函数。
我尝试了 SetParameter("spData", tvpObj)，但它返回此错误：

无法确定类的类型：...

无论如何，经过一些尝试和错误，下面的方法似乎有效。
Enlist() 函数是此方法的关键。它基本上告诉 SQLCommand 使用现有事务。没有它，将会出现错误

ExecuteNonQuery 要求命令在以下情况下具有事务：
分配给该命令的连接处于待处理的本地事务中...

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

由于我通过这种方法使用 SqlParameter 类，因此 SqlDbType.Structured 可用。

这是分配 wiSnList 的函数：

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

Answer 3

比接受的答案更简单的解决方案是使用 ADO.NET。 NHibernate 允许用户将 IDbCommands 加入 NHibernate 事务中。

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 4

我的第一个临时想法是实现我自己的 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，不是完整的解决方案，但它可以工作，并且在定制时可能会很有用。如果有人知道更好/更短的解决方案，请告诉我们。