Build Your Own ASP.NET 3.5 Website Using C# & VB (68 page)

ConfigurationManager.ConnectionStrings( _

Licensed to [email protected]

ADO.NET

379

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

comm = New SqlCommand( _

"SELECT EmployeeID, Name, Username FROM Employees", _

conn)

Try

conn.Open()

reader = comm.ExecuteReader()

employeesRepeater.DataSource = reader

employeesRepeater.DataBind()

reader.Close()

Finally

conn.Close()

End Try

End Sub

End Class

C#

Dorknozzle\CS\02_EmployeeDirectory.aspx.cs
(excerpt)

using System;

⋮

using System.Data.SqlClient;

public partial class EmployeeDirectory : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

SqlConnection conn;

SqlCommand comm;

SqlDataReader reader;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand(

"SELECT EmployeeID, Name, Username FROM Employees",

conn);

try

{

conn.Open();

reader = comm.ExecuteReader();

employeesRepeater.DataSource = reader;

employeesRepeater.DataBind();

reader.Close();

Licensed to [email protected]

380

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

}

finally

{

conn.Close();

}

}

}

Most of the code should look familiar, except for the following part, which reads

the connection string:

Visual Basic

Dorknozzle\VB\02_EmployeeDirectory.aspx.vb
(excerpt)

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

C#

Dorknozzle\CS\02_EmployeeDirectory.aspx.cs
(excerpt)

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

Back in
Chapter 5
, you learned that you can store various configuration options in
Web.config
. Anticipating that many applications will use
Web.config
to store their connection strings, the designers of .NET reserved a special place in
Web.config
for

database connection strings. If you open
Web.config
now, you’ll see an empty

connectionStrings element located inside the configuration element. Modify

Web.config
like this:

Dorknozzle\VB\03_web.config
(excerpt)

⋮

connectionString="Server=localhost\SqlExpress;

➥
Database=Dorknozzle;Integrated Security=True"

providerName="System.Data.SqlClient"/>

Licensed to [email protected]

ADO.NET

381

⋮

You can add more connection strings under the connectionStrings element by

inserting add elements with three attributes: connectionString contains the actual

connection string, name gives the connection string an identifier that we can reference

within our code, and providerName indicates the type of data provider we want to

use for the connection. In our case, providerName="System.Data.SqlClient"

specifies that we’re connecting to an SQL Server database.

To retrieve configuration data from
Web.config
, we use the ConfigurationManager

class, which is located in the System.Configuration namespace.

Also, you may have noticed that we don’t have a Catch block in our database

handling code. When a Catch block is not present, any exceptions that are raised

are not caught, although the code in the Finally block is still executed. In other

words, we’re choosing not to handle potential errors in
EmployeeDirectory.aspx
, but

we still want to ensure that the database connection is properly closed if an error

arises.

The rest of the code comprises the typical data access routine, involving a

SqlConnection object, a SqlCommand object, and a SqlDataReader object. Once the

reader has been filled with the database data, it is bound to the Repeater control’s

DataSource property, and from this point, the repeater takes control and reads all

the data from the data source. If you save and run this page, it should appear as

shown in Figure 9.9.

More Data Binding

The term
data binding
describes the act of associating a
data source
with a
data
consumer
. In our previous examples, the data source was an SqlDataReader object,

and the consumer was a Repeater control that read and displayed the data. Data

binding typically involves setting the DataSource property of the consumer object

to the data source object, and calling the DataBind method to apply the binding:

Licensed to [email protected]

382

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Visual Basic

employeesRepeater.DataSource = reader

employeesRepeater.DataBind()

C#

employeesRepeater.DataSource = reader;

employeesRepeater.DataBind();

As we discussed earlier, ASP.NET includes a few controls that specialize in displaying data that comes from data sources, but you can also bind data to numerous other controls, including lists, menus, text boxes, and so on. To explore the process

of control binding further, let’s open the Help Desk page again. If you remember,

we left the
Category
and
Subject
drop-down lists empty back in
Chapter 5. W
e did so because we knew that, eventually, those items would have to be populated dynamically through code. Sure, we could have hard-coded the values ourselves, but imagine what would happen if additions or deletions needed to be made to that

list. In order to make the necessary changes to the controls, we would have to open

every page that contained lists of categories and subjects.

It’s preferable to store the lists of categories and subjects in database tables, and to

bind this data to the drop-down lists in the Help Desk page. Whenever a change

needs to be made, we can make it once within the database; all the controls that are

bound to that database table will change automatically.

Let’s go ahead and add the necessary code to Page_Load in
HelpDesk.aspx
to populate

the DropDownList controls from the database. After the changes are made, the lists

will be populated with the data you added to your database in Chapter 7, as illus-

trated in Figure 9.10
.

Licensed to [email protected]

ADO.NET

383

Figure 9.10. A drop-down list created with data binding

Open
HelpDesk.aspx
in Design view and double-click an empty space on the form

to have the signature of the Page_Load method generated for you. First we’ll need

to import some namespaces. You’ll need two if you’re using VB, but only one if

you’re using C#. Add the following to the top section of the file:

Visual Basic

Dorknozzle\VB\04_HelpDesk.aspx.vb
(excerpt)

Imports System.Data.SqlClient

Imports System.Configuration

Partial Class HelpDesk

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

⋮

End Sub

End Class

Licensed to [email protected]

384

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

C#

Dorknozzle\CS\04_HelpDesk.aspx.cs
(excerpt)

using System;

⋮

using System.Data.SqlClient;

public partial class HelpDesk : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

⋮

}

}

Then, add the following code to the Page_Load method:

Visual Basic

Dorknozzle\VB\04_HelpDesk.aspx.vb
(excerpt)

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim conn As SqlConnection

Dim categoryComm As SqlCommand

Dim subjectComm As SqlCommand

Dim reader As SqlDataReader

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

categoryComm = New SqlCommand( _

"SELECT CategoryID, Category FROM HelpDeskCategories", _

conn)

subjectComm = New SqlCommand( _

"SELECT SubjectID, Subject FROM HelpDeskSubjects", conn)

Try

conn.Open()

reader = categoryComm.ExecuteReader()

categoryList.DataSource = reader

categoryList.DataValueField = "CategoryID"

categoryList.DataTextField = "Category"

categoryList.DataBind()

reader.Close()

reader = subjectComm.ExecuteReader()

subjectList.DataSource = reader

Licensed to [email protected]

ADO.NET

385

subjectList.DataValueField = "SubjectID"

subjectList.DataTextField = "Subject"

subjectList.DataBind()

reader.Close()

Finally

conn.Close()

End Try

End If

End Sub

C#

Dorknozzle\CS\04_HelpDesk.aspx.cs
(excerpt)

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

SqlConnection conn;

SqlCommand categoryComm;

SqlCommand subjectComm;

SqlDataReader reader;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

categoryComm = new SqlCommand(

"SELECT CategoryID, Category FROM HelpDeskCategories",

conn);

subjectComm = new SqlCommand(

"SELECT SubjectID, Subject FROM HelpDeskSubjects", conn);

try

{

conn.Open();

reader = categoryComm.ExecuteReader();

categoryList.DataSource = reader;

categoryList.DataValueField = "CategoryID";

categoryList.DataTextField = "Category";

categoryList.DataBind();

reader.Close();

reader = subjectComm.ExecuteReader();

subjectList.DataSource = reader;

subjectList.DataValueField = "SubjectID";

subjectList.DataTextField = "Subject";

subjectList.DataBind();

reader.Close();

Licensed to [email protected]