‫Paging کوئری‌های SQL در NHibernate

تصور کنید تعدادی کوئری SQL دارید که می‌خواهید Paging را با استفاده از NHibernate در آنها فعال کرده و بنا به دلایلی اجازه بازنویسی آنها با دیگر APIهای NHibernate نداشته و آنها را صرفاً باید از طریق ObjectDataSaource به کنترل‌های ASP.NET بخورانید. سه راه برای انجام این کار وجود دارد.

راه اول:
استفاده از stored procedureهای کمکی که خاص این قضیه ایجاد شده‌اند. یکی از آنها Paging_RowCount است که توسط دوستم مسعود رمضانی معرفی شده. این ابزارها queryی (و احتمالاً stored procedure) شما دریافت کرده و Paging را روی آنها اعمال می‌کنند. بدی این ابزارها این است که محدودیت‌های زیادی در query دریافتی اعمال و کرده و در خیلی از حالات کار نمی‌کنند. بدی دیگر آنها عدم خوانایی و قابلیت نگهداری پایین آنهاست چون Query ورودی این ابزارها معمولاً به صورت تیکه تیکه در داخل کد C#‎ نگهداری می‌شود. البته من چندان تخصصی روی SQL ندارم و با کل این ابزار به طور کامل کار نکرده‌ام اما همین دلیل دوم برای من کافی است تا از آن دوری کنم.

راه دوم:
استفاده از Named SQL Queries در NHibernate است. در این روش شما می‌توانید با استفاده از SetFirstResult و SetMaxResults وظیفه Paging را به عهده NHibernate بگذارید. در این حالت NHibernate خودش به طور خودکار و پشت پرده مشابه روش اول عمل کرده و Paging را برای شما مهیا می‌نماید. این روش بسیار زیبا، راحت، خوانا و قابل نگهداری است اما بدی آن این است که در بعضی حالات که Query کمی پیچیده می‌شود عمل نمی‌کند. یعنی این که بیشتر برای Queryهای ساده مناسب است.

راه سوم:
وقتی که نمی‌خواهید از روش اول استفاده کنید و روش دوم هم درست کار نمی‌کند مجبورید خودتان وظیفه Paging را به عهده بگیرید. یعنی کاری را که روش‌های اول و دوم به صورت خودکار انجام می‌دادند شما به صورت دستی انجام دهید. روش معمول برای Paging در MS SQL Server که راه‌های اول و دوم هم از آن استفاده می‌کنند روش ROW_NUMBER()‎ است. من خودم برای انجام این کار از یک Query حاضر و آماده که توسط NHibernate تولید می‌شد استفاده می‌کنم. به نمونه اسکریپت زیر توجه فرمایید:


SELECT TOP (@maxResults) * FROM (

select

*

,ROW_NUMBER() OVER(ORDER BY (@orderBy)) as _sort_row from

Toy

)as query WHERE query._sort_row > @firstResult
ORDER BY query._sort_row
البته من همین query را هم در داخل یک Named SQL Query قرار داده‌ام ولی به جای فراخوانی توابع SetFirstResult و SetMaxResults از پارامترهایی که خودم به همین اسامی در query تعبیه کرده‌ام استفاده می‌کنم.

‫استفاده مستقیم از SQL در NHibernate

NHibernate یک ORM است که شما را ترغیب می‌کند برای دسترسی به دیتابیس از APIهای خاص خودش یعنی HQL، ICriteria و Linq-to-NHibernate استفاده کنید. اما راه را برای آنها که به هر دلیل ترجیح می‌دهند یا مجبورند از SQL استفاده کنند نبسته است. NHibernate دو راه برای انجام این کار دارد: استفاده از ISession.CreateSQLQuery و استفاده از Named SQL queries.

استفاده از راه حل اول خیلی سر راست است ولی چون برنامه‌نویس را تشویق می‌کند کوئری SQL را در متن کدهای C#‎ نگهداری کند توصیه نمی‌شود. در عوض راه حل دوم یعنی Named SQL queries تمیزتر و قابل انعظاف‌تر عمل می‌کند. در این روش شما Queryهای مورد نظر را درست مشابه mappingها در فایل‌هایی با پسوند ‎.hbm.xml نگهداری می‌کنید. به این ترتیب هم کدهای C#‎ و اسکریپت‌های SQL با هم قاطی نمی‌شوند و هم ممکن است بتوانید از یک سری بهینه‌سازی‌ها و cachingهای SQL Server بهره‌مند شوید.

به عنوان یک نمونه از Named SQL queries به مورد زیر توجه فرمایید. منتها توجه داشته باشید که فایل query حتماً باید به صورت Embedded Resource کامپایل شده و dll آن به کمک AddAssembly به فهرست Assemblyها اضافه شده باشد.














برای کسب اطلاعات بیشتر به Documentation مربوط به NHibernate در nhforge مراجعه کنید.

Paging problem with Named SQL queries

Named SQL query in NHibernate is an easy way to apply paging through native SQL. SetFirstResult and SetMaxResults helps a lot here. But the problem is when query is get a bit complicated or for some other reasons that I don’t know, paging does not works on pages after first page. In this case a wrong sql query is generated.

From given answers I didn’t realized what’s the root cause and if there is any solutions or not. But because I was in emergency I used my work-around for it.

My work-around is adding paging elements directly in the query and pass “first result” and “max results” to the query as parameters instead of using SetFirstResult and SetMaxResults. Consider following example:





default-access="field.camelcase"
namespace="nhtest"
assembly="nhtest">



SELECT TOP (:maxResults) * FROM (

select

*

,ROW_NUMBER() OVER(ORDER BY (:orderBy)) as _sort_row from

Toy

)as query WHERE query._sort_row > :firstResult
ORDER BY query._sort_row

]]>





This code is inspired from script that NHibernate itself produces while correct paging. Parametrization “top” and “order” sections was not very easy. I got helped from here and here. Notice parametrized “order” is not necessary for paging. It was my own requirement.