Blog Home  Home Feed your aggregator (RSS 2.0)  
Custom Paging for TableAdapters without using SPs - Manuel Abadia's ASP.NET stuff
 
# Thursday, May 4, 2006

TableAdapters are a quick way to create a DAL writing very little code or none at all. However, there isn’t an easy way to modify the generated code. You can extend the generated class adding methods but it would be cool if you could modify the generated methods, for example, how the insertions are performed. As this is not possible you have to live with the code that is generated. Anyway, for small projects with very little business logic and data intensive projects, TableAdapters are very helpful.

Even for a simple project, you need to avoid retrieving all table records in a query for performance reasons so you have to do custom paging. TableAdapters don’t have direct support for custom paging (sad but true) so you are on your own to implement it.

If you search for examples of custom paging using TableAdapters you’ll probably find out that custom paging is implemented using stored procedures. Why? Well, the query editor that comes with a TableAdapter has very limited SQL support so you can’t use "declare" or "set", you can’t create dynamic SQL, etc so the natural solution is to use stored procedures. There’s an open debate about using stored procedures or avoiding them as much as possible. IMHO, stored procedures should be avoided as much as possible during development (and probably in production too) as they kill productivity and complicate changes. If something goes slow because we’re not using a stored procedure we can always change that later but it’s better to avoid making your life more complex in advance.

So our goal is to have custom paging without using stored procedures but avoiding having to write code that opens a connection, creates and configures a command and fills the parameters. There are a lot of methods to do custom paging (http://www.codeproject.com/aspnet/PagingLarge.asp) but we’re going to use the subquery method as it is the only one we can make it work in the query editor using a trick (and if the query editor understands our query it will automatically add and configure all the parameters used by our query). This method is one of the slowest methods for paging so keep that in mind if you need to make optimizations to your code.

We’re going to add custom paging to a web form showing data of the Customers table. The columns of the table are:

id, name, address, email and phone.

So we have to add a new item to the project and choose “DataSet”. Then we have to complete the wizard to create a query to retrieve all the data from the customers table. To finish (for now) with the DataSet editor, we are going to add a new query to the TableAdapter that will return the number of customers in the table (needed for paging):

SELECT COUNT(*) FROM Customers

 

The subquery method for paging is like this:

SELECT Columns FROM Table WHERE PK IN

    (SELECT TOP @MaxRows PK FROM Table WHERE PK NOT IN

        (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)

    ORDER BY SortColumn)

ORDER BY SortColumn

To add custom paging to the Customers TableAdapter using the subquery method (using @MaxRows = 10 and @StartRow = 0) we have:

SELECT id, name, address, email, phone FROM Customers

WHERE (id IN (

 SELECT TOP 10  id FROM Customers

WHERE (id NOT IN

  (SELECT TOP 0 id FROM Customers ORDER BY id)

 ) ORDER BY id)

) ORDER BY id

 

To do custom paging we need to be able to change the MaxRows and StartRow dynamically. However if we write something like:

SELECT TOP @MaxRows FROM Customers WHERE

 

The query editor complains about incorrect syntax as it’s expecting a number after the TOP keyword. In order to overcome this limitation we need to understand a bit the code generated by the TableAdapter. To see the generated code you can use the class view and double click on the class (the name of the class defaults to <name given to the xsd file>TableAdapter inside the <name given to the xsd file>TableAdapters namespace).

[Update: I have found out that using SELECT TOP(@MaxRows) does work. I'll leave this post as the technique used here to change the query on the fly may be usefull to somebody.]

If you look at the generated code you can see something like this:

public partial class CustomersTableAdapter : System.ComponentModel.Component {

 

        private System.Data.SqlClient.SqlDataAdapter _adapter;

 

        private System.Data.SqlClient.SqlConnection _connection;

 

        private System.Data.SqlClient.SqlCommand[] _commandCollection;

 

        private bool _clearBeforeFill;

 

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]

        public CustomersTableAdapter() {

            this.ClearBeforeFill = true;

        }

 

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]

        private System.Data.SqlClient.SqlDataAdapter Adapter {

            get {

                if ((this._adapter == null)) {

                    this.InitAdapter();

                }

                return this._adapter;

            }

        }

 

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]

        internal System.Data.SqlClient.SqlConnection Connection {

            get {

                if ((this._connection == null)) {

                    this.InitConnection();

                }

                return this._connection;

            }

            set {

                this._connection = value;

                if ((this.Adapter.InsertCommand != null)) {

                    this.Adapter.InsertCommand.Connection = value;

                }

                if ((this.Adapter.DeleteCommand != null)) {

                    this.Adapter.DeleteCommand.Connection = value;

                }

                if ((this.Adapter.UpdateCommand != null)) {

                    this.Adapter.UpdateCommand.Connection = value;

                }

                for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1)) {

                    if ((this.CommandCollection[i] != null)) {

                        ((System.Data.SqlClient.SqlCommand)(this.CommandCollection[i])).Connection = value;

                    }

                }

            }

        }

 

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]

        protected System.Data.SqlClient.SqlCommand[] CommandCollection {

            get {

                if ((this._commandCollection == null)) {

                    this.InitCommandCollection();

                }

                return this._commandCollection;

            }

        }

 

        [System.Diagnostics.DebuggerNonUserCodeAttribute()]

        public bool ClearBeforeFill {

            get {

                return this._clearBeforeFill;

            }

            set {

                this._clearBeforeFill = value;

            }

        }

 

 

The generated class has 4 private fields. For each private field there is an associated property. The first 3 fields (_adapter, _connection and _commandCollection) are initialized using lazy initialization when using the getter of the associated property. The InitAdapter method creates the DataAdapter, sets the table mapping for the Customers table and configures the Insert, Update and Delete commands and parameters. The InitConnection method creates a connection and sets the connection string reading the associated connection from the web.config file. Finally the InitCommandCollection creates an array of commands (one command for each query in the TableAdapter) and set its connection and text properties.

The generated code for a query depends on what the query does but it always uses the CommandCollection property to get the associated command for the query. Remember that what we want is to make this query:

 

SELECT id, name, address, phone FROM Customers

WHERE (id IN (

 SELECT TOP @MaxRows  id FROM Customers

WHERE (id NOT IN

  (SELECT TOP @StartRow id FROM Customers ORDER BY id)

 ) ORDER BY id)

) ORDER BY id

 

But you have to specify a fixed @MaxRows and @StartRow value to make the query editor happy.

If we write our custom paging query this way (name the query “GetPagedDataHelper”), the query editor does accept it:

SELECT id, name, address, phone FROM Customers

WHERE (id IN (

 SELECT TOP /*<@maxRows>*/ 10 /*</@maxRows>*/ id FROM Customers

WHERE (id NOT IN

  (SELECT TOP /*<@startRow>*/ 0 /*</@startRow>*/ id FROM Customers ORDER BY id)

 ) ORDER BY id)

) ORDER BY id

 

As you can see we’re specifying a fixed value for the SELECT TOP statements but we have added some comments before and after the value. This comments are specially formatted comments that specify a placeholder for a dynamically changed value. As the command text property is set in the InitCommandCollection method, if we are able to replace the fixed value between the comments with the value we really want after the command collection has been initialized, we have solved the problem!

To replace the value we can use the power of regular expressions. For example, this “simple” regular expression:

(/\*<)(?<var>myvar)(>\*/)(?<value>.+)(/\*</\k<var>>\*/)

can be used to find patterns like this:

/*<myvar>*/ 10 /*</myvar>*/

And replace the value inside the specially formatted comments for another value.

The following class does this for any value inside specially formatted comments and also has a helper method to replace the paging commands if they’re named @maxRows and @startRow:

// TableAdapterPagingHelper (c) 2006 Manuel Abadia (http://www.manuelabadia.com)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Text.RegularExpressions;

 

namespace Manu.Utils

{

    /// <summary>Helper class for adding paging support to a TableAdapter without using SPs</summary>

    public static class TableAdapterPagingHelper

    {

        /// <summary>Generates a pattern to use to locate variable names in the SQL statements</summary>

        /// <param name="varName">variable name to search</param>

        /// <returns>a string with the pattern</returns>

        private static string GeneratePattern(string varName)

        {

            return @"(/\*<)(?<var>" + varName + @")(>\*/)(?<value>.+)(/\*</\k<var>>\*/)";

        }

 

        public static void ChangePagingCommands(SqlCommand[] commands, int startRowValue, int maxRowsValue)

        {

            ChangeVar(commands, "@startRow", startRowValue.ToString());

            ChangeVar(commands, "@maxRows", maxRowsValue.ToString());

        }

 

        public static void ChangeVar(SqlCommand[] commands, string varName, string varValue)

        {

            // generates the pattern for the parameter

            string varPattern = GeneratePattern(varName);

 

            // iterates through the commands replacing the parameter

            foreach (SqlCommand cmd in commands) {

                cmd.CommandText = Regex.Replace(cmd.CommandText, varPattern, varValue);

            }

        }

    }

}


To be able to do custom paging using the above helper class, we have to extend the TableAdapter generated class. As the generated class is a partial class we can add a class to the project and call it CustomerTableAdapter. We’ll add a method called GetPagedData with two parameters startRow and maxRows to this file:

using System;

using System.ComponentModel;

using System.Data;

using Manu.Utils;

 

namespace CustomersTableAdapters

{

    public partial class CustomersTableAdapter

    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]

        public Customers.CustomersDataTable GetPagedData(int startRow, int maxRows)

        {

            // modify the fake parameters with the actual data

            TableAdapterPagingHelper.ChangePagingCommands(CommandCollection, startRow, maxRows);

 

            // execute the query and return the data

            return GetPagedDataHelper();

        }

 

        public int GetTotalCount(int startRow, int maxRows)

        {

            // call to the real method

            return (int)CountCustomers();

        }

   }

}

As the ObjectDataSource expects the SelectCountMethod to have the same parameters as the SelectMethod we also need to add the GetTotalCount method to call the query that returns the number of customers for paging to work properly.

Another example to get the customers with a specific name:

SELECT address, email, id, name, phone FROM Customers

WHERE (id IN (

 SELECT TOP /*<@maxRows>*/10 /*</@maxRows>*/ id FROM Customers

 WHERE (name LIKE @name) AND (id NOT IN (

  SELECT TOP /*<@startRow>*/0/*</@startRow>*/ id FROM Customers

  WHERE (name LIKE @name) ORDER BY id)

 ) ORDER BY id)

) ORDER BY id

 

 

SELECT COUNT(*) FROM Customers WHERE (name LIKE @name)

And the methods to add are:

[DataObjectMethodAttribute(DataObjectMethodType.Select, false)]

public Customers.CustomersDataTable GetPagedDataByName(string name, int startRow, int maxRows)

{

   // modify the fake parameters with the actual data

   TableAdapterPagingHelper.ChangePagingCommands(CommandCollection, startRow, maxRows);

 

   // execute the query and return the data

   return GetPagedDataByNameHelper("%" + name + "%");

}

 

public int GetTotalCountByName(string name, int startRow, int maxRows)

{

   // call to the real method

   return (int)CountCustomersByName("%" + name + "%");

}

 

To sum up, using the TableAdapterPagingHelper class you can add custom paging to your TableAdapters without using SPs, adding two methods and only 3 lines of code. With the trick used to dynamically change the SQL command to execute you can add more degrees of freedom to the SQL that can handle your custom TableAdapter.

I hope you find this useful!

Source code: CustomPaging.zip (205.86 KB)
Thursday, May 4, 2006 6:19:26 PM (Romance Daylight Time, UTC+02:00)  #    Comments [1]   ASP.NET  |  Tracked by:
"Custom Paging for TabletAdapters without SP" (blog.BonGeek.com) [Trackback]
http://blog.bongeek.com/PermaLink,guid,ca64bc64-0d55-48be-a086-eccfa392553f.aspx [Pingback]
Thursday, May 18, 2006 10:07:06 AM (Romance Daylight Time, UTC+02:00)
A very useful tip. Thank you very much!
Venice
All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Copyright © 2020 Manuel Abadia. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.