是否可以使用SqlDbType.Structured在NHibernate中传递表值参数? [英] Is it possible to use `SqlDbType.Structured` to pass Table-Valued Parameters in NHibernate?

问题描述

我想将一组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(IDbCommand 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.

这篇关于是否可以使用SqlDbType.Structured在NHibernate中传递表值参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！