Gridview Examples For Asp_net C#

GridView Examples for ASP.NET 2.0: Editing the Underlying Data in a GridView In addition to deleting a GridView's underlying data, another common need is to allow end users to edit the data displayed in a GridView. With ASP.NET 1.x's DataGrid control, editing the data is certainly possible, but requires creating three event handlers and writing a dozen or so lines of code. With the GridView and ASP.NET 2.0, it is possible to create an editable GridView without writing a single line of code! All of the necessary functionality is encapsulated within the GridView. The GridView allows editing on a row-by-row basis. An editable GridView contains an additional

column with an Edit button in each row. When the end user clicks on an Edit button that row

becomes editable, causing the Edit button to change to Update and Cancel buttons and the other columns to become TextBoxes. The end user can then update one or more column values and click Update to save their changes.

In this section we'll examine four examples. Our first demo will look at how to create an editable GridView whose data comes from a SqlDataSource. Next, we'll see how to create an editable

GridView from an ObjectDataSource. As you may have guessed, when creating an editable GridView whose data comes from an ObjectDataSource, the underlying data access layer class must provide an

appropriate Update method. We'll look at the required signature for the Update method and see how to configure the ObjectDataSource to use this method. In the last two demos we'll see how to

customize the editing interface for a GridView column. By default, the TextBoxes have no validation

controls associated with them, but your business rules might require certain validation. A particular field

may be required or might need to be entered in a specific format. Additionally, the standard TextBox as an editing interface might not be acceptable; we'll also look at an example that replaces the TextBox with a databound DropDownList.

Editing

GridView

SqlDataSource

Data

That

Comes

From

a

Creating an editable GridView whose data comes from a SqlDataSource is amazingly easy. To start, the GridView's SqlDataSource must contain an UpdateCommand with appropriate

parameters. As we saw in the Deleting a GridView's Underlying Data section, creating such a

SqlDataSource is as simple as checking the Generate Insert, Update, and Delete Statements

from the dialog box shown back in Figure 35. (Recall that this dialog box is accessed through the second step of the SqlDataSource wizard, by clicking the Advanced button.)

As with the delete example, checking the Generate Insert, Update, and Delete Statements will create not only an UPDATE statement, but INSERT and DELETE statements as well. These can be safely removed if you are creating a GridView that's limited to just editing the existing data. Once

you have configured a SqlDataSource with an UPDATE statement, creating an editable GridView

1

is a breeze. Just add a new GridView to the page and, from its Smart Tag, click the Enable

Editing checkbox (see Figure 39). This will add a CommandField to the GridView with an Edit button.

[

http://msdn.microsoft.com/en-us/library/ms972948.gridview_fig39l(en-us,MSDN.10).gif ] Figure 39 (Click on the graphic for a larger image) That's all there is to it! Figures 40 and 41 show screenshots of the editable GridView in action.

Figure 40 shows the GridView in its pre-editable state. Figure 41 shows the screen after an Edit button has been clicked; note that the clicked Edit button gives way to Update and Cancel buttons, while the columns in the editable row are rendered as TextBoxes.

2

Figure 40

Figure 41 In Figure 41 notice how the ProductID column is not editable. As the ASP.NET page's declarative

syntax below shows, you can mark a GridView column as non-editable by settings its ReadOnly property to True. When poking through the markup below, be sure to take note of the following:

3

The

•

SqlDataSource

contains

an

UpdateCommand

property

and

an

section, which describes the required parameters in the UPDATE statement. If you forget to add this UpdateCommand to the SqlDataSource you will not be able to create an editable GridView.

The tags in the section will, by default,

•

convert blank strings entered into the GridView to NULLs. You can override this behavior

by setting the ConvertEmptyStringToNull property to False for whatever parameters

needed.

•

The ProductID column is non-editable due to the ReadOnly="True" setting in the

•

This example demonstrates the GridView's default editing interface, which renders all

associated BoundField.

editable columns as TextBoxes.

<%@ Page Language="C#" %> <script runat="server">
>

">
Type="Int32"

Name="ProductID" rel="nofollow">

4

<PagerStyle ForeColor="#003399" HorizontalAlign="Left" BackColor="#99CCCC"> <SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True" BackColor="#009999">
ForeColor="#003399"

BackColor="White">

The above example was created without checking the Use optimistic concurrency checkbox. If you checked that along with the Generate Insert, Update, and Delete Statements checkbox, the UpdateCommand for the SqlDataSource would have included a more thorough WHERE clause.

5

Instead of just using WHERE [ProductID] = @original_ProductID, with optimistic concurrency the WHERE clause would have read:

WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock This would prevent an UPDATE from happening if, between the time the user clicked a GridView row's Edit button and having clicked the Update button, another user had modified that particular row.

Editing

GridView

ObjectDataSource

Data

That

Comes

from

an

Like deleting data from a GridView bound to an ObjectDataSource, creating an editable

GridView bound to an ObjectDataSource requires very few changes in the creation of the ASP.NET page. Most of the work is required in adding a method to the data access layer class to

handle the update. As with the Delete example, the signature for the Update method depends on

the ObjectDataSource's ConflictDetection property. If you are using the default behavior— OverwriteChanges—then the Update method needs to accept the original primary key field(s)

along with parameters for the non-primary key updated values. If, on the other hand,

CompareAllValues is used, the Update method must accept both the updated and original non-

primary key field values along with the original primary key field(s). To help clarify any confusion,

let's look at a concrete example. Let's create a GridView that displays fields from the Northwind

Products table, just like we did in the previous demo. For this demo, though, we'll need to add an

Update method to the ProductDAL class. (Recall that we created the ProductDAL class back in the

Accessing

Data

with

the

DataSource

Controls

section

when

examining

the

original_ProductID

As

ObjectDataSource.) Assuming that we're using the OverwriteChanges behavior, the Update

method would look like:

The UpdateProduct Method (Visual Basic) Public Class ProductDAL ... Public

Shared

Sub

UpdateProduct(ByVal

Integer, _ ByVal productName As String, ByVal unitPrice As Decimal, _ ByVal unitsInStock As Integer) 'Updates the Products table Dim sql As String = "UPDATE Products SET ProductName = " & _ " @ProductName, UnitPrice = @UnitPrice, UnitsInStock = " & _ "@UnitsInStock WHERE ProductID = @ProductID" Using myConnection As _ New SqlConnection( _ ConfigurationManager.ConnectionStrings("NWConnectionString").Connectio nString)

6

Dim myCommand As New SqlCommand(sql, myConnection) myCommand.Parameters.Add( _ New SqlParameter("@ProductName", productName)) myCommand.Parameters.Add(New SqlParameter("@UnitPrice", _ unitPrice)) myCommand.Parameters.Add(New

SqlParameter("@UnitsInStock",

_ unitsInStock)) myCommand.Parameters.Add(New SqlParameter("@ProductID", _ original_ProductID)) myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() End Using End Sub End Class The UpdateProduct Method (C#) public class ProductDAL { ... public static void UpdateProduct(int original_ProductID, string productName, decimal unitPrice, int unitsInStock) { // Updates the Products table string sql = "UPDATE Products SET ProductName = " + "@ProductName, UnitPrice = @UnitPrice, UnitsInStock = " + "@UnitsInStock WHERE ProductID = @ProductID"; using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NWConnectionStri ng"].ConnectionString)) { SqlCommand myCommand = new SqlCommand(sql, myConnection); myCommand.Parameters.Add(new SqlParameter("@ProductName", productName)); myCommand.Parameters.Add(new SqlParameter("@UnitPrice", unitPrice)); myCommand.Parameters.Add(new SqlParameter("@UnitsInStock", unitsInStock)); myCommand.Parameters.Add(new SqlParameter("@ProductID", original_ProductID)); myConnection.Open(); myCommand.ExecuteNonQuery();

7

myConnection.Close(); } } } As you can see, the Update method, UpdateProducts(), takes in the original_ProductID along with each of the editable columns in the GridView: productName, unitPrice, and

unitsInStock. If you had configured the ObjectDataSource to CompareAllValues you'd need an Update method with a signature like:

' Visual Basic .NET Public Shared Sub UpdateProduct(ByVal original_ProductID as Integer, _ ByVal original_productName as String, _ ByVal decimal original_unitPrice as Decimal, _ ByVal original_unitsInStock as Integer, _ ByVal productName as String, ByVal unitPrice as Decimal, _ ByVal unitsInStock as Integer) ... End Sub // C# public static void UpdateProduct(int original_ProductID, string original_productName, decimal original_unitPrice, int original_unitsInStock, string productName, decimal unitPrice, int unitsInStock) { ... } If you use the CompareAllValues approach, it is your responsibility as the DAL developer to take

whatever steps are needed to determine if the underlying data has been during the user's edits

and, if so, how to resolve the issue. Once the DAL class has been given an appropriate Update method, the final step is configuring the ObjectDataSource to utilize this Update method. To accomplish this, from the ObjectDataSource's wizard navigate to the UPDATE tab and select the

appropriate method (see Figure 42).

8

Figure 42 That's all there is to it! From the end user's perspective, updating a GridView that is bound to a

SqlDataSource

is

no

different

than

updating

a

GridView

that

is

bound

to

an

ObjectDataSource. You can refer back to Figures 40 and 41 to see the editable GridView in

action. The following shows the declarative syntax for the ASP.NET page. As with the SqlDataSource example, note that there's no code necessary in the ASP.NET page.

<%@ Page Language="C#" %> <script runat="server">
>

9


Type="Int32"

Name="ProductID" rel="nofollow"> <PagerStyle ForeColor="#003399" HorizontalAlign="Left" BackColor="#99CCCC"> <SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True" BackColor="#009999">

10


ForeColor="#003399"

BackColor="White">

Customizing the Editing Interface The past two examples illustrate how easy it is to create an editable GridView. One disadvantage of the default editing capabilities of the GridView, though, is that the editing interface is likely

suboptimal. By default, all editable columns in the editable GridView row are rendered as

TextBoxes, and none of them have any sort of validation logic associated with them. In real-world situations, though, oftentimes we'll need to add validation logic to the editable inputs, or perhaps even use editing controls other than the TextBox.

Regardless of how you want to alter the GridView's editing interface, the way it's accomplished is through using TemplateFields as opposed to BoundFields. A BoundField will always display a

TextBox in edit mode. With a TemplateField, however, you can specify precisely how the editing interface should function.

In this section we'll look at two examples of customizing the GridView's editing interface. In the

first, we'll see how to add validation controls; in the second, we'll look at replacing the standard TextBox with a DropDownList instead.

Adding Validation Controls to the Editing Interface Since the GridView's default editing interface does not provide any validation logic, an end user can enter any sort of data into the editable row's TextBoxes. Looking back at our last two editable GridView examples, consider what would happen if the user entered a value of, say, Sam for the

Unit Price of a product. Such actions would result in an exception, since the database cannot set a

decimal field to a string value. Similarly, we might want to impose certain business rules, such as the Unit Price is greater than 0, or that Product Name is a required field.

The GridView's editing interface can be manipulated on a column-by-column level. This is

accomplished by using TemplateFields in place of BoundFields, and specifying the editable

interface to be used. Let's examine how this is done by tweaking the earlier editable GridView bound to a SqlDataSource example to utilize validation controls in the Product, Unit Price, and

Units In Stock columns.

The first step is to transform those three columns into TemplateFields. The simplest way to do this

it to go to the Design view and click the Edit Columns link in the GridView's Smart Tag. Selecting

a BoundField from the column list in the bottom left hand corner, you will see a Convert this field into a TemplateField link (see Figure 43). Go ahead and turn the Product, Unit Price, and Units In Stock columns into TemplateFields.

11

Figure 43 As we saw earlier, when a GridView row is made editable, the BoundFields turn into TextBoxes.

The TemplateFields, on the other hand, render whatever HTML markup and Web control syntax you

specify

in

the

TemplateField's

EditItemTemplate.

To

edit

a

TemplateField's

EditItemTemplate, choose the Edit Templates link from the GridView's Smart Tag. This will

allow you to select what Template for what column you want to edit. Note

If a TemplateField lacks an EditItemTemplate the GridView column will be non-editable.

Notice that by converting the BoundFields into TemplateFields through the Design view, the

TemplateField will contain an EditItemTemplate with a TextBox. Figure 44 shows editing the

Product column's EditItemTemplate. The TextBox present was not added by myself, but placed

there automatically when converting to the TemplateField.

12

Figure 44 To add validation controls to an EditItemTemplate, simply drag and drop the appropriate validation

controls

from

the

Toolbox

into

the

EditItemTemplate.

Let's

add

a

RequiredFieldValidator in the Product EditItemTemplate, and CompareValidators in the Unit

Price and Units In Stock columns to ensure that the data entered is of the right type. Finally, add a ValidationSummary control to the page to display information about invalid data to the user.

Once you have added the validation controls your ASP.NET page's declarative syntax should look similar to the following:

<%@ Page Language="C#" %> <script runat="server">
>

"

13

UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock]

=

@UnitsInStock

WHERE

[ProductID]

=

@original_ProductID" SelectCommand="SELECT [ProductName], [ProductID], [UnitPrice], [UnitsInStock] FROM [Products]"> <PagerStyle ForeColor="#003399" HorizontalAlign="Left" BackColor="#99CCCC"> <EditItemTemplate>
14

ID="RequiredFieldValidator1" Runat="server" ErrorMessage="You must provide a Product Name." ControlToValidate="editProductName" rel="nofollow"> * <EditItemTemplate> * <EditItemTemplate>
15

Operator="DataTypeCheck" Type="Integer" rel="nofollow"> * <SelectedRowStyle ForeColor="#CCFF99" Font-Bold="True" BackColor="#009999">
ForeColor="#003399"

BackColor="White">

ID="ValidationSummary1"

Runat="server"

/ rel="nofollow">

When editing a GridView row you may be wondering how the TextBox is populated with the

correct field value for the editable row. As you can see in the TextBox Web control's declarative

syntax, the magic is in the Bind() method. Setting the TextBox's Text property to <%#

Bind(fieldName) %> displays the value of the field fieldName in the TextBox. This same syntax

is also used in the Label Web controls in the TemplateFields' ItemTemplates.

Figure 45 shows a screenshot of the enhanced editable GridView. Note that if invalid values are

entered into the fields the underlying data is not updated and the validation summary at the bottom of the GridView indicates what problems exist.

16

[

http://msdn.microsoft.com/en-us/library/ms972948.gridview_fig45l(en-us,MSDN.10).gif ] Figure 45 (Click on the graphic for a larger image)

Using an Alternative Web Control for the Editing Interface For certain scenarios the TextBox might not be the ideal editing interface. For example, when editing a date field, users will be less prone to error selecting the date using a Calendar Web

control, rather than having to type in the date in a TextBox. Similarly, for lookup data—data that

is restricted to a set of legal values, which are commonly defined in a separate database table— the best editing interface is typically a DropDownList.

The Northwind database's Products table contains a CategoryID field that associates a product with a category from the Categories table. This is a prime case where a DropDownList would be

suited for the editing interface. Modifying the editing interface to provide a DropDownList is remarkably easy, requiring no source code at all.

To start, create a SqlDataSource that's configured to return a list of products along with the

products' associated categories. To accomplish this you'll need to use a custom SQL SELECT

statement in the DataSource wizard, one that uses a join to retrieve the correct CategoryName for a product's CategoryID. The SQL query should look something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.CategoryID, dbo.Categories.CategoryName FROM dbo.Products INNER JOIN dbo.Categories ON

17

dbo.Products.CategoryID = dbo.Categories.CategoryID Remember when creating the SqlDataSource to include an UpdateCommand. If you forget to specify an UpdateCommand the GridView cannot be made editable.

Next, add a GridView, bind it to the SqlDataSource, and make it editable. Feel free to remove

the ProductID and CategoryID columns from the GridView, leaving just the ProductName and

CategoryName fields.

Our next task is to customize the editing interface for the CategoryName column so that it uses a DropDownList of categories as opposed to a TextBox. As we saw in the previous section, a

TemplateField is needed to create a customized editing interface, so convert the CategoryName

column into a TemplateField. Next, from the Design view, edit the CategoryName column's EditItemTemplate.

The CategoryName EditItemTemplate should contain a TextBox. Go ahead and remove this and

add the necessary controls to have a DropDownList of the available categories. Simply drag and drop a SqlDataSource onto the page configured to select the CategoryID and CategoryName

fields from the Categories table. Next, add a DropDownList to the EditItemTemplate and bind it

to this SqlDataSource (see Figure 46).

Figure 46

18

If you visit the ASP.NET page in a browser at this point, you'll find that when editing a row in the

GridView the CategoryName column displays a DropDownList of all categories, but regardless of the row's category, the first category in the DropDownList is always the one selected. We need to

improve our page so that when a row becomes editable, the DropDownList's SelectedValue is set

to the row's CategoryID value. This can be accomplished using the Bind() method we saw in the previous example, as shown in the TemplateField's markup below:


SortExpression="CategoryName"

HeaderText="CategoryName" rel="nofollow"> <EditItemTemplate> With this final addition—setting the DropDownList's SelectedValue='<%# Bind("CategoryID") %>—the example is complete. Figure 47 shows a screenshot of the GridView as it's being edited.

19

Figure 47 The following shows the complete declarative syntax for the ASP.NET page. Note that this entire exercise was accomplished without writing a line of code.

<%@ Page Language="C#" %> <script runat="server">
>

">
Type="Int32"

Name="CategoryID" rel="nofollow"> ">
20

DataSourceID="productsDataSource" DataKeyNames="ProductID" AutoGenerateColumns="False" AllowPaging="True" BorderWidth="1px" BackColor="White" CellPadding="3" BorderStyle="None" BorderColor="#CCCCCC" rel="nofollow">
ForeColor="#000066"

BackColor="White"> <PagerStyle ForeColor="#000066" HorizontalAlign="Left" BackColor="White"> <EditItemTemplate> <SelectedRowStyle ForeColor="White" Font-Bold="True" BackColor="#669999">

21

22