The Best C# Programmer In The World - Ben Perkins Member Login  | Newsletter | RSS Feeds


 
 
 
TheBestCSharpProgrammerInTheWorld.com 
 
The Best C# Programmer In The World - Ben Perkins
LINQ to NHibernate, JQuery/jqGrid SubGrid, HQL and ICriteria  
 
LINQ to NHibernate, JQuery/jqGrid SubGrid, HQL and ICriteria
 
My video on this topic can be found here.
 
We will do 4 things in the article.
 
  • Add a subGrid to a JQuery/jqGrid Grid.
  • Populate the Grid using ICriteria
  • Populate the subGrid using IQuery (HQL)
  • Change the ICriteria and HQL to use LINQ to NHibernate
 
First, let's open the Views/Home/Index.aspx file and add the /jqGrid declarations and source code:
 
                            script src="../../Scripts/jqgrid/src/grid.subgrid.js" 
                                            type="text/javascript">/script>
                            < id='PostList
                            /table>div id='PostList_pager'>/div>
                            script type='text/javascript'
                                jQuery(document).ready(function() {
                                    jQuery('#PostList').jqGrid({

                                        url: '/Home/PostList/',
                                        datatype: 'json',
                                        mtype: 'GET',
                                        colNames: ['Id', 'Title', 
                                                   'Text', 'PostedAt', 
                                                   'BlogId', 'UserId'],
                                        colModel:
                                            [
                                                { name: 'Id', index: 'Id', 
                                                    width: 60, align: 'left' },
                                                { name: 'Title', index: 'Title', 
                                                    width: 200, align: 'left' },
                                                { name: 'Text', index: 'Text', 
                                                    width: 200, align: 'left', 
                                                    sortable: false },
                                                { name: 'PostedAt', index: 'PostedAt', 
                                                    width: 75, align: 'left' },
                                                { name: 'BlogId', index: 'BlogId', 
                                                    width: 60, align: 'left' },
                                                { name: 'UserId', index: 'UserId', 
                                                    width: 75, align: 'left' },
                                            ],
                                        pager: jQuery('#PostList_pager'),
                                        rowNum: 25,
                                        rowList: [5, 10, 25, 50],
                                        height: 'auto',
                                        width: '950',
                                        sortname: 'ID',
                                        sortorder: "ASC",
                                        viewrecords: true,
                                        multiselect: false,
                                        subGrid: true,
                                        subGridRowExpanded: 
                                            function(subgrid_id, row_id) {
                                            var subgrid_table_id;
                                            subgrid_table_id = subgrid_id + "_t";
                                            jQuery("#" + subgrid_id).html("
                                            table id='" 
                                                + subgrid_table_id 
                                                + "' class='scroll'/table");
                                            jQuery("#" + subgrid_table_id).jqGrid({
                                                url: '/Home/BlogList/' + row_id,
                                                datatype: 'json',
                                                colNames: ['Id', 'Title', 
                                                           'Subtitle', 
                                                           'AllowsComments', 
                                                           'CreatedAt'],
                                                colModel:
                                                    [
                                                    { name: 'Id', 
                                                        index: 'Id', 
                                                        width: 60, 
                                                        align: 'left' },
                                                    { name: 'Title', 
                                                        index: 'Title', 
                                                        width: 200, 
                                                        align: 'left' },
                                                    { name: 'Subtitle', 
                                                        index: 'Subtitle', 
                                                        width: 200, 
                                                        align: 'left', 
                                                        sortable: false },
                                                    { name: 'AllowsComments', 
                                                        index: 'AllowsComments', 
                                                        width: 75, 
                                                        align: 'left' },
                                                    { name: 'CreatedAt', 
                                                        index: 'CreatedAt', 
                                                        width: 60, 
                                                        align: 'left' },
                                                    ]
                                            });
                                        }
                                    })
                                    jQuery("#PostList").jqGrid('navGrid', 
                                    '#PostList_pager', { add: false, 
                                                         edit: false, 
                                                         del: false, 
                                                         search: false })
                                })
                            
 
We have 2 mapped classes, not show here, that match with the Grid and SubGrid. The PostList method populates the Grid and the BlogList method populates the SubGrid.
 
The Controllers/HomeController.cs file contain the PostList and methods. For example reasons only, I have used the NHibernate ICriteria interface to populate the JSON object in the PostList and I implemented the IQuery interface in the BlogList. The IQuery interface utilizes HQL. See below.
 
The PostList method creates an instance of the NHibernate Configuration object, load the Assembly, creates the SessionFactory and Session needed to execute the ICriteria List. Once I get the result set back from NHibernate, I need to convert it to a JSON arrary so I can easily display it in the jqGrid.
 
                            public ActionResult PostList(string sidx, 
                                                            string sord, 
                                                            int page, int rows)
                            {
                                Configuration config = new Configuration();

                                config.AddAssembly("NHPosts");
                                ISessionFactory factory = 
                                        config.BuildSessionFactory();
                                ISession session = factory.OpenSession();

                                #region ICriteria

                                ICriteria criteria = 
                                        session.CreateCriteria(typeof(Posts));
                                criteria.SetMaxResults(25);
                                IList posts = criteria.List();

                                #endregion

                                var jsonData = new
                                {
                                    page = page,
                                    rows = (
                                            from po in posts
                                            select new
                                            {
                                                id = po.Id,
                                                cell = new string[] {
                                                    po.Id.ToString(),
                                                    po.Title,
                                                    po.Text,
                                                    po.PostedAt.ToString(),
                                                    po.BlogId.ToString(),
                                                    po.UserId.ToString()}
                                            }).ToArray()
                                };

                                return Json(jsonData, JsonRequestBehavior.AllowGet);
                            }
 
The BlogList is similiar to the PostList. In this method you see I have a query as a string and am passing it to the IQuery object so that it can be executed. Make a special note that creating an NHibernate SessionFactory is very time consuming. The way I am doing it here is not best practise, however, it is a simple way of getting the point across. I recommend that you create a Session Provider and create a SessionFactory for each domain via a singleton pattern.
 
                        public ActionResult BlogList(string sidx, 
                                                string sord, int page, 
                                                int rows, int id)
                            {
                                Configuration config = new Configuration();

                                config.AddAssembly("NHPosts");
                                ISessionFactory factory = 
                                        config.BuildSessionFactory();
                                ISession session = factory.OpenSession();

                                #region IQuery - HQL

                                IQuery query = 
                                    session.CreateQuery
                                            ("from Blogs where Id = :BLOGID");
                                query.SetString("BLOGID", id.ToString());
                                IList blogs = query.List();

                                #endregion

                                var jsonData = new
                                {
                                    page = page,
                                    records = 25,
                                    rows = (
                                            from bl in blogs
                                            select new
                                            {
                                                id = bl.Id,
                                                cell = new string[] {
                                                    bl.Id.ToString(),
                                                    bl.Title,
                                                    bl.Subtitle,
                                                    bl.AllowsComments.ToString(),
                                                    bl.CreatedAt.ToString()}
                                            }).ToArray()
                                };            

                                return Json(jsonData, JsonRequestBehavior.AllowGet);
                            }
 
The final part is using LINQ to NHibernate. The release of NHibernate 3.0 comes the LINQ to NHibernate capability. At the time of writing this post 3.0 is still in Beta so examples will be made using the Beta version. Before you attempt to use the LINQ functionality be sure you add the reference to the 3.0 binaries and remove the 2.0 references.
 
To implement the LINQ to NHibernate in the PostList method, simply comment out the code within the #region ICriteria and replace it with this code:
 
                        IList posts = (from p in session.Query()
                                  select p).Take(25).ToList();
 
It is goot practise to limit the result set, in case there happens to be a million rows. This is in regards to the .Take(2) method. This LINQ query will return 25 rows.
 
Similiar action to be taken in the BlogList method to convert to LINQ. Comment out the code within the #region IQuery - HQL and replace it with this code:
 
                        IList blogs = (from b in session.Query()
                                  where b.Id == id
                                  select b).ToList();
 
But wait, there is more. As a special added feature I want to show you the how Futures work. It is a very nice feature which can reduce execution time and improve network performance. What futures do is combine multiple queries together, send them together (most often across the network) to the database, executes them all and returns multiple result sets. In contrast, if a transaction requires multiple queries to complete, it would typically require multiple round trips to the database across the network.
 
In the PostList method, we will implement a Futures example.
 
Add a second query to run and mark it a Future.
 
var postsCount = session.Query().ToFuture();
 
Modify the LINQ to NHibernate query we added to the PostList method to include the Future method.
 
                            IList posts = (from p in session.Query()
	                select p).Take(25).ToFuture().ToList();
 
And then add a records variable to the jsonData creation logic.
 
                        var jsonData = new
                        {
                            page = page,
                            records = postsCount.Count(),
                            rows = (
                                    from po in posts
                                    select new
                                    {
                                        id = po.Id,
                                        cell = new string[] {
                                            po.Id.ToString(),
                                            po.Title,
                                            po.Text,
                                            po.PostedAt.ToString(),
                                            po.BlogId.ToString(),
                                            po.UserId.ToString()}
                                    }).ToArray()
                        };
 
And there you have it. A 'select count(*) from Posts' and a 'select * from Posts' will be executed within a single round trip to the database.
 
Download the source
 
 
 
Feedback / Question
 
Your Name:Your Email:
 
Subject:
 
Feedback/Question:
 
 
 
I had to remove the capability to leave feedback due to this. Will be back soon.
 
 
Comment posted: 1/15/2012 8:23:28 PM by seo company
 
This article on www.thebestcsharpprogrammerintheworld.com gives the light in which we can observe the reality.
Comment posted: 1/18/2012 10:55:18 AM by buy anabolics
 
I greatly appreciate every one of the informative read on www.thebestcsharpprogrammerintheworld.com. I most certainly will spread the phrase about your site with people. Cheers.
Comment posted: 1/13/2012 10:41:26 AM by seo company
 
I will really love for you for guests posting on www.thebestcsharpprogrammerintheworld.com
Comment posted: 4/30/2011 7:53:55 AM by Ordedsifs
 
be happy and love. kiss
Comment posted: 1/14/2012 11:44:37 AM by seo software
 
That was some interesting stuff here on www.thebestcsharpprogrammerintheworld.com Thanks for posting it.
 
page.Translate()
 
 
blog.Stats()
 
  Posts: 123
  Comments: 90
  Fundamentals: 16
 
my.Books()





 
me.About()
 
 
 
 
 
blog.Archive()
 
2014 February  (5)
2014 January  (5)

2013 December (2)
2013 November (2)
2012 December (2)
2012 November (2)
2012 October (2)
2012 September (2)
2012 August (1)
2012 July (3)
2012 June (2)
2012 May (4)
2012 April (5)
2012 March (4)
2012 February (4)
2012 January (5)
2011 December (2)
2011 November (6)
2011 October (7)
2011 September (7)
2011 August (9)
2011 July (9)
2011 June (8)
2011 May (9)
2011 April (7)
2011 March (9)
2011 February (8)
2011 January (8)
2010 December (7)
2010 November (8)
2010 October (4)
 
site.Visits()
 
free counters
 
tag.Cloud()
 
code.Disclaimer()
 
The sample code on this website is provided to illustrate a concept and should not be used in applications or Web sites without proper professional consultation, as it may not illustrate the safest coding practices. I assume no liability for incidental or consequential damages should the sample code be used for purposes other than as intended.
 
   


The Best C# Programmer In The World - Ben Perkins, © 2010, All Rights ReservedContact Ben