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).