Programming Microsoft Web Forms (Pro Developer)

 

The MultiView Control

You can allow data to be edited on a Web Form in many ways. In addition to using the DetailsView control or editing templates, as shown in the previous examples, you can also create a single page to handle display and editing of data by using a MultiView control. A MultiView control can contain any number of sections of markup, called views, with only a single view visible at a time. Using the MultiView control falls somewhere between using multiple Panel controls, covered in Chapter 2, "A Multitude of Controls," and using the Wizard control, covered in Chapter 3, "Web Form Layout."

The previous iteration of the Default.aspx page allowed for relatively convenient and attractive editing of blog entry details. One problem remains. Let's look back at the structure of the underlying BlogEntry table. In addition to the columns that have been used in the GridView control, several other columns were not included because their appearance in the GridView control would be problematic. Here is the SQL CREATE script that allows you to create the BlogEntry table in SQL Server, which also shows all the columns in the BlogEntry table.

CREATE TABLE [dbo].[BlogEntry] ( [BlogEntryID] [int] NOT NULL , [DateEntered] [datetime] NOT NULL , [EnteredBy] [nvarchar] (50) NULL , [DateModified] [datetime] NULL , [ModifiedBy] [nvarchar] (50) NULL , [WeatherConditionID] [int] NULL , [WindDirectionID] [int] NULL , [WindStrengthID] [int] NULL , [MilesBiked] [smallint] NULL , [Subject] [nvarchar] (255) NULL , [Message] [text] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

For example, the WeatherConditionID, WindDirectionID, and WindStrengthID columns are just ID columns that point to the real data in supporting tables. Displaying a number would be meaningless. Rather than editing a number for the various weather-related ID columns, the user would certainly prefer to see the string values pointed to by these ID columns.

Data editing can be allowed in several ways. A common way is to create a second page that is called when the user edits one of the rows. However, this creates a clutter of pages required for a single task. An alternative is to create a single page with a control new to ASP.NET 2.0 called a MultiView control.

On a new page, named BetterEdit.aspx, I dropped an SqlDataSource control onto the form, configured it, and then dropped a MultiView control onto the form. Then, in Source view, I added two View tags to the MultiView control, which resulted in the markup shown in Listing 5-2.

Listing 5-2: BetterEdit.aspx after Adding an SqlDataSource Control, a MultiView Control, and Two View Elements

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="BetterEdit.aspx.cs" Inherits="BetterEdit" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form runat="server"> <div> <asp:SqlDataSource runat="server" ConnectionString= "<%$ ConnectionStrings:BikeBlogConnectionString %>" DeleteCommand="DELETE FROM [BlogEntry] WHERE [BlogEntryID] = @original_BlogEntryID" InsertCommand="INSERT INTO [BlogEntry] ([DateEntered], [EnteredBy], [DateMocified], [ModifiedBy], [Subject], [Message]) VALUES (@DateEntered, @EnteredBy, @DateMocified, @ModifiedBy, @Subject, @Message)" SelectCommand="SELECT [BlogEntryID], [DateEntered], [EnteredBy], [DateMocified], [ModifiedBy], [Subject], [Message] FROM [BlogEntry] ORDER BY [DateEntered] DESC" UpdateCommand="UPDATE [BlogEntry] SET [DateEntered] = @DateEntered, [EnteredBy] = @EnteredBy, [DateMocified] = @DateModified, [ModifiedBy] = @ModifiedBy, [Subject] = @Subject, [Message] = @Message WHERE [BlogEntryID] = @original_BlogEntryID"> <DeleteParameters> <asp:Parameter Name="original_BlogEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DateEntered" Type="DateTime" /> <asp:Parameter Name="EnteredBy" Type="String" /> <asp:Parameter Name="DateMocified" Type="DateTime" /> <asp:Parameter Name="ModifiedBy" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="Message" Type="String" /> <asp:Parameter Name="original_BlogEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DateEntered" Type="DateTime" /> <asp:Parameter Name="EnteredBy" Type="String" /> <asp:Parameter Name="DateMocified" Type="DateTime" /> <asp:Parameter Name="ModifiedBy" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="Message" Type="String" /> </InsertParameters> </asp:SqlDataSource> </div> <asp:MultiView runat="server"> <asp:View runat=Server> </asp:View> <asp:View runat=server> </asp:View> </asp:MultiView> </form> </body> </html>

The markup for the SqlDataSource1 control is generated by the wizard; however, looking at this markup and understanding it will help if you need to modify your code after the initial creation of the data source. Directly inside the SqlDataSource tag, the markup sets the ConnectionString attribute, using special syntax that retrieves the value from the Web.config file, as shown here.

ConnectionString= "<%$ ConnectionStrings:BikeBlogConnectionString %>"

Next, the markup defines a series of commands: DeleteCommand, InsertCommand, and SelectCommand. The values for these attributes are SQL strings that include, where appropriate, parameter placeholders, in this case specified by a variable name that begins with an at sign (@). These commands can be manually modified; however, if the number or names of parameters change, the parameters in the next section must be modified to match. Parameters are declared in the markup as shown here.

<DeleteParameters> <asp:Parameter Name="original_BlogEntryID" Type="Int32" /> </DeleteParameters>

Each of the commands have a corresponding parameters section that must match the parameters in the corresponding command.

Inside the markup for the MultiView control, I added the two View controls. The View controls work in a way that is very similar to the earlier EditItemTemplate markup blocks shown in previous examples. The View controls act as containers for other controls. I named the views appropriately: Grid and DetailsEdit. In Design view in Visual Studio, the page looks like Figure 5-38.

Figure 5-38: BetterEdit.aspx after adding the MultiView control

There is not much support in Design view for the MultiView control, so adding the View tags manually is the easiest way to create the views. After the views are added, you can drag and drop controls onto the views as you would on any standard page. I dropped a GridView control onto the first view (named Grid), and then I created a table formatted as a data entry form (two columns, one right justified for labels, one left justified for the edit controls, both set to 50 percent of the width of the table).

Next, I added three SqlDataSource controls to the form. These are used to select data from the WeatherCondition, WindDirection, and WindStrength tables. In Chapter 2, I introduced the DropDownList control and indicated that, normally, it is populated from a database. This data entry form will be an example of that, with each of the columns, WeatherConditionID, WindDirectionID, and WindStrengthID, displayed and edited by using a DropDownList control. After configuring the SqlDataSource controls to select data from the respective tables and changing the names from the default names to meaningful names, I added labels, text boxes, and combo boxes as needed to the DetailsEdit view, and I modified the names of the controls as appropriate. Note that none of these SqlDataSources controls required anything other than a SELECT statement, because the data will not be edited with this form.

After the SqlDataSource controls are configured and the DropDownList controls are renamed appropriately, the next step is to select a DropDownList control in Visual Studio and open the Properties window. After the DataSourceID property is correctly set (in the ddlWeatherCondition control, for example, this would be the data source named dsWeatherCondtion), two other important properties must be set. First is the DataTextField property, which selects the field that will appear as the text in the drop-down list. The other is the DataValueField property, which selects the field to use as the underlying value for the control. Figure 5-39 shows the Properties dialog box for the Weather Condition drop-down list.

Figure 5-39: Properties dialog box with data-related properties configured

Populating the Details Editing Form

To populate the detail controls in the DetailsEdit view in the MultiView control, I had a few possible options. For example, I could have used the Select method of the SqlDataSource control. In this way, only a line or two of code would be required to get the SqlDataSource control to return the specific row from the BlogEntry table. In this example, that would be a viable alternative, but to show programmatic database access, I opted not to use the Select method.

The programmatic ways to access data using ADO.NET fall into two general categories:

Whether to use a DataReader or DataSet object is the subject of some intense discussion. In fact, I have written on the topic myself (http://www.simple-talk.com/2005/06/10/adonet-data-access/). My general preference is to use DataReader objects for Web Forms. My reasons are:

The Many Faces of ADO.NET

One aspect of ADO.NET that causes significant confusion is the fact that there are some database-agnostic classes and some classes that are specialized for a particular database or type of database access. I will provide much more detail about these classes later in this chapter, but simply understanding which are generic and which are tied to a particular database will be helpful.

The DataSet class, for example, is totally isolated from the actual location of the data. In general, your code should ignore where the data might have come from and just use the data from the DataSet. The Command, DataReader, and DataAdapter classes are available for several different databases. Each family of classes of database-specific access components is generally thought of, when all tied together, as a provider. In Visual Studio, you can choose from providers for SQL Server, OLE DB, and ODBC data. Other providers (including a provider for MySQL) can be obtained from third parties.

Although this might sound confusing (and sometimes it really is), keep in mind that each class provided by each of the providers implements a known interface. So, every one of the Command objects (such as SqlCommand and OleDbCommand) implements a known interface (such as IDbCommand). This mapping, however, is imperfect. For example, in ADO.NET 1.1, Microsoft added a property named HasRows to the SqlDataReader object to allow the developer to determine whether a data reader will return any data. (The significance of this will be covered a bit later). Unfortunately, this property was added only to the SqlDataReader class; it is not available in any of the other standard implementations, and it is not part of the IDataReader interface, nor any new interface that data readers could implement. This is still true in ADO.NET 2.0.

For now, just remember that a DataSet is independent of any provider, and virtually all other database-related objects are tied to a specific provider and implement a known interface.

ADO.NET 1.x provided two basic objects to get data, and both still exist in ADO.NET 2.0. The first is DataReader. A DataReader object is a one-way, read-only view of the returned result set. To access any data from a DataReader, you must call the Read method of the DataReader. The Read method of the DataReader returns a Boolean value; it returns true when there is a row to navigate to. After the DataReader has been read, the developer can access the data in the current row by either indexing the DataReader (by using a column name or column ordinal) or using one of the many access routines to get specific types of data. When the Read method returns false, there are no more rows in the result set.

Interestingly, in the implementation of all DataReader objects (except the SqlClient version), there is no non-destructive way to determine whether a DataReader is returning any rows. After you call the Read method, the pointer is moved to the first row of the DataReader, and you cannot move back so that the DataReader is in its initial state. As I just mentioned, the SqlClient implementation (named SqlDataReader) is an exception: It exposes a HasRows property that is a Boolean value of true or false, indicating whether there are any rows waiting in the DataReader. The DataReader has the advantage of being very fast and relatively efficient in terms of resources; however, using a DataReader can be a problem if lengthy processing must be done on each row returned, because the connection must be held open the entire time the DataReader is being accessed. DataReaders can return multiple result sets, and you can navigate (one way, from the first result set, to the second, and so on) by using the NextResult method.

The second major way to access a database is with the DataSet object. A DataSet is an in-memory, database-independent representation of one or more tables. To fill a DataSet, you must use one of the database-specific DataAdapter objects. For instance, the SqlClient provider has the SqlDataAdapter object that can be used to fill a DataSet. After the DataSet has been filled, the data in the resulting tables can be accessed by using the Tables collection. Rows are accessed by using the Rows collection of the individual DataTable objects from the Tables collection. The Rows collection can be indexed by using both a row ordinal and a column name or column ordinal.

Using the DataSet has several advantages. First, the DataSet provides random access to the data returned from the database. The developer can look at the first row of the first table, then the third row of the second table, then the twelfth row of the first table, and so on, without causing any problems. Another advantage is that the DataSet can be serialized into XML and passed between tiers of a system, even if the tiers are in different process spaces or on different machines. DataSet objects can also be cached by ASP.NET, and caching is a very important feature for creating scalable, high-performance Web Forms applications. DataSet objects can even be passed back from a Web service. (Many developers consider using a DataSet in this way to be a terrible idea, at least in part because the DataSet is a relatively heavyweight object to serialize; the DataSet is also proprietary and cannot be used by most Web services not created with .NET.)

Note 

In ADO.NET 2.0, the members of the DataSet object's Tables collection (DataTable objects) are also serializable, meaning that DataTable objects can be passed independent of a DataSet from tier to tier of an application or returned from a Web service. The difference between serializing a single-table DataSet and just the single table is minimal, and it does not address concerns related to broad compatibility.

Although DataSet objects can be very useful, they might not always be ideal. For example, because DataSet objects are in-memory representations of the data, they take up a great deal of memory, especially if there are many rows or many tables.

For demonstration purposes, I created methods that retrieved the data using both objects. After the data was obtained and ready (in a DataSet or DataReader), I populated the forms appropriately.

Note 

The first thing I did to use any of the SqlClient objects, such as SqlDataAdapter and SqlDataReader, was add the following line to the top of the source file.

using System.Data.SqlClient;

This line allows you to refer to the classes in the SqlClient namespace without including System.Data.SqlClient.

You can select and save data in SQL Server in a variety of ways. One way is to use raw SQL SELECT, INSERT, and UPDATE statements. Although it is common practice to create these statements by simply concatenating strings, that is a dangerous thing to do. The safest way to use raw SQL statements is to create the SQL string with parameters, as shown in the markup in Listing 5-2.

An alternative, which is unsafe and subject to an SQL injection attack, is to build up an SQL statement in a string by concatenating literal strings with SQL syntax and variables. Variables that come from user input should not be trusted. Rather than a reasonable value, a user-supplied variable could have additional SQL commands appended. When you use a user-supplied variable, rather than use parameters, to build up an SQL string, you could be bit by an SQL injection attack.

Note 

SQL strings passed into the SqlClient command object should contain named parameters. Named parameters are a wonderful thing. They allow you to add parameters in any order convenient, and, when used with stored procedures, they allow you to provide default values a useful tactic when you are adding parameters to an existing stored procedure and you don't want to break old code. For OleDb, parameters are positional rather than named. So, when parameters appear in an SQL string, they should appear as ? characters rather than by name. Another consequence of how OleDb parameters work is that they must be added to the Parameters collection in the order in which they appear in the SQL string, left to right.

Tip 

The vast majority of the database code in this chapter can be made to work for the OleDb provider by replacing "Sql" with "OleDb" in the name of the objects used to access data. For instance, change SqlCommand to OleDbCommand (and ensure that parameters are placed in the SQL string as specified in the previous note,) and most code will work for the OleDb provider. Note that the connection string must be changed as well.

Although it is not required to prevent an SQL injection attack, using stored procedures provides some additional advantages. Stored procedures are blocks of SQL code that can be called by name and can accept virtually any number of parameters. In this respect, stored procedures are similar to functions in programming languages such as C#. Stored procedures can contain any number of SELECT, UPDATE, INSERT, and DELETE statements.

The first advantage is that all the database-related work is done near the database. This can be more efficient, especially when you are combining several operations in a single stored procedure. Listing 5-3 shows the two stored procedures, spSelectBlogEntry and spSaveBlogEntry, used to select and save BlogEntry rows, respectively.

Listing 5-3: Stored Procedures to Select and Save BlogEntry Rows

CREATE PROCEDURE dbo.spSelectBlogEntry ( @BlogEntryID int ) AS SET NOCOUNT ON SELECT BlogEntryID, DateEntered, EnteredBy, DateMocified, ModifiedBy, WeatherConditionID, WindDirectionID, WindStrengthID, Subject, Message FROM BlogEntry WHERE BlogEntryID=@BlogEntryID RETURN CREATE PROCEDURE dbo.spSaveBlogEntry ( @BlogEntryID int, @Subject nvarchar(128), @Message text, @WeatherConditionID int, @WindDirectionID int, @WindStrengthID int, @EnteredBy nvarchar(128) = 'DougR' ) AS IF Exists(SELECT * FROM BlogEntry WHERE BlogEntryID=@BlogEntryID) BEGIN UPDATE BlogEntry SET Subject=@Subject, Message=@Message, WeatherConditionID=@WeatherConditionID, WindDirectionID=@WindDirectionID, WindStrengthID=@WindStrengthID, ModifiedBy=@EnteredBy, DateModified=GetDate() WHERE BlogEntryID=@BlogEntryID END ELSE BEGIN INSERT INTO BlogEntry( Subject, Message, WeatherConditionID, WindDirectionID, WindStrengthID, EnteredBy) VALUES( @Subject, @Message, @WeatherConditionID, @WindDirectionID, @WindStrengthID, @EnteredBy) SET @BlogEntryID=SCOPE_IDENTITY() END RETURN @BlogEntryID

The first stored procedure, spSelectBlogEntry, is very straightforward and requires little explanation. It takes a single parameter, @BlogEntryID, which is an integer. The first line of the stored procedure, after the parameter declaration, is SET NOCOUNT ON, which instructs SQL Server not to send a message after each statement indicating the number of rows affected. In some cases, not setting NOCOUNT ON can cause problems with reading records, and in any event the row counts being returned will increase network traffic. The guts of the stored procedure is the same sort of SELECT statement used in some previous examples.

The second stored procedure, spSaveBlogEntry, is a bit more complicated. The pattern used is worth learning. The parameters passed in are all the parameters required to insert (or update) a row in the BlogEntry table. One of the parameters, EnteredBy, has a default value passed in, 'DougR'. Because of the way that SqlClient parameters are processed, if a parameter is not passed in and a default value is present, the stored procedure runs as if the default value were actually passed in to the procedure.

Inside the stored procedure, the first step is to determine whether the row specified by the @BlogEntryID parameter exists. If the row exists, it is updated; otherwise, the row is inserted. All other parameters except the @EnteredBy parameter are used to insert or update the column of the table with the same name. Note that the @EnteredBy parameter is used to fill in the EnteredBy column for a row inserted, and the ModifiedBy column for a modified row. If the BlogEntry is inserted, the stored procedure sets the @BlogEntryID variable to SCOPE_IDENTITY( ), a special SQL Server function to retrieve the IDENTITY value just inserted. SCOPE_IDENTITY( ) returns the value of the column in the most recently inserted table that has been identified as the IDENTITY column in the table; in this case the BlogEntryID column is the IDENTITY value. Finally, the @BlogEntryID parameter is returned from the stored procedure.

You might ask, "Why write a single stored procedure to save the data, rather than a separate Update and Insert stored procedure?" I have found that a single stored procedure used to insert or update, as appropriate, has worked better for me. One benefit of a single stored procedure that will handle both inserts and updates is that it allows the editing of new and existing rows to be handled in the same way in your code.

Before looking at the code, let's look at the screens to see how everything should work. When the page is first loaded, it looks like Figure 5-40.

Figure 5-40: BetterEdit.aspx when first loaded, showing the GridView control

The four most recent rows are displayed, as well as a button that allows the user to add a new entry. To edit one of the rows, click a Select link, and a page like the one shown in Figure 5-41 appears.

Figure 5-41: Editing the details of a row

The Cancel button causes the GridView control to reappear without any change to the data. The Save button saves the data, rebinds the GridView control, and makes the GridView control visible again. Listing 5-4 shows the complete code for BetterEdit.aspx.cs.

Listing 5-4: BetterEdit.aspx.cs

using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class BetterEdit : System.Web.UI.Page { public int EditBlogEntryID { get { return ViewState["EditBlogEntryID"] == null ? 0 : (int)ViewState["EditBlogEntryID"]; } set { ViewState["EditBlogEntryID"] = value; } } protected void Page_Load(object sender, EventArgs e) { if (this.IsPostBack==false) { this.MultiView1.SetActiveView(this.Grid); } } protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e) { this.MultiView1.SetActiveView(this.DetailsEdit); this.EditBlogEntryID = int.Parse( GridView1.DataKeys[e.NewSelectedIndex].Value.ToString()); this.BindDetailsEdit(); } private void BindDetailsEdit() { // This code is not normal practice. I have set up code that // can be fed using a DataReader or a DataSet IDataReader dr; DataSet ds; ds = this.SelectBlogEntryDS(this.EditBlogEntryID); dr = (IDataReader)ds.CreateDataReader(); // Comment the two lines above and uncomment the line below // to use a DataReader directly. // dr = this.SelectBlogEntryDR(this.EditBlogEntryID); try { if (dr.Read()) { this.edSubject.Text = dr["Subject"].ToString(); this.edMessage.Text = dr["Message"].ToString(); this.ddlWeatherCondition.SelectedIndex = this.ddlWeatherCondition.Items.IndexOf( this.ddlWeatherCondition.Items.FindByValue( dr["WeatherConditionID"].ToString())); this.ddlWindDirection.SelectedIndex = this.ddlWindDirection.Items.IndexOf( this.ddlWindDirection.Items.FindByValue( dr["WindDirectionID"].ToString())); this.ddlWindStrength.SelectedIndex = this.ddlWindStrength.Items.IndexOf( this.ddlWindStrength.Items.FindByValue( dr["WindStrengthID"].ToString())); } else { this.edSubject.Text = string.Empty; this.edMessage.Text = string.Empty; this.ddlWeatherCondition.SelectedIndex = 0; this.ddlWindDirection.SelectedIndex = 0; this.ddlWindStrength.SelectedIndex = 0; } } finally { dr.Close(); } } private DataSet SelectBlogEntryDS(int BlogEntryID) { string spName = "spSelectBlogEntry"; SqlConnection cn = new SqlConnection( ConfigurationManager.ConnectionStrings[ "BikeBlogConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand(spName,cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception Ex) { // Handle the error... } return ds; } private SqlDataReader SelectBlogEntryDR(int BlogEntryID) { string spName = "spSelectBlogEntry"; SqlDataReader dr = null; SqlConnection cn = new SqlConnection( ConfigurationManager.ConnectionStrings[ "BikeBlogConnectionString"].ConnectionString); cn.Open(); try { SqlCommand cmd = new SqlCommand(spName, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { // Close the connection in case of error... cn.Close(); } // If no error, return the DataReader, and the connection // will be closed when the DataReader is closed. return dr; } private bool SaveBlogEntry(string Subject, string Message, int WeatherConditionID, int WindDirectionID, int WindStrengthID, int BlogEntryID) { bool ret = false; string spName = "spSaveBlogEntry"; SqlConnection cn = new SqlConnection( ConfigurationManager.ConnectionStrings[ "BikeBlogConnectionString"].ConnectionString); cn.Open(); try { SqlCommand cmd = new SqlCommand(spName, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); cmd.Parameters.AddWithValue("@Subject", Subject); cmd.Parameters.AddWithValue("@Message", Message); cmd.Parameters.AddWithValue("@WeatherConditionID", WeatherConditionID); cmd.Parameters.AddWithValue("@WindDirectionID", WindDirectionID); cmd.Parameters.AddWithValue("@WindStrengthID", WindStrengthID); cmd.ExecuteNonQuery(); ret=true; } finally { // Close the connection in case of error... cn.Close(); } return ret; } protected void cmdCancel_Click(object sender, EventArgs e) { this.MultiView1.SetActiveView(this.Grid); this.EditBlogEntryID = 0; } protected void cmdSave_Click(object sender, EventArgs e) { this.SaveBlogEntry(this.edSubject.Text, this.edMessage.Text, int.Parse(this.ddlWeatherCondition.SelectedValue), int.Parse(this.ddlWindDirection.SelectedValue), int.Parse(this.ddlWindStrength.SelectedValue), this.EditBlogEntryID); this.EditBlogEntryID = 0; this.GridView1.DataBind(); this.MultiView1.SetActiveView(this.Grid); } protected void btnNewEntry_Click(object sender, EventArgs e) { this.MultiView1.SetActiveView(this.DetailsEdit); this.EditBlogEntryID = 0; this.BindDetailsEdit(); } }

Error Handling and Connections

When working with databases, it is important to remember that connections should be closed and disposed of as soon as possible. The following code ensures that the connection, if opened, will be closed as soon as the code in the try block is executed.

cn.Open(); try { // Use the connection... } finally { // Close the connection in case of error... cn.Close(); }

If the connection is not closed, it will eventually be closed when the connection object is collected by garbage collection. However, because of the way that .NET garbage collection works, the connection could remain open for a long time.

What this code does not address is what will happen if the call to the Open method of the cn variable (an SqlConnection object) fails. There are several schools of thought on how to handle an exception that takes place before the connection is actually opened. When code is split between the user interface and the database access code (as it often will be in production code), determining how to handle such an exception can be problematic. A failure of a database connection is often a catastrophic error that cannot be overcome easily. In much of my production code, I allow such an exception to bubble up to the user interface code to either have the user interface code handle it, or just as likely have a global exception handler handle the problem. Alternately, the call to the Open method could be moved inside the try block, and a catch block could be added.

The first thing you should notice in this code is the integer property named EditBlogEntryID. This property allows the page to remember the currently edited BlogEntryID. A property is a special type of member of a class. The underlying value of the property can be a private variable of the class, or it can be calculated or retrieved from some sort of persistent storage. In this example, the EditBlogEntryID property is persisted in view state. The get section of the property contains an interesting bit of code that is not entirely obvious.

get { return ViewState["EditBlogEntryID"] == null ? 0 : (int)ViewState["EditBlogEntryID"]; }

C# (like C++ before it) has what is called a tertiary operator. This operator has three parts:

The set section of the property declaration simply stores the value, which is a keyword that represents the value being set, to the ViewState collection.

Clicking the Select link opens the details page. When the Select link is clicked, the SelectedIndexChanging event is fired. The code for this method controls the action of the page.

protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs e) { this.MultiView1.SetActiveView(this.DetailsEdit); this.EditBlogEntryID = int.Parse( GridView1.DataKeys[e.NewSelectedIndex].Value.ToString()); this.BindDetailsEdit(); }

The first line of the event handler sets the active view of the MultiView control. The MultiView control allows only one view to be active at a time, so setting a view as the active view hides all other views (in this case, the grid view is hidden). Next, the EditBlogEntryID property is set. EditBlogEntryID is a property that is used to hold the BlogEntryID that is currently being edited. The GridViewSelectEventArgs argument (e, in this example) has a property named NewSelectedIndex. The code next uses the NewSelectedIndex property as an index into the collection of DataKeys, which is a collection of the values of each row specified by the DataKeyNames property of the GridView control. The DataKeyNames property in the GridView1 object is set to BlogEntryID, so the following code returns the BlogEntryID column of the selected row.

GridView1.DataKeys[e.NewSelectedIndex].Value.ToString()

This value is a string, so it is parsed to get an integer.

The next line, calling the BindDetailsEdit method, is what causes the edit controls in the details section of the page to be populated. The BindDetailsEdit method first declares several variables. The following bit of code requires some explanation.

ds = this.SelectBlogEntryDS(this.EditBlogEntryID); dr = (IDataReader)ds.CreateDataReader(); // dr = this.SelectBlogEntryDR(this.EditBlogEntryID);

Two methods retrieve a specific blog entry. The one used here is the SelectBlogEntryDS method. After the DataSet object is returned in the ds variable, I use a new method of the DataSet object, CreateDataReader, to return a DataReader object based on the data in the DataSet. I use the CreateDataReader method here just as a convenience for this example. By using the CreateReader method, I can show creation of both a DataSet and a DataReader, while the code that fills in the text boxes can always use a DataReader. Alternately, if the top two lines are commented out and the third line is uncommented, the DataReader is returned directly from the other method for selecting a blog entry, named SelectBlogEntryDR. In both cases, the routines to select a blog entry are passed the EditBlogEntryID property.

The two selection methods are similar, but both deserve discussion because they could both be repeated often in your own code.

Note 

The two selection methods are members of the page class here. In a real application, you might place these and other data access functions is another class, so that they could be accessed from multiple pages. This degree of isolation allows more code reuse, but it is often awkward for examples in a book.

The SelectBlogEntryDS method returns a DataSet.

private DataSet SelectBlogEntryDS(int BlogEntryID) { string spName = "spSelectBlogEntry"; SqlConnection cn = new SqlConnection( ConfigurationManager.ConnectionStrings[ "BikeBlogConnectionString"].ConnectionString); SqlCommand cmd = new SqlCommand(spName,cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception Ex) { // Handle the error... } return ds; }

After declaring and setting the stored procedure name, the code creates a connection. To create a connection, you must have a connection string. While creating the first of the SqlDataSource controls in Visual Studio, I created a connection string that was saved in the Web.config file. In the Web.config file, the following section is devoted to connection strings.

<connectionStrings> <add name="BikeBlogConnectionString" connectionString= "Data Source=DELL670;Initial Catalog=BikeBlog;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>

The code uses the connectionStrings collection of the ConfigurationManager class, indexes it with the name of the connection ("BikeBlogConnectionString" in this case), and uses the ConnectionString member of that object, which is a string.

After the connection is created, the code creates a command object, of type SqlCommand. The CommandType property is set to CommandType.StoredProcedure. The three possible values for CommandType are:

CommandType.Text is the default value, which is used when you are specifying a raw SQL statement, such as SELECT, INSERT, UPDATE, or DELETE. TableDirect is used when the command text is the name of a table. CommandType.TableDirect is used only by the OleDb provider. StoredProcedure is used when the name of a stored procedure is specified by the command text. (Note that rather than setting the CommandType property to CommandType.StoredProcedure, I could have left it set to CommandType.Text and passed in "EXEC spSelectBlogEntry @BlogEntryID" as the command text. I always use StoredProcedure.) The BlogEntryID parameter is then added to the command's Parameters collection, using the AddWithValue method.

Next, I created a new SqlDataAdapter object and a new, empty DataSet. Then I called the Fill method of the SqlDataAdapter object, which fills in the DataSet passed as a parameter. Finally, the DataSet is returned. One interesting aspect of this code is that the SqlConnection object is never opened. When the connection associated with the DataAdapter object is closed, it is opened only when required and safely closed when the DataAdapter object is done using it. This is a convenient feature, because it eliminates the need for the developer to manually manage the state of the connection.

The SelectBlogEntryDR method is similar in many respects to the SelectBlogEntryDS method, but it's a little more complex because the state of the connection does need to be managed. The connection is created in this method in the same way that it is created in the DataSet returning method. After the connection is created, however, it is opened by calling the Open method of the connection. Immediately after the Open call, the code has a try/catch block, as shown here.

cn.Open(); try { SqlCommand cmd = new SqlCommand(spName, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); dr = cmd.ExecuteReader(CommandBehavior.closeConnection); } catch { // Close the connection in case of error... cn.Close(); } return dr;

In a try/catch block, the code in the try section is executed, and if an exception occurs, the catch section is executed. In this example, I called the ExecuteReader method of the command object inside the try block. The ExecuteReader method can accept a single parameter, and I passed in CommandBehavior.CloseConnection. This parameter passed to the ExecuteReader method is required when you have a method that will pass a DataReader object back to the caller. Without this parameter, when you passed the DataReader back to the caller, the underlying connection would remain open until garbage collection. When this parameter is used, the underlying connection is closed when the DataReader is closed. In the preceding code, the catch block is used so that the connection is closed if there is a problem with executing the command. Finally, the DataReader is returned.

Now that I have described the two possible methods to retrieve the data from the database, I will continue with the balance of the explanation of the BindDetailsEdit method seen in Listing 5-4. After creating the DataReader (either directly or by creating a DataSet and converting it to a DataReader), the code enters a try block. Note that rather than using a catch block at the end, I used a finally block, so that the code inside the finally block will always be executed. Inside the finally block, the DataReader is closed, which is critical, because when calling SelectBlogEntryDR method, the underlying connection will be closed only when the DataReader is closed.

In the try/finally block in the BindDetailsEdit method, the first step is to call the Read method on the DataReader. This is required, because initially, the DataReader is logically pointing to just before the first row returned. If Read is not called, the code will cause an exception indicating an attempt to read data when none is present. The Read method returns a Boolean value; if (dr.Read( )) means that the code under the if clause will be executed only if there is at least one row returned. In this example, we know that only a single row will be returned (because the stored procedure retrieves a row based on a primary key, BlogEntryID). In other cases, in which multiple rows will be returned, using while rather than if will allow the code to execute for each row returned.

If the Read method returns true, the individual edit controls will be populated. The DataReader is indexed by using the field name. The ordinal of the column can also be used, but in practice this can be awkward to do, because you also need to know the order of rows returned by the command. Just as important, if the order of the returned columns change, you will need to ensure that the ordinal you pass as a parameter is updated as well. In this example, I used a DataReader, even though a DataSet is the source of the DataReader, simply to show creation of both a DataReader and a DataSet. Accessing data in a DataSet is similar to accessing data in a DataReader. The code to read the data from a DataReader is shown here.

if (dr.Read()) { this.edSubject.Text = dr["Subject"].ToString(); // ... and so on ...

If the DataSet were used directly, the code would appear as follows.

if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count>0)) { this.edSubject.Text = ds.Tables[0].Rows[0]["Subject"].ToString(); // ... and so on ...

Warning 

In both of the preceding cases in which the Subject is retrieved, if Subject could be null, the value from the column should be tested to determine whether it is null, using DbNull.Value as shown here.

if ( dr["Subject"]!=DbNull.Value) { this.edSubject.Text=dr["Subject"].ToString(); }

The drop-down lists are set to values reflecting the data returned as well. The SelectedIndex property is set to the index of the item found by using the FindByValue method, passing in the data from the DataReader. The code to set each DropDownList control looks a little complicated, but it does the job of properly setting the values in the DropDownList control.

If the DataReader does not contain any rows, the edit controls are all set to default values in the else statement associated with the if (dr.Read( )) statement.

The user indicates that he or she wants to save the modified entry by clicking the Save button. The event handler is shown here.

protected void cmdSave_Click(object sender, EventArgs e) { this.SaveBlogEntry(this.edSubject.Text, this.edMessage.Text, int.Parse(this.ddlWeatherCondition.SelectedValue), int.Parse(this.ddlWindDirection.SelectedValue), int.Parse(this.ddlWindStrength.SelectedValue), this.EditBlogEntryID); this.EditBlogEntryID = 0; this.GridView1.DataBind(); this.MultiView1.SetActiveView(this.Grid); }

First, the data is saved. The SaveBlogEntry method is called, passing in the entered values from the edit controls. Next, the EditBlogEntryID property is set to 0, meaning that there is no currently edited row. I called the DataBind method on the GridView1 object to ensure that the changes just made are reflected in the grid view when it is made visible in the final line of the event handler.

The SaveBlogEntry method is reasonably straightforward.

private bool SaveBlogEntry(string Subject, string Message, int WeatherConditionID, int WindDirectionID, int WindStrengthID, int BlogEntryID) { bool ret = false; string spName = "spSaveBlogEntry"; SqlConnection cn = new SqlConnection( ConfigurationManager.ConnectionStrings[ "BikeBlogConnectionString"].ConnectionString); cn.Open(); try { SqlCommand cmd = new SqlCommand(spName, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@BlogEntryID", BlogEntryID); cmd.Parameters.AddWithValue("@Subject", Subject); cmd.Parameters.AddWithValue("@Message", Message); cmd.Parameters.AddWithValue("@WeatherConditionID", WeatherConditionID); cmd.Parameters.AddWithValue("@WindDirectionID", WindDirectionID); cmd.Parameters.AddWithValue("@WindStrengthID", WindStrengthID); cmd.ExecuteNonQuery(); ret = true; } finally { // Close the connection in case of error... cn.Close(); } return ret; }

The connection is created as it was in the selection methods. The connection is opened, and then the code enters a try block to ensure that the connection is closed, because the call to close is inside the finally block. After creating the command and adding parameters, I called the ExecuteNonQuery method of the command object. The ExecuteNonQuery method is used to execute SQL statements that do not return any result sets.

Note 

There is one other method on the command object to execute an SQL command. The ExecuteScalar method returns the first column of the first row of the SQL command. Generally, the ExecuteScalar method is used when you know that the command will return only a single value.

Two additional event handlers allow the user to add a totally new entry, as well as cancel changes while editing details.

protected void btnNewEntry_Click(object sender, EventArgs e) { this.MultiView1.SetActiveView(this.DetailsEdit); this.EditBlogEntryID = 0; this.BindDetailsEdit(); } protected void cmdCancel_Click(object sender, EventArgs e) { this.MultiView1.SetActiveView(this.Grid); this.EditBlogEntryID = 0; }

The event handler for the new entry button sets DetailsEdit as the active view, sets the EditBlogEntryID property to 0, and then calls the BindDetailsEdit method. Because the EditBlogEntryID property is 0 and BlogEntryID cannot be 0, this ensures that no row will be returned and the edit controls will be initialized.

The event handler for the cancel button on the DetailsEdit view sets the grid view as the active view, and then sets the EditBlogEntryID to 0.

 
Related

Категории