Beginning ASP.NET 2.0 With CSharp (2006) [eng]

Chapter 8

Figure 8-5

How It Works

ASP.NET 2.0 has again made all of this easy by including the functionality of updating within the data controls. To enable updates in the data source control, you checked off the option to include INSERT, UPDATE, and DELETE commands. This is more than you need for now (although you will use the INSERT and DELETE later in this chapter). VWD built two changes to the data source control that enable it to perform updates on data. First is the addition of an UpdateCommand as follows:

<asp:SqlDataSource ID=”SqlDataSource1” ...

UpdateCommand=”UPDATE [Fixtures] SET [FixtureDate] = @FixtureDate, [FixtureType] = @FixtureType, [GoalsFor] = @GoalsFor, [GoalsAgainst] = @GoalsAgainst, [Notes] = @Notes, [Opponents] = @Opponents WHERE [FixtureID] = @original_FixtureID”>

VWD then added a set of parameters that will hold the values you will use for updates, as shown in the following code. Note that the FixtureID is saved in two parameters (the first and last). The first is the new value, if the user enters one. The second is named original_FixtureID and will hold the original value so it can be used in the WHERE clause to match which record to change:

<UpdateParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”FixtureType” Type=”String” /> <asp:Parameter Name=”GoalsFor” Type=”Int16” /> <asp:Parameter Name=”GoalsAgainst” Type=”Int16” /> <asp:Parameter Name=”Notes” Type=”String” /> <asp:Parameter Name=”Opponents” Type=”String” /> <asp:Parameter Name=”original_FixtureID” Type=”Int32” />

</UpdateParameters>

In the GridView control, you added the single property of displaying the Edit button in a column:

<asp:GridView ID=”GridView1” ...

<Columns>

<asp:CommandField ShowSelectButton=”True” ShowEditButton=”True” />

The procedure was almost the same for the DetailsView. You modified the SqlDataSource2 control to enable updates by adding an UpdateCommand and a set of update parameters. However, VWD is more

268

Writing Data

conservative with DetailsView and provides original_ parameters for all fields and compares all fields in the WHERE clause. You excised those extra comparisons and just relied on matching the

FixtureID.

Adding New Records

Adding new records, known in SQL parlance as INSERT or INSERT INTO, is almost as easy as changing an existing record. The main difference is that the GridView does not support adding a new record, which will be considered shortly.

As discussed previously, four changes must be made to the data controls. First, in the data source control you have the Advanced button in the Create New Data Source wizard. A click there and a check on Generate INSERT, UPDATE, and DELETE gave you all three of the SQL statements. That created two additions to the data source control. The first is the InsertCommand itself. The second is the set of INSERT parameters. (Actually, commands and parameters were also created for UPDATE and DELETE, but it is INSERT that is of interest to us in this section.)

In the data-bound control, you enable inserting with a check box on the data-bound control’s Common Tasks panel. Two changes result. The first is in the <asp:commandfield> where you now have the

ShowInsertButton. This will co-exist with the ShowEditButton and ShowDeleteButton if they have been selected. The ShowInsertButton enables the functionality to add new records. As with the other CommandField buttons, ShowInsertButton automatically hides itself and displays other buttons (Insert and Cancel) when the control switches to INSERT mode.

GridView is a special case because it does not directly support the addition of new records. The most common path is to display existing records in the GridView and create a button on the GridView that goes to a DetailsView (which can be on the same page or a second page). The DetailsView can then open in INSERT mode, ready to accept the values for the new record. The option to open in INSERT mode is set by the DefaultMode property of the data-bound control.

As in editing, constraints on data are acceptable. For example, if you were adding a new match report, the fixture must match an existing fixture. You can’t comment on a match that did not happen. As with editing, you can decrease these conflicts by giving users a list of possible options instead of typing. If they must type, then run the values through a validation control.

In this Try It Out, you add the INSERT capability to the Fixtures.aspx page. You cannot insert new records in a GridView, so this exercise only affects the DetailsView control.

1.Open VWD and your Fixtures.aspx page in Source View and scroll down to the SqlData Source2 control (the data source for the DetailsView control). Observe that in the last exercise when VWD added the capability to update a record, it also added similar tags for insert and delete operations. Therefore, in the data source control the insert behavior is already enabled.

2.Select the DetailsView control, open its Common Tasks panel, and enable inserting, as shown in Figure 8-6.

269

Chapter 8

Figure 8-6

3.In the Properties window, find DefaultMode (see Figure 8-7) and change its value to Insert.

Figure 8-7

4.Save and view in the browser (see Figure 8-8). Try adding a new fixture and refresh the page.

Figure 8-8

5.Try entering some nonsense data, such as a date of 99/99/2006. Note how the error is not, at this point, handled very gracefully. Chapter 15 looks at handling errors.

270

Writing Data

How It Works

Many of the tags for this process were created in the previous exercise when you configured the data sources and VWD offered the Advanced button with options to create INSERT, UPDATE, and DELETE commands. It may have seemed like an overwhelming number of tags at first, but you can now see how the pattern of a command plus a set of parameters enables a data-writing function.

Specifically, you are using the InsertCommand of the SqlDataSource2 that supports the DetailsView. That command relies on the set of InsertParameters, as shown in the following code. Note that there is no need for a WHERE clause because you do not need to identify an existing record upon which to carry out the action:

<asp:SqlDataSource ID=”SqlDataSource2” ...

InsertCommand=”INSERT INTO [Fixtures] ([FixtureDate], [FixtureType], [GoalsFor], [GoalsAgainst], [Notes], [Opponents]) VALUES (@FixtureDate, @FixtureType, @GoalsFor, @GoalsAgainst, @Notes, @Opponents)”

...

<InsertParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”FixtureType” Type=”String” /> <asp:Parameter Name=”GoalsFor” Type=”Int16” /> <asp:Parameter Name=”GoalsAgainst” Type=”Int16” /> <asp:Parameter Name=”Notes” Type=”String” /> <asp:Parameter Name=”Opponents” Type=”String” />

</InsertParameters>

...

</asp:SqlDataSource>

ASP.NET 2.0 handles INSERT operations in a way that is very similar to the UPDATE operations you did in the previous section. When you select the option in the Advanced dialog box, VWD builds a SQL INSERT INTO statement with a set of parameters that can hold the data entered by the user. When you enable INSERT on the data-bound control, VWD adds the CommandField button for adding a new record. All the rest of the code is taken care of by Microsoft, hidden from your view in the data controls. The only trap to remember is that inserting new records is available in DetailsView and FormView, but not GridView.

Deleting Records

By now you have a good feel for how writing data works and can anticipate that the procedure for deleting a record is similar. In the data source control, you must include the DELETE command, which can be automatically generated in the Advanced button of the control’s Configure New Data Source dialog box. That button will also create a set of DeleteParameters. But there are differences. For example, the DeleteParameters will be used to make an exact match for deletion in the WHERE clause, but they will not be used for changing information in the record.

As discussed before, there are some hazards when writing data and these are particularly true for deleting. The first is the human error of deleting the wrong record. More sophisticated systems respond to a request to delete by actually moving the record to a Deleted table. That gives a chance for recovery, at least until the Deleted table is purged. Discussions of this strategy are common in database texts and are

271

Chapter 8

not difficult to implement in ASP.NET 2.0. A second problem is when the user attempts to delete a record that the database has blocked from deletion. That block is most often the result of another record being dependent on the record that was to be deleted. For example, a table of orders would have a field for the ID of a customer that placed an order. If you delete that customer, you are left with a value in the Orders table that points to nowhere. In this case, the database will return an error.

In this Try It Out, you enable the capability to remove a match from the list of fixtures.

1.Continue using VWD with Fixtures.aspx in Design View. Both of the data source controls already have the DeleteCommand and a set of DELETE parameters.

2.Select the GridView control, open its Common Tasks panel, and select Enable Deleting (see Figure 8-9), which adds the following shaded line to the GridView control. That code also adds the Delete command fields in Figure 8-9.

<asp:GridView ID=”GridView1” runat=”server”

...

<Columns>

<asp:CommandField

ShowEditButton=”True”

ShowSelectButton=”True” ShowDeleteButton=”True” />

...

</Columns>

</asp:GridView>

Figure 8-9

3.Select the DetailsView control, open its Common Tasks panel, and select Enable Deleting. Go to the DetailsView’s Properties window and change the DefaultMode back to ReadOnly.

4.As before, when VWD creates a DetailsView with deleting, it is overly cautious in checking fields to match up the correct record to delete. The original DELETE statement is shown in the following code. (Be sure you are looking at SqlDataSource2, the one for the DetailsView.) Delete the shaded portion. Double-check that the DeleteCommand still ends with a double quote:

272

Writing Data

DeleteCommand=”DELETE FROM [Fixtures] WHERE [FixtureID] = @original_FixtureID AND [FixtureDate] = @original_FixtureDate

AND [FixtureType] = @original_FixtureType AND [GoalsFor] = @original_GoalsFor

AND [GoalsAgainst] = @original_GoalsAgainst AND [Notes] = @original_Notes

AND [Opponents] = @original_Opponents

“

5.Save the page and test it in your browser. It should look like Figure 8-10.

Figure 8-10

Note that if you try to actually delete a fixture you will get an error message. Many other aspects of the site rely on the list of fixtures, so deleting one will leave those data hanging without a fixture. This situation is called a reference constraint as mentioned in the error.

How It Works

The pattern is the same as for other means of changing data. The data source control must have a DeleteCommand. When you checked the option in the Advanced step of the Configure Data Source wizard, VWD created the DeleteCommand for you. VWD also created a set of DeleteParameters that will be used by the WHERE clause to identify the record to delete. As with UPDATE, VWD tries to match every field in the DeleteParameters list for DetailsView. This may be necessary in some cases, but for now a simple match of the FixtureID is sufficient.

You then enabled deleting on the data-bound control, which instructed VWD to add a CommandField of the type Delete to the data-bound control. The term “field” here is a little odd because there is no connection to a field in the data table, but it is an addition to the data-bound control that is rendered similarly to a field.

One thing you may have noticed while running through the exercises in this chapter is that you have two ways of showing and editing the fixtures: GridView and DetailsView. When you edited data in one, the changes weren’t reflected in the other. To get around this, you need to add code to the data

273

Chapter 8

source controls, and although you only need a single line of code, it has to be entered in several places. This is because there are several ways the data can change: you can insert new fixtures, edit existing fixtures, or delete fixtures. Each of these causes the SqlDataSource control to raise an event, and it is within that event that you have to rebind the data for the other control.

The code for this is shown here:

protected void SqlDataSource1_Deleted(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ DetailsView1.DataBind(); }

protected void SqlDataSource1_Inserted(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ DetailsView1.DataBind(); }

protected void SqlDataSource1_Updated(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ DetailsView1.DataBind(); }

protected void SqlDataSource2_Deleted(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ GridView1.DataBind(); }

protected void SqlDataSource2_Inserted(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ GridView1.DataBind(); }

protected void SqlDataSource2_Updated(object sender, System.Web.UI.WebControls.SqlDataSourceStatusEventArgs e)

{ GridView1.DataBind(); }

You can see that there are three events for each control. The Deleted event is raised after a record is deleted, the Inserted event is raised after a record is added, and the Updated event is raised when a record is updated. Even through you are using a GridView control to display one set of data, and a DetailsView control for the other, the events look the same because it is the SqlDataSource control that raises the event. For the GridView, which is bound to SqlDataSource1, the DetailsView needs to be refreshed, so the DataBind() method is called on the DetailsView, which instructs it to re-fetch the data. A similar procedure is done for the events of SqlDataSource2, which is used by the DetailsView, but this time the DataBind() method is called on the GridView1 control. It’s a simple procedure — when data in one control changes, you refresh the data in the other control.

Uploading Pictures

ASP.NET 2.0 offers an easy way to upload pictures (or other files) from the browser to the server. Although not strictly a database issue, the topic is covered here. The toolbar offers the FileUpload tool, which can be added to the page to produce a text box with a browse button. You, as the designer, must also add a button to give the user the capability to actually execute the upload.

In the button’s click code, the simplest option is shown in the following code. The file that the user indicated (either by typing or browsing) will be transferred to the server:

FileUpload1.SaveAs(FileUpload1.FileName);

274

Writing Data

But this code is too simplistic because the file will be plopped into the root of the web site. You can add a literal string to be appended in front of the file name that will direct the file into an appropriate folder on the server. Note that when you open the page in your browser you can view the source, but the path on your server is not revealed. The following code places the file in MyImageFolder:

using System.IO

string ImagesFolder = “MyImageFolder”; string savePath;

string saveFile;

savePath = Path.Combine(Request.PhysicalApplicationPath, ImagesFolder); saveFile = Path.Combine(savePath, FileUpload1.FileName); FileUpload1.SaveAs(saveFile);

When the FileUpload.SaveAs method is invoked, ASP.NET 2.0 creates an object named FileUpload

.PostedFile with several properties about the operation. The most obvious are FileName and ContentLength. So if you create a label named Label1 you can display in its text the name of the file that was uploaded as follows:

FileUpload1.SaveAs(saveFile);

Label1.Text = FileUpload1.PostedFile.FileName;

What if the user clicks the button without first selecting a file? You can avoid this problem with an IF THEN statement as follows (code structures such as IF THEN are explained in Chapter 9):

FileUpload1.SaveAs(saveFile);

// Displays status of success Label1.Text =”Your file was uploaded successfully.”;

}

else // probably no file selected

{

// Display status of failure

Status.Text = “You did not specify a file to upload.”;

}

Other errors can occur, so you should encase the FileUpload method in an error-catching routine as follows:

protected void ButtonUsingTryCatch_Click(object sender, EventArgs e)

{

string ImagesFolder = “MatchImages”; string savePath;

string saveFile;

if (FileUpload1.HasFile)

{

try

{

// perform the upload

savePath = Path.Combine(Request.PhysicalApplicationPath,

ImagesFolder);

275

Chapter 8

saveFile = Path.Combine(savePath, FileUpload1.FileName); FileUpload1.SaveAs(saveFile);

// Displays status of success

StatusLabel.Text = “Your file was uploaded successfully.”;

}

catch(Exception exUpload)

{

// display status of error StatusLabel.Text = exUpload.Message;

}

}

else // probably file was not selected

{

// Display status of failure

StatusLabel.Text = “You did not specify a file to upload.”;

}

In the following Try It Out, you give users a way to add pictures to the gallery.

1.Using VWD, create a new page named GalleryUpload.aspx using the Web Form template. As you have with most pages up to this point, use the site.master as the Master page, use Visual C#, and enable the option to place the code on a separate page.

2.In Design View, add a FileUpload control from the Toolbox and a Label control that will have an ID of FileUploadReport with an empty text property. Also add a button control with the text property set to “Upload”.

3.Double-click the button to go to its code. Add the following shaded code to the Sub:

using System; using System.IO; using ...

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

{

protected void Button1_Click(object sender, EventArgs e)

{

string ImagesFolder = “MatchImages”; string savePath;

string saveFile;

if (FileUpload1.HasFile)

{

try

{

// perform the upload

savePath = Path.Combine(Request.PhysicalApplicationPath,

ImagesFolder);

saveFile = Path.Combine(savePath, FileUpload1.FileName); FileUpload1.SaveAs(saveFile);

// Displays status of success

FileUploadReport.Text = “Your file was uploaded successfully.”;

}

catch(Exception exUpload)

276

Writing Data

{

// display status of error FileUploadReport.Text = exUpload.Message;

}

}

else // probably file was not selected

{

// Display status of failure

FileUploadReport.Text = “You did not specify a file to upload.”;

}

}

}

4.Save the page and view it in your browser (see Figure 8-11). You probably won’t have pictures of the hapless Wrox United fumblers, but you can try uploading any jpeg or gif you have on your hard drive.

Figure 8-11

How It Works

The FileUpload control itself is a simple drag-and-drop. The browsing capability is built in. However, there is no built-in means to execute the upload. So you added a button to fire the SaveAs method of the FileUpload control. That method needs an argument specifying where to put the file on the server. You hard-coded a literal for the path and then appended the name of the file the user entered into the

FileUpload control.

You have done some embellishments beyond the basic control. The FileUpload control has a handy property called HasFile. When there is a valid file name in the text box, the HasFile property will be TRUE. The IF statement determines whether the user actually typed or browsed to a file to upload. If not, the code hops down to the ELSE statement to display an error message. Other things could go wrong, like the Wrox United webmaster (even more hapless than the team) changes the name of the folder in which to store images. So you encapsulated the execution of the SaveAs within a Try...Catch block.

277