SqlDbType.Structured to pass Table-Valued Parameters in NHibernate to select without a :param?

Question

I want to pass a collection of IDs (via table-valued parameter) to an NHibernate IQuery select statement to be used in a join: In native SQL, I can do this (SQLSelectData below). Notice there is no :param in the SqlCommand sql:

public static bool SQLSelectData()
{
    string conACME = System.Configuration.ConfigurationManager
                           .AppSettings["conACME"].ToString();
    DataTable tblBusUnit = new DataTable();
    tblBusUnit.Columns.Add("VALUE", typeof(int));
    DataRow dRow = tblBusUnit.NewRow();
    dRow["Value"] = 1;
    tblBusUnit.Rows.Add(dRow);
    dRow = tblBusUnit.NewRow();
    dRow["Value"] = 6;
    tblBusUnit.Rows.Add(dRow);
    using (SqlConnection con = new SqlConnection(conACME))
    {
        con.Open();

        SqlDataReader rdr;
        SqlCommand cmd = new SqlCommand(
         "select bus_unit_id, BusUnit " +
         "from BusUnit b " + 
         "join @tvpBusUnit s on s.value = b.BUS_UNIT_ID;",
         con);
        cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@tvpBusUnit",
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.[DLTableTypeInt]",
            Value = tblBusUnit
        });
        rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            string stBusUnitId = rdr["bus_unit_id"].ToString();
            string strBusUnit = rdr["BusUnit"].ToString();
            Console.WriteLine("Bus Unit:" + strBusUnit);
        }
    }
    return true;
}

How do I do this in NHibernate? I tried the accepted solution of this question by using the Sql2008Structured and Structured2008Extensions classes.

See code below that calls the SetStructured():

public void SQLSelectTVP<T>()
{
    objNSession = NHibernateHelper.GetCurrentSession(strConn);
    DataTable tblBusUnit = new DataTable();
    tblBusUnit.Columns.Add("VALUE", typeof(int));
    DataRow dRow = tblBusUnit.NewRow();
    dRow["Value"] = 1;
    tblBusUnit.Rows.Add(dRow);
    dRow = tblBusUnit.NewRow();
    dRow["Value"] = 6;
    tblBusUnit.Rows.Add(dRow);
    StringBuilder sbSQL = new StringBuilder();
    sbSQL.Length = 0;
    sbSQL.Append("select bus_unit_id, Business_Unit " +
                 "from tblBUSINESS_UNIT b " +
                 "join @tvpBusUnit s on s.value = b.BUS_UNIT_ID");
    IQuery sqlQuery = objNSession.CreateSQLQuery(sbSQL.ToString());
    sqlQuery.SetStructured("tvpBusUnit", tblBusUnit);
    var lstQR = sqlQuery.List<T>();  

}

However, it errors because there is no :param in the SQL:

Parameter tvpBusUnit does not exist as a named parameter in [select bus_unit_id, Business_Unit from tblBUSINESS_UNIT b join @tvpBusUnit s on s.value = b.BUS_UNIT_ID]

How can I fix this?

What are you trying to do? I'm not sure what you mean by "there is no :param in the sqlcommand sql." — Tumen_t, Feb 14 '17 at 14:13
Have you tried putting `@` in `sqlQuery.SetStructured("tvpBusUnit", tblBusUnit);`? Did you also mean to put `inner join` in the sql statement? — Tumen_t, Feb 14 '17 at 14:59
yes. It also gives an error. The issue is no parameter is in the sql just the table valued parameter (@tvpBusUnit): "select bus_unit_id, Business_Unit from tblBUSINESS_UNIT b join @tvpBusUnit s on s.value = b.BUS_UNIT_ID" — Remy, Feb 14 '17 at 15:26

score 1 · Answer 1 · edited Sep 15 '17 at 17:09

1

From the link you posted, I think the way you are accessing the structured variable is incorrect.

s.CreateSQLQuery("EXEC some_sp @id = :id, @par1 = :par1")
 .SetStructured("id", dt)

Your code does not use the :id part, that is, :tvpBusUnit.

Also note that the TableType (in TypeName) may have to be created on the DB beforehand. Please check if this is required. From your code:

TypeName = "dbo.[DLTableTypeInt]",

Some discussion on passing table value parameters is provided here but NHibernate has an update without having to create types like this: Passing table valued parameters to NHibernate. But this may need a pull-request. The answer provided in the other post you referred to allows you to create such types, one for each TableType.

edited Sep 15 '17 at 17:09

Michael

8,362
6
61
88

answered Feb 16 '17 at 06:22

B Charles H

95
2
9

The data for `@tvpBusUnit` needs to be passed to nhibernate to an sql query (NOT a stored procedure). The first example that uses straight sql works and passes the data for `@tvpBusUnit`. Is there a way to mofidy setStructured code to pass for this condition. – Remy Feb 16 '17 at 18:26
@Remy, Yes I meant it for sql query. It is for stored procedures that you don't use the **:X** format. For raw sql query, you will need the **:X** format. Sorry for the delayed reply... Hope this helps. I can advise you to create a table value parameter inside the sql and then assign it using the :X format. I did this for a XML parameter that I passed like: `.SetParameter("Ids", sb.ToString(), NHibernateUtil.StringClob)` and set it in the SQL like this: `SET @IdsXml = :Ids;` – B Charles H Feb 22 '17 at 19:00
@Remy, for stored procedures, the input parameters are clear from the definition of the stored procedure, but for raw sql queries, there is no standard way of providing input values. NHibernate seems to allow this using the :X format. This is my understanding - did not read this somewhere. – B Charles H Feb 22 '17 at 19:15
@Remy, or anyone else, if this answer works for you... can you please mark it as so? That will help others also and confirms it for me too. Thanks! – B Charles H Nov 04 '19 at 10:09

SqlDbType.Structured to pass Table-Valued Parameters in NHibernate to select without a :param?

1 Answers1