So, you've invested in DBXL, which is great news for your organization -- all of our web services can speed your form development, help you create more performant forms, give you a great deal of flexibility for saving the data from your forms to different SQL tables and generally make you look like an InfoPath super-hero.

There's just one problem. You have a legacy form, built the old-fashioned way, based off a database. You have one (or more) tables that this form has been submitting to. You want to be able to update your existing data, like you can with your legacy form, but you want to start using DBXL for submitting your data to SQL. Patrick Halstead describes the current DBXL architecture here, and since DBXL takes a delete then insert approach to SQL, you don't have a way to update your existing rows..... Now what?

Never fear. A little SQL magic and you'll be back in your cape, saving the day yet again.

At the highest level, we'll create a new form, starting with a blank form. We'll use Qdabra's QueryDB web service to return existing data to the new form and add a trigger to the main table we shred to that will delete the loaded data when we submit via DBXL. Other Qdabra tools like qRules can assist for special cases, like forms with repeating data sections.

The Scenario 

For our sample case, I've created a simple database bound expense report form:

The tables have auto incrementing IDs and a foreign key relationship:

I've already submitted to these tables using my SQL bound form:

ExpenseReports (Parent Table)

ExpenseItems (Child Table)

The New Form

In order to make use of this existing data with a form that has been created to submit to DBXL, we will first want to recreate the form, starting from a blank template:

My new main data source collects the same data that my old SQL bound form did:

Using Qdabra's QueryDB web service, I've added data connections to first return data from the ExpenseReports table, so that I can select from an existing record:

My drop down is bound to the ID field in my form, and while the display value is the name, the value is the record's original ID.

I've added rules to my ID field to populate the Name and Purpose when an ID is selected. I've also added a rule to run another QueryDB query to return the items for the selected expense report. Since InfoPath doesn't currently include a way to copy data from one repeating table to another, you will need to either use code or qRules to copy the returned data if you have a repeating section. I've chosen to use qRules:

I've added a web service Submit connection to my form, and I'm using the Submit method of the DBXLDocumentService to sumit my form. At this point, then, my new form can load data previously submitted by my SQL bound form. If I create a new document type in DBXL and create a SQL mapping for my form and database as they currently exist:

I'll be in for a surprise when I submit my form after having loaded it with existing data and modified some information. I'll end up with new SQL rows for the same data!

The Workaround

First, we need to make some database changes. Some of you will not be overy excited about what I'm going to suggest here, so please bear with me -- I have another solution that may sit more comfortably with you, but does involve code, and I'll talk about that at the end of this post. For those of you who don't want the overhead of adding, writing and maintaining code, this workaround may be acceptable.

We need to set our ID on our main table to not auto increment, remove the foreign key relationship between our two tables, and change the ID column on our main table to not be the primary key. In fact, our main table will be without a primary key all together.

I'm not crazy about removing keys and relationships from my tables. However, since I’m only going to allow data to be entered into my tables from DBXL, and I know that I’m setting up my mapping with the DocId (an int unique to my DBXL install) as the primary key for my parent table and the foreign key to my child table, I feel like I am minimizing my risks. If there comes a day that all of my pre-existing data has been loaded into XML and resaved to the database, I’ll be able to set the DocId as my key in my SQL tables.

Disclaimers now in place, we'll forge ahead. We need to add a column to both our parent and child tables for the DBXL DocId and remove the key and auto increment on the ID field in our parent table. We also need to remove our foreign key relationship:

I'll modify our database mapping to incorporate the DocId. I'm going to manually select the DocId as the key for the parent table. I'll use the DocId field to create a relationship between my tables. I also want to be sure I'm still shredding the original ID from forms that had been previously submitted, because I'll need that information from my inserted data for my trigger:

Finally, I'm adding a trigger to the parent table. This trigger checks to see if there is a row in our parent table that has the same ID as the data being inserted, but a null value in the DocId field. If such a row exists, we know we are dealing with data that has not been inserted by DBXL before, so we need to delete the original rows:

USE [QdabraSamples]

GO

 

/****** Object:  Trigger [dbo].[DemoDelete]    Script Date: 10/12/2009 15:12:40 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

-- =============================================

-- Author:                                Hilary Stoupa

-- Create date: 2009-10-02

-- Description:         Demo Delete Trigger

-- =============================================

Create TRIGGER [dbo].[DemoDelete]

   ON  [dbo].[ExpenseReports]

   AFTER INSERT

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;

               

                Declare @id int;

                Set @id = (Select ID from Inserted);

               

                Delete from ExpenseItems

                Where ReportID = @id

                AND DocId is null;

 

                Delete from ExpenseReports

                Where ID = @id

                AND DocId is null;

 

END

 

GO

Now, if we use our new form, and populate with existing data, we don't end up with duplicate rows:

We also now have an XML file in the DBXL Administrative Tool that corresponds with our database row.

Tidying Up

Remember that drop down in the new form that allows us to select and load an existing row? That should be modified to only display rows where the DocId field is null in our parent table. Once we have an XML file for a row, we want to open that file to make updates to our data, and allow DBXL to handle the SQL tables as usual.

An Alternate Route

For the programmer, or if you don't mind the maintenance costs of adding code to your forms, there are other alternatives. For example, you can use custom code for your submit that will run a stored procedure or SQL statement to delete any rows that have the same ID as the data loaded into your form prior to the web service submit. This works well for data that has been using unique identifiers for keys.