Office Test - Tutorial 2

We are about to start the 2nd page of the OfficeTest General Tests tutorial. If you haven't review the first page of the tutorial you can find it here JobPosition: Simple web page using SqlDataSource a DetailsView (inserting) and a GridView (Editing, Deleting). Information covered in previous tutorial pages will not be covered in later pages to keep focus on new issues.

2. Keeping the DetailsView synchronized with the GridView.

    The next web page we'll look at is the - BillingType: Simple page. This page was designed visually and uses 2 SqlDataSources, a GridView and a DetailsView. The GridView handles display only for records from the JobPosition table. The DetailsView handles all editing (Insert, Update, Delete) for the JobPosition table. The DetailsView shows only the current record selected in the GridView. Each view control requires its own SqlDataSource.

    BillingType: Simple

    Lets breakdown the important objects and property settings:

    1. SqlDataSource: dsAllBillingType

      The following is a list of the important properties.

      • SelectCommand='SELECT "BillingTypeID", "BillingTitle", "BillingDesc" FROM "BillingType"'

      There is nothing new here compared to the previous tutorial. It is important to note that this SqlDataSource only needs to supply a SelectCommand - not an Update, Delete or Insert. Also no bindings to events are necessary. This SqlDataSource just provides the full contents of the BillingType table to the GridView for display.

    2. SqlDataSource: dsCurrentBillingType

      The following is a list of the important properties.

      • DeleteCommand='DELETE FROM "BillingType" WHERE "BillingTypeID" = @BillingTypeID'
      • UpdateCommand='UPDATE "BillingType" SET "BillingTitle" = @BillingTitle, "BillingDesc" = @BillingDesc WHERE "BillingTypeID" = @BillingTypeID'
      • InsertCommand='INSERT INTO "BillingType"("BillingTypeID","BillingTitle","BillingDesc") VALUES(@BillingTypeID,@BillingTitle,@BillingDesc)'
      • SelectCommand='SELECT "BillingTypeID", "BillingTitle", "BillingDesc" FROM "BillingType" WHERE ("BillingTypeID" = ?)'
      • SelectParameters
        • ControlParameter: Name="BillingTypeID"
        • ControlID="gvBillingType"
        • PropertyName="SelectedValue"
        • Type="Int32"
      • OnDeleted="dsCurrentBillingType_Deleted"
      • OnInserted="dsCurrentBillingType_Inserted"
      • OnUpdated="dsCurrentBillingType_Updated"
      • OnDeleting="dsCurrentBillingType_Deleting"
      • OnInserting="dsCurrentBillingType_Inserting"
      • OnUpdating="dsCurrentBillingType_Updating"

      This SqlDataSource will be doing most of the work so we need to define the Delete, Insert and Update commands.

      In addition we also have to define the SelectCommand. In this case we don't want the entire table just the record we have focused in the GridView (see below for more information). I used the visual SQL designer to create the SelectCommand so the WHERE syntax might look odd - ("BillingTypeID" = ?). Don't let this syntax confuse you. The ? denotes the next parameter binding. This is common for database drivers that don't support named binding. In this case parameters must be bound in the correct order. Since we only have 1 parameter here it really makes no difference (I could have used @BillingTypeID instead of ?). What is important here is the SelectParameters bindings. We have 1 ControlParameter BillingTypeID which will bind to the GridView through gvBillingType.SelectedValue. This means that only the record where BillingTypeID = the BillingTypeID from the GridView's selected record will be retrieved. This is what keeps the DetailsView in sync with the GridView.

      Notice that OnDeleted, OnInserted and OnUpdated are defined as they were in the previous tutorial. This is for the same purpose and the same code snippet exists in the BillingType.aspx.cs file to handle the Firebird parameter binding quirk as before. Whats new here is the addition of OnDeleting, OnInserting and OnUpdating. The following code has been added for these events:

      protected void dsCurrentBillingType_Inserted(object sender, SqlDataSourceStatusEventArgs e) {
        gvBillingType.DataBind();
      }
      protected void dsCurrentBillingType_Updated(object sender, SqlDataSourceStatusEventArgs e) {
        gvBillingType.DataBind();
      }
      protected void dsCurrentBillingType_Deleted(object sender, SqlDataSourceStatusEventArgs e) {
        gvBillingType.DataBind();
        gvBillingType.SelectedIndex = 0;
      }
               

      gvBillingType.DataBind();: This is called whenever the DetailsView finishes inserting, updating or deleting according to our event handling. It tells the GridView to rebind (update its data) to make sure the changes made in the DetailsView are reflected in the GridView.

      gvBillingType.SelectedIndex = 0;: This is only called after a delete. Since the row we had selected was deleted this will reset the first row as the selected row. If you omit this statement then no row is selected in the GridView and no record is retrieved for the DetailsView and the DetailsView will not render or render a NULL record, depending on your settings.

    3. GridView: gvBillingType

      The following is a list of the important properties.

      • DataSourceID="dsAllBillingType"
      • DataKeyNames="BillingTypeID"

      We need to make sure to assign the dsAllBillingType SqlDataSource to the GridView. It is necessary to provide the Primary Key for the BillingType table in the DataKeyNames. The GridView needs this information to update the value of the SelectedValue that the DetailsView uses to stay synchronized.

    4. DetailsView: dvBillingType

      The following is a list of the important properties.

      • DataSourceID="dsCurrentBillingType"
      • DataKeyNames="BillingTypeID"

      Make sure to assign the dsCurrentBillingType SqlDataSource to this control. Again we will need to define the DataKeyNames since we are performing updates and deletes. The rest of this control works as was discussed in the previous tutorial.

    Why two SqlDataSources
    I think a big question most people have is "Why do we need 2 SqlDataSources". Depending on their coding background some developers may be use to the idea that the focused row in a dataset control changes the datasets focus and therefore all controls using it are affected. SqlDataSource doesn't work this way, it instead provides more of a way to get data rather than holding a view of it. This means that if the GridView and DetailsView both used the same SqlDataSource they would still retrieve 2 full and separate copies of the data. The focus of one control doesn't affect the other control. If caching is used on the SqlDataSource it will store the query results in memory and use the same results for both controls but each control will still make its own local copy of the data separate from the cache.

See next Tutorial...Employee: Simple web page using a GridView and DetailsView to demonstrate TemplatedFields, FieldValidators and uses DropDownLists in a templated field to edit lookup values.