ADO.NET 101: Data Rendering with a DataGrid Control - Adding a SQLCommand

(Page 5 of 7 )

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).

Next: Display formatted data using AutoFormat >>
 

More ASP Articles
More By Jayaram Krishnaswamy

Comments On: ADO.NET 101: Data Rendering with a DataGrid Control

· "When the controls are bound to a data source, the three controls Repeater,...    · I was away on a holiday where I did not have access to a computer(unbelievable, but...   >>> Post your comment now!