Read Build Your Own ASP.NET 3.5 Website Using C# & VB Online
Authors: Cristian Darie,Zak Ruvalcaba,Wyatt Barnett
Tags: #C♯ (Computer program language), #Active server pages, #Programming Languages, #C#, #Web Page Design, #Computers, #Web site development, #internet programming, #General, #C? (Computer program language), #Internet, #Visual BASIC, #Microsoft Visual BASIC, #Application Development, #Microsoft .NET Framework
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
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-
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]