November 2008 - Posts - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

November 2008 - Posts

  • Fun with Sequential Form IDs, SQL and Sprocs!

    I may be exagerating when I say 'fun'. Forgive me for that.

    There are a variety of ways that you can create a unique, human readable form Id. This blog post is for those of you who prefer not to have to write or buy a web service, and don't want to write more than a little code. I'm warning you -- you will have to write some stored procedures. Or, you know, copy / paste them. It sounds scarier than it is, so let's soldier on.

    The Problem

    You have a form that you want to give an Id field, for whatever reason. You want your Ids to start with a particular number (we are going to start with 435, just because) and for each subsequently saved form to increment by 1. Now, if your form submits to a database, you can just use an auto incrementing Id column and sleep soundly knowing your form will get the next Id when it is submitted. You don't have a problem. But if we have a form that we save in a network folder, or submit to SharePoint, or do something else with ... well, you have a problem.

    The Solution

    Actually, a solution. There are a lot. This one involves creating a single SQL table to create and track Id numbers, and two stored procedures to help us make sure the right form gets the right Id. While I am going to add this code to the save request in the form, you can add it to another event if you prefer. We will be adding a couple of data connections to the form, so it will need domain trust, but it shouldn't require full trust.

    The Steps

    The SQL

    Create a new table in a SQL database called 'FormIdNumbers' with two columns: GUID, which will be a unique identifier, and ID, which will be an auto incrementing identity column.
    Id Table design mode

    Next, create two stored procedures. The first, called 'GenerateGUID' simply creates a new GUID we can use to uniquely identify our form while we create and retrieve its Id number:

    Create Procedure GenerateGUID
    As
    Begin
     
          Set NoCount On;
     
          Select NewID() as GUID;
     
    End

    The second procedure will take a GUID for a parameter, and insert a new row in our FormIdNumbers table:

    Create Procedure FormIdInsertRow
          @GUID uniqueidentifier
    As
    Begin

         Set implicit_transactions off     

         Insert  into FormIdNumbers (GUID)
         Values (@GUID)
    End

    The Form 

    Create a simple form with a field on it that you want to populate with your Id number. I'm just using a text field, so I don't have to fuss about with the xsi:nil attribute. You may be more particular and wish to use an int.

    First, we will add a receive data connection that we are going to modify to call our GenerateGUID sproc. Walk through the data connection wizard, and connect to the FormIdNumbers table. When you get to the screen that shows your table and columns:
    Data Connection Wizard
    click the EditSql button.

    Inside the Edit SQL field, type Execute GenerateGUID:
    Edit SQL window

    Select OK. You'll get a warning, you can select Yes on that:
    Tree view warning

    As an aside, all that means is you won't see a view of your data structure when the dialog box closes and you are back in the Data Connection Wizard.

    When you select Next in the Data Connection Wizard, you'll get another warning:
    Sproc Warning

    It is true. InfoPath does not know if the query is safe. We do, though. Select Yes again. No need to select the "Store a copy of the data in the form template" checkbox on the next screen. On the last screen, rename the data connection GenerateGUID and decide if you'd like to retrieve the data when the form opens. I'm not going to -- I'm going to run the query from my code. If you run the query on load, you won't need to run it in code, so keep that in mind later....
    GenerateGUID data connection

    Create another receive data connection in your form to the FormIdNumbers table -- this time, leave the SQL alone, so we have a normal old connection to the table. Deselect the "Automatically retrive data when the form is opened" checkbox, as we won't want the data until we've run our FormIdInsertRow sproc and modified this data connection to include a 'where' clause. Name the connection FormIdNumbers:
    FormIdNumbers data connection

    The Code 

    You need to make an architectural decision at this point. When do you want to generate the Id number? As I mentioned earlier, I plan to generate my Id and populate the Id field in the Save Request event for my form. You may wish to add a button, or put the code in a particular field's Changed event -- it is up to you.

    First I have to set my form to use custom code for its save behavior. Go to Tools > Form Options and select the "Save using custom code" checkbox:
    Form Options

    Click the Edit button to generate the event handler. You may wish to check your programming language before clicking the Edit button to generate the event handler. The following code sample is C# for the InfoPath 2007 Object Model. Replace this line in the FormEvents_Save method:

    // Write your code that will run before a Save or Save As operation here.

    With this:

                //get the value of the Id node. You'll need to replace "/my:myFields/my:IdNumber" with the XPath to your node
                XPathNavigator idNode = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:IdNumber", this.NamespaceManager);
                //if not blank, don't bother, we already have an Id
                if (idNode.Value.Equals(""))
                {
                    try
                    {
     
                        //set up the secondary data sources
                        XPathNavigator domGuid = this.DataSources["GenerateGUID"].CreateNavigator();
                        XPathNavigator domFormId = this.DataSources["FormIdNumbers"].CreateNavigator();
     
                        //set up the connections
                        AdoQueryConnection genGUID = (AdoQueryConnection)DataConnections["GenerateGUID"];
                        AdoQueryConnection formId = (AdoQueryConnection)DataConnections["FormIdNumbers"];
     
                        //query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
                        genGUID.Execute();
     
                        //get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
                        string guid = domGuid.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID", this.NamespaceManager).Value;
     
                        //set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
                        string origGuidCommand = genGUID.Command;
                        genGUID.Command = "Execute FormIdInsertRow '" + guid + "'";
                        genGUID.Execute();
     
                        //get the original command for the FormIdNumbers data connection
                        string origFormIdCommand = formId.Command;
     
                        //add the 'where' clause
                        formId.Command = origFormIdCommand + " where GUID = '" + guid + "'";
                        //execute the query
                        formId.Execute();
     
                        //set the commands back to their original states
                        genGUID.Command = origGuidCommand;
                        formId.Command = origFormIdCommand;
     
                        //set the field to the ID
                        idNode.SetValue(domFormId.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID", this.NamespaceManager).Value);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString());
                    }
                }

    Of course, if you changed the names of the data connections, stored procedures, or anything else as you walked through this, you will need to modify the code to address those changes.

    If you are using InfoPath 2003, here is a JScript version:

          //get the value of the ID node. If blank, skip
          var idNode = XDocument.DOM.selectSingleNode("/my:myFields/my:IdNumber");
          if (idNode.text == "")
          {
                try
                {
         
                      var domGuid = null;
                      var domFormId = null;
                     
                      //set up the secondary DOMs
                      domGuid = XDocument.GetDOM("GenerateGUID");
                      domGuid.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
     
                      domFormId = XDocument.GetDOM("FormIdNumbers");
                      domFormId.setProperty("SelectionNamespaces", 'xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
                     
                      //get the data objects 
                      var genGUID = XDocument.DataObjects("GenerateGUID");
                      var formId = XDocument.DataObjects("FormIdNumbers");
                     
                      //query the GenerateGUID connection once to generate the GUID. If you are retrieving this data on form open, you can skip this.
                      genGUID.Query();
                     
                      //get the GUID we just retrieved. You may want to recopy this XPath from your data source task pane....
                      var guid = domGuid.selectSingleNode("/dfs:myFields/dfs:dataFields/d:row/@GUID").text;
     
                      //set the command to execute our FormIdInsertRow sproc. Save the original command before modifying, so you can set it back after. We are passing in the GUID we created earlier as a param in our sproc. This way, when we create our new id, we know that we can retrieve it with our Guid and be secure that we have the right Id for the right form
                      var origGuidCommand = genGUID.QueryAdapter.Command;
                      genGUID.QueryAdapter.Command = "Execute FormIdInsertRow '" + guid + "'";
                      genGUID.Query();
     
                      //get the original command
                      var origFormIdCommand = formId.QueryAdapter.Command;
     
                      //add the 'where' clause
                      formId.QueryAdapter.Command = origFormIdCommand + ' where "GUID" = ' + "'" + guid + "'";
                      //execute the query
                      formId.Query();
     
                      //set the commands back to their original states
                      genGUID.QueryAdapter.Command = origGuidCommand;
                      formId.QueryAdapter.Command = origFormIdCommand;
     
                      //set the field to the ID
                      idNode.text = domFormId.selectSingleNode("/dfs:myFields/dfs:dataFields/d:FormIdNumbers/@ID").text;
                }
                catch (err)
                {
                      XDocument.UI.Alert(err.description);
                }
          }

    And now....

    The Results

    Preview your form to test it. When I save my form, since this is the first time my form will connect to the data sources I've created, I get a warning:
    Data Connection warning

    If I click Yes, my Id field will be populated before the Save Location dialog opens:
    PopulatedId

    Addendum 

    Those of you who are stored procedure savvy will recognize that if the FormIdInsertRow sproc contained a 'Select' we could return our result set with that instead of running another query.

    I ran into an exciting InfoPath feature when it comes to auto incrementing identity columns. When InfoPath uses the data connection to create the schema, it rolls back the transaction, but not the identity. This means that if I used a sproc that added my new row and returned the results so I would have my new Id number for my form, I would effectively get every other Id number. This thread on a different site describes this issue a bit further.

    Because of the double call issue, the solution above executes a sproc that takes a GUID as a parameter, then uses the same GUID to retrieve a row from our data table to get the ID number associated with that GUID.

    Originally, this blog post had an unbalanced 'Commit' statement in the suggested sproc. That was because when I modified an InfoPath receive data connection command to execute a sproc with a parameter, it only worked if the unbalanced commit was there. Jimmy Rishe saved the day (again) and found out that if we set implicit_transactions to off in our stored procedures, everything runs like NASCAR.

Copyright © 2003-2017 Qdabra Software. All rights reserved.
View our Terms of Use.