Generic grid search and data paging

• Download source code - 66.5 KB

Introduction

This article describes the DataFinder (user control) which proved to be very useful to me.

Background

• Problem, usually presents the following requirements:
• Create a page for displaying a list of records from a SQL query.
• Enable paging of the grid, where if you get 1000000 records, and all this information through the network (in case the database is not on the same server as the applications), then this information is loaded on the memory of the server application where it is filtered for the records that we really want to see (the current page). All this causes bad performance, but surely, many of us already have applications running this way.
• Finally, or as they say "Adding the cherry on the cake", add a generic search feature to this grid.
• Analysis. For the solution, we have to:
• Create our SQL query in a Stored Procedure.
• Add code to get data from the query.
• Create a GridView control and customize the columns to receive the data.
• Improve our Stored Procedure to add parameters using which we send page info for the data in the database.
• Finally, we need to add more functionality to our Stored Procedure to receive parameters to send our search criteria.
• Solution. Everything listed in the two previous points can be done, but would take considerable time and will become a repetitive task evolving according to the system. This is where the user control DataFinder proves helpful. This control simplifies all our work, and further enhances our performance (performes paging in the database).

Using the Code

You need to download the solution accompanying this article, which has the search code and an example of how to use it.

• Open the solution, and the search engine is in the Controles folder. Images and CSS used by this control are in the CSS/Buscador folder (try to keep the structure of files as found in the example, so you won't have to change the style sheet and paths of the images for the User Control).
• In the web.config, we have the CNN link chain that connects the database "AdventureWorks". You can download this database from: AdventureWorks. It is important to mention here that I connect to a SQL Server 2008 database.
• Create your SQL query and store it in the folder "Consultas" in the same folder that has the "Ejemplo.sql" file (the file does not necessarily have the .sql extension) with the suggested format. The identifier "- $TART -" is the delimiter to indicate where the query begins, and what you have before you is a comment.
SQL

--------------------------------------------
-- Name: Kevin Guisbert Segales
-- Company: KDev
-- Date: 13/01/2010
-- Copyright 2010
-- La Paz - Bolivia
--------------------------------------------
-- $TART --
SELECT  e.EmployeeID,
        e.NationalIDNumber,
        e.Title,
        e.VacationHours
FROM    HumanResources.Employee e

• Create an ASPX page. In this example, we have the page "Default.aspx" where you have to add the following file header attribute:
SQL

EnableEventValidation="false"

• In the Head section, add the reference to the stylesheet of the page:
XML

<link href="CSS/Buscador/Buscador.css" rel="stylesheet" type="text/css" />

• Drag the user control "Buscador.ascx" to your page.
• In the code-behind, add the following properties:
VB

Protected Sub Page_Load(ByVal sender As Object, _
              ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        '- Inicializamos datos del buscador -
        Me.Buscador1.Consulta = "Ejemplo.sql"
        Me.Buscador1.Titulo = "Buscador Genérico"
        Me.Buscador1.OrderBy = "Title"
        Me.Buscador1.DataBind()
    End If
End Sub

Where:

• Consulta is the name of the file containing the query and is placed in "Consultas" folder (required).
• Titulo is the title to show in the control (optional).
• OrderBy is the name of the field by which to order the query (required).
• DataBind() is the method to proceed with the binding (required).
• If you have followed the steps above, you can proceed to run your application and enjoy the control.

I hope this control will be useful. I will soon be enhancing this control.

Points of Interest

This application was tested using:

• Windows XP SP3
• Internet Explorer 6
• Visual Studio 2008
• SQL Server 2008