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

2022-01-02 00:00:00 sql-server nhibernate table-valued-parameters

我想将一组 id 传递给将使用 NHibernate 映射的存储过程.这种技术是在 Sql Server 2008 中引入的(更多信息在这里=> 表值参数).我只是不想在 nvarchar 参数中传递多个 id，然后在 SQL Server 端截断它的值.

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.

推荐答案

我的第一个临时想法是实现我自己的 IType.

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

没有更多的方法被实现；throw new NotImplementedException(); 在所有其余部分.接下来，我为 IQuery 创建了一个简单的扩展.

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

我的典型用法是

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 类型的名称.

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

而 some_sp 可能就像

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

它当然只适用于 Sql Server 2008，在这个特殊的实现中使用单列 DataTable.

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

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

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.

相关文章