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

Licensed to [email protected]

Managing Content Using Grid View and Details View

483

conn.Open()

comm.ExecuteNonQuery()

Finally

conn.Close()

End Try

employeeDetails.ChangeMode(DetailsViewMode.ReadOnly)

BindGrid()

BindDetails()

End Sub

C#

Dorknozzle\CS\19_AddressBook.aspx.cs
(excerpt)

protected void employeeDetails_ItemUpdating(object sender,

DetailsViewUpdateEventArgs e)

{

int employeeId = (int)employeeDetails.DataKey.Value;

TextBox newAddressTextBox =

(TextBox)employeeDetails.FindControl("editAddressTextBox");

TextBox newCityTextBox =

(TextBox)employeeDetails.FindControl("editCityTextBox");

string newAddress = newAddressTextBox.Text;

string newCity = newCityTextBox.Text;

SqlConnection conn;

SqlCommand comm;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

comm = new SqlCommand("UpdateEmployeeDetails", conn);

comm.CommandType = CommandType.StoredProcedure;

comm.Parameters.Add("EmployeeID", SqlDbType.Int);

comm.Parameters["EmployeeID"].Value = employeeId;

comm.Parameters.Add("NewAddress", SqlDbType.NVarChar, 50);

comm.Parameters["NewAddress"].Value = newAddress;

comm.Parameters.Add("NewCity", SqlDbType.NVarChar, 50);

comm.Parameters["NewCity"].Value = newCity;

try

{

conn.Open();

comm.ExecuteNonQuery();

}

finally

{

conn.Close();

Licensed to [email protected]

484

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

}

employeeDetails.ChangeMode(DetailsViewMode.ReadOnly);

BindGrid();

BindDetails();

}

This code is fairly straightforward. It starts by reading the value of the DataKey of

the DetailsView object. As we saw earlier, the DetailsView, like the GridView, is

able to store the ID of the record (or records) it’s displaying. You’ll remember that

we made the DetailsView object aware of the EmployeeID data key when we bound

the DetailsView to its data source in the BindDetails method. We read this information in the ItemUpdating event handler, like so:
Visual Basic

Dorknozzle\VB\19_AddressBook.aspx.vb
(excerpt)

Dim employeeId As Integer = employeeDetails.DataKey.Value

C#

Dorknozzle\CS\19_AddressBook.aspx.cs
(excerpt)

int employeeId = (int) employeeDetails.DataKey.Value;

The next step is to find the TextBox objects that contain the updated data. We do

this using the FindControl method, as we’ve seen previously. After we obtain the

control references, we obtain the string values that we’re interested in simply by

reading their Text properties, as you can see in the following code snippets:

Visual Basic

Dorknozzle\VB\19_AddressBook.aspx.vb
(excerpt)

Dim newAddressTextBox As TextBox = _

employeeDetails.FindControl("editAddressTextBox")

Dim newCityTextBox As TextBox = _

employeeDetails.FindControl("editCityTextBox")

Dim newAddress As String = newAddressTextBox.Text

Dim newCity As String = newCityTextBox.Text

Licensed to [email protected]

Managing Content Using Grid View and Details View

485

C#

Dorknozzle\CS\19_AddressBook.aspx.cs
(excerpt)

TextBox newAddressTextBox =

(TextBox)employeeDetails.FindControl("editAddressTextBox");

TextBox newCityTextBox =

(TextBox)employeeDetails.FindControl("editCityTextBox");

string newAddress = newAddressTextBox.Text;

string newCity = newCityTextBox.Text;

After we obtain the data we wish to insert into the database, we call the UpdateEmployeeDetails stored procedure we created earlier. The database connection and command code is identical to the code we’ve used previously, so we don’t need to

examine it again.

In the last part of employeeDetails_ItemUpdating, we rebind both the GridView

and the DetailsView with the updated information, and switch the DetailsView

back to its read-only state:

Visual Basic

Dorknozzle\VB\19_AddressBook.aspx.vb
(excerpt)

employeeDetails.ChangeMode(DetailsViewMode.ReadOnly)

BindGrid()

BindDetails()

C#

Dorknozzle\CS\19_AddressBook.aspx.cs
(excerpt)

employeeDetails.ChangeMode(DetailsViewMode.ReadOnly);

BindGrid();

BindDetails();

The code is ready to be executed! Try updating the addresses of your employees to

ensure the feature works correctly—
it should display as shown in Figure 11.18.

Summary

As we’ve seen throughout this chapter, GridViews and DetailsViews provide

enormous flexibility and power in terms of presenting database data dynamically

within the browser. In these pages, we learned how to create both of these data

controls and bind data to them. We also learned how to customize the appearance

Licensed to [email protected]

486

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

Figure 11.18. Updating an employee’s address and city

of elements using templates and styles, and saw how various commands allow us

to select and update items. You can use these techniques to add new records, and

update existing ones.

The next chapter will begin to introduce you to advanced programming topics, as

we investigate even more possibilities for data binding the DetailsView control to

allow for the editing, updating, and insertion of new records with minimal coding

effort. We’ll also have a chance to explore the topic of code-free data binding in that

chapter.

Licensed to [email protected]

Advanced Data Access

In the last three chapters, you learned some of the important concepts of data access

and presentation. You learned how to use the SqlConnection class to establish a

connection to the database, you learned how to use the SqlCommand class to execute

a query on a database table, and you learned how to return the results of the command into an SqlDataReader for use within the application. In this chapter, we’ll discuss the alternatives to using the SqlDataReader object for

retrieving data from your database. For starters, it’s important to understand that

SqlDataReader has both advantages and disadvantages. The two main points you

need to keep in mind about SqlDataReader are:

■ It represents the fastest method available to read data from the data source.

■ It allows read-only, forward-only access to data.

In other words, the SqlDataReader achieves very high performance at the cost of

providing a direct connection to the data source. It doesn’t store the data locally,

so after it reads one record and moves to the next, there’s no way to go back. The

SqlDataReader basically offers an input data stream that you can read in the order

in which it was sent, but which you are unable to modify.

Licensed to [email protected]

488

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

However, SqlDataReader isn’t the only way to access your data, and in many

scenarios, it makes sense to use one of the two popular alternatives:

1. The first option involves using the new ADO.NET data source controls, which

are tightly integrated with the GridView and DetailsView controls. They allow

you to implement reading, updating, deleting, inserting, paging, and sorting features very easily—for the most part, you don’t even need to write any code!

2. The second approach involves using the SqlDataAdapter class in conjunction

with the DataTable, DataView, and DataSet classes, which are able to read data

from the database and store it locally, allowing you to browse, filter, and sort

data in your code without leaving a connection to the database open. This

method occupies more memory on the server that runs your application, and

means that fewer of Visual Web Developer’s automated features are available to

you (you have to write more code!), but it does give you more flexibility in terms

of what you can do with your data.

In this chapter, you’ll learn how to use both of these data access methods.

Using Data Source Controls

The .NET framework offers six
data source controls
: SqlDataSource,

AccessDataSource, LinqDataSource, ObjectDataSource, XmlDataSource, and

SiteMapDataSource. These objects enable automatic connection to various data

sources and provide easy ways to read or modify your database using data-bound

controls:

■ SqlDataSource allows you to connect to any data source that has an ADO.NET

data provider. The default providers are SqlClient, OracleClient, OleDb, and

Odbc. Even though the name of the class is SqlDataSource, the fact that its name

begins with
Sql
doesn’t mean it works only with SQL Server—it’s really very

flexible. This is the data source we’ll work with in this chapter.

■ AccessDataSource is the data source object we use to connect to Access databases.

■ LinqDataSource is the data source object you can use to connect to LanguageIntegrated Query (LINQ) data sources. This is an advanced ADO.NET feature that we won’t cover in this book.

Licensed to [email protected]

Advanced Data Access

489

■ ObjectDataSource allows us to connect to custom data access classes, and is

appropriate when we’re creating complex application architectures.

■ XmlDataSource knows how to connect to XML files.

■ SiteMapDataSource knows how to connect to a site map data source, and can

be used to generate site maps. We worked a little with this data source control

in Chapter 4.

You can find these controls in the
Data
tab of Visual Web Developer’s Toolbox, as

Figure 12.1 shows.

Figure 12.1. The Data Source Controls in the Toolbox

In Chapter 11, we implemented the functionality required to show employee details

in the Dorknozzle Address Book page (
AddressBook.aspx
). We used the SqlDataReader

class for the task, which means we’ve achieved the best possible performance.

However, we wrote quite a bit of code to implement the viewing and editing features

for that page, and we’d need to do even more hand coding to implement paging,

sorting, and inserting features.

This time, to make it easier on our fingers, we’ll use the SqlDataSource object instead. This object can automate many tasks for us, and while it may not always Licensed to [email protected]

490

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

provide the best performance or the greatest flexibility, it’s important that we know

how to use it, because it can come in very handy for quick programming tasks.

Binding the GridView to a SqlDataSource

We’ll start by binding the GridView control in
AddressBook.aspx
to a SqlDataSource;

we’ll deal with the DetailsView control later. Since the data sources work with

different SQL queries, we’ll need to create two data sources: one that reads all employees (to populate the GridView), and one that reads the details of one employee (to populate the DetailsView).