{"id":393,"date":"2011-06-23T22:09:25","date_gmt":"2011-06-24T03:09:25","guid":{"rendered":"http:\/\/hoolihan.net\/blog-tim\/?p=393"},"modified":"2012-10-19T23:10:57","modified_gmt":"2012-10-20T04:10:57","slug":"nh-named-sql-queries-with-parameters","status":"publish","type":"post","link":"http:\/\/hoolihan.net\/blog-tim\/2011\/06\/23\/nh-named-sql-queries-with-parameters\/","title":{"rendered":"NHibernate Named SQL Queries with Parameters"},"content":{"rendered":"

I had to create a stored procedure to be called from NHibernate. You could use Session.Connection to execute with ADO.Net, but I like the idea of staying in NHibernate for consistency. Anyway, I found a lot of documentation on how to call one, but not with a parameter, so I thought I’d document that here. Note that I recommend NHibernate in Action<\/a> for understanding all the various query methods.<\/p>\n

This will be a simple and contrived example, that in no way justifies not just using NH linq, Criteria, or HQL to query. But let’s say you have a book table, and you want to query by author and for some reason you need to do this in a stored proc, because there is some aspect of the code or optimization that you only can do in the db.<\/p>\n

Create your stored proc:<\/p>\n

\r\nUse [MyDatabase]\r\nGo\r\n\r\nif OBJECT_ID('[dbo].sp_BooksByAuthor') is not null\r\nbegin\r\n\tdrop proc [dbo].sp_BooksByAuthor\r\nend\r\ngo\r\n\r\ncreate proc [dbo].sp_BooksByAuthor\r\n   @author_id bigint\r\nas\r\nbegin\r\nSet NOCOUNT on\r\nSelect b.* \r\nFrom Books b\r\nWhere AuthorId = @author_id\r\nend\r\nGo<\/code><\/pre>\n
*Note the alias for books is optional, but if you do it, you need to specify it in the query mapping (see below).<\/p>\n
Then map the query. Somewhere in one of your mapping files, but outside of a class put:<\/p>\n
<sql-query name=\"MyBookByAuthorQuery\">\r\n  <return class=\"Book\" alias=\"b\" \/>\r\n  exec sp_BooksByAuthor :AuthorId\r\n<\/sql-query><\/code><\/pre>\n
Finally, your NHibernate query would look like as follows:<\/p>\n
public IList<Book> GetBooksByAuthor(Author author)\r\n{\r\n   var session = SessionFactory.GetCurrentSession();\r\n   var qry = session.GetNamedQuery(\"MyBookByAuthorQuery\");\r\n   qry.SetParameter(\"AuthorId\", author.Id);\r\n   return qry.List<Book>();\r\n}<\/code><\/pre>\n
Your data access may vary a lot from a simple method like that, but you get the idea.<\/p>\n
One thing worth noting, if you get an error about clazz_, it’s related to polymorphism. Go read this post for how to write your sql to account for it: http:\/\/www.methodicmadness.com\/2009\/01\/nhibernate-what-is-heck-clazz.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Using SQL Queries in the .NET ORM NHibernate with parameters. <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,34,18],"tags":[41,69,154,103,272,153],"class_list":["post-393","post","type-post","status-publish","format-standard","hentry","category-aspnet","category-microsoft","category-programming","tag-net","tag-linkedin","tag-named-queries","tag-nhibernate","tag-programming","tag-stored-procedures"],"_links":{"self":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts\/393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/comments?post=393"}],"version-history":[{"count":0,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/posts\/393\/revisions"}],"wp:attachment":[{"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/media?parent=393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/categories?post=393"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/hoolihan.net\/blog-tim\/wp-json\/wp\/v2\/tags?post=393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}