For my case, my stored procedure needs to be called in the middle of an open transaction.
If there is an open transaction, this code works because it is automatically reusing the existing transaction of the NHibernate session:
NHibernateSession.GetNamedQuery("SaveStoredProc")
.SetInt64("spData", 500)
.ExecuteUpdate();
However, for my new Stored Procedure, the parameter is not as simple as an Int64. It's a table-valued-parameter (User Defined Table Type)
My problem is that I cannot find the proper Set function.
I tried SetParameter("spData", tvpObj), but it's returning this error:
Could not determine a type for class: …
Anyways, after some trial and error, this approach below seems to work.
The Enlist() function is the key in this approach. It basically tells the SQLCommand to use the existing transaction. Without it, there will be an error saying
ExecuteNonQuery requires the command to have a transaction when the
connection assigned to the command is in a pending local transaction…
using (SqlCommand cmd = NHibernateSession.Connection.CreateCommand() as SqlCommand)
{
cmd.CommandText = "MyStoredProc";
NHibernateSession.Transaction.Enlist(cmd); // Because there is a pending transaction
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@wiData", SqlDbType.Structured) { Value = wiSnSqlList });
int affected = cmd.ExecuteNonQuery();
}
Since I am using the SqlParameter class with this approach, SqlDbType.Structured is available.
This is the function where wiSnList gets assigned:
private IEnumerable<SqlDataRecord> TransformWiSnListToSql(IList<SHWorkInstructionSnapshot> wiSnList)
{
if (wiSnList == null)
{
yield break;
}
var schema = new[]
{
new SqlMetaData("OriginalId", SqlDbType.BigInt), //0
new SqlMetaData("ReportId", SqlDbType.BigInt), //1
new SqlMetaData("Description", SqlDbType.DateTime), //2
};
SqlDataRecord row = new SqlDataRecord(schema);
foreach (var wi in wiSnList)
{
row.SetSqlInt64(0, wi.OriginalId);
row.SetSqlInt64(1, wi.ShiftHandoverReportId);
if (wi.Description == null)
{
row.SetDBNull(2);
}
else
{
row.SetSqlString(2, wi.Description);
}
yield return row;
}
}