In the coming examples, you’ll create a more complex display with a DataGrid, and you’ll display data from multiple tables, but to get started, you’ll keep it as simple as possible. In this first example, you’ll create a simple Windows Form with a single list box called lbBugs. You’ll populate this list box with bits of information from the Bugs table in the ProgASPDotNetBugs database.
Create a new C# ASP.NET web application project named
SimpleBugListBox
. Drag a list box onto the form
and name it lbBugs (that is, change the value of its ID property to
lbBugs). The list box will size itself to fit the data, so you can
leave its default size for now.
Example 11-2 is the complete source for the code-behind page; code lines that are not generated by Visual Studio automatically are shown in boldface. Analysis follows the listing.
Example 11-2. A simple ADO.NET example
using System; using System.Collections; using System.ComponentModel;using System.Data;
using System.Data.SqlClient;
using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace SimpleBugsListBox { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.ListBox lbBugs; public WebForm1( ) { Page.Init += new System.EventHandler(Page_Init); } private void Page_Load(object sender, System.EventArgs e) {// connect to the Bugs database
string connectionString =
"server=YourServer; uid=sa;
pwd=YourPassword; database=ProgASPDotNetBugs";
// get records from the Bugs table
string commandString =
"Select BugID, Description from Bugs";
// create the dataset command object
// and the DataSet
SqlDataAdapter dataAdapter =
new SqlDataAdapter(
commandString, connectionString);
DataSet dataSet = new DataSet( );
// fill the dataset object
dataAdapter.Fill(dataSet,"Bugs");
// Get the one table from the DataSet
DataTable dataTable = dataSet.Tables[0];
// for each row in the table, display the info
foreach (DataRow dataRow in dataTable.Rows)
{
lbBugs.Items.Add(
dataRow["BugID"] +
": " + dataRow["Description"] );
}
}
private void Page_Init(object sender, EventArgs e) { InitializeComponent( ); } #region Web Form Designer generated code private void InitializeComponent( ) { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }
With just about eight lines of code in the Page.Load event handler, you have extracted a set of data from the database and displayed it in the list box, as shown in Figure 11-6.
The eight lines accomplished the following tasks:
Created the string for the connection. The connection string is whatever string is needed to connect to the database, in the case of our example:
string connectionString = "server=YourServer; uid=sa; pwd=YourPassword; database=ProgASPDotNetBugs";
Created the string for the select
statement, which
generates a table containing bug IDs and their descriptions:
string commandString = "Select BugID, Description from Bugs";
Created the DataAdapter to extract the data from the SQL Server database and pass in the selection and connection strings:
SqlDataAdapter dataAdapter = new SqlDataAdapter( commandString, connectionString);
DataSet dataSet = new DataSet( );
Filled the dataset with the data obtained from the SQL
select
statement using the DataAdapter:
dataAdapter.Fill(dataSet,"Bugs");
Extracted the data table from the DataTableCollection object:
DataTable dataTable = dataSet.Tables[0];
Iterated the rows in the data table to fill the list box:
foreach (DataRow dataRow in dataTable.Rows) { lbBugs.Items.Add( dataRow["BugID"] + ": " + dataRow["Description"] ); }
The Visual Basic .NET Page_Load equivalent is shown in Example 11-3.
Example 11-3. Page_Load in VB.NET
Private Sub Page_Load(ByVal sender As System.Object, _ Dim connectionString As String connectionString = _ "Server=Neptune; uid=sa; pwd=oWenmEany; database=ProgASPDotNetBugs" Dim commandString As String commandString = "Select BugID, Description from Bugs" Dim myDataAdapter As New System.Data.SqlClient.SqlDataAdapter(commandString, _ Dim myDataSet As New DataSet( ) myDataAdapter.Fill(myDataSet, "Bugs") Dim myDataTable As DataTable myDataTable = myDataSet.Tables(0) Dim theRow As DataRow For Each theRow In myDataTable.Rows lbBugs.Items.Add(theRow("BugID") & ": " & theRow("Description")) Next End Sub
13.58.50.156