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

■ When the same column header is clicked multiple times, the grid should alternate

between sorting the data in that column in ascending and descending modes.

When a column heading is clicked, the grid’s Sorting event is fired. In our case,

the Sorting event handler (which we’ll look at in a moment) saves the details of

the sort column and direction in two properties:

■ gridSortExpression retains the name of the column on which we’re sorting the

data (such as Department).

■ gridSortDirection can be either SortDirection.Ascending or SortDirection.Descending.

We create a sorting expression using these properties in BindGrid:

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

Dim sortExpression As String

If gridSortDirection = SortDirection.Ascending Then

sortExpression = gridSortExpression & " ASC"

Else

sortExpression = gridSortExpression & " DESC"

End If

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

string sortExpression;

if(gridSortDirection == SortDirection.Ascending)

{

sortExpression = gridSortExpression + " ASC";

}

Licensed to [email protected]

Advanced Data Access

539

else

{

sortExpression = gridSortExpression + " DESC";

}

In order to implement the sorting functionality as explained above, we need to remember between client requests which column is being sorted, and whether it’s being sorted in ascending or descending order. That’s what the properties

gridSortExpression and gridSortDirection do:

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

Private Property gridSortDirection() As SortDirection

Get

If (ViewState("GridSortDirection") Is Nothing) Then

ViewState("GridSortDirection") = SortDirection.Ascending

End If

Return ViewState("GridSortDirection")

End Get

Set(ByVal value As SortDirection)

ViewState("GridSortDirection") = value

End Set

End Property

Private Property gridSortExpression() As String

Get

If (ViewState("GridSortExpression") Is Nothing) Then

ViewState("GridSortExpression") = "DepartmentID"

End If

Return ViewState("GridSortExpression")

End Get

Set(ByVal value As String)

ViewState("GridSortExpression") = value

End Set

End Property

Licensed to [email protected]

540

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

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

private SortDirection gridSortDirection

{

get

{

if (ViewState["GridSortDirection"] == null)

{

ViewState["GridSortDirection"] = SortDirection.Ascending;

}

return (SortDirection) ViewState["GridSortDirection"];

}

set

{

ViewState["GridSortDirection"] = value;

}

}

private string gridSortExpression

{

get

{

if (ViewState["GridSortExpression"] == null)

{

ViewState["GridSortExpression"] = "DepartmentID";

}

return (string) ViewState["GridSortExpression"];

}

set

{

ViewState["GridSortExpression"] = value;

}

}

Here, we use the ViewState collection to store information about which column is

being sorted, and the direction in which it’s being sorted.

When the Sorting event handler fires, we set the gridSortExpression and

gridSortDirection properties. The method starts by retrieving the name of the

clicked column:

Licensed to [email protected]

Advanced Data Access

541

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

Protected Sub departmentsGrid_Sorting(ByVal sender As Object,

➥ ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs)

➥ Handles departmentsGrid.Sorting

Dim sortExpression As String = e.SortExpression

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

protected void departmentsGrid_Sorting(object sender,

GridViewSortEventArgs e)

{

string sortExpression = e.SortExpression;

Next, we check whether the previously clicked column is the same as the newly

clicked column. If it is, we need to toggle the sorting direction. Otherwise, we set

the sort direction to ascending:

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

If (sortExpression = gridSortExpression) Then

If gridSortDirection = SortDirection.Ascending Then

gridSortDirection = SortDirection.Descending

Else

gridSortDirection = SortDirection.Ascending

End If

Else

gridSortDirection = WebControls.SortDirection.Ascending

End If

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

if (sortExpression == gridSortExpression)

{

if(gridSortDirection == SortDirection.Ascending)

{

gridSortDirection = SortDirection.Descending;

}

else

{

gridSortDirection = SortDirection.Ascending;

Licensed to [email protected]

542

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

}

}

else

{

gridSortDirection = SortDirection.Ascending;

}

Finally, we save the new sort expression to the gridSortExpression property,

whose value will be retained in case the user keeps working (and changing sort

modes) on the page:

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

gridSortExpression = sortExpression

BindGrid()

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

gridSortExpression = sortExpression;

BindGrid();

After we store the sort expression, we rebind the grid to its data source so that the

expression will reflect the changes we’ve made to the gridSortExpression and

gridSortDirection properties.

Filtering Data

Although we’re not using the DataView control in the Dorknozzle project, it’s interesting to note that this control can filter data. Normally you’d have to apply WHERE

clauses to filter the data before it reaches your application, but in certain cases you

may prefer to filter data on the client.

Imagine that you wanted to display employees or departments whose names started

with a certain letter. You could retrieve the complete list of employees or departments from the database using a single request, then let the user filter the list locally. The DataView class has a property named RowFilter that allows you to specify an

expression similar to that of an SQL statement’s WHERE clause. For instance, the

following filter selects all departments whose names start with “a”:

Licensed to [email protected]

Advanced Data Access

543

Visual Basic

dataTable.DefaultView.RowFilter = "Department LIKE 'a%'"

C#

dataTable.DefaultView.RowFilter = "Department LIKE 'a%'";

Updating a Database

from a Modified DataSet

So far, we’ve used the DataSet exclusively for retrieving and binding database data

to controls such as the GridView. The reverse operation—updating data within a

database from a DataSet—is also possible using the Update method of the

SqlDataAdapter.

The SqlDataAdapter has the following four properties, which represent the main

database commands:

■ SelectCommand

■ InsertCommand

■ UpdateCommand

■ DeleteCommand

The SelectCommand contains the command that’s executed when we call Fill. The

other properties are quite similar, except that, to execute them, you must call the

Update method instead.

If we want to insert, update, or remove records in a database, we simply make

modifications to the data in the DataSet or DataTable, then call the Update method

of the SqlDataAdapter. This will automatically execute the SQL queries specified

in the InsertCommand, UpdateCommand, and DeleteCommand properties as appropriate.

The excellent news is that ADO.NET also provides an object named

SqlCommandBuilder, which creates the UPDATE, DELETE, and INSERT code for us.

Basically, we just need to populate the DataSet or DataTable objects (usually by

Licensed to [email protected]

544

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

performing a SELECT query), then use SqlDataAdapter and SqlCommandBuilder to

do the rest of the work for us.

In the example below, we’ll see a modified version of BindGrid that adds a new

department, called New Department, to the database. The new lines are highlighted

(note that I’ve simplified BindGrid by removing the code that stores and retrieves

the DataSet from view state, as well as the code that sorts the results):

Visual Basic

Private Sub BindGrid()

Dim conn As SqlConnection

Dim dataSet As New DataSet

Dim adapter As SqlDataAdapter

Dim dataRow As DataRow

Dim commandBuilder As SqlCommandBuilder

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", _

conn)

adapter.Fill(dataSet, "Departments")

dataRow = dataSet.Tables("Departments").NewRow()

dataRow("Department") = "New Department"

dataSet.Tables("Departments").Rows.Add(dataRow)

commandBuilder = New SqlCommandBuilder(adapter)

adapter.Update(dataSet.Tables("Departments"))

departmentsGrid.DataSource = _

dataSet.Tables("Departments").DefaultView

departmentsGrid.DataBind()

End Sub

C#

private void BindGrid()

{

SqlConnection conn;

DataSet dataSet = new DataSet();

SqlDataAdapter adapter;

DataRow dataRow ;

SqlCommandBuilder commandBuilder;

Licensed to [email protected]

Advanced Data Access

545

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

adapter = new SqlDataAdapter(

"SELECT DepartmentID, Department FROM Departments",

conn);

adapter.Fill(dataSet, "Departments");

dataRow = dataSet.Tables["Departments"].NewRow();

dataRow["Department"] = "New Department";

dataSet.Tables["Departments"].Rows.Add(dataRow);

commandBuilder = new SqlCommandBuilder(adapter);

adapter.Update(dataSet.Tables["Departments"]);

departmentsGrid.DataSource =

dataSet.Tables["Departments"].DefaultView;

departmentsGrid.DataBind();

}

If you run this code a few times, lots of departments titled New Department will be

added to the database, as shown in
Figure 12.29.

As you can see, adding a new record is a trivial task. The work that’s required to

submit the changes to the database requires us to write just two rows of code. The

rest of the new code creates the new row that was inserted.

We create an SqlCommandBuilder object, passing in our SqlDataAdapter. The

SqlCommandBuilder class is responsible for detecting modifications to the DataSet

and deciding what needs to be inserted, updated, or deleted to apply those changes

to the database. Having done this, SqlCommandBuilder generates the necessary SQL

queries and stores them in the SqlDataAdapter for the Update method to use. It

should be no surprise, then, that our next action is to call the Update method of the

SqlDataAdapter object, passing in the DataTable that needs updating.

Deleting all of these new departments is also an easy task. The following code

browses the Departments DataTable and deletes all departments with the name

New Department:

Licensed to [email protected]

546

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

Figure 12.29. Adding many new departments