One of the first issues you encounter when working with a data bound control is the decision of how to provide data to the control. The WebDataGrid works with many data source options and this article will demonstrate how to use three of the most common data sources:
Regardless of which data source approach you select, begin by first adding a ScriptManager and a WebDataGrid to your WebForm. In the Visual Studio designer your page should look something like this:
Using a custom object collection to fill the grid is among the easiest approaches to bind grid to the page. Once you have a collection of objects you may simply set the collection equal to the DataSource property of the grid and call the DataBind method.
The above example uses nBuilder to quickly generate instances of the Person class for demonstration purposes. Data in your application will likely come from some sort of business logic layer, service or even perhaps directly from a data access layer.
When you run the page a grid will now appear on the page. The grid defaults to automatically generate columns, so you are not forced to define columns manually to make the grid useful.
The ObjectDataSource is a control that provides an interface to your business logic layer to perform operations against your objects to interact with a data bound control. The WebDataGrid uses the select, update, insert and delete commands in the ObjectDataSource to achieve interactivity for the control.
The first step is to point the data source control to the class that contains the methods used for selecting and manipulating data in your application. Once you add a ObjectDataSource to your page, the first step is to click on the smart tag and then click on the Configure Data Sources link.
Next, you must locate a class name that has the methods the data source control needs in the drop down list. Once you have selected the class, click Next >.
In the next steps of the wizard, the dialog windows ask you to point the control’s commands to different methods on an object which is responsible to selecting, updating, adding (inserting) or deleting data. Often the method names on the class will reflect the command its being associated to in the data source control. For instance the PersonRepsoitory class in the screenshot below contains a method named GetAll which obviously works well with the SELECT command.
In the same fashion the UPDATE command is mapped to an Update method:
...and the INSERT command to an Insert method:
...finally the DELETE command to a Delete method:
Once you complete mapping all the methods to the commands and click finish, the next step is to associate the data source control to the grid. This association is achieved by clicking on the smart tag and clicking on the Choose Data Source drop down in the smart tag. When you select the id for the ObjectDataSource a notification dialog will appear in Visual Studio.
This dialog is asking if you would like the designer to generate the column and key definitions automatically for grid for you. Unless you are working with an existing grid and do not want to loose any customizations made to the grid, you should click Yes and allow designer to create the boilerplate code necessary to configure the grid.
Once the column and key definitions are created, then the grid is ready to launch in the browser.
The SqlDataSource is a control that provides an interface to your data base. This control will store raw SQL statements or commands that map to stored procedures in the database. The first step to using the SqlDataSource control is to define to point to a database connection string. The sample application used to generate these screenshots already had a NorthwindConnectionString defined in the web.config, so this option automatically appears in the drop down list.
Note: Should you need to establish a new connection to the database from scratch just click on the New Connection... button and follow the steps in the wizard to generate the connection string.
The next step in the wizard gives you an opportunity to choose from what table, view or stored procedure the control should select the data. The example used in this article will use raw SQL commands for simplicity’s sake. The screenshot below shows you how to point the control to the Products table (as selected in the drop down list) and how to narrow the query down by only selecting the ProductID, ProductName and QuanityPerUnit from the table.
To generate the SQL required to handle the remaining INSERT, UPDATE and DELETE commands click on the Advanced... button where the following dialog appears.
Click the Generate INSERT, UPDATE and DELETE statements checkbox to allow the designer to generate these statements.
The next step in the wizard gives you an opportunity to test the SELECT command generated for the data source control. By clicking on the Test Query button you are presented with a preview table of the data returned from the database from your query. If the data looks like what you were expecting to get from the query, then clicking the Finish button will close the wizard.
Now that the data source control is configured you may point the WebDataGrid to the SqlDataSource. Return to the Visual Studio designer and click on the instance of the WebDataGrid on the page and then click on the arrow in the upper right hand corner of the control which will launch the smart tag. When the tag appears click on the drop down labeled Choose Data Source. From the drop down select the data source id you gave the data source when you initially placed it on the page.
When you pick a data source ID the following dialog appears in the designer:
Once you have configured the grid you may launch the page and have grid that looks much like the one pictured here:
While the WebDataGrid is engineered to work seamlessly with the SqlDataSource control, think through any decisions to make widespread use of the SqlDataSource control. Unfortunately, the SqlDataSource injects raw T-SQL code into your presentation layer which exposes your application possible problems in the future should the database schema change. Further, one of the holy grails of software development is the ’dont’t repeat yourself (DRY) principle. If you have two or more pages that must access a particular table, each page will have the SQL statements stored in each page. Adhering to DRY is impossible using the SqlDataSource. In the end you will know what is best for your application, but just make sure you appreciate the implications of using the SqlDataSource.