How can the NamedSQLQueryDefinition class be used dynamically as a sql-query equivalent?

Question

I need to dynamically add named queries to the NHibernate configuration object. The search engines return few hits referencing NamedSQLQueryDefinition or NamedQueryDefinition. Below is what I'm attempting to do. I don't know what to provide to the NamedSQLQueryDefinition constructor to successfully create a query. Can anyone provide a sample of how to create a new NamedSQLQueryDefinition the right way? Thanks!!

Session initializer:

    private static ISessionFactory CreateSessionFactory()
    {
        var configuration = new Configuration();

        return Fluently.Configure(configuration.Configure())
            .ExposeConfiguration(AddQueries)
            .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Program>())
            .Mappings(m => m.HbmMappings.AddFromAssemblyOf<Program>())
            .BuildConfiguration()
            .BuildSessionFactory();
    }

The AddQueries would look something like this:

    private static void AddQueries(Configuration cfg)
    {
        var nameQuery = new NamedSQLQueryDefinition("exec pr_GETCustomer ?", ...)

        cfg.NamedSQLQueries.Add("pr_GETCustomer", nameQuery);
        var cust = cfg.GetClassMapping(typeof (Customer));

        cust.LoaderName = "pr_GETCustomer";
    }

PS: I'm trying this route because Fluent NHibernate does not implement a way to configuring the loader & sql-query elements from the hbm file.

Answer 1

I'm pretty new to this but most of the parameters look to be determinable from the attributes you can provide in the HBM file. That said, I'm not too sure what QuerySpaces are. The closest I've got to what I think you are trying to achieve is to use the following (untested):

ISQLQuery q = session.CreateSQLQuery("exec pr_GETCustomer :p1");

if (q is SqlQueryImpl)
{
    IDictionary<string, TypedValue> namedParams = new Dictionary<string, TypedValue>();
    namedParams.Add("p1", new TypedValue(NHibernateUtil.Int32, 12345);

    IDictionary<string, string> paramTypes = new Dictionary<string, string>();

    NativeSQLQuerySpecification spec = 
        (q as SqlQueryImpl).GenerateQuerySpecification(namedParams);

    NativeSQLQueryDefiniton def = new NativeSQLQueryDefiniton(
        spec.QueryString,
        spec.SqlQueryReturns,
        spec.QuerySpaces,
        false,
        null,
        -1,
        -1,
        FlushMode.Never,
        CacheMode.Normal,
        true,
        "blah",
        paramTypes,
        false
    );
}

Obviously, I don't like the cast to SqlQueryImpl. I'm hoping that, once one of us has done it once, obscure properties like querySpaces can be understood, so you won't have to.

Not that I expect it to 100% work, but it may be more achievable from here.

Answer 2

The AddQueries method would be implemented as follows below to "fix" the Fluent NHibernate lack of Loader support. The trick is to properly set up the INativeSQLQueryReturn[] value to contain the mapping from the table columns to the entity properties. It should mimic the contents of the return element of sql-query in the HBM file where the class (with namespace) and property mappings are defined (see XML below). Thanks to @jimbobmcgee for getting me started in this direction!

private static void AddQueries(Configuration cfg)
{
    var namedQuery = new NamedSQLQueryDefinition(
        "exec dbo.pr_GETCustomers @CustomerID=?",
        new INativeSQLQueryReturn[]
            {
                new NativeSQLQueryRootReturn(
                    "Customers",
                    "VehicleInfo.Entities.Customers",
                    new Dictionary<string, string[]>
                        {
                                    {"CustomerID", new[] {"CustomerID"}},
                                    {"CompanyName", new[] {"CompanyName"}}
                    },
                    LockMode.Read)
            },
        new List<string> { "dbo.Customers" },
        true,
        null,
        15,
        1000,
        FlushMode.Auto,
        CacheMode.Normal,
        false,
        "",
        null,
        true);

    cfg.NamedSQLQueries.Add("pr_GETCustomers", namedQuery);
    var cust = cfg.GetClassMapping(typeof(Customers));

    cust.LoaderName = "pr_GETCustomers";
}

Sample HBM file that does the same thing:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    default-access="property" auto-import="true" 
    default-cascade="none" default-lazy="true">
    <class xmlns="urn:nhibernate-mapping-2.2"
        mutable="true" name="VehicleInfo.Entities.Customers, VehicleInfo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="Customers">
        <id name="CustomerID" type="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
            <column name="CustomerID" />
            <generator class="assigned" />
        </id>

        <property name="CompanyName" type="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
            <column name="CompanyName" />
        </property>

        <loader query-ref="pr_GETCustomers"/>

        <sql-insert callable="true" check="none">exec dbo.pr_INSERTCustomers @CompanyName=?, @CustomerID=?</sql-insert>
        <sql-update callable="true" check="none">exec dbo.pr_UPDATECustomers @CompanyName=?, @CustomerID=?</sql-update>
        <sql-delete callable="true" check="none">exec dbo.pr_DELETECustomers @CustomerID=?</sql-delete>
    </class>
    <sql-query name="pr_GETCustomers">
        <return alias="cust" class="VehicleInfo.Entities.Customers, VehicleInfo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
            <return-property name="CustomerID" column="CustomerID"></return-property>
            <return-property name="CompanyName" column="CompanyName"></return-property>
        </return>
        exec dbo.pr_GETCustomers @CustomerID=?
    </sql-query>
</hibernate-mapping>