One of the most-asked questions when it comes to the ASP.NET Ajax RadGrid is "How do I improve performance?". We have covered this in some Webinars and videos that can be found on Telerik TV in a series which has been released in three parts: Part One, Part Two and Part Three. One thing I wanted to bring up with respect to performance is the usage of LINQ (Language Integrated Query) and our RadGrid. Although LINQ has been out since the initial release of .NET 3.5 I still receive quite a few questions when it comes to using it with our RadGrid for ASP.NET Ajax. The abbreviated answer is that yes, you can use LINQ in conjunction with our RadGrid. The somewhat longer version is the reason for this blog post!

What I wanted to display today is nothing new (we have some examples on our demo page which displays the usage of our RadGrid and LINQ) but I still feel like it is necessary to show, perhaps in a slimmed down version with a bit of explanation, how easy it is to use LINQ. I know that some of you might look at LINQ with a bit of suspicion since it might look a bit cryptic to begin with - especially if you're used to MS SQL, but hopefully this blog post will help shed some light on the ease of use and performance gains you can receive from implementing it.

The absolutely easiest way to use LINQ and our RadGrid is to take use of the LinqDataSource which is provided by default within the .NET 3.5 framework. Some of you might be familiar with how easy it is to place a SqlDataSource on a page and use that with the RadGrid - and it's basically just as easy with LINQ.

The first thing to do is of course to have a database to connect to! In my project I simply included a copy of Northwind.mdf. This can be found in the same directory as our local demos that come from an install of our controls, and it can be found in the following directory: [Installed Directory]\Telerik\RadControls for ASP.NET AJAX [Version]\Live Demos\App_Data\. Just add this as an "existing item" in your App_Data folder:

Solution View

We now need to set up our LINQ to SQL connection, so if you right-click on your project and select Add > New Item and under the Data menu item you will find the LINQ to SQL Classes item. You will then be greeted by a graphical interface where you can simply drag tables from your favorite Database onto the surface (in my case the Customer table from Northwind):

Adding LINQ to SQL Class


Customer Table

Once you have dragged out the table and you see the above table save and compile your project. Now we have a LINQ to SQL setup that we can leverage through a LinqDataSource object which can be used with the RadGrid! The next steps are just as easy as what we've done so far, and will be very similar to what you might have done previously with SqlDataSource objects. Under the Data tab in the toolbox select a LinqDataSource

LinqDataSource in Toolbox

Drag this onto your page and in the design-view select "Configure Data Source..." from the control's smart tag. You should see the start of the configuration wizard, and if you check the "Show only DataContext objects" the dropdown should display the data context from the .dbml file that we generated previously. As you can see I named my LINQ to SQL Class "LINQDataClass" (original name, I know!) and there is a suffix of "DataContext" added to it.

LinqDataSource Wizard

Hitting next will bring you to the next wizard step which allows you to generate the query you want to use for your RadGrid. Here I simply selected a few fields (CustomerID, CompanyName, ContactName, City, Bool) from the Customers table that I dragged out from Northwind. Once you hit finish you now have a LinqDataSource ready to go!

LinqDataSource Wizard - Query

Now all you have to do is drag out a Telerik RadGrid on your page, click on the smart-tag and in the dropdown on the top of this menu select LinqDataSource1. I would also recommend checking off the Paging, Sorting and Filtering options under "General Features"

RadGrid Smart-tag

And that's it! Now you have successfully connected a Telerik RadGrid for ASP.NET Ajax to a LinqDataSource! You might be wondering what the big fuss is about when you have to go through all of those steps when compared to the SqlDataSource object - and why use LINQ at all? Well there are a few different reasons why you might want to use this. There are a few articles around the internet which discusses the pros and cons of using a LinqDataSource versus the other various data sources (such as
this one). In terms of our RadGrid for ASP.NET Ajax it actually takes full use of LINQ in order to do all of its paging, sorting and filtering. This applied to both the .NET 3.5 and .NET 4.0 version of our RadGrid, while the .NET 2.0 (since LINQ was introduced in .NET 3.5) takes a bit of a different approach. Essentially the RadGrid uses LINQ to execute all of these operations on the database level rather than pull all data from the database and process all of that information on the web server. This reduces the time taken do page, sort, filter etc. in the RadGrid by quite a bit, and allows for a very efficient RadGrid in an ASP.NET Ajax application. We actually have a very nice demo that shows how using a LinqDataSource like above, along with caching and a few other techniques, can grant a very snappy and responsive RadGrid. The demo even shows the time taken to load the page of the RadGrid so that you can track how each technique can impact the performance. I recommend checking it out!

Now have bound our RadGrid to a LinqDataSource for just displaying data - but what about the CRUD operations? No need to worry, the steps to take are quite easy. Of course, as with most things within the RadGrid, there's an automatic way and a manual way.

Using the above example we can very easily set up automatic CRUD operations. The first thing to do is click on the LinqDataSource's smart-tag that should look something like this:

LinqDataSource Smart-tag

If you check all three of those checkboxes and set the following properties in the RadGrid to true: AllowAutomaticInserts, AllowAutomaticUpdates, and AllowAutomaticDeletes then you will have automatic inserts, updates, and deletes in your RadGrid! I know that seems a bit easy - but that's all you need to do! Since the LinqDataSource takes care of the insert, updates and deletes when we use the smart-tag the RadGrid knows to use those functions when any of the operations occur. We also have a demo that shows off this functionality, which I highly recommend that you check
out. There is also a very helpful documentation article which provides a bit more information regarding this particular example.

As I mentioned before there is also the manual way to do things. Although this is a bit trickier it's still fairly easy to implement. While you can't simply set up a declarative source and have the RadGrid automatically populate based on this you just need to subscribe to a few events and you're good to go. The events to look out for, and why, are the following:

  • OnNeedDataSource: This takes care of the initial binding of the RadGrid as well as paging, sorting and filtering.
  • OnInsertCommand: Subscribing to this event allows us to intercept the insertion of an item.
  • OnUpdateCommand: Subscribing to this event allows us to intercept the update of an item.
  • OnDeleteCommand: Subscribing to this event allows us to intercept the deletion of an item.

So how does this all come together? Well first we can take a look at the .aspx page:

<telerik:RadGrid  runat="server" ID="RadGrid1" AutoGenerateColumns="false"
    AllowPaging="true" OnNeedDataSource="RadGrid1_NeedDataSource" OnUpdateCommand="RadGrid1_UpdateCommand"
    OnInsertCommand="RadGrid1_InsertCommand" OnDeleteCommand="RadGrid1_DeleteCommand">
    <MasterTableView DataKeyNames="CustomerID" CommandItemDisplay="Top" InsertItemPageIndexAction="ShowItemOnCurrentPage" EditMode="InPlace">
        <Columns>
            <telerik:GridEditCommandColumn ButtonType="ImageButton" />
            <telerik:GridBoundColumn DataField="CustomerID" HeaderText="Customer ID" ReadOnly="true"
                ForceExtractValue="Always" ConvertEmptyStringToNull="true" />
            <telerik:GridBoundColumn DataField="CompanyName" HeaderText="Company Name" />
            <telerik:GridBoundColumn DataField="ContactName" HeaderText="Contact Name" />
            <telerik:GridBoundColumn DataField="City" HeaderText="City" />
            <telerik:GridBoundColumn DataField="Bool" HeaderText="Bool" />
            <telerik:GridButtonColumn ConfirmText="Delete this product?" ConfirmDialogType="RadWindow"
                ConfirmTitle="Delete" ButtonType="ImageButton" CommandName="Delete" />
        </Columns>
        <EditFormSettings>
            <EditColumn ButtonType="ImageButton" />
        </EditFormSettings>
    </MasterTableView>
    <PagerStyle Mode="NextPrevAndNumeric" />
</telerik:RadGrid>

As you can see above I've subscribed to the events that I mentioned as well as defining the columns that I want to display in my RadGrid. Here's what everything looks like in the code-behind:

private LINQDataClassDataContext _dataContext;
 
        protected LINQDataClassDataContext DataContext
        {
            get
            {
                if (_dataContext == null)
                {
                    _dataContext = new LINQDataClassDataContext();
                }
                return _dataContext;
            }
        }
 
        public override void Dispose()
        {
            if (_dataContext != null)
            {
                _dataContext.Dispose();
            }
            base.Dispose();
        }
 
        protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            RadGrid1.DataSource = DataContext.Customers;
        }
 
        protected void RadGrid1_UpdateCommand(object source, GridCommandEventArgs e)
        {
            var editableItem = ((GridEditableItem)e.Item);
 
            //extract CustomerID of GridItem being updated
            var customerID = (string)editableItem.GetDataKeyValue("CustomerID");
 
            //retrive entity form the database associated with the above CustomerID
            var customer = DataContext.Customers.Where(n => n.CustomerID == customerID).FirstOrDefault();
            if (customer != null)
            {
                //update entity's information with what was entered in the RadGrid
                editableItem.UpdateValues(customer);
 
                try
                {
                    //submit above changes to the database
                    DataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    errorLiteral.Text = ex.ToString();
                }
            }
        }
 
        protected void RadGrid1_InsertCommand(object source, GridCommandEventArgs e)
        {
            var editableItem = ((GridEditableItem)e.Item);
             
            //create new Cusomter entity
            Customer customer = new Customer();
 
            //extract values from the item we are inserting
            Hashtable values = new Hashtable();
            editableItem.ExtractValues(values);
 
            //populate entity's relevant properties
            customer.CompanyName = (string)values["CompanyName"];
            customer.ContactName = (string)values["ContactName"];
            customer.City = (string)values["City"];
            customer.Bool = (bool?)values["Bool"];
 
            //queue entity for insertion in the database on next submit
            DataContext.Customers.InsertOnSubmit(customer);
 
            try
            {
                //submit above insert to database
                DataContext.SubmitChanges();
            }
            catch (Exception ex)
            {
                errorLiteral.Text = ex.ToString();
            }
        }
 
        protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e)
        {
            var customerID = (string)((GridDataItem)e.Item).GetDataKeyValue("CustomerID");
 
            //retrieve entity to be deleted from the database
            var customer = DataContext.Customers.Where(n => n.CustomerID == customerID).FirstOrDefault();
            if (customer != null)
            {
                //queue entity for deletion from the database on next submit
                DataContext.Customers.DeleteOnSubmit(customer);
                try
                {
                    //submit above delete to database
                    DataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    errorLiteral.Text = ex.ToString();
                }
            }
        }

As you can follow in each event's code and comments we essentially just have to manually take care of inserting, updating, and deleting the items in the Data Context that I have representing the database. We also have a few LINQ expressions (this is the main topic of this post after all) in these events as well, leveraging the power of LINQ for the CRUD operations.

Of course for all of these examples you can dive in a bit further, using more complicated LINQ expressions over multiple tables (I'm just using a single one in these samples), but this will give you a great running start when it comes to taking use of the power of LINQ with our RadGrid for ASP.NET Ajax. Since there were a few links mentioned above, and there are a few more out there, I'll list all of the links below:

Demos:

LinqToSql CRUD Operations

LinqToSql Manual CRUD Operations

300,000 rows with LINQ

Documentation:

LINQ to SQL - Automatic CRUD Operations

LINQ to SQL - Manual CRUD Operations

General Articles:

Using LINQ to SQL - Part 1

Using LINQ to SQL - Part 2

Using LINQ to SQL - Part 3

Using LINQ to SQL - Part 4

Using LINQ to SQL - Part 5

You can find the sample application with all of the source code attached - just reference your local copy of Telerik.Web.UI in the references folder and you'll be ready to go!

Source Code


About the Author

Iana Tsolova

is Product Manager at Telerik’s DevTools division. She joined the company back in the beginning of 2008 as a Support Officer and has since occupied various positions at Telerik, including Senior Support Officer, Team Lead at one of the ASP.NET AJAX teams and Technical Support Director. Iana’s main interests are web development, reading articles related to geography, wild nature and latest renewable energy technologies.

Related Posts

Comments