asp:Feature
LANGUAGES: ALL
ASP.NET VERSIONS: 2.0
Rapid Response
Search and Display Data Using the GridView Control with No Code!
By Ricardo Sanchez
One of the most common requests from users is to have a page where they can search for data stored in a database. Although there are many commercial tools that will let you do this, those tools are usually very expensive and not easy to implement. If you have the need to create a page for users to perform simple searches, you now can do so using the GridView control and a stored procedure. GridView is one of ASP.NET 2.0 s new controls, and is an updated version of the always useful DataGrid control. The GridView control offers the ability to connect to existing data sources, create new ones, and manipulate data in different ways.
For this example we are going to connect to the SQL Server 2005 Express AdventureWorks sample database and create a stored procedure within Visual Web Developer 2005 Express. If you don t have the AdventureWorks_Data.mdf file, it is available for download from Microsoft s Web site at http://www.microsoft.com/downloads/details.aspx?familyid=9697aaaa-ad4b-416e-87a4-a8b154f92787&displaylang=en. You can also use any of your databases for this exercise. One of the great features of Visual Web Developer 2005 Express is the database integration, which allows the developer to interact with databases, create stored procedures, etc., without the need to have the SQL Server 2000 or 2005 client tools installed.
Create a Stored Procedure in Visual Web Developer 2005 Express
Open Visual Web Developer Express and create a new Web site (ASP.NET Website). In the Database Explorer window, right-click on Data Connections and select Add Connection. If the Database Explorer window is not visible, click Database Explorer under the View menu or press Ctl+Alt+S.
In the Add Connection dialog box, if the Data source list does not display Microsoft SQL Server Database File, click Change, and in the Change Data Source dialog box, select Microsoft SQL Server Database File, which allows you to select a database file (mdf) from SQL Server 2005 Express. You can also use Microsoft SQL Server (SqlClient) if you want to use a database from SQL Server 2000 or 2005.
Click on Browse and navigate to the SQL Server 2005 Express data directory the default location of this directory is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and double-click on the AdventureWorks_Data.mdf file. Click Test connection, and when you are sure that it works, click OK. Your new connection has been created under Data Connections in the Database Explorer.
Under Data Connections in the Database Explorer, expand the data connection you just created so that you can see all the Tables, Views, and Stored Procedures. Right-click Stored Procedures, then click Add New Stored Procedure.
Add the following code in the new stored procedure, overwriting any existing code:
CREATE PROCEDURE dbo.getEmployee (
@EmployeeName nvarchar(50)) AS
SELECT * FROM HumanResources.vEmployee
WHERE FirstName LIKE '%' + @EmployeeName + '%'
RETURN
Close the window and click Yes to create the stored procedure. If you are using another database, be sure you make the appropriate changes to the stored procedure to query your database instead of the AdventureWorks sample database.
Create a Search Page
Open the Default.aspx page in design view and then drag and drop the following controls onto the page: a TextBox, Button, and GridView. Keep in mind that the GridView control is under the Data tab in the toolbox; the TextBox and the Button controls are under the Standard tab.
We need to configure the GridView control so it can display data from the database using the stored procedure we created earlier. Right-click on the GridView control and select Show Smart Tag; you should see something similar to Figure 1.
Figure 1: GridView s ShowSmartTag
menu.
The GridView control now provides the option to choose an existing data source or create a new one. Select New Data Source and the Data Source Configuration Wizard will be displayed. For this sample, select the Database option and leave the default id for the connection. In the following window, select from the dropdown the existing AdventureWorks connection created in the previous create stored procedure step and click Next.
The next window prompts you on how you want to retrieve data from your database; select Specify a custom SQL statement or stored procedure, as shown in Figure 2.
Figure 2: The new Configure Data
Source Wizard allows you to select either a stored procedure or a table/view to
retrieve data from your database.
Then click Next. Select the option Stored Procedure in the next window and then select getEmployee from the dropdown list; this is the stored procedure we created at the beginning of this article. Again, click the Next button. The following window allows you to choose the source of your parameter(s); there are different options, such as:
- None
- Cookie
- Control
- Form
- Profile
- QueryString
- Session
For this sample we are going to use the Control option, then under the controlID option select the TextBox1 (which is the TextBox control we added to the Default.aspx page); click the Next button. The next window allows you to test your query; click on Test Query and then type a letter in the Value field. For example, if type the letter F , the test query will return all the records from the getEmployee stored procedure where the letter F is included anywhere in the first name, as illustrated in Figure 3.
Figure 3: The getEmployee stored
procedure returns a data source to the Test Query window.
After you test the query, click the Finish button. That s it; you now have a page where users can do simple searches or lookups. Click Ctrl+F5 to launch your page in the browser and view the results.
Depending on how you set up the LIKE statement in your WHERE clause, you can modify the stored procedure to match an entire name, just the first letter, or whatever.
Enable Sorting and Paging
Sorting and paging are necessary when displaying multiple records to the user. Sorting allows the user to order the records in ascending or descending order, and paging simplifies the viewing of multiple records by splitting large amounts of records into smaller groups that are displayed in multiple pages. Sorting and Paging are options available on Visual Studio .NET 2003 s DataGrid control; however, the sorting is limited to sorting records only in ascending order unless you apply some custom code (you also have to write your own functions to make both the paging and sorting work).
Visual Web Developer 2005 Express new GridView control makes the sorting and paging functionality very simple to add, without writing any code. Seriously. In the design view of your aspx page, select the GridView control, then look at the Properties window on the right side of the screen. If you don t see the Properties window, right-click on the GridView control and select Properties. Follow these steps to configure and personalize the Paging and Sorting in your GridView control:
- In the Properties window, set to True the options AllowPaging and AllowSorting.
- In the same Properties window, find the option PagerSettings and expand it by clicking on the plus [+] sign. For this sample I ll set the Mode property to NextPreviousFirstLast, which will be displayed as << < > >> at the bottom of the GridView control; it basically allows the user to move to the first, previous, next, and last records (see Figure 4).
Figure 4: GridView results with
paging and sorting enabled.
There are several more options you can set in the PagerSettings option, such as using your own text or images to be displayed instead of the characters << < > >>, setting the position of the paging UI, setting the number of pages to be shown in the paging UI, and more. Two other options to customize the paging in your GridView control are the PagerStyle and PagerSize properties. PagerStyle allows the developer to add style to the paging UI; PagerSize is where you set the number of rows from the data source to be displayed per page.
Conclusion
The GridView control is without doubt a major upgrade from the DataGrid control because it decreases the time spent developing Web pages that are used to display, update, delete, insert, and search data in a database. You can even add search engine-style sorting and paging to your GridView control without writing a single line of code. You can also create a connection to your database or database file and create stored procedures from within Visual Web Developer 2005 Express, without having to have the SQL Server client tools installed.
Ricardo Sanchez is a Programmer Analyst with D. R. Horton in Austin, TX.
