是否可以使用“SqlDbType”。结构化'在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.

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

4 个解决方案

#1

My first, ad hoc, idea was to implement my own IType.

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

No more methods are implemented; a throw new NotImplementedException(); is in all the rest. Next, I created a simple extension for IQuery.

不再实现任何方法;把新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);    }}

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.

好吧，但是什么是“不可名状”呢?它是为传递表值参数而创建的SQL类型的名称。

CREATE TYPE IntTable AS TABLE(    ID INT);

And some_sp could be like

某种sp可能是这样的

CREATE PROCEDURE some_sp    @id IntTable READONLY,    @par1 ...ASBEGIN...END

It only works with Sql Server 2008 of course and in this particular implementation with a single-column DataTable.

它只适用于Sql Server 2008，并且在这个特定的实现中使用一个单列数据表。

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.

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

#2

A simpler solution than the accepted answer would be to use ADO.NET. NHibernate allows users to enlist IDbCommands into NHibernate transactions.

比公认的答案更简单的解决方案是使用ADO.NET。NHibernate允许用户将idbcommand添加到NHibernate事务中。

DataTable myIntsDataTable = new DataTable();myIntsDataTable.Columns.Add("ID", typeof(int));// ... Add rows to DataTableISession 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();}

#3

You can pass collections of values without the hassle.

您可以轻松地传递值集合。

Example:

例子:

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

NHibernate will create a parameter for each element.

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

#4

Have others gotten this implementation to work? I need to pass multiple rows of multiple columns (5 integers to be exact) using this method– I defined my underlying SQL structured type and changed the ColumnSpan to return 5. The only other difference I can see is that I don't return any values in my stored procedure execution. It's just consuming the values supplied to it.

其他人实现这个实现了吗?我需要通过这个方法传递多行多列(5个整数)——我定义了我的底层SQL结构化类型，并更改了ColumnSpan来返回5。我能看到的另一个区别是，我在存储过程执行中没有返回任何值。它只消耗提供给它的值。

DataTable dt = ...;var query = session.CreateSqlQuery("EXEC foo_proc_here @data = :data")     .SetStructured("data", dt)     .ExecuteUpdate();

Unfortunately, this throws a "Could not execute native bulk manipulation query" message. Inner exceptions complain about "Index Out of Range" errors.

不幸的是，这会抛出“无法执行本机批量操作查询”消息。内部异常抱怨“索引超出范围”错误。

I'm not really sure where to even turn next, as the topic of structured types / table value parameters with NHibernate seems esoteric enough that I keep ending up at this same Stack Overflow post.

我甚至不知道下一个要转到哪里，因为使用NHibernate的结构化类型/表值参数的主题看起来非常深奥，以至于我一直在这个堆栈溢出帖子上。

#1