This how-to tutorial describes displaying the data from a DataReader using the DataGrid Iterative data bound control. In earlier tutorials displaying data using the data bound RepeaterControl and a DataList control were described. This tutorial is self-contained like the others, however, the reader will find it helpful to review the earlier tutorials here and here, which were related to the data being retrieved using the DataReader.
The iterative RAD technique described in this tutorial offers flexible, free form formatting of displayed data. When the controls are bound to a data source, the three controls Repeater, DataList, and DataGrid loop through the data row by row, and apply a template (html) in rendering on a row by row basis. Each of the controls has its own way of formatting the data. In this tutorial the several ways that data can be formatted is described.
DataReader is unbeatable for fast retrieval of data from a database. However it is only suited for Read-only, Forward- only type of data retrieval. The iterative controls can fire specialized events. In this tutorial we are only looking at binding the data to the DataReader and configuring the control. Also, data retrieved using a DataReader cannot be sorted; more on this later.
Iterative Controls
The following picture shows the three controls in VS 2003 IDE's toolbox. The DataList and DataGrid icons show intuitively that they are related to data.
What makes the DataGrid such a flexible tool is the large number of properties and methods available for the designer as seen in the following class view for DataGrid.
The first step is to start a new web application and change the default file name of the web form to something meaningful such as DataGridExample.aspx. Following this you need to make connections to a database by inserting the control SQLConnection; then, add a command to retrieve data using the control SQLCommand. This is followed by writing appropriate code to retrieve data using the DataReader as a source of the data. To display data you add DataGrid and then call the methods of the DataGrid to port the data to the DataGrid by calling the DataBind() method after indicating that the DataGrid's DataSource is the DataReader.
Connecting to the SQL Server
VS 2003 IDE has a menu item, Server Explorer, that lists all the connections made to the various servers, including SQL 2000 Server, MSDE, MS Access, and so on. If the sources are not moved they can be refreshed by going to the connection, right clicking the node and choosing Refresh. For example, the next picture shows refreshing a connection to the MS Access Database file.
As to the SQL 2000 server, whether or not you have the correct version of the VS 2003 IDE, you will not be able to connect to a SQL Server. With a standard edition of VS 2003, you may connect only to MSDE and MS Access database servers.
There are two MSDE databases on this machine and the procedure for connecting to one of them will be described.
In the Sever Explorer window above, right click on the node Data Connections and choose Add Connection, which brings up the Data Link Properties page as shown below. You need to type in or choose from the drop down the name of the server; here the MSDE server XPHTEK\NetSDK has been chosen. Then you need to choose a method of authentication, which is in this case, the Windows Integrated method of authentication.
Finally you need to choose a database. If your earlier information is correct (server name and authentication method), you should be able to display all the databases from the drop down and display. You may choose any one of the databases to work with. On the other hand if any of the above information is incorrect you will not be able to connect to a database. You may also test the connection once all the information is given to this window as shown.
This is easily accomplished by dragging the SQLConnection control from the Data tab of the Toolbox onto the design pane as shown.
Now if you right click the SQLConnection that you added, you may examine its properties and also make entries to this connection object. Adding a SQLConnection object has not established a connection, as you need to make the SQLConnection by defining the Connection String. This is done easily by clicking in the empty area by the side of the ConnectionString item in this dialogue, and following the instruction, which consists of choosing an existing connection, namely SQLConenction1. This is already shown as being finished in the next picture.
If you examine the ConnectionString you will see all the items you chose in the Data Link Properties window previously as in:
This is also quite easy, and you can drag and drop the SQLCommand to the design area which adds a default command, SQLCommand1 (you may rename for convenience and readability). This needs to be configured, first by indicating that the SQLCommand works through the SQLConnection that is established. Second you need to tell what the command is that you want to give. This has been dealt with in the other tutorials, but it is sufficient to write a proper SQL Statement in the CommandText item of the SQL Command1's property window as shown below.
By right clicking an empty area by the side of the CommandText, you will be able to access the Query Builder. To keep this tutorial short, however, a SQL Statement was typed into this box. Specifically, the statement:
SELECT CompanyName, Address, City, ContactName, PostalCode, Country for Customers
Writing code for data retrieval using DataReader
The code is usually written in such a fashion that when the page is browsed, the data is retrieved. However, in this case, the code is written to the click event of a button as shown. The first thing to do is to open the connection. Then you need to instantiate a DataReader by declaring it [dr]. To get the data out you read it, and while reading data-in, you display it by writing appropriate code. You must close both the DataReader and the connection.
Private Sub Button1_Click(ByVal sender As System.Object,
_ & ByVal e As System.EventArgs) Handles Button1.Click
SqlConnection1.Open()
Dim dr As SqlClient.SqlDataReader
dr = SqlCommand1.ExecuteReader
While dr.Read
'Here display related code will be added
End While
dr.Close()
SqlConnection1.Close()
End Sub
Adding a DataGrid
This can also be accomplished by a drag and drop operation. You will find this control in the web forms tab of the Toolbox as seen in an earlier picture. When the control is added it shows a default grid in the design view with some three columns as shown.
Displaying data in the grid
The DataGrid (the default name is DataGrid1) has the property Source and a method called DataBind(). These are the only two properties needed for minimally displaying the data. DataGrid1 has many other properties we saw earlier in the classview. Adding this code, the click event now becomes:
Private Sub Button1_Click(ByVal sender As System.Object, _ &
ByVal e As System.EventArgs) Handles Button1.Click
SqlConnection1.Open()
Dim dr As SqlClient.SqlDataReader
dr = SqlCommand1.ExecuteReader
While dr.Read DataGrid1.DataSource = dr
DataGrid1.DataBind() End While
dr.Close()
SqlConnection1.Close()
End Sub
When this page is now browsed, we see the following in the web browser (only a part of this is shown).
If you click the link shown in the property window of the DataGrid, you open up the following window where you can choose pre-canned formatting of the DataGrid.
For the chosen scheme, Colorful3, the displayed data will be as shown:
Autoformatting makes changes to the following aspects of the DataGrid.
Appearance
Backcolor
Bordercolor
borderwidth
Layout
Cellpadding
cellspacing
Style
HeaderStyle
FooterStyle
AlternatignItemStyle
ItemStyle
Display formatted data by configuring the Properties window
Design time properties can be set so as to affect the following aspects of the DataGrid. You can make entries to the available design time properties such as:
Appearance
Behavior
Data
Layout
Miscellaneous
Paging
Style
You can also mix the methods. You can choose some from AutoFormat and later make some changes to the properties via the Property window. For example some HeaderStyle information is changed here as shown.
With these changes made to the autoformatted data, the browser display is now as shown here.
You may write the necessary code to format the data which becomes effective at run time. The run time formatting overrides whatever configured at design time. It is also easy to write the code for formatting the DataGrid, because you get visible cues as to the properties to be changed as shown here.
The following code added to the data retrieval shown earlier will render the DataGrid as shown in the next picture.
Private Sub Button1_Click(ByVal sender As System.Object, _ &
ByVal e As System.EventArgs) Handles Button1.Click
SqlConnection1.Open()
Dim dr As SqlClient.SqlDataReader
dr = SqlCommand1.ExecuteReader
While dr.Read
'adding formatting and style With DataGrid1
.BackColor = System.Drawing.Color.BlueViolet
.HeaderStyle.ForeColor =
System.Drawing.Color.Yellow
.CellPadding = 2
.CellSpacing = 3
.ItemStyle.ForeColor =
System.Drawing.Color.NavajoWhite
.HeaderStyle.Font.Bold = True
.PagerStyle.ForeColor = System.Drawing.Color.Red
.AlternatingItemStyle.BackColor =
System.Drawing.Color.Turquoise
.AlternatingItemStyle.ForeColor =
System.Drawing.Color.Tomato
.BorderStyle = BorderStyle.Groove
End With DataGrid1.DataSource = dr
DataGrid1.DataBind()
End While
dr.Close()
SqlConnection1.Close()
End Sub
Using the property Builder
You may also use the PropertyBuidler to build the properties of the DataGrid. In order to access the PropertyBuilder, click the link shown in the DataGrid's property window. This brings up the following window where you can configure the DataGrid's properties.
For demonstration purposes some property changes were made to the DataGrid's properties in the PropertyBuilder's window. The browser display is as shown below for these settings.
Summary
In the present tutorial the DataSource used was a DataReader which supports neither sorting nor paging. There are workarounds to add some additional code, or invoke the other interfaces that support paging, such as ArrayList, or DataTable. This will be considered in another tutorial. The DataGrid user interface is very rich and has a number of ways with which you can change the look and feel of the formatted data, all of which have been covered in the tutorial. As mentioned earlier you may mix the different methods to get the effect you want.
/ 79 
ASP.NET Export to Excel and Word using VB.NET and C#
