NH-3736 - Pass Table Variable as a Input parameter to Stored Procedure

[nhibernate-bot]

SATISH created an issue — 18th November 2014, 19:02:24:

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 don't know how to Pass the Table Variable(contains complete data table) as a input Parameter to the SQL Server Stored procedure using NHibernate Mapping. Can you please help me on this.

Thanks in advance. Satish

---

Ricardo Peres added a comment — 19th November 2014, 6:45:31:

How do you do that, can you post here a simple example?

---

Ricardo Peres added a comment — 19th November 2014, 13:08:09:

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

---

SATISH added a comment — 19th November 2014, 15:27:50:

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

---

Ricardo Peres added a comment — 20th November 2014, 12:06:03:

Pull request: #376
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();

---

SATISH added a comment — 20th November 2014, 21:23:08:

Thank you Peres, for given solution, please let me try this and let you know if any questions.

---

SATISH added a comment — 8th December 2014, 16:48:50:

Hi Peres,

I tried with
var Mytable = new DataTable("dbo.TableType");
Mytable.Columns.Add("CategoryID", typeof(int));
Mytable.Columns.Add("CategoryName", typeof(string));
Mytable.Rows.Add(1, "TestSatish");

var result = session.CreateSQLQuery("EXEC usp_TestSatiUpdateCatgorys :t").SetParameter("t", Mytable).List();. But I am getting "Operand type clash: varbinary is incompatible with TestSatishTableType1 error.

Then i tried with other option which is var result = session.CreateSQLQuery("EXEC dbo.TableProcedure :t").SetParameter("t", Mytable, NHibernateUtil.Structured("dbo.TableType")).List(); But I am getting Compilation error NHibernateUtil class does not contains a definition for Structured.

When I check at SQl Server Side Stored procedure and Table variable are working fine. Can you please let me know where I am doing wrong.

At Database side my table variable is below
'CREATE TYPE .[TestSatishTableType1] AS TABLE(
[int] NULL,
[CategoryName] nvarchar NULL
)
GO'

and my Stored Procedure is below

ALTER PROCEDURE .[usp_TestSatiUpdateCatgorys]
@testsatish as TestSatishTableType1 READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Test_SatishTable as TestSatishTableType1
insert into dbo.TestSatishCategoryTable1 (CATID,CATNAME) select nc.CategoryID, nc.CategoryName FROM @testsatish AS nc;
END
GO

Can you please let me know where I am doing mistake.

Thanks in advance.

---

Ricardo Peres added a comment — 8th December 2014, 20:06:11:

But did you clone NHibernate, got my pull request, compiled NH and tested with this version?

---

SATISH added a comment — 11th December 2014, 21:07:44:

Thank you Peres, Finally I got it work.
I followed the link https://stackoverflow.com/questions/3701364/nhibernate-sqldbtype-structured

in Sql2008Structured Class , at the below method I changed the statement s.Parameters.TypeName = "MyTablevariableType";
public void NullSafeSet(IDbCommand st, object value, int index, NHibernate.Engine.ISessionImplementor session) {
var s = st as SqlCommand;
if (s != null) {
s.Parameters.SqlDbType = SqlDbType.Structured;
s.Parameters[index].TypeName = "IntTable"; -----This is my TablevariableType.
s.Parameters[index].Value = value;
}
else {
throw new NotImplementedException();
}
Then It works fine.
Thanks for your help and quick reply.

---

Ricardo Peres added a comment — 11th December 2014, 21:29:34:

Yes... that is precisely what my fix does...

---

Alexander Zaytsev added a comment — 23rd April 2015, 11:07:56:

What about other dialects?