The latest installment of the Summer of NHibernate screencast series is now available for download from the Summer of NHibernate main site. In this session we catch up on some odds and ends from miscellaneous user requests for topics that weren’t really part of the originally-planned curriculum.
Most of these were excluded from the original curriculum because I didn’t consider them part of the ‘foundational’ concepts that this series was designed to cover. However, each of these topics received enough requests for coverage that I felt it was important to address them even if I still don’t consider them to really be ‘foundational’ concepts. Many of them start to look like edge-cases, mid-level, or advanced topics to me, but one person’s edge-case is another’s run-of-the-mill everyday challenge depending on your situation, so I caved in to the repeated requests to cover some of this stuff.
In this session we explore NHibernate’s support for interacting with stored procedures to perform CRUD operations, introduce the methods for responding to ISession events using classes that implement the IInterceptor interface, and briefly explore the really useful NHibernate Query Analyzer (by Oren Eini).
As usual, comments, feedback, suggestions, etc. are encouraged.
I just wanna say, tahnk you for sharing your knowledge and also, request a little thing, if possible for you. Please, teach us tha best practices – well, me :)- on how to manage sessions in winforms applications. Alway Asp.Net is shown and never windows forms…
thanks againg for the amazing work and time spent.
The reason you tend to see so many examples of managing session lifespan in a web app is that this is the context in which its the most challenging/there are the most issues to overcome.
In a winforms app, the management of this is much more straightforward, but how about this: I will promise that in the upcoming session on managing Session lifespan in a web app I will compare and contrast the techniques that I am planning to show for the web with how such a thing would be done in a windows app. That way both are covered.
Hi Steven – thanks so much for the screencasts.
I would just like to echo what Paulo says above – it would be awesome to see how to handle session in a winforms app.
when i run mygeneration tool for creating sp i get this error message on the console
8/21/2008 4:39:14 PM – ERROR: [System.UnauthorizedAccessException] – Access to the path ‘C:\Program Files\MyGeneration13\Settings\DefaultSettings.xml’ is denied.
8/21/2008 4:39:14 PM – Template execution failed.
8/21/2008 4:39:14 PM – Completed
i fixed the error and the fix is: i’m running vista and when i open mygeneration tool i have to run “run as administrator” otherwise does not work i guess…
question on SP:
i have a question how NHIB expects parameters and every time you write run SP first you have to run HQL to see how NHIB is taking the parameters and then you modified your SP based on that? correct? don’t you think its a royal pain?
which raises another question.. let say in future if i add another parameter to my SP does it mean i have to run first see HQL how NHIB parameter is taking and then modified ?
please shed some light.
thanks for great video… i luv it 🙂
You are correct that its indeed ‘a pain’ to have to structure your SPs to match the order that Nhib emits parameters for its own generated parameterized SQL; that’s sort of why the slide in the deck that says “its a myth that NHib’s support for sprocs is completely flexible and without limit” 🙂
As for having to write HQL (or criteria queries) first just to see the order of the params NHib will use, this is not really needed if you just understand the following coventions that NHib *usually* abides by when generating its SQL:
1) the delete SQL always just passes a single param, the ID of the entity to delete (unless as we saw in the video you are using VERSIONing for optimistic concurrency support or as you can guess if you are using TIMESTAMP for concurrency support)
2) the insert SQL always passes the params in the *exact* order of each [property /] tag (square brackets used here since WordPress strips out angle-brackets) in the mapping file with the following exception: the ID param is almost always passed in the LAST param position (no concurrency support needed for an INSERT so no special rules about concurrency in these cases)
3) the update SQL always passes the params in the same order as specified in the [property /] tags in the mapping files except for the id which is passed last UNLESS you are using VERSION for concurrency support in which case ID is second-to-last and ‘oldversion’ is the last param passed
You can sort of deduce/infer these ‘rules’ from watching the screencast so if you want to just develop your SPs based on these ‘conventions’ you can USUALLY end up mostly OK with them and not need to actually write HQL to inspect the generated SQL.
You should note a few things about this approach however:
1) you of course need some unit tests around the calls that use these sprocs to ensure that all the ‘inferring’ (guessing!) you’re doing per the above rules is really completely correct
2) these ‘rules’ are really just reverse-engineering of NHib’s generated SQL ‘conventions’ and they are absolutely NOT documented anywhere and NOT guaranteed to be true in all cases or even from one version of NHib to the next; like all ‘conventions’ that are unwritten and undocumented, they may change without notice as you are really relying on a completely undocumented ‘feature’ of NHib that may change unexpectedly
The bottom line is that while you MAY proceed based on these ‘rules’ (inferences about NHib’s behavior when generating its SQL), you certainly do so at your own risk 🙂
thank you so much Steve!!! you are awesome… you are!
I just listened to session 1 this week and noticed that in setting up the configuration for NHibernate there are a few “gotchas” around config files etc. Do you know of any Visual Studio template for handling this for you? It would help us noobies a lot when getting up and running for the first time.
I’m not aware of any such thing out there (though it certainly doesn’t mean that it doesn’t exist 🙂 )
However, VS 2005 and 2008 make it pretty simple for users to create new item and new project templates (check out several very good articles about this on http://msdn.microsoft.com) and so you could easily create your own once you got one setup that suits your (common) needs.
The order of the parameters is not important if you call the stored procedure and explicitly set the variable like…
exec UpdateCustomer @LastName = ?, @FirstName = ?, @Version = ?, @CustomerId = ?
This is a nice tip — good catch~!
Corey: is this valid ?
exec UpdateCustomer @Version = ?, @CustomerId = ?,
@LastName = ?, @FirstName = ?
I think the suggestion was that this snippet was a way to not have to set the params order in your sproc to perfectly match the order that NHibernate would generate them for use in its own parameterized SQL statements.
This doesn’t do anything that changes the order that NHib sends the params, but would let you not have to change the order of the params in the sproc, I think.
Yes the order that you pass the parameters should match nHibernate’s ordering in the mapping file, but my suggestion would not require you to modify a sprocs parameter ordering to facilitate nHibnerate’s needs.
So the question of is this valid using @CustomerId as the second parameter, no that would not be valid because nHibernate would require the last parameter to be @CustomerId.
First of all a BIGGGG thanksss to you for these videos they really are wonderfulv(and I really hope that we have similar “Summer of ” kinda stuff for other technologies. The video are to the point and really informative.
A small request….The use of the stored procedures you just showed almost used INSERTS and UPDATES and DELETES. Can you please show a example of a stored proc that actually retrieves data and spit it back to the object that we created.
Thanks for the feedback; indeed I skipped over using sprocs to retrieve data and return objects. This is actually pretty straightforward compared to INSERT, UPDATE, and DELETE so I skipped over it.
If you’re interested in doing some research on how READS using sprocs work, here is the (high-level) overview:
NHibernate supports the concept in the mapping file(s) of ‘named-queries’. You can define a named query as any valid SQL statement for your target platform (e.g., T-SQL for MSSQL, pl/sql for ORA, etc.) so its perfectly legal for this to be an EXEC… statement that calls a sproc.
You then call that named query from the ISession instance and (usually) use AliasToBeanResultTransformer(…) to convert the resultset into your desired object(s).
AliasToResultBeanTransformer(…) is a function-name hold-over from the Java/Hibernate world where the original intent of the method was to return a fully-hydrated Enterprise Java Bean object ready to be used in your application. It works on the assumption that your query will return a column name that correctly matches perfectly with the name of each property on your object/class and it just sets each property to the value from each column with a matching name (for more details, refer to the NHib docs on this method).
An alternate method if your sproc doesn’t return cols with names that perfectly match the props on your class is to use the HQL-only strategy of doing a SELECT new MyClass(…) kind of statement to dynamically invoke the construction of your desired result object as I showed in either session 02 or 02a when returning my strongly-typed CustomerFirstnameCounter objects.
Hope this helps~!
Greetings and thanks for the prompt reply.
Well, it actually worked, but with some minor changes
I was able to execute a stored procedure (listed below) by using the named-query syntax
CREATE PROCEDURE [dbo].[GetCustomerByFirstNameAndLastName]
select * from Customer where Firstname=@Firstname And Lastname=@Lastname
exec GetCustomerByFirstNameAndLastName :Firstname, :Lastname
it uses rather than as I was getting some weird error when using . I googled and came to know that one should use . Is this what you suggested ? or this is compeltly a different approach ?
I also got stuck when I tried to use the HQL syntax for executing stored procedures and could not figure out the way to implement the “SELECT new MyClass()….” sysntax. Help ?
Cheers and Thanks,
Sorry about sending you on a wild goose chase there re: the SELECT new MyClass(…) idea — that won’t work for calling a sproc; I seem to have conflated two separate issues in my reply.
Glad you got it working otherwise; sorry for the misdirection there; lost my head 🙂
i’m on the same boat now 🙂 can you please give more detail on how did you able to use named-query from your class? snippet will be the good.
what would you do for Select ?
exec CustomerInsert ?,?,?,?
exec CustomerUpdate ?, ?, ? ,?, ?
exec CustomerDelete ?, ?
NHib does not provide for Get ?
ok so far i found this:
but still unable to execute my method
here is what i have done so far:
1) i have created my sp
2) created my hbm.xml file like this:
exec GetVisitorInformationByVisitInfoID :visit_info_id
3) how would you execute SP?
i tried something like this:
public IList VisitInformationByVisitInfoID_SP(int VisitId)
i know the above query throws an error but still trying to understand
oops my hbm.xml file wiped out
i will try without ”
<?xml version=”1.0″ encoding=”utf-8″?>
<class name=”DataTransfer.VisitInformation_SP,DataTransfer” table=”`VISIT_INFORMATION`” lazy=”true”>
<id name=”VisitInfoId” column=”`visit_info_id`” type=”int”>
<generator class=”native” />
<property type=”string” not-null=”true” length=”4″ name=”le_fst_nm” column=”`le_fst_nm`” />
<property type=”string” not-null=”true” name=”le_mid_nm” column=”`le_mid_nm`” />
<property type=”string” not-null=”true” name=”le_last_nm” column=”`le_last_nm`” />
<property type=”string” not-null=”true” length=”4″ name=”visit_info_nm” column=”`visit_info_nm`” />
<return alias=”visit_information” class=”DataTransfer.VisitInformation,DataTransfer” />
exec GetVisitorInformationByVisitInfoID :visit_info_id
[…] public links >> exceptions Summer of NHibernate Session 10: SProcs, Interceptors, and NHQA is … First saved by lifeoffthetube | 1 days ago SOAP, Web Services, and PHP First saved by […]