InfoPath and Yukon: The Details

InfoPath and Yukon: The Details

In a recent post, I touched upon the reasons why you might want to go with SQL Server XML columns as the storage for your InfoPath forms. In this article, we'll talk about actually making it work.

InfoPath's strength is working with XML data sources: web services. In the implementation below, we'll write a simple web service that will help us retrieve and submit the data to Yukon.

Scenario

Build a resume database for the HR department. We want to store highly structured resumes in a database. Highly structured here is the opposite of freeform: a resume in our scenario isn't a blob of text; we have the graduation date, employment dates, actual titles and other things parsed into data structures.

 

Data Structure

We will store candidate records as XML blobs in a Yukon XML column. Each resume will be stored as a separate record; each record will also have a primary key - a JobCandidateID - to simplify our development. We'll define only one table, called JobCandidate:

Populate the table with a few Resume XML files that all satisfy the same schema. Store the schema somewhere where the web server can access it.

 

Core Implementation in English: we'll write a web service as a middle tier between SQL Server 2005 and the InfoPath form. This web service will have two methods:

1) GetCandidateRecord: given a JobCandidateID (an integer), return a DataSet (for our purposes, an XSD-typed XML blob) that contains the candidate record. Give me the job candidate ID, I'll give you his resume.

2) UpdateCandidateRecord: take a JobCandidateID (an integer) and a Resume (an XML document), and update the resume of the candidate with that particular ID to the resume passed in as a second parameter. Nothing fancy, really.

 

Core Implementation in C#

I promised you two methods, here they are. First, GetCandidateRecord.

[WebMethod]
public DataSet GetCandidateRecord(int JobCandidateID)
{
    DataSet result = null;

    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        SqlCommand command = conn.CreateCommand();
        command.CommandText = @"
            SELECT Resume
            FROM " + tableName + @"
            WHERE JobCandidateID = @x";           
        command.Parameters.Add("@x", SqlDbType.Int);
        command.Parameters[0].Value = JobCandidateID;
        SqlDataReader reader = command.ExecuteReader();

        if (reader.Read())
        {
            DataSet ds = new DataSet();
            ds.ReadXmlSchema(@"C:\Inetpub\wwwroot\infopath_yukon\Resume.xsd");
            XmlDataDocument xd = new XmlDataDocument(ds);
            xd.Load(new StringReader((string)reader.GetValue(0)));
            result = xd.DataSet;
        }
        conn.Close();
        return result;
    }
}

Things are fairly straightforward here:

- Open a SqlConnection using ASP.NET credentials (make sure the ASPNET user has read/write rights to the database).

- Build a simple SELECT statement to return a resume. Recall that the resume is just an XML document stored as-is in the database.

- Cast the resume dataset into a typed dataset by applying a schema stored somewhere on the web server. Oh, I forgot to tell you - you need a schema :-). Why? InfoPath form needs to know what to expect from the web service, and while InfoPath can infer the shape of the data from the instance, this method is very much error prone. For example, how can InfoPath know of a repeating structure if only one instance was present in a sample XML document? How about choice or optional structures? Because of all of these reasons, you need to provide a typed dataset through your web service.

- Return the typed dataset for the Resume record.

 

Next, let's look at UpdateCandidateRecord.

[WebMethod]
public void UpdateCandidateRecord(XmlDocument xml, int JobCandidateID)
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        SqlCommand command = conn.CreateCommand();

        command.CommandText = @"
            UPDATE " + tableName + @"
            SET Resume = @x
            WHERE JobCandidateID = @y";
        command.Parameters.Add("@x", SqlDbType.Xml);
        command.Parameters[0].Value = xml.InnerXml.ToString();
        command.Parameters.Add("@y", SqlDbType.Int);
        command.Parameters[1].Value = JobCandidateID;
        command.ExecuteNonQuery();
        conn.Close();
    }
}

- Open a SqlConnection

- Build a simple UPDATE statement to save the resume for a given candidate. Note that you must use SqlCommand Parameters: just concatenating the XML blob won't do.

- Execute the UPDATE statement. Note that we are replacing the entire resume with the new one; no partial updates are done. This means that simultaneous editing of Resume records won't be possible.

 

Basic Form Template

Now that the web service is set up, we can easily build a form template based on it. The template may or may not be browser-enabled; the method described here works with both. Just launch InfoPath designer, and pick "start from web service" as your data source. Specify GetCandidateRecord as the "receive" piece of the web service, and UpdateCandidateRecord as the submit part.

InfoPath will ask you for sample JobCandidateID values for the receive web service; since our database already has a few Resumes, we can type in the JobCandidateID for one of them. You may be wondering - wait, I thought InfoPath won't do the schema inference by example! It won't - the dataset returned by your web service will contain a schema (that's why we called DataSet.ReadXmlSchema() in GetCandidateRecord), and InfoPath will use that schema to build your data source tree.

After you've gone through the initial data connection setup, you'll notice that your main data source tree is correctly populated with the data types from your schema. Complex structures should show up just fine - repeating, optional, choice structures, non-string datatypes, XSD validation... And the Submit button should be configured to save the modified Resumes back to SQL Server.

FAQ

1. Why do we have to use a custom web service, and not built-in Yukon web services?
There are unfortunate technical limitations that currently require you to write a custom web service to work with SQL Server 2005 in a manner described above. The web service is, as you saw, very easy; we know that this is something that can be made better, and will consider addressing this in future versions of InfoPath and SQL Server.

2. Why not XSD-typed XML columns?
When InfoPath submits datasets to the web service, it adds dataset tracking information; while you can add optional attributes to your InfoPath-generated schema and upload it to Yukon, this would complicate maintenance quite a bit.

3. What other resources are available on the topic?
Be sure to check out this article by S.Y.M. Wong-A-Ton.

Alex Weinstein
Program Manager

Filed under: Writing Code, Data Connections轉：http://blogs.msdn.com/infopath/archive/2007/02/05/infopath-and-yukon-the-details.aspx