Sometimes it seems that InfoPath form templates are like snowflakes -- no two are alike. I thought it might be helpful to have more samples of data mapping using DBXL, and figured I may as well try DBXL v2.2 (available for download on Qdabra.com). It's new -- I'm new -- we have a lot in common. I'm also using InfoPath 2007 and SQL Express 2005.
For this sample, I've created a Status Report form (you can download a copy by right-clicking here and selecting "Save Target As..."). It has a repeating section for projects, with a nested repeating section for any issues. The main data source looks like this:
The template has a straightforward design -- a repeating table bound to Project and an optional section bound to Issues with another repeating table bound to Issue.
In design mode, the template looks like this:
And in preview:
A couple of points -- my form is configured to submit to my DBXL webservice -- the sample available for download above is now. Also, we need to set up a database for the data we'll be mapping from our form. We want to be sure to use compatible data types when we do that. My form has several date fields that I'll want to map to datetime SQL columns. We also want to make sure that our columns are the correct size for the data we wish to insert. I asked David Airapetyan for some guidance on database design for mapping the other day and he said:
As a rule of thumb, you want the data field size to be the maximum of what you expect the user to enter. For example, a phone number will unlikely be more than 30-40 characters even with spaces, country codes and extensions.
The task of picking the data field size is not trivial. For example, some people have very long names that can break your expectations. When you set up DBXL shredding and the actual field size is greater to what you have specified in the database, a shredding error will occur. One best practice to avoid those issues is to set the maximum textbox length in InfoPath to match your column size in the database.
So, when you create your database column size, if your fields in your form do not have limits, you may want to set them. If you can't change your form, you may want increase your column size.
Because of my two repeating groups, I plan to create two tables. I'm naming my datbase StatusReports and my tables Projects and Issues. My Projects table looks like this:
And my Issues table:
I have given my table columns the same names as my form fields. That is not required, but I lack imagination. Note that I added an ID column (ReportId) to my table that isn't related to a field in my form. I set ReportId as my primary key in my Projects table -- I also set it as an identity so that it would auto-increment:
Finally, I created a foreign key relationship between my Issues table and my Projects table on ReportId. While not necessary for the form to shred correctly, it is always a good idea to make those table relationships formal.
Now that my database is set up, I'll publish my form and attach it to the DBXL Administration Tool for my DBXL installation (DAT).
First, open DAT, and select New Configuration:
Then name your configuration and attach your published form:
Select the Database tab:
We need to fill in the Connection String field. The connection string, if you wish to use integrated authentication, is written thus: Integrated Security=SSPI;Data Source=<server>;Initial Catalog=<database> with <server> and <database> replaced with the name of your server and your database. For SQL Authentication, the connection string would be: Data Source=<server>;Initial Catalog=<database>;User Id=<userId>;Password=<password> with, of course, <userId> and <password> replaced with your information.
You'll see a Test Connection button after you tab out of the Connection String field -- be sure to give it a click:
A message box will pop up and tell you if your connection was successful. If it fails, the reason for failure will display. When your test succeeds, save your configuration before proceeding to the next steps.
Click Insert Table or Column under the Database Map section to add a section for our Projects table:
Note the Select Database Table button at the right of the Table Name field. Click it and the Database Tree opens in the taskpane:
If you double-click Projects in the Database Tree, the Table Name field will populate:
Now, in the Node Path field next to the Table Name, click the Select Schema Node button, which opens the Schema Tree:
Double-click the node associated with the first repeating group (my:Project) to populate the Node Path field:
To start adding our columns, select the top Insert Table or Column directly under the Table Name field.
For Type, select Column and map a column to a field in your form. You can type in the column name, of course, but if you select it from the Database Tree, the DB Type is selected for you. Also, it is more fun. The Node Path, too, can be typed in or selected from the Schema Tree. Needless to say, you can XPath up that Node Path all you want, which is good news since I have two fields in my form that I'd like to map that are not children of the table level Node Path.
After mapping all the fields to our Projects table, the map looks like this:
That was almost too easy. Good news, adding the Issues table will be even more straighforward! Select Insert Table or Column directly under Description (as if adding another column) and for Type select Table:
The Table Name will be Issues and the Node Path will be my:Projects/my:Project/my:Issues/my:Issue (note that the Node Path is to the nested repeating group). Select Insert Foreign Key below Column Name and put the ReportId column from the Issues table into the Column Name field and the ReportID column from the Projects table into the Parent Column Name field:
All that is left to do is map those Issues columns to the fields in the Issue group. Select Insert Table or Column directly below Insert Foreign Key to add columns:
Our completed mapping:
We are all done with mapping. Save your configuration, return to the form Catalog, and fill out your Status Report. I am going to put in three different projetcts, and add several issues to one project:
Now, submit the form and gleefully run off and check your SQL Tables -- I'll do the same.
