Office Test - Tutorial 1

This is the start of the OfficeTest Firebird ASP.NET 2.0 tutorial. Although most of the topics covered in this tutorial are fairly straight forward I hope it will help some of you who are new to .net and Firebird get past some of the quirks.

Something that should be kept in mind about this tutorial is the dialect of the test database. I created the database in Firebird 1.5.3 using dialect 3 and all metadata was created using mixed case. This means that I purposely complicated all the syntax for SQL calls both within the database (stored procedures, triggers) and for calls from the code. If you create the database as dialect 1 or you force the metadata to UPPERCASE then all the places I added extra \" (quotes) around portions of SQL are no longer necessary.

1. Our first basic visually designed web page:
We'll start with the simplest web page in the example code - Job Position: Simple. This page was designed visually and uses a basic SqlDataSource, GridView and DetailsView. The GridView handles display, updating and deleting records from the JobPosition table. The DetailView handles only inserts for the JobPosition table. Both controls use the same SqlDataSource.

Job Position: Simple

Lets breakdown the objects and property settings:

1. SqlDataSource: dsJobPosition

The following is a list of the properties that were changed from their default values.
• ConnectionString="<%$ ConnectionStrings:OfficeTestFirebird %>"
• ProviderName="<%$ ConnectionStrings:OfficeTestFirebird.ProviderName %>"
• SelectCommand='SELECT "JobPositionID", "JobTitle", "JobDesc" FROM "JobPosition"'
• DeleteCommand='DELETE FROM "JobPosition" WHERE "JobPositionID" = @JobPositionID'
  • DeleteParameters
    Parameter Name="JobPositionID"
• InsertCommand='INSERT INTO "JobPosition"("JobPositionID", "JobTitle", "JobDesc") VALUES (@JobPositionID, @JobTitle, @JobDesc)'
  • InsertParameters
    Parameter Name="JobPositionID"
    Parameter Name="JobTitle"
    Parameter Name="JobDesc"
• UpdateCommand='UPDATE "JobPosition" SET "JobTitle" = @JobTitle, "JobDesc" = @JobDesc WHERE ("JobPositionID" = @JobPositionID)'
  • UpdateParameters
    Parameter Name="JobPositionID"
• OnDeleting="dsJobPosition_Deleting"
• OnInserting="dsJobPosition_Inserting"
• OnUpdating="dsJobPosition_Updating"

ConnectionString/ProviderName
Lets start by taking a look at the ConnectionString. There are multiple ways to set your connection string. Start by right clicking on the SqlDataSource and select Configure Data Source... from the drop down menu. If Firebird is properly integrated into Visual Studio then you should get the following:



From here you just select a connection from the list. The list will contain all connection strings from your Web.Config as well as all connections created in your server explorer. You could also create a new connection as this time. I recommend creating a connection in your Web.Config and using that since it centralizes your connection information in a standard place and in the future changes to that configuration are automatically reflected in the code. Once you select your connection this screen lets you continue on to defining your SQL.

Another way to selected your connection is from the properties panel. Just click in the property field next to ConnectionString and select it from the drop down list. Selecting your connection this way or using the preceding method will automatically set the ProviderName, assuming it was also set in the Web.Config.



If you never managed to get Firebird fully integrated into Visual Studio don't worry, its still possible to fully develop an application anyway. To select your connection information from the Web.Config go to the property panel and select the (Expression) property. Click the button on the right to open the following dialog:



This dialog allows a developer to pick any configuration information and assign it directly to a controls property. In our case we want to select ConnectionString from the list on the left. Then in the Expression type: drop down list also select ConnectionStrings. Now under Expression properties: you should be able to choose you connection alias from the Web.Config from a drop down list next to the ConnectionName property. Selecting your connection gives the same result as the previous methods except that the ProviderName is not automatically set. You will need to manually select the FirebirdSql.Data.FirebirdClient provider.

SelectCommand, DeleteCommand, InsertCommand, UpdateCommand
Defining the SQL is straight forward, just remember to put "" (quotes) around table and column names if your using dialect 3 and lower or mixed case as I am in this example.

The order of Bind parameters does not matter for Firebird. It is important to match BindParameter names to the column names (although case and quotes are not necessary) unless you want to manually configure the matching of parameter values. The reason bind parameters need to match the columns is because the fields defined in your controls (GridView, DetailView) use the column names on the fields and this gives an automatic one to one mapping.

Something you may notice looking at my code is that several bind columns are missing for the UpdateCommand. Well as it turns out, none of the bind parameters in this example are even necessary. If all your parameters are exact matches for the column names it makes no difference if you add them or some of them or not. The missing ones are bound automatically by name anyway. NOTE: They have to be in the SQL statement just not necessarily defined on the SqlDataSource - they are automatically added if they are missing.

OnDeleting, OnInserting, OnUpdating
The last properties and the first real Firebird "QUIRK" we need to look at is the event bindings. If this code was using SqlServer these events would not need to be bound at all and no code would be necessary in the JobPositionSimple.aspx.cs class file. Unfortunately the current version of the Firebird client dll doesn't use the parameter name bindings from the SqlDataSource the same way SqlServer does.

If you bind the parameters without the @ in front of them (this is the way they are created if you leave them out altogether) then when the binding occurs it thinks the parameters are missing. If you bind them with @ added it adds the default parameters in and then binds to the wrong ones. This means your real parameters all end up with NULL values. The only solution I know of to solve this problem is to do the following:


          foreach (System.Data.Common.DbParameter parm in e.Command.Parameters)
            parm.ParameterName = "@" + parm.ParameterName;
       

Add the above code to the _Deleting, _Inserting and _Updating events. This will added @ infront of the parameters names after the checks for missing parameters is done. This will allow the automatic bindings to work correctly.

NOTE: I haven't checked the new beta release to see if this problem is solved yet.

2. DetailsView: dvJobPosition

The following is a list of the properties that were changed from their default values and are important.
• DataSourceID="dsJobPosition": Obviously you have to select the SqlDataSource for this control.
• AutoGenerateRows="False": Set AutoGenerateRows to false unless you want all the columns added automatically. Usually at least something, even just the header, will end up being modified so I can't see anyone using AutoGenerateRows regularly.
• DataKeyNames="JobPositionID": Alot of default functionality requires the DataKeyNames to be set to the correct primary keys. In this example its not necessary on the DetailsView but the GridView needs it for updating and deleting. In our case JobPositionID is readonly, but if it wasn't then the DataKeyNames would not be necessary during updating. However it would still be required for deleting.
• DefaultMode="Insert": This forces the DetailView to always be in insert mode and ready for input for a new JobPosition.

3. GridView: gvJobPosition

The following is a list of the properties that were changed from their default values and are important.
• DataSourceID="dsJobPosition": see same field on DetailsView
• AutoGenerateColumns="False": see AutoGenerateRows on DetailsView
• DataKeyNames="JobPositionID": see same field on DetailsView

As you can see most of this is really quite simple. If the quirk in Firebird with the @ symbol and the default parameter definitions is fixed no code would be necessary.

See next Tutorial...BillingType: Simple web page using 2 SqlDataSources a GridView (display only) and a synchronized DetailsView (all editing).

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.

Office Test - Tutorial 3

We are about to start the 3rd page of the OfficeTest General Tests tutorial. If you haven't reviewed the second page of the tutorial you can find it here BillingType: Simple. Information covered in previous tutorial pages will not be covered in later pages to keep focus on new issues.

3. Field Validators, Lookup Tables and Templated Fields.

The next web page we'll look at is the Employee: Simple page. This page was designed visually and uses 3 SqlDataSources, a GridView and a DetailsView. The GridView handles display for records from the Employee table. The DetailsView handles all editing (Insert, Update, Delete) for the Employee table.

The overall design of this page is the same as the BillingType: Simple page. In fact the contents of the EmployeeSimple.aspx.cs file are exactly the same as the BillingTypeSimple.aspx.cs file. The Employee table links to the JobPosition table which means we have a new display and editing issue to handle. I also added some basic FieldValidator controls to make sure at the least the Not Null fields are supplied when editing. In order to implement these features we need to look at the concept of field templates.

Employee: Simple


Lets breakdown the important objects and property settings:

1. SqlDataSource: dsAllEmployees

The following is a list of the important properties.
• SelectCommand='SELECT "EmployeeID", "FirstName", "LastName", "HireDT", E."JobPositionID", "JobTitle" FROM "Employee" E INNER JOIN "JobPosition" J on J."JobPositionID" = E."JobPositionID"'

The configuration of the primary SqlDataSource is exactly as in the previous tutorial. The only property we need to look at is the SelectCommand. In this example I decided to perform an INNER JOIN to get the JobPosition information for each Employee. Specifically I wanted the JobTitle for display purposes. While it isn't absolutely necessary to do it this way, it does make it easier.

2. SqlDataSource: dsCurrentEmployee

The following is a list of the important properties.
• SelectCommand='SELECT "EmployeeID", "FirstName", "LastName", "HireDT", E."JobPositionID", "JobTitle" FROM "Employee" E INNER JOIN "JobPosition" J on J."JobPositionID" = E."JobPositionID" WHERE ("EmployeeID" = ?)'

Again when getting just the current employee I still want to lookup the JobTitle in the select so its available to the DetailsView during display, without requiring any special code or additional lookup.

3. SqlDataSource: dsJobPosition

The following is a list of the important properties.
• SelectCommand='SELECT "JobPositionID", "JobTitle" FROM "JobPosition"'

The EmployeeSimple page also has a third SqlDataSource, which just supplies a full list of all the JobPosition records.

4. GridView: gvEmployee

The only import change here is the use of a field that is bound to the JobTitle.

<Columns>
  <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
</Columns>
         

There is nothing really special about this field which is why we selected the JobTitle back in an inner join in the first place. This way just a normal BoundField could be used to display the JobPosition.JobTitle.

5. DetailsView: dvEmployees

The difference with the DetailsView dvEmployees lies in the Templated fields. We'll take a look at the templating for a few of the fields in detail. Before we do that lets take a look at creating templated fields. You can either create them by simply editing the HTML or they can be created visually.

To change a normal field into a templated field first right-click on the DetailsView and select "Show Smart Tag" from the popup menu and then select "Edit Fields". This will open the Fields dialog.

As you can see I have HireDT selected since its the only fields that is not already a template. On the bottom right of the dialog you will find a link called "Convert this field into a TemplateField". Click that link to turn HireDT or any other field into a templated field.

Again right-click on the DetailsView and select "Show Smart Tag".


From this dialog select "Edit Templates" from the bottom. This will open the template editing mode dialog.


This popup allows you to select a single template for any field or you can choose the field itself. If you choose the field then you can edit all templates for that field at once. Select the EmployeeID field to get the following:


From here you can drag and drop and select and modify controls just as you would anywhere else on the web page. If you right-click on the label in the ItemTemplate and from the popup menu select "Edit DataBindings" you will get this dialog:


The DataBindings dialog will allow you to choose how to bind the field. If you converted an existing field the bindings are most likely already done. Otherwise you need to manually select or enter the bindings. Something you may noticed, if you are editing a template on the DetailsView, is that the top right fields under "Binding for Text" are disabled. This occurs because the select SQL for the DetailsView SqlDataSource has a parameter which is dependent on the GridView's SqlDataSource. Visual Studio cannot resolve this link without the code running and thus it can't lookup field information. This also blocks lookups of fields and auto field/code generation in other areas of the interface as well. The code will work fine when it runs but you have to type the field names or binding information manually.

Lets take a look at the fields themselves now.
EmployeeID

The primary key field contains the standard 3 templates - ItemTemplate, EditItemTemplate and InsertItemTemplate.

ItemTemplate


<asp:Label ID="Label2" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
         

The ItemTemplate contains only a label since this template is used for display purposes not editing. The syntax <%# Eval("EmployeeID") %> is used to retrieve the value from the EmployeeID field from the datasource and place it in the label. Eval is a one-way binding call which can only be used to retrieve values. <%# Bind("EmployeeID") %> could also be used here which provides two-way binding but since this is a label editing isn't possible anyway.

EditItemTemplate


<asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
         

Since this is the primary key field it doesn't require editing and therefore the EditItemTemplate is exactly the same as the ItemTemplate.

InsertItemTemplate


<asp:TextBox ID="txtInsertEmployee" runat="server" Text='<%# Bind("EmployeeID") %>'></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtInsertEmployee" ErrorMessage="EmployeeID is required."></asp:RequiredFieldValidator>
         

The InsertItemTemplate is a little more complex for 2 reasons. First we need to be able to edit the EmployeeID and also because I added a validator.

Notice that we replaced the label control with a TextBox and in this case we must use the <%# Bind("EmployeeID") %> (two-way) binding syntax. This way the value entered in the TextBox is automatically sent to the EmployeeID field to be used as a parameter in the insert SQL.

A RequiredFieldValidator was also added to ensure the EmployeeID is not left blank when the page is posted. The validator is not necessary to make this work of course. It was just added to avoid code level validation or SQL errors. Templates provide a handy way of adding validators into your DetailsView or GridView so the error message displays beside or below the associated field.

FirstName

ItemTemplate

<asp:Label ID="Label3" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
         

This template works the same as it does for the EmployeeID and either Eval or Bind can be used.

EditItemTemplate


<asp:TextBox ID="txtEditFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtEditFirstName" ErrorMessage="FirstName is required."></asp:RequiredFieldValidator>
         

This template works like the InsertItemTemplate does for the EmployeeID because we can edit the FirstName for updating.

InsertItemTemplate


<asp:TextBox ID="txtInsertFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtInsertFirstName" ErrorMessage="FirstName is required."></asp:RequiredFieldValidator>
         

This template works the same as it does for the EmployeeID.

JobPosition

ItemTemplate

<asp:Label ID="Label1" runat="server" Text='<%# Bind("JobTitle") %>'></asp:Label>
         

This template works the same as it does for the EmployeeID and FirstName.

EditItemTemplate


<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="dsJobPosition" DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
</asp:DropDownList>
         

Now we have a new case. JobPositionID is a lookup from the JobPosition table and we want the user to be able to select a value from a list. We already have a SqlDataSource with all the JobPosition records so all we need is a DropDownList inside this template. Notice the DataSourceID is assigned to "dsJobPosition" and the binding is this case goes on the SelectedValue.

InsertItemTemplate


<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="dsJobPosition" DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
</asp:DropDownList>
         

The InsertItemTemplate template works exactly the same as the EditItemTemplate. Its actually too bad you can't just create a generic template and assign modes to it.

HireDT

<asp:BoundField DataField="HireDT" HeaderText="HireDT" SortExpression="HireDT" ApplyFormatInEditMode="True" DataFormatString="{0:yyyy-MM-dd}" HtmlEncode="False" />
         

Finally we'll take a look at the HireDT field which is not actually a template. The reason we're looking at HireDT is to show the format settings. Notice that DataFormatString is set to "{0:yyyy-MM-dd}" which formats the date as 2006-01-15. In order for this format style to work you need to remember to set HtmlEncode to "False".

This last tutorial isn't really so much a tutorial on Firebird. All the concepts covered here really apply to any database. However since I use the features described in this tutorial in other pages I wanted to make sure they were covered first.

See next Tutorial...BillingType: Editable Grid/SpreadSheet web page using a fully editable GridView more like a spreadsheet.

Office Test - Tutorial 4

The 4th page of the OfficeTest General Tests tutorial applies to more that just Firebird. In this tutorial we'll look at making a normal GridView work more like a spreadsheet or an editable grid. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example. Information covered in previous tutorial pages will not be covered however this topic differs considerably from the previous pages. The 3rd tutorial (Employee: Simple) covers templated fields and manual binding which will be used here as well.

4. Editable GridView - acts more like a spreadsheet.

This web page brings us back to working with the BillingType table. This time however we will not be using SqlDataSource for handling selects, inserts, updates or deletes. Also for the first time the GridView control will perform all actions (Display and ALL editing).

BillingType: EditGrid - run time view


Looking at this runtime image of the web page we can discuss how it should work:

There is 1 visable row in the grid for each row of data and a new row is added when the "Add New Row" button is pressed. Every field in the grid is editable at all times allowing you to quickly jump from field to field or row to row and just type any changes. There is a delete button for each row to make it more obvious and easier to delete a particular row. Changes made in the grid are not really made until the "Apply Changes" button is clicked. When changes are applied the deleted rows are actually deleted, edits are updated and new rows are inserted. In fact we could easily add a "Cancel Changes" button which would require almost no code.

The TAB key allows you to move quickly to the next edit field. Tabbing beyond the last edit field moves to the "Delete" button and then down to the next row. I didn't add any key event handling so you can't cursor up or down rows like in a real spreadsheet.

Below the grid is a label I use for showing a change log. The changes listed are the ones actually done when the "Apply Changes" button is pressed.

BillingType: EditGrid - design time view


The design time view looks almost exactly the same as the run time view. This is because the page is not using any nonvisual controls such as SqlDataSource. The web page itself is a very simple design. Essentially the web page has a GridView, 2 buttons and a label. The GridView has 5 templated columns containing 3 textboxes, a button and a label.

Markup Tags/Code

The tags related to the GridView gvBillingType and the templated fields is really what we want to concentrate on from the HTML first before we look at the real code.


<asp:GridView ID="gvBillingType" runat="server" AutoGenerateColumns="False" Caption="BillingType Editable Grid" OnRowCommand="gvBillingType_RowCommand" CellPadding="4" ForeColor="#333333" GridLines="None">
  <Columns>
    <asp:TemplateField HeaderText="ID">
      <ItemTemplate>
        <asp:TextBox ID="txtBillingTypeID" runat="server" Text='<%# Bind("BillingTypeID") %>' OnTextChanged="TextChanged" Width="47px"></asp:TextBox>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="BillingTitle">
      <ItemTemplate>
        <asp:TextBox ID="txtBillingTitle" runat="server" Text='<%# Bind("BillingTitle") %>' OnTextChanged="TextChanged" Width="180px"></asp:TextBox>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="BillingDesc">
      <ItemTemplate>
        <asp:TextBox ID="txtBillingDesc" runat="server" Text='<%# Bind("BillingDesc") %>' OnTextChanged="TextChanged" Width="250px"></asp:TextBox>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Operations">
      <ItemTemplate>
        <asp:Button ID="cmdDelete" runat="server" CommandArgument='<%# Eval("BillingTypeID") %>' CommandName="DeleteRow" Text="Delete" OnClick="cmdDelete_Click" />
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Status">
      <ItemTemplate>
        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Status") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
</asp:GridView>
   

gvBillingType primary properties:


<asp:GridView ID="gvBillingType" runat="server" AutoGenerateColumns="False" Caption="BillingType Editable Grid" OnRowCommand="gvBillingType_RowCommand" CellPadding="4" ForeColor="#333333" GridLines="None">
</asp:GridView>
   

Breaking out the tags just related to the GridView itself and not the templated fields the only property we really care about is OnRowCommand="gvBillingType_RowCommand". Although I commented out the code related to this event in the BillingTypeEditGrid.aspx.cs file it can be very usefull. It can provide a more powerfull way of handling row commands and CommandArguments. I will discuss this in more detail later in the coding section.

ID field template:


<asp:TemplateField HeaderText="ID">
  <ItemTemplate>
    <asp:TextBox ID="txtBillingTypeID" runat="server" Text='<%# Bind("BillingTypeID") %>' OnTextChanged="TextChanged" Width="47px"></asp:TextBox>
  </ItemTemplate>
</asp:TemplateField>
   

Right away you should notice we only have one template for the field - the ItemTemplate. This is because we are not going to be switch between edit modes. This grid is always in the default mode and we will use it for both display and editing. The default mode uses the ItemTemplate so that is the one we will work with. A single simple TextBox was added to the template with 3 important property settings.
• ID="txtBillingTypeID": This ID does not really need to be set to anything meaningfull normally, but in our case I parse the name to reduce code in the OnTextChanged event handler.
• Text='<%# Bind("BillingTypeID") %>': I use the two-way binding call in the Text property to allow us to get and set the internal BillingTypeID field value on the GridView.
• OnTextChanged="TextChanged": This event handler will be used to let us know the value in this field was modified. This event does not cause any postbacks so there is no hit to performance. In the code section further on I will discuss how this event is processed.

BillingTitle and BillingDesc work exactly the same so I won't go over them in detail. Just make sure to note that the OnTextChanged event handler is bound to the same TextChanged procedure for all 3 fields.

Operations field template:


<asp:TemplateField HeaderText="Operations">
  <ItemTemplate>
    <asp:Button ID="cmdDelete" runat="server" CommandArgument='<%# Eval("BillingTypeID") %>' CommandName="DeleteRow" Text="Delete" OnClick="cmdDelete_Click" />
  </ItemTemplate>
</asp:TemplateField>
   

The "Operations" field was added to hold the delete button. The important properties on the delete button are the CommandArgument, CommandName and OnClick.
• CommandName="DeleteRow": This value is meant to be used with gvBillingType_RowCommand event I mentioned above. The GridViewCommandEventArgs parameter contains a CommandName property which will be equal to this value ("DeleteRow"). This feature is very usefull when you have multiple commands all processed through the global RowCommand event handler on the grid. The code related to this is currently commented out so this feature is not necessary when your using the OnClick event handler.
• CommandArgument='<%# Eval("BillingTypeID") %>': This value is also meant to be used with gvBillingType_RowCommand event. The GridViewCommandEventArgs parameter contains a CommandArgument property which will be equal to this value (Eval("BillingTypeID") which will equal the BillingTypeID for the current row). This feature is very usefull for passing row specific information through the global RowCommand event handler on the grid. The code related to this is currently commented out so this feature is not necessary when your using the OnClick event handler.
• OnClick="cmdDelete_Click": If the previous 2 properties were actually being used it would not be necessary to add an OnClick event handler but I wanted to show another method of handling the deletes. See cmdDelete_Click function in the related code comments below.

Status field template:


<asp:TemplateField HeaderText="Status">
  <ItemTemplate>
    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Status") %>'></asp:Label>
  </ItemTemplate>
</asp:TemplateField>
   

The "Status" field was added to hold text information related to the state of a row. The text is updated from within the code which is why a label is used. The binding should have been a one-way binding to make it more efficient but the two-way binding will work just as well.

Page 2 of this tutorial (BillingTypeEditGrid.aspx.cs, C# Code)

Office Test - Tutorial 5

The 5th page of the OfficeTest General Tests tutorial covers stored procedures and briefly looks at using triggers. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example. The 3rd tutorial (Employee: Simple) is similar to this one except that stored procedures and triggers are not used. I won't be covering the basic setup in this tutorial since it's already covered in the earlier one.

NOTE: You may find the test application gives insert errors when using the trigger example. This will occur if you have performed inserts using the other web pages and provided your own keys. The gEmployeeID generator may be out of sync. Just hit the insert several times and ignore the error. The generator will eventually more forward past your other keys values. Otherwise go back and delete those additional rows.

5. Using stored procedures and triggers.

Again we will look at the Employee table. This time we are using a single SqlDataSource for retrieving the Employees data and one for JobPosition data. A GridView is used for display and deleting. A DetailsView is used for inserting. There is another separate SqlDataSource for the Stored Procedure DetailsView.

Employee: Stored Procedure/Trigger


The related triggers and stored procedures can be found in the test database or you can view them with IBDataWorks if you own it. I will show them here as well so its not necessary to go look them up.

Insert Employee Trigger


CREATE TRIGGER Employee_INSERT FOR "Employee" ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW."EmployeeID" is NULL) THEN
    NEW."EmployeeID" = GEN_ID("gEmployeeID", 1);
END
   

This trigger fires automatically on every insert into the Employee table. It checks to see if the primary key (EmployeeID) is NULL. If its NULL then it uses the generator (gEmployeeID) to generate the next primary key before the insert happens. If an EmployeeID is provided then it does nothing.

Insert Employee stored procedure


CREATE PROCEDURE insertEmployee (FirstName varchar(50), LastName varchar(50), HireDT date, JobPositionID integer)
RETURNS ( EmployeeID INTEGER )
AS
BEGIN
  EmployeeID = GEN_ID("gEmployeeID",1);

  INSERT INTO "Employee"("EmployeeID", "FirstName", "LastName", "HireDT", "JobPositionID")
    VALUES(:EmployeeID, :FirstName, :LastName, :HireDT, :JobPositionID);
END
   

This stored procedure accepts all Employee table fields except the EmployeeID as parameters. It generates the EmployeeID using the gEmployeeID generator and then does an insert into the Employee table. The EmployeeID is also a RETURN parameter so the code calling this stored procedure can get access to the primary key used for the insert.

The C# code: EmployeeTriggerStoredProc.aspx.cs
There are 4 functions in the C# code. The first 3 are the same standard ones used in the other pages we've looked at so far. The last function is not necessary but I added it to retrieve the values of the stored procedure parameters after an insert. The dsInsertWithStoredProc_Inserted just helps demonstrate that we can get the primary key value generated inside the stored procedure back after the insert.

The HTML code: EmployeeTriggerStoredProc.aspx
Let's concentrate on the HTML part of the code. I'll go over any properties for the objects that may not be obvious.

The dsAllEmployees SqlDataSource is straight forward as it works exactly as it would if no trigger was involved. This is because it doesn't need to know about the trigger at all. The trigger fires automatically during any insert.

The dsJobPosition SqlDataSource is also just a standard select of all the JobPosition records.

SqlDataSource: dsInsertWithStoredProc


<asp:SqlDataSource ID="dsInsertWithStoredProc" runat="server"
  InsertCommandType="StoredProcedure" InsertCommand='INSERTEMPLOYEE'>
  <InsertParameters>
    <asp:Parameter Name="FirstName" />
    <asp:Parameter Name="LastName" />
    <asp:Parameter Name="HireDT" />
    <asp:Parameter Name="JobPositionID" />
    <asp:Parameter Direction="ReturnValue" Name="EmployeeID" />
  </InsertParameters>
</asp:SqlDataSource>
   

I've only listed the properties that need our attention. Since this is an example of setting up an insert through a stored procedure we will focus on the InsertCommand.
• InsertCommandType="StoredProcedure": We need to set the insert command type to stored procedure.
• InsertCommand='INSERTEMPLOYEE': The command itself is the name of the stored procedure. Notice the full capitals and single quotes (') around the name. This is because the database is dialect 3 and the stored procedure was created in full uppercase.
• asp:Parameter Name="FirstName": This is just a standard input parameter to the stored procedure. Notice that FirstName, LastName, HireDT and JobPosition are all input parameters. Check the syntax of the stored procedure above to see that this matches our stored procedure parameter list.
• asp:Parameter Direction="ReturnValue" Name="EmployeeID": The INSERTEMPLOYEE stored procedure also has 1 return parameter so we can get back the primary key. This parameter must be set to Direction="ReturnValue" in order to retrieve that value.

The ReturnValue is the most common mistake many people make. If you open the InsertQuery property dialog and click the "Show advanced properties" link:

You can see here how to setup the parameter. Remember you must use Direction="ReturnValue" not Output or InputOutput.

The dsJobPosition SqlDataSource is also just a standard select of all the JobPosition records.

The dvEmployees and DetailsView1 DetailsViews are also straight forward setups. dvEmployees uses the dsAllEmployees SqlDataSource and DetailsView1 uses the dsInsertWithStoredProc SqlDataSource. You should notice that for both DetailsViews the EmployeeID is not defined as a field. In the case of dvEmployees the EmployeeID is passed to the insert as NULL which fires the trigger and allows it to generate the primary key. In the case of DetailsView1 the stored procedure doesn't require the EmployeeID so there is no need for it at all.

In this particular example either case works just fine. The trigger example is marginally easier. In more advanced situations the stored procedure will likely prove the better option since it gives the most control and also makes it easier to retrieve values (such as the primary key) back without additional scripting.

See next Tutorial...Employee: DataSet web page which manually creates a dataset with multiple tables and then links the tables in memory.

Office Test - Tutorial 6

The 6th page of the OfficeTest General Tests tutorial covers manually creating and linking datasets and assigning them to a GridView. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

This topic is more just for interest sake and to provide a simple example of manually manipulating datasets. I've seen other discussions on other sites related to this topic but honestly I don't see a lot of use for this functionality.

6. Manually creating and linking datasets.

The HTML code for this example is very simple. It just contains a single simple GridView. We won't waste any additional time reviewing the HTML. The purpose here is to show how datasets can be loaded for separate tables and then a new result set built which combines the relationship data in memory. Really there are many ways to achieve the end result of this example and most are easier and more obvious than this method. The idea here however is to use the internal functionality already provided in the DataSet object.

Populate
The Populate() method loads the entire results of the Employee table and then the entire results of the JobPosition table. The data is loaded into separate named views within the dataset. Once the data is loaded the Relations functionality of the dataset is used to create a merged dataset which contains the Employee data with the JobPosition.JobTitle added in place of JobPositionID.

NOTE: There is a typo in the current code. The JobPositionID in the JoinedTables was suppose to be the EmployeeID. This doesn't really change anything or cause any problems.

Loading the data


DataSet myDataSet = new DataSet();

string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
FbConnection con = new FbConnection(connectionString);
try {
  con.Open();
  FbCommand cmd = new FbCommand("SELECT \"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", \"JobPositionID\" FROM \"Employee\"", con);
  FbDataReader reader = cmd.ExecuteReader();
  myDataSet.Load(reader,LoadOption.OverwriteChanges,new string[]{"Employee"});
  reader.Close();

  cmd = new FbCommand("SELECT \"JobPositionID\",\"JobTitle\",\"JobDesc\" FROM \"JobPosition\"", con);
  reader = cmd.ExecuteReader();
  myDataSet.Load(reader, LoadOption.OverwriteChanges, new string[] { "JobPosition" });
  reader.Close();
} finally {
  con.Close();
}
   

The code snippet shown above creates the DataSet (myDataSet) which will stored all the data. It then used FbCommand and FbDataReader to select the data from the Employee and JobPosition tables. myDataSet.Load is used to load the entire results of the reader into the dataset. Notice that the table name is used for the 3rd parameter to the Load method. This assigns an alias to that view of the corresponding data. In this case all the Employee table data is in the "Employee" view in the DataSet. All the JobPosition data is stored separately in the same dataset but under the "JobPosition" alias.
DataTable employee = myDataSet.Tables["Employee"];
DataColumn employeeJobPositionID = employee.Columns["JobPositionID"];

DataTable jobPosition = myDataSet.Tables["JobPosition"];
DataColumn jobPositionJobPositionID = jobPosition.Columns["JobPositionID"];

myDataSet.Relations.Add("JobPositionEmployee", jobPositionJobPositionID, employeeJobPositionID);
   

Here a pointer to the Employee table within the dataset is retrieved and used to get a pointer to the column definition related to the foreign key to the JobPosition table. Likewise a pointer to the JobPosition table is retrieved and used to get a pointer to the column definition for the primary key for the JobPosition table.

Next a relation is added to the dataset linking the JobPosition.JobPositionID primary key to the Employee.JobPositionID foreign key column. An alias ("JobPositionEmployee") is assigned to the relationship.

myDataSet.Tables.Add(new DataTable("JoinedTables"));
myDataSet.Tables["JoinedTables"].Columns.Add("EmployeeID");
myDataSet.Tables["JoinedTables"].Columns.Add("FirstName");
myDataSet.Tables["JoinedTables"].Columns.Add("LastName");
myDataSet.Tables["JoinedTables"].Columns.Add("HireDT");
myDataSet.Tables["JoinedTables"].Columns.Add("JobTitle");
   

Here we manually add a new table with alias ("JoinedTables") to the our existing dataset. The columns must also be manually created. Note I changed the first column back to EmployeeID from JobPositionID since it was a typo. This column structure is basically the column structure for the Employee table except we removed JobPositionID and replaced it with JobTitle.
foreach(DataRow row in myDataSet.Tables["JobPosition"].Rows) {
  DataRow[] joinRows = row.GetChildRows("JobPositionEmployee");
  foreach (DataRow joinRow in joinRows) {
    DataRow newRow = myDataSet.Tables["JoinedTables"].NewRow();
    newRow["EmployeeID"] = joinRow["EmployeeID"];
    newRow["FirstName"] = joinRow["FirstName"];
    newRow["LastName"] = joinRow["LastName"];
    newRow["HireDT"] = joinRow["HireDT"];
    newRow["JobTitle"] = row["JobTitle"];
    myDataSet.Tables["JoinedTables"].Rows.Add(newRow);
  }
}
   

This loop goes over each row in the JobPosition table we loaded from the database and checks for child rows created in the relationship "JobPositionEmployee" we built earlier. One Employee table row should exist, as a child of the current JobPosition row, in the relationship "JobPositionEmployee" for each Employee using that JobPosition. Since the child rows are from the Employee table the joinRow contains all the columns needed for the employee data.

For each matching child row we simply manually assign the data to a new row we created for the "JoinedTables" table we created in the dataset. The "JobTitle" must be retrieved from the "JobPosition" table row since the child rows are from the Employee table which don't have the JobTitle.

gvEmployees.DataSource = myDataSet.Tables["JoinedTables"].DefaultView;
gvEmployees.DataBind();
   

Finally the new "JoinedTables" table in the dataset is assigned as the data source to our GridView and DataBind is called to force it to refresh.

Summary
This code is really very straight forward except perhaps the follow:


myDataSet.Relations.Add("JobPositionEmployee", jobPositionJobPositionID, employeeJobPositionID);
   

The relations functionality is setup to work based on the master/primarykey table. This means you build the relationship so that Employee data is added as children of the JobPosition data not the other way around.

As I mentioned before there are many ways the same results could be achieved. We could have simply selected the data in that format from the database to start with. We could also just manually loop through the Employees and record by record lookup the corresponding JobPosition. In this example I only wanted to demonstrate how the DataSet Relations functionality could be used. In some cases it may also improve performance. If you already have Employee and JobPosition data in cached DataSets and don't want to retrieve a new joined result from the database this method might prove useful and faster.

See next Tutorial...Employee: Select Procedure web page which calls a stored procedure with a result set directly and through a select SQL query.

Office Test - Tutorial 7

The 7th page of the OfficeTest General Tests tutorial looks at using select stored procedures. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

7. Select Stored Procedures.

One nice feature of Firebird is the ability to create select stored procedures. A select procedure can be used in 2 different ways. You can either simply call the procedure and retrieve the records returned or you can select from the procedure as if it was a table. The 2nd part is what makes it very handy. This allows you to easily filter and order the results and even decide to return only the columns you require. Most databases support the first part - returning a result set - but not the second the part.

This is a very powerfull feature because the stored procedure ends up working like a view that supports an entire programming langauge.

The example web page is very simple. It provides a dropdown list containing all the possible JobPositions. You can select any single JobPosition and then the GridView below it is filled with the results of all Employees for that JobPosition. If the checkbox is checked then the stored procedure is called directly and all columns are returned. If the checkbox is not checked then a select from the stored procedure is called which selects only the firstname.

Employee: Stored Procedure/Trigger


I won't spend any time on the HTML since it's very straight forward and not the focus of this tutorial.

GetEmployeesByJob


CREATE PROCEDURE "GetEmployeesByJob" ( "JobPositionID"  INTEGER )
RETURNS ( "EmployeeID"  INTEGER, "FirstName"  VARCHAR(50), "LastName"  VARCHAR(50) )
AS
BEGIN
  FOR select "EmployeeID", "FirstName", "LastName"
      from "Employee"
      where "JobPositionID" = :"JobPositionID"
      into :"EmployeeID", :"FirstName", :"LastName" DO
    SUSPEND;
END
   

This stored procedure simply takes 1 parameter for the JobPositionID and does a select from the Employee table using it as a filter. Notice that SUSPEND is called on each pass of the loop to send that row of data out as return record. The RETURNS must also be defined. Here we have EmployeeID, FirstName and LastName.
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd;
    if (cbGetAllColumns.Checked) {
      cmd = new FbCommand("\"GetEmployeesByJob\"", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
    } else {
      cmd = new FbCommand("SELECT \"FirstName\" FROM \"GetEmployeesByJob\"(@JobPositionID)", con);
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
    }
    FbDataReader reader = cmd.ExecuteReader();
    try {
      gvEmployees.DataSource = reader;
      gvEmployees.DataBind();
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
}
   

Whether the stored procedure is used directly or a select is performed we still setup the FbCommand and FbDataReader the same way. When the reader is created is can be assigned to the GridViews (gvEmployees) DataSource property. Calling DataBind will then force a refresh of the GridView with the readers data.

Stored Procedure


cmd = new FbCommand("\"GetEmployeesByJob\"", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
   

In this case the command is just the name of the stored procedure. Keep in mind I need to add \" around the name to maintain case because the database is dialect 3. The CommandType is of course StoredProcedure. Then we simply add one parameter to the stored procedure and set it to the SelectedValue property of the dropdown list for JobPosition. The command will automatically create a reader with the rows returned by the stored procedure. The result of this call will have 3 columns - EmployeeID, FirstName and LastName.

Selecting from a procedure


cmd = new FbCommand("SELECT \"FirstName\" FROM \"GetEmployeesByJob\"(@JobPositionID)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.Parameters.Add("@JobPositionID", Int32.Parse(lstJob.SelectedValue));
   

Here we switch CommandType to Text since we are really performing a select, not executing a stored procedure. The SQL for the select must contain the parameter for the stored procedure as well. The same row results will be return as in the previous case, since we didn't add a WHERE clause, but with only the FirstName column.

Well thats really all there is to it. There are times when putting additional logic in the stored procedure will provide better performance by reducing the data returned to the calling application. However the application may still want to tailor the information it receives. This mechanism provides an elegant solution to that problem.

See next Tutorial...Stored Procedure web page which calls a stored procedure with no result set and retrieves a computed value.

Office Test - Tutorial 8

The 8th page of the OfficeTest General Tests tutorial looks at using a simple stored procedure to add 2 numbers and return the sum. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

8. Calling a Stored Procedure.

The web page for this tutorial just provides a very simple and basic example of using a stored procedure for some sort of computation (not a result set) and retieving a result from it. In this case the page allows the user to provide 2 integers and calls a stored procedure to add the values and return the sum.

Stored Procedure Page


I won't spend any time on the HTML since it's very straight forward and not the focus of this tutorial.

AddTwoValues


CREATE PROCEDURE "AddTwoValues" ( "Value1"  INTEGER, "Value2"  INTEGER )
RETURNS ( "TheSum"  INTEGER )
AS
BEGIN
  "TheSum" = "Value1"+"Value2";
END
   

This is about as simple as we can get for a stored procedure. "TheSum" is a RETURNS paramter for the procedure.
protected void cmdAddTwoValues_Click(object sender, EventArgs e) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("\"AddTwoValues\"", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@Value1", Int32.Parse(txtNum1.Text));
    cmd.Parameters.Add("@Value2", Int32.Parse(txtNum2.Text));
    FbParameter param = new FbParameter("@TheSum", TypeCode.Int32);
    param.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(param);
    cmd.ExecuteScalar();
    txtAddTwoValues.Text = param.Value.ToString();
  } finally {
    con.Close();
  }
}
   

As we have shown in previous examples CommandType must be set to StoredProcedure. An FbParameter is created separately so we can assign its DataType to Int32 and the Direction to ReturnValue.

Whats important here and new is how we execute the stored procedure. ExecuteScalar must be called on the FbCommand because we do not want or expect a result set. Once we call ExecuteScalar we can now go back and get the Value of the FbParameter we created for the return value.

That is really all there is to know about calling a stored procedure. Just remember ExecuteScalar is used for non dataset procedures.

See next Tutorial...BillingType: Strongly Typed DataSets web page which uses Visual Studio 2005's built-in dataset tool and ObjectDataSources.

Office Test - Tutorial 9

The 9th page of the OfficeTest General Tests tutorial looks at using Strongly Typed DataSets and ObjectDataSource in place of SqlDataSource. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

ObjectDataSource
ObjectDataSource is a replacement for SqlDataSource. Where SqlDataSource provides a direct link to the database through SQL, ObjectDataSource provides a connection to more abstract data classes. TableAdapter, DataTable and properly defined custom data classes can be linked to an ObjectDataSource and provide method calls that perform Inserts, Updates, Deletes and Selects. This provides a middle tier so that the interface is not so directly linked to the database through SQL calls. The Insert, Update, etc. methods can contain additional C# logic and do not even need to work with a database or possibly could even work with multiple databases.

To configure the ObjectDataSource just drag and drop one on your web page and then right-click and select "Configure Data Source" just as you would with a SqlDataSource. You will get the following dialogs:

Configure ObjectDataSource

Rather than choosing a connection string you will instead pick your custom data class, TableAdpater, DataTable etc..

Rather than enter a SQL statement instead you select the appropriate method from the data object you chose in the previous step.

The end result will generate HTML code that looks something like this:

The TypeName represents the data object and then each of the method names follow. Parameters are automatically added matching the parameters for each of the methods.

Configuring the ObjectDataSource once your data object is created is pretty straight forward. Now we'll spend some time looking at the different types of data objects it can work with. In this tutorial we'll cover Visual Studio's built in Strongly Typed DataSet design. In later tutorials we'll discuss building our own more robust business objects.

9. Strongly Typed DataSets.

To add a new DataSet schema to your application go to the solution explorer and right-click on the App_Code directory if you have one. If you don't then you can do the same from the root solution branch as well. Select "Add New Item" and then from the dialog pick "DataSet". When the page for the schema appears you'll be asked to select a connection string or database connection.

Initial Setup
Once you've supplied a connection Visual Studio will start you through the process of creating your first TableAdapter. You can also right-click on the page background and select "Add" and then "TableAdapter", from the menu, to create a new one at any time.

1. Select SQL

First you need to supply a select statement for the dataset. Optionally you can click the query builder button to help build the SQL.

2. Get/Fill Methods

Then you'll be asked to choose your Fill (retrieves all records into a dataset supplied as a parameter) and GetData (returns a DataTable object filled with all records) methods. The defaults for this dialog are generally what you'll want. Although, if the database table does not get modified from you web site, the last checkbox can be unchecked and no Insert, Update or Delete methods will be generated.

3. Finish

The last dialog just lets you know what was generated.

That's really all there is to it unless you want to add additional selects and filters. The Insert, Update and Delete commands are built automatically using the columns and table name provided in the select SQL. In my case I noticed they kept the " (quotes) around the column and table names when the Insert, Update and Delete commands were generated, which is good.

Adding a single row select
I wanted to add a single row select since that is how I normally keep my GridView and DetailsView synchronized. To do this right-click on the TableAdapter image in the schema and select "Add" and then "Query" from the menu.

SQL/Stored Procedure

Choose the method you want for selecting the data row.

Type of Query/SQL

Choose the query type. In our case we want the first option. We only want 1 row but its still a result set of 1 we want. The 2nd option is only for aggregate SQL statements (like "select count(*) from MyTable").

Add the SQL and Parameters

Enter the new query and remember to use bind parameters so we can filter to a single row.

Method Name

Finally we can add a method name that has some meaning for us. In this case adding "ByID" on the end of the Fill and GetData let us know this query selects only by the primary key. We could use any name we want of course.

Employee/BillingType DataSet Schema
My final schema for this web page, and others we'll be looking at, looked like this:

In some ways this is actually quite handy. It provides a relatively simple and fast way to generate methods we can use to work with our data. In addition we get a nice visual picture of the schema including the names of all the methods each table will be using. It even provides relationship information as foreign keys. Having said that I should also point out that this is not really the best use of ObjectDataSource and I would actually never use Strongly Typed DataSets in any real web site.

A look at the code
First off if you look at the BillingTypeStronglyTypedDataSet.aspx.cs unit you'll notice there is no code at all. No code is really required for our example and even the minimal code we had in the other web pages for the Firebird parameter binding quirks are not required here.

If you go back to the DataSet Schema page and right-click on the page and select "View Code" another editor tab will open with the dataset code. Notice this is just more XML tags and parameters. While this is nice and simple it is also the biggest drawback of using this method. If real C# business objects were created then we would have something to work with for adding more advanced features or real data layer abstraction. The XML really gives us nothing to work.

Why ObjectDataSource?
One of the best reasons to use ObjectDataSource is to provide a middle tier to your application. The interface (HTML) and code directly bound to it through events and callbacks represents your first tier. Normally with SqlDataSource this code also includes your SQL and connection information. The Database would represent your 2nd tier and since the SQL is embedded in the first tier they are directly and inseparably linked.

ObjectDataSource provides a simple mechanism that allows the code to work with 3 or more tiers. Again the interface is your first tier. The interface does not contain SQL or connection information but rather just binds to business object methods. The business objects represent your 2nd or middle tier. Those objects supply data to the interface through parameters, classes and objects that are not directly related to the database. The business objects contain the connection code and SQL (or whatever data storage access code is required) necessary to talk to the database which is now the third tier.

This sort of design adds a layer of abstraction between the interface and the database. At any time the database could be changed from SqlServer to Oracle or Firbird or a flat file and the interface doesn't need to know or change. Only the connection and SQL code on the business objects needs to be modified. Also since we have a lot of control over that code we can add logic allowing it to support multiple types of data storage - such as both Oracle and Firebird at the same time.

Creating datasets (Strongly Typed DataSets) the way we do in this tutorial while fast, simple and visual does gain any of those benefits. The SQL is separated from the interface directly but is still tightly coupled with the DataSet XML and does not provide enough flexibility to handle complex data or multiple data storages. There is also no real way to add object factory style functionality to these datasets which is common in large scale applications. While not obvious when you first look at the DataSet designer it is actually possible to extend Strongly Typed DataSets. The tutorial following this one will discuss briefly how you do this but in the end it doesn't change my stance on this method of development.

See next Tutorial...Employee: Strongly Typed DataSets web page which provides a slightly more complex example of Strongly Typed DataSets with relations and ObjectDataSources.

Office Test - Tutorial 10

The 10th page of the OfficeTest General Tests tutorial looks again at using Strongly Typed DataSets and ObjectDataSource in place of SqlDataSource. This time however we will review a slightly more complex example. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

10. ObjectDataSource, Strongly Typed DataSets and Relations

This tutorial is a cross between the Employee: Simple and BillingType: Strongly Typed DataSets tutorials. Essentially this web page has the same functionality and setup as the Employee: Simple but uses the ObjectDataSource and Strongly Typed DataSets method used in BillingType: Strongly Typed DataSets.

Employee: Strongly Typed DataSets.

If you've been going through the earlier tutorials then the design of this screen should be really familiar. Again we have a GridView that handles display of all the records and a DetailsView which handles all editing for the currently selected record. This time however we are using Strongly Typed DataSets.

Taking a quick look at the DataSet schema again we can see the Employee table and the JobPosition table plus a foreign key link joining the 2 tables.

You can add a relation like this by right-clicking on a particular table object in the schema and selecting "Add" and "Relation". This will open dialog with options for defining the relationship.

The basic setup of the web page including the GridView and DetailsView are essentially the same as before. The code in the EmployeeStronglyTypedDataSet.aspx.cs file is the same except we don't need the parameter binding fix as we did with SqlDataSource.

You should notice in this case that the GridView only shows the JobPositionID, not the JobTitle. I left this as is to demonstrate that the Strongly Typed DataSets don't provide any simple relation benefit that gives us access to this information. We could modify the select for the Employee table to include that information but that would de normalize our dataset schema.

In the DetailsView I handle this limitation so we'll concentrate on that code for the moment.


<asp:DetailsView ID="dvEmployee" runat="server" AutoGenerateRows="False" DataKeyNames="EmployeeID" DataSourceID="odsCurrentEmployee">
  <Fields>
    <asp:TemplateField HeaderText="JobPositionID" SortExpression="JobPositionID">
      <EditItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="odsAllJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </EditItemTemplate>
      <InsertItemTemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="odsAllJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </InsertItemTemplate>
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# ((ExampleDataSets.JobPositionDataTable)((new ExampleDataSetsTableAdapters.JobPositionTableAdapter()).GetDataByID((int)Eval("JobPositionID")))).Rows[0]["JobTitle"].ToString() %>'>
        </asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Fields>
</asp:DetailsView>
   

I removed all the parameters and tags we don't need to focus on from the DetailsView HTML. If you look at the JobPositionID field you'll notice we have the typical 3 templates. The EditItemTemplate and InsertItemTemplate templates are actually completely standard. The data source they use is an ObjectDataSource linked to the JobPosition strongly typed dataset but the setup is still exactly the same as we've seen before.

Lets look at the ItemTemplate.


<asp:Label ID="Label1" runat="server" Text='<%# ((ExampleDataSets.JobPositionDataTable)((new ExampleDataSetsTableAdapters.JobPositionTableAdapter()).GetDataByID((int)Eval("JobPositionID")))).Rows[0]["JobTitle"].ToString() %>'>
</asp:Label>
   

This template contains a single label that will display the JobTitle from the JobPosition strongly typed dataset. Your first thought should be WOW that's quite the EVAL you got going there. Lets break this down a little.
• Eval("JobPositionID"): Gives us the value of the JobPositionID for the current row in the DetailsView, which we then cast as an int type.
• (new ExampleDataSetsTableAdapters.JobPositionTableAdapter()): Here I create a new instance of the JobPosition TableAdapter from the schema. This is the only way to gain access to the data in the table adapter but is not really efficient. I wouldn't recommend this for a large dataset.
• GetDataByID: I added this function to the JobPosition TableAdapter so we could select a single row base on the JobPositionID.
• (ExampleDataSets.JobPositionDataTable): We need to cast the result of the call to GetDataByID as a JobPositionDataTable so we can access the fields correctly.
• Rows[0]["JobTitle"].ToString(): Since we called GetDataByID to return the DataTable we know that it will have exactly 1 row accessed at index=0. We want the "JobTitle" field from that row.

This is a pretty ugly way to get access to something as simple as the JobTitle. There are other ways to do this. One way would be to simply select the JobTitle back in the Employee select. This seems to be the standard method most developers follow because it's the simplest. I would tend to agree although I hate the idea that I need to dirty the Employee table when the JobPosition table and relation information are already defined. No matter what method you use to handle your data, when it comes to accessing sub table or sub object information or properties I would say Microsoft really dropped the ball in their design.

If you decide to follow the method of selecting back the JobTitle you have to keep in mind that joins to other tables removes the ability to perform Inserts, Updates and Deletes. Ya, I know it makes no sense, since we could just supply the XSD interface with the SQL for those.

Sub select in place of a join
If you want to get around this problem with SQLServer you can perform a subselect on the column -
I'm not sure what the equivelant syntax is in Firebird if its even supported.


SELECT EmployeeID, FirstName, LastName, HireDT, JobPosition,
    (SELECT JobTitle FROM JobPosition
     WHERE JobPosition.JobPositionID = Employee.JobPositionID)  AS JobTitle
FROM Employee

There is another way to provide more functionality to your TableAdapter. If you open the ClassView while in the dataset design tab you will get something like the following:

Here we can navigate to the ExampleDataSetsTableAdapter branch and expand it. Below is the 3 datasets we have defined. If you select the EmployeeTableAdapter class then the list below (to the right in the image) shows the methods for that class. You can double click on a method to jump to the associated code. The code for these classes is actually generated at runtime or while debugging so you can't really modify the class directly. However if you either make your own subclass or define it as a partial class you can override or add your own methods.

Knowing this we could add a new column to the Employee TableAdapter called JobTitle and then override the Fill method and make it also get the JobTitle from the JobPosition TableAdapter. We could also just add a GetJob function that returns the JobTitle from the JobPosition TableAdapter and use that function in the field EVAL on the DetailsView.

I still don't like these solutions because retrieving lookup table (sub table or foreign key table) column information is very common and really you don't want to start messing around with adding overrides and partial classes to all this generic or generated code. If your going to start doing all that work it quickly becomes easier to just write your own business objects.

See next Tutorial...JobPosition: Custom Business/DataAccess objects which uses ObjectDataSource with custom made business classes instead of datasets.

Office Test - Tutorial 11

The 11th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource but with custom made business objects this time around. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

11. ObjectDataSource, Custom business objects

This tutorial steps back to the JobPosition table as a simple example for making custom business objects. .

JobPosition: BLL - Business Logic Layer.

If you've been going through the earlier tutorials then the design of this screen should be really familiar. Again we have a GridView that handles display, edit and delete of all the records and a DetailsView which handles inserting new records. Also as in the last couple of tutorials we are using the ObjectDataSource instead of SqlDataSource.

Since the webpage code works pretty much the same as it did for the strongly typed datasets we'll concentrate on the BLL to start.

JobPositionData


public class JobPositionData
{
  private int jobPositionID;
  private string jobTitle;
  private string jobDesc;

  public JobPositionData() {
    this.jobPositionID = -1;
  }

  public JobPositionData(int jobPositionID, string jobTitle, string jobDesc) {
    this.jobPositionID = jobPositionID;
    this.jobTitle = jobTitle;
    this.jobDesc = jobDesc;
  }

  public override string ToString() {
    if (this.jobPositionID == -1)
      return DBNull.Value.ToString();
    else
      return jobTitle;
  }

  public int JobPositionID {
    get {
      return jobPositionID;
    }
    set {
      jobPositionID = value;
    }
  }

  public string JobTitle {
    get {
      return jobTitle;
    }
    set {
      jobTitle = value;
    }
  }

  public string JobDesc {
    get {
      return jobDesc;
    }
    set {
      jobDesc = value;
    }
  }
}
   

The JobPositionData class represents the business object itself. In our simple case here we don't really have any business logic on the class other than the properties necessary to hold the related columns of data from the database table.

This code is nice because it is simple and clean. It does only what we want it to do and nothing more. If we need to modify it in the future, it doesn't take an expert developer to understand the class and extend it.

Essentially this class has a private member for each column in the JobPosition table and a corresponding Public property to get and set the private member. I've added 2 constructors, a base parameterless constructor and a full constructor that can instantiate the class with the members fully initialized.

public JobPositionData(int jobPositionID, string jobTitle, string jobDesc) is not absolutely required but is nice for manually creating an instance of JobPositionData with all the column correctly assigned.

public JobPositionData() IS REQUIRED because the ObjectDataSource needs to create new instances of JobPositionData from time to time and it always creates the class using the default parameterless constructor.

If we have any business logic such as validations or calculated fields those could be added to the JobPositionData class unless they are rules that apply to rows of data as a set rather than a single row at a time. If you required rules that act on several rows or need information from several rows I would create a JobPositionListBLL and JobPositionListDAL class in place of the current JobPositionList. The BLL would contain rules not related to a particular database directly. The DAL would just be a descendant and contain all the database specific SQL calls. There is no absolute requirement to do it this way but its usually better to separate the Business Logic from the Data Access Layer.

NOTE: The name used for the class is irrelevant so you don't have to follow any special naming conventions. For my examples I used xxxxData for the record class and xxxxList for the container class.

JobPositionList


[DataObject]
public class JobPositionList
{
  public List<JobPositionData> items;

  public JobPositionList() {
  }

  public void Populate() {
    // This populate is not necessary but can be used for when the DataSet object
    // is cached or used in code not related to an ObjectDataSource
    items = LoadTable();
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<JobPositionData> LoadTable() {
    return LoadTable(0, -1);
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<JobPositionData> LoadTable(int startRowIndex, int maximumRows) {
    // This select routine fully supports paging
    List<JobPositionData> items = new List<JobPositionData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    string sql;
    if (maximumRows == -1)
      sql = "SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
    else
      sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() +
            " \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                     (string)reader["JobTitle"],
                                                     reader["JobDesc"].ToString());
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  public int Count() {
    return Count(0,-1);
  }

  public static int Count(int startRowIndex, int maximumRows) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    FbCommand cmd = new FbCommand("SELECT COUNT(1) FROM \"JobPosition\"", con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        reader.Read();
        return (int)reader[0];
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<JobPositionData> LoadRecord(int JobPositionID) {
    List<JobPositionData> items = new List<JobPositionData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\" " +
                                    "WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", JobPositionID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                     (string)reader["JobTitle"],
                                                     reader["JobDesc"].ToString());
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  [DataObjectMethod(DataObjectMethodType.Insert)]
  public static int Insert(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("INSERT INTO \"JobPosition\"(\"JobPositionID\",\"JobTitle\",\"JobDesc\") " +
                                    "VALUES(@JobPositionID,@JobTitle,@JobDesc)", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      cmd.Parameters.Add("@JobTitle", data.JobTitle);
      cmd.Parameters.Add("@JobDesc", data.JobDesc);
      int affectedRows = cmd.ExecuteNonQuery();
      return data.JobPositionID;
    } finally {
      con.Close();
    }
  }

  [DataObjectMethod(DataObjectMethodType.Update)]
  public static int Update(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("UPDATE \"JobPosition\" set \"JobTitle\" = @JobTitle, \"JobDesc\" = @JobDesc " +
                                    "WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobTitle", data.JobTitle);
      cmd.Parameters.Add("@JobDesc", data.JobDesc);
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      int affectedRows = cmd.ExecuteNonQuery();
      return affectedRows;
    } finally {
      con.Close();
    }
  }

  [DataObjectMethod(DataObjectMethodType.Delete)]
  public static int Delete(JobPositionData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("DELETE FROM \"JobPosition\" WHERE \"JobPositionID\" = @JobPositionID", con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
      int affectedRows = cmd.ExecuteNonQuery();
      return affectedRows;
    } finally {
      con.Close();
    }
  }
}
   

Looking at our data container we can again see just how simple the overall structure of the class is. Even in this simple example I've added a little extra complexity just to add support for other methods I'm not really using in this example.

Something you should notice immediately is the declarative code syntax preceeding the class and most method declarations.

• [DataObject]: Marks this class as a DataObject that can be used in an ObjectDataSource.
• [DataObjectMethod(DataObjectMethodType.Select)]: Marks this method as a select method.
• [DataObjectMethod(DataObjectMethodType.Insert)]: Marks this method as a insert method.
• [DataObjectMethod(DataObjectMethodType.Update)]: Marks this method as a update method.
• [DataObjectMethod(DataObjectMethodType.Delete)]: Marks this method as a delete method.

When a method or class is marked it just means that Visual Studio will be able to know when to provide it as an option in the appropriate configuaration dialog:





You can see from the images above that each of the marked methods becomes a method choice in the appropriate list when you configure the datasource to use JobPositionList.

Method delarations are important. You can't just define any old method with any list of parameters. It has to be possible for the ObjectDataSource to know how to fill in the parameters. It can match parameters up in 2 ways. Either it can match the record class (JobPositionData in this case) or a property from the class (i.e. JobPositionData.JobPositionID).

Therefore the following is possible:

• public static int Insert(JobPositionData data)
• public static int Insert(int JobPositionID)
• public static int Insert(int JobPositionID,string JobTitle,string JobDesc)

But the following is not:

• public static int Insert(AnotherClass myClass)
• public static int Insert(int ID)
• public static int Insert(int ID,string Title)

The ObjectDataSource can either create an instance of JobPositionData and match the properties to the binding names or it can match the method parameters to the binding names. You can't use parameters or classes that don't match any binding source.

Short breakdown of JobPositionList


public void Populate() {
  // This populate is not necessary but can be used for when the DataSet object
  // is cached or used in code not related to an ObjectDataSource
  items = LoadTable();
}

[DataObjectMethod(DataObjectMethodType.Select)]
public static List<JobPositionData> LoadTable() {
  return LoadTable(0, -1);
}

[DataObjectMethod(DataObjectMethodType.Select)]
public static List<JobPositionData> LoadTable(int startRowIndex, int maximumRows) {
  // This select routine fully supports paging
  List<JobPositionData> items = new List<JobPositionData>();

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  string sql;
  if (maximumRows == -1)
    sql = "SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
  else
    sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() +
          " \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\"";
  FbCommand cmd = new FbCommand(sql, con);
  try {
    con.Open();
    FbDataReader reader = cmd.ExecuteReader();
    try {
      items.Clear();
      while (reader.Read()) {
        JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                   (string)reader["JobTitle"],
                                                   reader["JobDesc"].ToString());
        items.Add(data);
      }
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
  return items;
}
   

These methods are responsible for populating the ObjectDataSource. LoadTable() is the default load method I use which returns a new List object. The ObjectDataSource needs a list object returned to it so you have to return a list as part of this method. If you want to use data caching (which I recommend in larger scale applications) you can change this class to not be static and remove the static directive from all the functions. Change LoadTable to simple return items, the private member List I added. Use to populate to fill the list and then add your instance of the JobPositionList to a cache (such as the session cache). This way, when you call LoadTable it just passes back the list already in memory instead of rebuilding it.

LoadTable(int startRowIndex, int maximumRows) supports paging. If you use a GridView and turn paging on then you must pick this method as your SELECT method. The GridView will pass in the correct values for startRowIndex and maximumRows as you flip through pages.

public int Count() {
  return Count(0,-1);
}

public static int Count(int startRowIndex, int maximumRows) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  FbCommand cmd = new FbCommand("SELECT COUNT(1) FROM \"JobPosition\"", con);
  try {
    con.Open();
    FbDataReader reader = cmd.ExecuteReader();
    try {
      reader.Read();
      return (int)reader[0];
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
}
   

These routines are really just here to support paging. The Count(int startRowIndex, int maximumRows) method aids the LoadTable(int startRowIndex, int maximumRows) method in determining the number of pages and how to step through them.
[DataObjectMethod(DataObjectMethodType.Select)]
public static List<JobPositionData> LoadRecord(int JobPositionID) {
  List<JobPositionData> items = new List<JobPositionData>();

  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("SELECT \"JobPositionID\", \"JobTitle\", \"JobDesc\" FROM \"JobPosition\" " +
                                  "WHERE \"JobPositionID\" = @JobPositionID", con);
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", JobPositionID);
    FbDataReader reader = cmd.ExecuteReader();
    try {
      items.Clear();
      while (reader.Read()) {
        JobPositionData data = new JobPositionData((int)reader["JobPositionID"],
                                                   (string)reader["JobTitle"],
                                                   reader["JobDesc"].ToString());
        items.Add(data);
      }
    } finally {
      reader.Close();
    }
  } finally {
    con.Close();
  }
  return items;
}
   

I added this routine to provide a general method for selecting back a single row based on the primary key. In this example I don't use it but normally this makes linking a GridView and DetailsView together easier. The DetailsView needs to select back a single record to maintain syncronization with the GridView and this select method will do that for you.

Again, if you want to use caching and be more efficient on your database calls, you should really just get the record from the private items list and return it without going to the database. You can't do that here because I'm using this class statically in the example so the items list doesn't really exist.

[DataObjectMethod(DataObjectMethodType.Insert)]
public static int Insert(JobPositionData data) {
  string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
  FbConnection con = new FbConnection(connectionString);
  try {
    con.Open();
    FbCommand cmd = new FbCommand("INSERT INTO \"JobPosition\"(\"JobPositionID\",\"JobTitle\",\"JobDesc\") " +
                                  "VALUES(@JobPositionID,@JobTitle,@JobDesc)", con);
    cmd.Parameters.Clear();
    cmd.Parameters.Add("@JobPositionID", data.JobPositionID);
    cmd.Parameters.Add("@JobTitle", data.JobTitle);
    cmd.Parameters.Add("@JobDesc", data.JobDesc);
    int affectedRows = cmd.ExecuteNonQuery();
    return data.JobPositionID;
  } finally {
    con.Close();
  }
}
   

Insert, Update and Delete work pretty much all the same. In my case I wanted them to all receive a JobPositionData object instead of each parameter individually.

Something important to keep in mind
ObjectDataSource is not really that efficient when passing back classes. You would think that since you sent it a copy of your class in a list it would just pass it back modified but thats not how it works. It actually creates a new instance of the class and populates the properties it has in the attached GridView or DetailsView or whatever is holding the results. In the case of the Delete method it doesn't even do that. It creates a new instance and only populates the properties corresponding to the DataKeyNames property on the GridView of DetailsView. This means that JobPositionID will be set for the JobPositionData class passed to Delete but JobTitle and JobDesc will be null. If I wanted my delete to use logic related to other properties in the class the information will not be there. This is another case of Microsoft providing the simplest of functionality and overlooking the big picture.

WebPage Markup properties
I'm not going to delve too deep into the aspx files since the partial class has no code and the markup properties are almost exactly as we seen through all the previous tutorials. I will go over some of the differences briefly.

ObjectDataSource


<asp:ObjectDataSource ID="odsJobPosition" runat="server" InsertMethod="Insert" DeleteMethod="Delete"
  UpdateMethod="Update" SelectMethod="LoadTable" TypeName="JobPositionList" DataObjectTypeName="JobPositionData">
</asp:ObjectDataSource>
   

Notice the properties for the ObjectDataSource. TypeName="JobPositionList" specifies the container class, while DataObjectTypeName="JobPositionData" specifies the record data class. The InsertMethod, DeleteMethod, UpdateMethod and SelectMethod specify which method on JobPositionList will handle the corresponding functionality.

DetailsView


<asp:DetailsView ID="dvJobPosition" runat="server" AutoGenerateRows="False" Caption="Insert a new JobPosition"
  CellPadding="4" DataSourceID="odsJobPosition" DefaultMode="Insert" ForeColor="#333333"
  GridLines="None" Height="50px" Width="125px" DataKeyNames="JobPositionID">
  <Fields>
    <asp:BoundField DataField="JobPositionID" HeaderText="JobPositionID" SortExpression="JobPositionID" />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
    <asp:BoundField DataField="JobDesc" HeaderText="JobDesc" SortExpression="JobDesc" />
    <asp:CommandField ShowInsertButton="True" />
  </Fields>
</asp:DetailsView>
   

The setup for DetailsView is exactly the same as it would be for any other method of building a DataSource. The properties of JobPositionData are used as the binding sources just as columns from a table would be. DataKeyNames is used to specify the primary key binding. In the details view this is probably not needed in this example.

GridView


<asp:GridView ID="gvJobPosition" runat="server" AutoGenerateColumns="False" Caption="JobPositions"
  CellPadding="4" DataSourceID="odsJobPosition" ForeColor="#333333" GridLines="None" DataKeyNames="JobPositionID">
  <Columns>
    <asp:CommandField SelectText="**" ShowSelectButton="True" />
    <asp:BoundField DataField="JobPositionID" HeaderText="JobPositionID" SortExpression="JobPositionID" />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression="JobTitle" />
    <asp:BoundField DataField="JobDesc" HeaderText="JobDesc" SortExpression="JobDesc" />
    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
  </Columns>
</asp:GridView>
   

The setup for GridView is also exactly the same as it would be for any other method of building a DataSource. DataKeyNames is required here in order for the delete to work.

Overall this is really a better method to approach your website design. You have clean Business objects and Data Objects with only the code you need and no uncessary overhead. The code is quick and simple to write and far easier to maintain especially when additional functionality is required.

If you later decide to use the business objects with other processes (WebService, WinForms applications, ...) they will be easier to work with. You can easily add additional layers of abstraction such as a core JobPositionList class with structure and no SQL. Then add mulitple descendant classes for different databases (JobPositionListSQLServer, JobPositionListOracle, JobPositionListXML, ...).

See next Tutorial...Employee: Custom Business/DataAccess objects which uses ObjectDataSource with custom made business classes instead of datasets.

Office Test - Tutorial 12

The 12th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource and custom made business objects. This time with a slightly more complex example. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

12. ObjectDataSource, Custom business objects

This example web page is similar to the last tutorial but this time we'll work with the employee table so we can show one method of handling foreign key relationships. This example also uses the synchronized GridView and DetailsView design I've used in past tutorials. I've also added in paging and sorting. As with all tutorials, I'm only going to cover what's actually different from previous ones..



Employee: BLL - Business Logic Layer.
Lets start by taking a look at whats different in the EmployeeData class. Overall its basically the same. There is a private member and a public property for each column in the Employee table and 2 constructors.

EmployeeData


public class EmployeeData
{

...

  private Nullable<DateTime> hireDT;
  private int jobPositionID;
  private string jobTitle;

  public Nullable<DateTime> HireDT {
    get {
      return hireDT;
    }
    set {
      hireDT = value;
    }
  }

  public int JobPositionID {
    get {
      return jobPositionID;
    }
    set {
      jobPositionID = value;
    }
  }

  public string JobTitle {
    get {
      return jobTitle;
    }
    set {
      jobTitle = value;
    }
  }
}
   

Whats different in this example is HireDT and JobTitle.

HireDT is a Date column that can also be null. DateTime however does not support nulls in C#. There is a generic class called Nullable that allows you to make any non-nullable datatype into one that does support nulls. I've decided to use this to make life a little easier for the HireDT property.

JobTitle is new only because it doesn't come from the Employee table. It instead is a lookup or code table value that comes from JobPosition. There are many ways to handle this sort of issue when making custom classes. In fact one of the nice things about custom classes is the freedom you have to approach problems. In this case I've decided to use a very simple approach by adding a property to the Employee table just to store the JobTitle associated to the JobPositionID. Some developers might consider this to be a dirty addition to the base BLL layer for Employee. The correct way would be to have one property such as JobPositionData JobPosition since this would create a true business logic relationship between EmployeeData and JobPositionData. I agree this is the best approach but it is also a little more complex due to limitations in the way Microsoft decided to implement their ObjectDataSource. I will look into that method in a later tutorial.

EmployeeList


[DataObject]
public class EmployeeList
{

...

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<EmployeeData> LoadTable(int startRowIndex, int maximumRows, string sortBy) {
    // This select routine fully supports paging and sorting.
    List<EmployeeData> items = new List<EmployeeData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    string sql;
    if (maximumRows == -1)
      sql = "SELECT ";
    else
      sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() + " ";
    sql += "\"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", E.\"JobPositionID\", J.\"JobTitle\" FROM \"Employee\" E ";
    sql += "INNER JOIN \"JobPosition\" J ON J.\"JobPositionID\" = E.\"JobPositionID\" ";
    if (sortBy == "")
      sql += " ORDER BY E.\"EmployeeID\"";
    else {
      sql += " ORDER BY " + sortBy;
    }
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          EmployeeData data = new EmployeeData((int)reader["EmployeeID"],
                                               (string)reader["FirstName"],
                                               (string)reader["LastName"],
                                               DBUtils.GetNDateTime(reader, "HireDT"),
                                               (int)reader["JobPositionID"],
                                               (string)reader["JobTitle"]);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<EmployeeData> LoadRecord(int EmployeeID) {
    List<EmployeeData> items = new List<EmployeeData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      string sql = "SELECT \"EmployeeID\", \"FirstName\", \"LastName\", \"HireDT\", E.\"JobPositionID\", ";
      sql += "J.\"JobTitle\" FROM \"Employee\" E ";
      sql += "INNER JOIN \"JobPosition\" J ON J.\"JobPositionID\" = E.\"JobPositionID\" ";
      sql += "WHERE E.\"EmployeeID\" = @EmployeeID";
      FbCommand cmd = new FbCommand(sql, con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@EmployeeID", EmployeeID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          EmployeeData data = new EmployeeData((int)reader["EmployeeID"],
                                                           (string)reader["FirstName"],
                                                           (string)reader["LastName"],
                                                           DBUtils.GetNDateTime(reader, "HireDT"),
                                                           (int)reader["JobPositionID"],
                                                           (string)reader["JobTitle"]);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

...

}
   

The first thing to note about both LoadRecord routines is the INNER JOIN to the JobPosition table from the Employee table. We need this in order to retrieve and store the JobTitle. In our case JobPositionID is not null but if it supported nulls the INNER JOIN would of course need to be a LEFT OUTER JOIN.

We waste a little memory with this approach because JobTitle is stored once per Employee record not once per JobPosition. If we have 100 employees with the same JobPosition then we store JobTitle in memory 100 times. Using an object factory centralized instance of JobPosition as a JobPositionData class and linking to it would solve this problem. However I don't want to concentrate on that issue at the moment and unless you have over a 1000 employees this is hardly worth considering. Keep in mind this data does not stay in memory. Its only used to gather the data and pass it to the ObjectDataSource and then to the GridView. The grid uses it to populate the WebPage and then discards it. Regardless of what method you use to get the data the amount of text contained in the webpage will be the same if it displays the JobTitle in the grid. The memory if only kept if you actually cache the results.

The LoadTable(int startRowIndex, int maximumRows, string sortBy) method now has an additional paramter string sortBy which receives the column binding for the column title you click on in the GridView. This allows us to sort by that column. The sorting is pretty straight forward since all we do is ORDER BY on the sortBy column.

Notice the "SELECT FIRST ", " SKIP " syntax used in the select. This was added to handle paging. In this case we only return the rows from the database required for the current page base on which page we are on and the number of records shown in the page.

If we were caching the data in memory we would just could just select all the records in the List used for the cache. When LoadTable was called for a particular page we would just make a new List and add into only the records we needed from the current memory cache. The "SELECT FIRST ", " SKIP " syntax would not be necessary.

When designing any DAL (DataAccess Layer) you need to carefully consider performance based on the parameters of your application. Small tables are fast to load and also take up little memory but 100s of users constantly loading the same data over and over again eats of alot of CPU cycles. Loading large tables into memory to avoid constantly selecting the data eats up memory and if you cache the data per user you may find you server performance drops drastically when it start swapping memory around. Consider each case and tune the method as related to its particular needs. A singular method for all cases will almost always be the wrong approach.

One last thing you should notice in the LoadTable method is the following DBUtils.GetNDateTime(reader, "HireDT") line of code. I added a function in the DBUtils.cs class to covert a DataTime field from a DbDataReader into Nullable. This just simplies the code by automatically checking for a null value in the reader. A frustrating fact you will encounter in .NET like most other languages is the apparent lack of easy null handling. I've always found that most languages don't bother to take that extra step required to easily SET and GET null fields. C# was nice enough to add in the Nullable generic class but unfortunately almost nothing related to the database API actually works with it automatically. Since we are using the Firebird API here they could have added this support themselves but didn't :(.

WebPage Markup properties
Lets take a quick look at the aspx code. This time we have 2 datasources, a GridView and a DetailsView.

ObjectDataSource


<asp:ObjectDataSource ID="odsAllEmployees" runat="server" EnablePaging="True" OldValuesParameterFormatString="original_{0}" SelectCountMethod="Count" SelectMethod="LoadTable" SortParameterName="sortBy" TypeName="EmployeeList">
  <SelectParameters>
    <asp:Parameter Name="startRowIndex" Type="Int32" />
    <asp:Parameter Name="maximumRows" Type="Int32" />
    <asp:Parameter Name="sortBy" Type="String" />
  </SelectParameters>
</asp:ObjectDataSource>

<asp:ObjectDataSource ID="odsCurrentEmployee" runat="server" DataObjectTypeName="EmployeeData"
  DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}"
  OnDeleted="odsCurrentEmployee_Deleted" OnInserted="odsCurrentEmployee_Inserted"
  OnUpdated="odsCurrentEmployee_Updated" SelectMethod="LoadRecord" TypeName="EmployeeList"
  UpdateMethod="Update">
  <SelectParameters>
    <asp:ControlParameter ControlID="gvEmployees" Name="EmployeeID" PropertyName="SelectedValue"
      Type="Int32" />
  </SelectParameters>
</asp:ObjectDataSource>

<asp:ObjectDataSource ID="odsJobPositions" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="LoadTable" TypeName="JobPositionList">
</asp:ObjectDataSource>
   

Since the DetailsView is handling all the editing odsAllEmployees only needs to define the SelectMethod="LoadTable" and TypeName="EmployeeList". However since we now support paging and sorting it also needs EnablePaging="True", SelectCountMethod="Count" and SortParameterName="sortBy".

odsCurrentEmployee is used with the DetailsView which does all the work so we need to also define DataObjectTypeName="EmployeeData", DeleteMethod="Delete", InsertMethod="Insert", SelectMethod="LoadRecord" and UpdateMethod="Update".

Finally odsJobPositions was added to support a drop down list for selecting new JobPositions for an employee.

DetailsView


<asp:DetailsView ID="dvEmployee" runat="server" AutoGenerateRows="False" Caption="Current Employee"
  CellPadding="4" DataKeyNames="EmployeeID" DataSourceID="odsCurrentEmployee" ForeColor="#333333">
  <Fields>
    <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True" SortExpression="EmployeeID" InsertVisible="False" />
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
    <asp:BoundField ApplyFormatInEditMode="True" DataField="HireDT" DataFormatString="{0:yyyy.MM.dd}"
      HeaderText="HireDT" HtmlEncode="False" SortExpression="HireDT" />
    <asp:TemplateField HeaderText="JobPositionID" SortExpression="JobPositionID">
      <EditItemTemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="odsJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </EditItemTemplate>
      <InsertItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="odsJobPositions"
          DataTextField="JobTitle" DataValueField="JobPositionID" SelectedValue='<%# Bind("JobPositionID") %>'>
        </asp:DropDownList>
      </InsertItemTemplate>
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("JobTitle") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />
  </Fields>
</asp:DetailsView>
   

Don't forget to define the DataKeyNames="EmployeeID" in order to support deletes. I also defined the EmployeeID field as ReadOnly="True". I did this because I'm using a generator to handle the EmployeeID so I don't want the user editing the field.

Notice that the JobPosition is handled with a templated field. This works just as it did in our earlier tutorial using SQLDataSource. The EditItemTemplate and InsertItemTemplate actually bind to the JobPositionID because they use a drop down list to select the Job Position. However the ItemTemplate which does not use a list binds to the JobTitle instead. This provides a very simple way of displaying the title. This is also much easier and more obvious than in our previous SQLDataSource and Strongly Typed DataSet examples.

GridView


<asp:GridView ID="gvEmployees" runat="server" AllowPaging="True" AllowSorting="True"
  AutoGenerateColumns="False" Caption="All Employees" CellPadding="4" DataKeyNames="EmployeeID"
  DataSourceID="odsAllEmployees" ForeColor="#333333" PageSize="5">
  <Columns>
    <asp:CommandField ButtonType="Image" SelectImageUrl="~/Images/select1.png" ShowSelectButton="True" />
    <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression='E."EmployeeID"' />
    <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression='"FirstName"' />
    <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression='"LastName"' />
    <asp:BoundField DataField="HireDT" DataFormatString="{0:yyyy.MM.dd}" HeaderText="HireDT"
      HtmlEncode="False" SortExpression='"HireDT"' />
    <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" SortExpression='"JobTitle"' />
  </Columns>
</asp:GridView>
   

The GridView is very straight forward. The only thing worth noting is the use of JobTitle and that JobPositionID isn't even used at all. Since the GridView is only for display the JobTitle is all we need.

Again we can see how simple it is to use custom business objects. Overall its easier than SqlDataSource or Strongly Typed DataSets as your needs become more specific and more complex. There is a little more work to create the classes in the first place but they are not difficult and in my case I just coded a class generator to handle that for me.

See next Tutorial...Project: Custom Business/DataAccess objects including subclasses for handling foreign key relationships.

Office Test - Tutorial 13

The 13th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource and custom made business objects. This time we'll look at one method of using sub-class properties to handle foreign key relationships. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

13. ObjectDataSource, Custom business objects

This example web page is similar to the last tutorial but this time we'll work with the project table so we can show one method of handling foreign key relationships. This example also uses the synchronized GridView and DetailsView design I've used in past tutorials. As with all tutorials, I'm only going to cover what's actually different from previous ones..



Project: BLL - Business Logic Layer.
Lets start by taking a look at whats different in the ProjectData class. Overall its basically the same. There is a private member and a public property for each column in the Project table and 2 constructors. This time however the ManagerID, which is a foreign key to the Employee table, is replaced with a sub-class object of type EmployeeData.

ProjectData


public class ProjectData
{

...

  private EmployeeData manager;

  public ProjectData() {
    this.projectID = -1;
    this.manager = new EmployeeData();
  }

  public ProjectData(int projectID, string name, string description, bool isActive, EmployeeData manager) {
    this.projectID = projectID;
    this.name = name;
    this.description = description;
    this.isActive = isActive;
    this.manager = manager;
  }

  public EmployeeData Manager {
    get {
      return manager;
    }
    set {
      manager = value;
    }
  }

...

}
   

The property EmployeeData Manager uses the BLL for the Employee table we created in an earlier tutorial. Generally this is the best way to represent a foreign key link in your business logic layer. In this example we will keep the logic in the ProjectData class simple but as you will see later this creates some complications in the WebPage code.

ProjectList


[DataObject]
public class ProjectList
{

...

  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<ProjectData> LoadTable(int startRowIndex, int maximumRows, string sortBy) {
    // This select routine fully supports paging and sorting.
    List<ProjectData> items = new List<ProjectData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    string sql;
    if (maximumRows == -1)
      sql = "SELECT ";
    else
      sql = "SELECT FIRST " + maximumRows.ToString() + " SKIP " + startRowIndex.ToString() + " ";
    sql += "\"ProjectID\", \"Name\", \"Description\", \"IsActive\", \"ManagerID\" FROM \"Project\" ";
    if (sortBy == "")
      sql += " ORDER BY \"ProjectID\"";
    else {
      sql += " ORDER BY " + sortBy;
    }
    FbCommand cmd = new FbCommand(sql, con);
    try {
      con.Open();
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        EmployeeList managers = new EmployeeList();
        managers.Populate();
        while (reader.Read()) {
          EmployeeData manager = new EmployeeData();
          if (reader["ManagerID"] != DBNull.Value) {
            int managerID = (int)reader["ManagerID"];
            foreach (EmployeeData man in managers.items) {
              if (man.EmployeeID == managerID) {
                manager = man;
                break;
              }
            }
          }
          ProjectData data = new ProjectData((int)reader["ProjectID"],
                                             (string)reader["Name"],
                                             reader["Description"].ToString(),
                                             ((short)reader["IsActive"] == 1),
                                             manager);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }


  [DataObjectMethod(DataObjectMethodType.Select)]
  public static List<ProjectData> LoadRecord(int ProjectID) {
    List<ProjectData> items = new List<ProjectData>();

    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      string sql = "SELECT \"ProjectID\", \"Name\", \"Description\", \"IsActive\", \"ManagerID\" FROM \"Project\" "+
                   "WHERE \"ProjectID\" = @ProjectID";
      FbCommand cmd = new FbCommand(sql, con);
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@ProjectID", ProjectID);
      FbDataReader reader = cmd.ExecuteReader();
      try {
        items.Clear();
        while (reader.Read()) {
          // This routine should only return a single row really so we can just load
          // the single employee entry for it using the static LoadRecord method.
          EmployeeData manager = new EmployeeData();
          if (reader["ManagerID"] != DBNull.Value) {
            int managerID = (int)reader["ManagerID"];
            List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
            manager = managers[0];
          }
          ProjectData data = new ProjectData((int)reader["ProjectID"],
                                             (string)reader["Name"],
                                             reader["Description"].ToString(),
                                             ((short)reader["IsActive"] == 1),
                                             manager);
          items.Add(data);
        }
      } finally {
        reader.Close();
      }
    } finally {
      con.Close();
    }
    return items;
  }

  [DataObjectMethod(DataObjectMethodType.Insert)]
  public static int Insert(ProjectData data) {
    string connectionString = WebConfigurationManager.ConnectionStrings["OfficeTestFirebird"].ConnectionString;
    FbConnection con = new FbConnection(connectionString);
    try {
      con.Open();
      FbCommand cmd = new FbCommand("insertProject", con);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Clear();
      cmd.Parameters.Add("@Name", data.Name);
      cmd.Parameters.Add("@Description", data.Description);
      int isActive = 0;
      if (data.IsActive)
        isActive = 1;
      cmd.Parameters.Add("@IsActive", isActive);
      if (data.Manager.EmployeeID != -1)
        cmd.Parameters.Add("@ManagerID", data.Manager.EmployeeID);
      else
        cmd.Parameters.Add("@ManagerID", FbDbType.Integer);
      FbParameter param = new FbParameter("@ProjectID", TypeCode.Int32);
      param.Direction = ParameterDirection.ReturnValue;
      cmd.Parameters.Add(param);
      cmd.ExecuteScalar();
      return (int)param.Value;
    } finally {
      con.Close();
    }
  }

...

}
   

In ProjectList we'll concentrate on the above 3 routines and how they handle the manager class.

LoadTable


EmployeeList managers = new EmployeeList();
managers.Populate();
while (reader.Read()) {
  EmployeeData manager = new EmployeeData();
  if (reader["ManagerID"] != DBNull.Value) {
    int managerID = (int)reader["ManagerID"];
    foreach (EmployeeData man in managers.items) {
      if (man.EmployeeID == managerID) {
        manager = man;
        break;
      }
    }
  }
  ProjectData data = new ProjectData((int)reader["ProjectID"],
                                     (string)reader["Name"],
                                     reader["Description"].ToString(),
                                     ((short)reader["IsActive"] == 1),
                                     manager);
  items.Add(data);
}
   

When we call load record, just before the main loop, I create a new instance of EmployeeList and call Populate. This fills the items property on the EmployeeList with all the rows from the Employee table. While we loop through all the project records I lookup an instance of EmployeeData from the EmployeeList we populated and find the one with the matching EmployeeID. You can also see that I create a new instance of EmployeeData before checking. If none is found then manager will at least be a new instance of the class with the default ManagerID = -1 initialized. This is not necessarily the most efficient way to handle this but its fine for this little example.

Obviously rebuilding the EmployeeList each time we load the the Project table is a waste. In a larger application the EmployeeList should really be pulled from a cache.

LoadRecord


EmployeeData manager = new EmployeeData();
if (reader["ManagerID"] != DBNull.Value) {
  int managerID = (int)reader["ManagerID"];
  List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
  manager = managers[0];
}
   

LoadRecord works the same way except we can use EmployeeList.LoadRecord(managerID) to load only the employee record we need.

Insert/Update


if (data.Manager.EmployeeID != -1)
  cmd.Parameters.Add("@ManagerID", data.Manager.EmployeeID);
else
  cmd.Parameters.Add("@ManagerID", FbDbType.Integer);

   

This code isn't really unique to this particular issue but I haven't had to handle binding a null int value before so I added here to take a look at. When you create your parameters for Insert/Update any datatype that doesn't support nulls still has to be initialized and you have to set the datatype manually. Even if you use a Nullable for ManagerID you still have to do this because Nullable is not supported by parameters unfortunately.

WebPage Markup properties
Lets take a quick look at the aspx code. The ObjectDataSource odsAllProjects and odsEmployees work the same as in other tutorials so we don't really need to look at them.

ObjectDataSource


<asp:ObjectDataSource ID="odsCurrentProject"
     OnInserting="odsCurrentProject_Inserting"
     OnUpdating="odsCurrentProject_Updating">
</asp:ObjectDataSource>
   

I cut everything out of odsCurrentProject except the OnInserting and OnUpdating event handlers because this is really all that is new for this data source. Keep these in mind because we'll look at the code related to them a little further down in this tutorial.

DetailsView


<asp:DetailsView ID="dvProject">
  <Fields>
    <asp:TemplateField HeaderText="Manager" SortExpression="Manager">
      <EditItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees" DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Eval("Manager.EmployeeID") %>' OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" OnDataBound="DropDownList1_DataBound">
          <asp:ListItem Selected="True" Value="-1">Null</asp:ListItem>
        </asp:DropDownList>
      </EditItemTemplate>
      <InsertItemTemplate>
        <asp:DropDownList ID="DropDownList2" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees" DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Eval("Manager.EmployeeID") %>' OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"  OnDataBound="DropDownList1_DataBound">
          <asp:ListItem Selected="True" Value="">Null</asp:ListItem>
        </asp:DropDownList>
      </InsertItemTemplate>
      <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Manager") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Fields>
</asp:DetailsView>
   

The DetailsView contains a Templated field again for handling the Manager. Notice in the drop down list that AppendDataBoundItems="True". We also hard coded a null entry with value = -1 in the list. We need this to handle when no employee is selected. If you don't set AppendDataBoundItems="True" then the datasource for employees will clear the hard coded entry from the drop down list.

The binding for the selected value of the list is Eval("Manager.EmployeeID"). This is a pretty simple binding but it doesn't get us an instance of the EmployeeData class which is what we want in the end. OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" and OnDataBound="DropDownList1_DataBound" event handlers have been added so we can get the EmployeeData class instance we need when an employee is selected from the list. We'll discuss how we do this further down in this tutorial.

GridView


<asp:GridView ID="gvProjects">
  <Columns>
    <asp:BoundField DataField="Manager" HeaderText="Manager" />
  </Columns>
</asp:GridView>
   

The GridView is simple. We just bind directly to the Manager property. Because Manager is a class the only generic way to display it is to call its ToString() method. I made sure to setup a ToString() method on EmployeeData which returns the name of the employee.

Partial WebPage class code
Now lets take a look at the code related to the event handlers mentioned above.

public partial class ProjectDataObjects : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e) {
    if (!this.IsPostBack) {
      Session["Manager"] = new EmployeeData();
      gvProjects.SelectedIndex = 0;
    }
  }

...

  protected void odsCurrentProject_Updating(object sender, ObjectDataSourceMethodEventArgs e) {
    // For some reason after updating all fields are correct except the manager is cleared
    ProjectData project = (ProjectData)e.InputParameters[0];
    EmployeeData manager = (EmployeeData)Session["Manager"];
    project.Manager = manager;
  }
  protected void odsCurrentProject_Inserting(object sender, ObjectDataSourceMethodEventArgs e) {
    ProjectData project = (ProjectData)e.InputParameters[0];
    EmployeeData manager = (EmployeeData)Session["Manager"];
    project.Manager = manager;
  }
  protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {
    // Set to new manager after selection
    SetManager((DropDownList)sender);
  }
  protected void DropDownList1_DataBound(object sender, EventArgs e) {
    // Grab and store the original manager since it seems to get lost
    SetManager((DropDownList)sender);
  }
  private void SetManager(DropDownList list) {
    // During INSERT all fields are initialized as EMPTY or DBNull. This causes a problem with
    // the -1 issue. Would be better if it started with an initialized ProjectData() object instead.
    // It may be possible to do this but I'm not sure the cleanest way.
    int managerID = -1;
    if (list.SelectedValue != "")
      managerID = Int32.Parse(list.SelectedValue);
    EmployeeData manager = new EmployeeData();
    if (managerID != -1) {
      List<EmployeeData> managers = EmployeeList.LoadRecord(managerID);
      manager = managers[0];
    }
    Session["Manager"] = manager;
  }
}
   

On the Page_Load I initialize a new empty EmployeeData entry into the session cache. This is used if no employee is selected from a drop down list.During the event handlers odsCurrentProject_Updating and odsCurrentProject_Inserting I get the current ProjectData , pull the selected EmployeeData from the session cache and assign it to the class.

The reason the manager is cleared is because the original class instance is not used. A new instance is created during insert or update and the other properties are initialized from what is available in the DetailsView. Unfortunately sub-classes are not handled because no way was provided in the current architecture to handle them automatically.

In fact the entire concept of sub-classes and foreign key relationships is not addressed in any way in any part of the entire .NET framework development tools. It appears Microsoft never even considered it a requirement and just skipped it altogether. Unfortunately it is a very real requirement in all applications and thus you'll notice that no matter what method you use to work with a webpage the easy approach is always messed up by trying to come up with an elegant solution to this problem.

When an employee is selected from one of the drop down lists the DropDownList1_SelectedIndexChanged or DropDownList1_DataBound is called, which I use to call SetManager. SetManager intializes and loads an EmployeeData record for the selected EmployeeID and puts it in the cache so its available to the odsCurrentProject_Updating and odsCurrentProject_Inserting events.

Conclusions
This tutorial demonstrates the issues of dealing with a subclass in your business logic layer. If you try to keep your BLL clean by not adding unnecessary subclass handling code you end up with the mess we have here in your webpage code.

The final code we have in the WebPage partial class is less than ideal and becomes much more complicated as the number of subclasses grows, especially if more than one subclass of the same class type exists in your primary class.

While the core class and methods used here are a good starting point I would not recommend the process I used here for keeping the Manager in sync with the Project. In the next tutorial I'll go over the same code and the same basic webpage but with a cleaner solution.

See next Tutorial...Project 2: Custom Business/DataAccess objects including subclasses for handling foreign key relationships.

Office Test - Tutorial 14

The 14th page of the OfficeTest General Tests tutorial looks again at using ObjectDataSource and custom made business objects. This time we'll look at one method of using sub-class properties to handle foreign key relationships. If you want to review the other tutorials, the central page can be found here Firebird and .NET 2.0 development Example.

14. ObjectDataSource, Project 2

This tutorial is really just an extension of the last one. It uses the same classes and almost the same webpage. The only real difference is a couple of methods one the data class and binding settings on the webpage..



Project 2: BLL - Business Logic Layer.
Lets start by taking a look at what we are using that is different in the ProjectData class.

ProjectData


public class ProjectData
{

...

  public int ManagerID {
    get {
      return Manager.EmployeeID;
    }
    set {
      if (value == -1) {
        manager = new EmployeeData();
      }else {
        List<EmployeeData> managers = EmployeeList.LoadRecord(value);
        manager = managers[0];
      }
    }
  }

  public string ManagerName {
    get {
      return manager.FirstName + " " + manager.LastName;
    }
  }

...

}
   

Two new public properties ManagerID and ManagerName have been added to ProjectData. Manager name is a simple readonly property than returns the employees name in a format fit for our display. We don't absolutely need this but it allows us to show the name in a format usefull for our current needs rather than forcing the EmployeeData class to know how we want to use it.

More important is the ManagerID. This property has a more complex set construct. When the ManagerID is set to an EmployeeID it uswa the static LoadRecord method from EmployeeList to load the data for that EmployeeID automatically. This saves us having to write code outside the class to handle this. It essentially makes maintaining the manager class property mostly transparent to outside code. I say mostly because we still need an EmployeeID property which is separate from our Manager property.

ProjectList
ProjectList is exactly the same for this tutorial as the last so we don't really need to look at this code. However I threw in one additional method that is unrelated but handy for other purposes.

[DataObjectMethod(DataObjectMethodType.Select)]
public static List<ProjectData> LoadRecord(OrderedDictionary keyValues) {
  int projectID = (int)keyValues["ProjectID"];
  return LoadRecord(projectID);
}
   

This new LoadRecord routine uses the DataKeyNames property from a GridView or DetailsView as a parameter instead of int ProjectID. This is usefull when you want to develop a more generic base List class that supports multi-column primary keys. In this simple example I just extract the ProjectId from the OrderedDictionary and call the existing LoadRecord(int ProjectID). You need to pass GridView.SelectedDataKey.Values which contains the values for the current row related to the columns listed in DataKeyNames.

WebPage Markup properties
We'll take a look at ObjectDataSource odsCurrentProject and the DetailsView since these are the only objects that really changed for this tutorial.

ObjectDataSource


      <asp:ObjectDataSource ID="odsCurrentProject" runat="server" DataObjectTypeName="ProjectData"
        DeleteMethod="Delete" InsertMethod="Insert" OldValuesParameterFormatString="original_{0}"
        OnDeleted="odsCurrentProject_Deleted" OnInserted="odsCurrentProject_Inserted" OnUpdated="odsCurrentProject_Updated"
        SelectMethod="LoadRecord" TypeName="ProjectList" UpdateMethod="Update">
        <SelectParameters>
          <asp:ControlParameter ControlID="gvProjects" Name="keyValues" PropertyName="SelectedDataKey.Values"
            Type="Object" />
        </SelectParameters>
      </asp:ObjectDataSource>
   

I've shown the code for odsCurrentProject just so you can see where the definition for SelectedDataKey.Values is assigned. The select method is still LoadRecord but only one LoadRecord accepts an OrderedDictionary as a parameter so the correct one will be called.

DetailsView


<asp:TemplateField HeaderText="Manager" SortExpression="Manager">
  <EditItemTemplate>
    <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees"
      DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Bind("ManagerID") %>'>
      <asp:ListItem Selected="True" Value="-1">Null</asp:ListItem>
    </asp:DropDownList>
  </EditItemTemplate>
  <InsertItemTemplate>
    <asp:DropDownList ID="DropDownList2" runat="server" AppendDataBoundItems="True" DataSourceID="odsEmployees"
      DataTextField="FirstName" DataValueField="EmployeeID" SelectedValue='<%# Bind("ManagerID") %>'>
      <asp:ListItem Selected="True" Value="">Null</asp:ListItem>
    </asp:DropDownList>
  </InsertItemTemplate>
  <ItemTemplate>
    <asp:Label ID="Label1" runat="server" Text='<%# Bind("ManagerName") %>'></asp:Label>
  </ItemTemplate>
</asp:TemplateField>
   

We are looking at just the Manager template field for the DetailsView here. As you can see the template field is simpler than the last tutorial. The new ManagerID property gives us something even simpler to bind to. The ItemTemplate uses the ManagerName for display but both edit templates use ManagerID. When a new employee is selected and the ManagerID is assigned the SET method in the property will automatically fill out the rest of the EmployeeData class represented by the Manager property.

If you look at the partial class code for the WebPage you'll notice that mess of code required in the last tutorial is now gone. Only the code for keeping our DetailsView in sync with the GridView is required.

Conclusions
Sometimes adding a little extra code into the base business logic layer can remove a lot of complexity in other areas. The current code used to fill out the Manager class should really be modified to use an EmployeeList already populated and stored in a global cache somewhere. This would reduce calls to the database and improve overall performance. Changes like this are simple to make under the current design of the class.

This is the last tutorial in this series. I hope it has been helpfull in getting you started with building basic ASP.NET 2.0 WebSites using Firebird or any other database.