How to Pass Table Variable as a Input parameter to Stored Procedure using NHibernate Mapping..?

[Satish Kumar]

Hi All,

I know how to execute stored procedures with Parameter Name and Parameter Value using var query= session.GetNamedQuery(StoredProcedureName);

query.SetParameter(ParameterName,ParameterValue).

But I need to Pass the Table Variable(contains complete table data) as a input Parameter to the SQL Server Stored procedure using NHibernate Mapping. 

Appreciate your help on this(If possible with C# example coding).

Thanks 

Satish

[Ricardo Peres]

I don't think it is possible now. You can create a request for in Jira (NHibernate.jira.com).

RP

[ENOTTY]

https://stackoverflow.com/questions/3701364/nhibernate-sqldbtype-structured

[Satish Kumar]

Thanks for the reply with below link, As per the below url we can pass collection of values to procedure for that I have to loop through all values for each column, even I found another url (http://klausnji.wordpress.com/2014/01/11/returning-an-ienumerable-using-nhibernate-and-stored-procedures/) this link also executes the same for collection values. But I need to send complete DataTable as input parameter to SQL Server stored procedure, please let me know if any solution. Thanks in advance :)

On Wednesday, November 19, 2014 3:43:15 AM UTC-5, ENOTTY wrote:

https://stackoverflow.com/questions/3701364/nhibernate-sqldbtype-structured

[ENOTTY]

The stackoverflow accepted answer below contains a working solution to your problem. Did you even try it?

[Ricardo Peres]

Hi,

I submitted a pull request that handles this (passing tables as parameters). This involved creating a new NHibernate IType (StructuredType) and SqlType (StructuredSqlType).

Because the SQL Server's implementation needs to take the name of the type (table-valued parameter), I propose the following convention: set the type as the DataTable name, as in:

var table = new DataTable("dbo.TableType");

table.Columns.Add("a", typeof (int));

table.Columns.Add("b", typeof(int));

table.Rows.Add(1, 2);

var result = session.CreateSQLQuery("EXEC dbo.TableProcedure :t").SetParameter("t", table).List();

The type must be passed, there is no other way. The only way I see is to explicitly pass the type as a parameter, which is also supported:

var result = session.CreateSQLQuery("EXEC dbo.TableProcedure :t").SetParameter("t", table, NHibernateUtil.Structured("dbo.TableType")).List();

Let me know if it solves your problem.

RP

[Ricardo Peres]

Forgot this: it's issue https://nhibernate.jira.com/browse/NH-3736.

RP