Dr.Bob Examines... #130: ADS in Delphi Prism XE

Delphi Clinic

C++Builder Gate

Training & Consultancy

Delphi Notes Weblog

Dr.Bob's Webshop


	Dr.Bob Examines... #130
See Also:	Dr.Bob's Delphi Papers and Columns

Using ADS with Delphi Prism and ASP.NET
Advantage Database Server (ADS) is an ideal replacement for old and deprecated Borland Database Engine (BDE) local tables, as I’ve demonstrated in previous articles. However, ADS is much more than that, and can be used for any database task you have in mind.Stand-alone (local), but also client/server, multi-tier or as internet (web) database. In this article, I will demonstrate the use of ADS as a web database for an ASP.NET web project (written using Delphi Prism XE) that implements a registration form for events and seminars.

Advantage Database Server v10
First of all, in this article I will use ADS version 10, although you can also use version 9 if you want (I’m not using any of the special new features). To design the database, I use the Advantage Data Architect application, which is free and can be downloaded from the Advantage website, and is written in Delphi (full source code included).The Data Architect is a nice project to learn how to work with and manipulate ADS tables. For the example of this article, I need to store the name and address information from people who want to attend an event or seminar.The usual fields are: Company, Name (or FirstName + LastName), Address, Postal Code, City, Country, E-mail address, Phone number, and optionally one or two fields related to the event (like which version of Delphi you are currently using).The example registration form that I want to implement today, is for the Advantage Database System Training Day on November 3rd in Utrecht, The Netherlands.For this registration, we’d like to know which version of ADS (if any) the visitor is currently using, so we can adjust the sessions when needed. In short, using the Advantage Data Architect, I have created a new Data Dictionary called Events and a new table with the following layout:

Note the ID field, which is the primary key of type autoinc.This table is saved as Registration.adt, and can now be used by the application we’ll make with Delphi Prism.But before we continue, we must make sure that apart from the Advantage Database Server, we’ve also installed the Advantage .NET Data Provider (so we can use ADO.NET and ASP.NET with declarative data binding to connect to the Advantage database).

Delphi Prism XE
Delphi Prism XE is the most recent edition of Delphi Prism at the time of writing (people with a subscription received no less than two major updates in the last year: first from Delphi Prism 2010 to Delphi Prism 2011, and then – a few weeks ago – Delphi Prism XE). It can run in both Visual Studio 2008 and 2010, but for this article I’m using Delphi Prism XE in Visual Studio 2010, together with ADS v10 as mentioned before. Using Delphi Prism XE, we can create ASP.NET Web Projects, with File | New Project, using the dialog from the following screenshot:

For the purpose of this demo, I’ll give the project the name EventRegistration. The ASP.NET project will consist of one page Default.aspx, where we should start by placing a FormView control from the Data category of the Toolbox.The FormView has a number of tasks, including one to Choose the Data Source.Since there is no Data Source on the page, yet, we should select the option instead:

This will produce the Visual Studio Data Source Configuration Wizard, where we can specify where the application will get its data from.

In our case, that’s from a SQL Database, so click on the SQL Database item. This will automatically generate a default ID for the data source (SqlDataSource1) and place this ID in the textbox so we can modify it if needed.Click on OK to go to the next page of the wizard. In the second page, we can choose the data connection.Either from a list of existing connections, of by clicking on the New Connection button.

If you click on the New Connection button, a dialog will pop-up in which we can choose the data source.Here, we can select the type of data source from a list that contains Advantage Database Server (if you’ve installed the Advantage .NET Data Provider), as well as for example DataSnap, InterBase, and several Microsoft drivers.

If you click on the Continue button, a new dialog follows were we can specify the specific details to connect to the ADS Data Dictionary. Unless you’ve specified a username and password to access the Data Dictionary, this usually only means that we have to specify the location of the .add file.

Click on Test Connection to ensure that we can connect to the Data Dictionary. Click on OK if everything works, and back in the Configure Data Source wizard, we can click on OK to get to the next page were the option is offered to save the connection in the web.config file.This is handy, since it means we can modify the connectionstring without having to recompile the application.

The next page allows us to build the way we want to retrieve the data from the database. We can use a SQL statement or stored procedure, or use the dialog to select a table (there is only one: Registration) and specify the fields that we want to use in a SELECT statement for example. Note that by default the wizard will check the “*" for all fields, but I prefer to explicitly check the fields I need instead.

Optionally, we can add an ORDER BY clause to the SELECT statement, to sort the results based on the LastName field for example. The resulting SELECT statement will select all fields for the Registration table, and since we don’t use a WHERE clause, we will see all records.Which is ideal when I want to have a list of all people who registered for the event, but not if I want to enter a new registration.For that, I need an INSERT statement.We can automatically produce one, by clicking on the Advanced button in the wizard’s dialog. In the Advanced SQL Generation Options, we can specify that we want to generate INSERT, UPDATE and DELETE statements:

This will ensure that we can use the FormView in INSERT mode to enter new registrations. After we close the dialog, we can click on OK to get to the last page of the wizard, and close that one as well. The result is that we now have a FormView with a newly configured SqlDataSource component that connects to the Registrations table from the Event Data Dictionary.

ASP.NET Page
We can now configure the ASP.NET page, and especially the FormView to show itself in INSERT mode only. This can be done by selecting the FormView, and in the Properties Inspector making sure DefaultMode is set to Insert, with the following result:

Now we can give the application a test run, by selecting Debug | Start Without Debugging, which will start the ASP.NET Development Server as well as the default browser, showing the registration application in action:

Obviously, there are some issues with this page. First of all, the ID field is of type autoinc, so that shouldn’t be part of the input screen.And second, after we click on the Insert hyperlink, the page doesn’t jump to a “Thank you!" page (something we didn’t implement, yet), but gives an error message instead:

This problem is caused by the fact that the declarative data binding in the generated .aspx file is using positional parameters in the INSERT statements, but named parameters in the list of parameters that follows it. In detail, the InsertCommand is specified as follows:

  InsertCommand="INSERT INTO [Registration] ([FirstName], [LastName], [Address], [Postcode],
      [City], [Country], [Company], [Email], [Phone], [ADS], [ID])
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

While the InsertParameters include the names, as follows:

            <InsertParameters>
                <asp:Parameter Name="FirstName" Type="String" />
                <asp:Parameter Name="LastName" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="Postcode" Type="String" />
                <asp:Parameter Name="City" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Company" Type="String" />
                <asp:Parameter Name="Email" Type="String" />
                <asp:Parameter Name="Phone" Type="String" />
                <asp:Parameter Name="ADS" Type="String" />
                <asp:Parameter Name="ID" Type="Int32" />
            </InsertParameters>

We should change the ? in the INSERT statement to :fieldname items, turning it into the following (also removing the ID field):

  InsertCommand="INSERT INTO [Registration] ([FirstName], [LastName], [Address], [Postcode], [City],
      [Country], [Company], [Email], [Phone], [ADS])
    VALUES (:FirstName,:LastName,:Address,:Postcode,:City,:Country,:Company,:Email,:Phone,:ADS)"

Finally, we should ensure that the ID parameter is also removed from the visible fields on screen (the Bind(“ID")), since we won’t be used it anymore.

With these fixes in place, we can click on the Insert link to enter a registration in the database, with the result that we get a new empty page again.Ready for the next registration.This is nice if more people are standing in line behind the same machine, but it would be nicer (I think) so show a little “Thank you!" message as well.

We can implement this – and catch INSERT errors – in the Inserted event of the SqlDataSource. In the designer, select the SqlDataSource and then double-click on the Inserted event.Here, we can check if there were any INSERT errors (I leave it up to the reader to display them in a user friendly way), or to display the "Thank you!" message using a call to Response.Write.

  method _Default.SqlDataSource1_Inserted(sender: System.Object;
    e: System.Web.UI.WebControls.SqlDataSourceStatusEventArgs);
  begin
    if Assigned(e.Exception) then
    begin
      // handle error?
    end
    else
    begin
      Response.Write('<h1>Thank you for your registration!</h1>');
    end;
  end;

Summary
And that’s it. Of course, the final registration page will need to look much better when deployed on the internet (including the use of validators to ensure certain fields are required), but the skeleton is in place.If you want to know more about the actual ADS Training Day in The Netherlands we want to use it for, and attend the session on November 3rd where I’ll build this application “live’ (including the validators), then check out http://www.bobswart.nl/ADS.Thanks in advance!