blocks|key|930813|text|您可以轻松地传递多个值的集合。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|930814|示例：|930815|var+ids+=+new[]+{1,+2,+3};
var+query+=+session.CreateQuery("from+Foo+where+id+in+(:ids)");
query.SetParameterList("ids",+ids);|code-block|syntax|javascript|930816|NHibernate将为每个元素创建一个参数。|930817|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can pass collections of values without the hassle.

Example:

<pre class="lang-cs prettyprint-override"><code>var ids = new[] {1, 2, 3};
var query = session.CreateQuery("from Foo where id in (:ids)");
query.SetParameterList("ids", ids);
</code></pre>

NHibernate will create a parameter for each element.

blocks|key|1145348|text|我的第一个特别想法是实现我自己的IType。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1145349|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...
}|code-block|syntax|javascript|1145350|不再实现更多的方法；所有其他方法中都有一个throw+new+NotImplementedException();。接下来，我为IQuery创建了一个简单的扩展。|1145351|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);
++++}
}|1145352|我的典型用法是|1145353|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>();|1145354|好吧，但是什么是"IntTable"呢？它是为传递表值参数而创建的SQL类型的名称。|1145355|CREATE+TYPE+IntTable+AS+TABLE
(
++++ID+INT
);|1145356|而some_sp可能是这样的|1145357|CREATE+PROCEDURE+some_sp
++++@id+IntTable+READONLY,
++++@par1+...
AS
BEGIN
...
END|1145358|当然，它只适用于Sql+Server2008，并且在这个特定的实现中使用单列DataTable。|1145359|var+dt+=+new+DataTable();
dt.Columns.Add("ID",+typeof(int));|1145360|它只是POC，不是一个完整的解决方案，但它可以工作，并且在定制时可能会很有用。如果有人知道更好/更短的解决方案，请让我们知道。|1145361|entityMap^0|G|5|0|0|L|10|1S|6|0|0|0|0|8|A|0|0|1|7|0|0|12|9|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1H|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1I|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1J|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1K|8|@$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|U|3|V|5|H|7|1N|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|1R|8|@]|D|@]|E|$I|J]]|$1|10|3|11|5|6|7|1S|8|@$9|1T|A|1U|B|C]]|D|@]|E|$]]|$1|12|3|13|5|H|7|1V|8|@]|D|@]|E|$I|J]]|$1|14|3|15|5|6|7|1W|8|@]|D|@]|E|$]]|$1|16|3|-4|5|6|7|1X|8|@]|D|@]|E|$]]]|17|$]]

My first, ad hoc, idea was to implement my own <code>IType</code>.



<pre class="lang-cs prettyprint-override"><code>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...
}
</code></pre>

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

<pre class="lang-cs prettyprint-override"><code>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);
 }
}
</code></pre>

Typical usage for me is

<pre class="lang-cs prettyprint-override"><code>DataTable dt = ...;
ISession s = ...;
var l = s.CreateSQLQuery("EXEC some_sp @id = :id, @par1 = :par1")
 .SetStructured("id", dt)
 .SetParameter("par1", ...)
 .SetResultTransformer(Transformers.AliasToBean&lt;SomeEntity&gt;())
 .List&lt;SomeEntity&gt;();
</code></pre>

Ok, but what is an <code>"IntTable"</code>? It's the name of SQL type created to pass table value arguments.



<pre class="lang-sql prettyprint-override"><code>CREATE TYPE IntTable AS TABLE
(
 ID INT
);
</code></pre>

And <code>some_sp</code> could be like

<pre class="lang-sql prettyprint-override"><code>CREATE PROCEDURE some_sp
 @id IntTable READONLY,
 @par1 ...
AS
BEGIN
...
END
</code></pre>

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

<pre class="lang-sql prettyprint-override"><code>var dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
</code></pre>

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.

blocks|key|930898|text|比公认的答案更简单的解决方案是使用ADO.NET，NHibernate允许用户将IDbCommands登记到NHibernate事务中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|930899|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();
}|code-block|syntax|javascript|930900|entityMap^0|14|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

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

<pre class="lang-cs prettyprint-override"><code>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();
}
</code></pre>

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 => <a href="http://msdn.microsoft.com/en-us/library/bb510489.aspx" rel="noreferrer">Table-Valued Parameters</a> ). I just don't want to pass multiple ids within an <code>nvarchar</code> parameter and then chop its value on the SQL Server side.

Is it possible to use `SqlDbType.Structured` to pass Table-Valued Parameters in NHibernate?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望将一个I集合传递给一个将使用NHibernate映射的存储过程。这项技术是在Sql Server2008中引入的(更多信息请点击这里=>  )。我只是不想在一个nvarchar参数中传递多个ids，然后在SQL Server端砍掉它的值。

问在NHibernate中可以使用`SqlDbType.Structured`传递表值参数吗？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在NHibernate中可以使用`SqlDbType.Structured`传递表值参数吗？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在NHibernate中可以使用`SqlDbType.Structured`传递表值参数吗？
EN