pátek 14. května 2010

Submit entire InfoPath form to web service

This post explains on a simple example how to submit the entire XML of the InfoPath form to a Web Service and process it.

During my last assignment, I needed to submit the content of Repeating Table of InfoPath form to Access Database.
The form had to be published on SharePoint Site - so I could not submit directly to the database, but I was forced to use the web service (because web enabled forms do not allow access directly to the database)

So I build a web service method which allowed me to submit one row of the Repeating Table to the database and I thought that I would be able to submit each row in some kind of a loop inside the InfoPath form. Well it did not work, I was just able to submit the first row all the time and nothing more.

So I decided to submit "the entire form" and than parse it on the web service side. Here is a short example of how to do this.

Preparing the InfoPath form


First let's create simple InfoPath form with one repeating table (eg. list of products)



Now what you have to do is rename the fields in the "Data sources" tab. Later you will use these names of the fields to get the values stored XML representing the InfoPath form.


Creating the Web Service


In the web service you will need a method which takes XmlDocument and will parse this XML document representing the InfoPath form and store it's values to the database.

[WebMethod]
public void SubmitDocument(XmlDocument doc)
{           
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable); 
nsManager .AddNamespace(formNamespace, formURI);
nsManager .AddNamespace("dfs",
"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution");

XmlNode root = doc.DocumentElement;
XmlNodeList list = root.SelectNodes("/dfs:IPDocument/my:myFields/my:prodList/my:product", nsManager);

foreach (XmlNode node in list)
{
string name = node.SelectSingleNode("/dfs:IPDocument/my:myFields/my:prodList/my:product/my:name", nsManager).InnerText;
string price = node.SelectSingleNode("/dfs:IPDocument/my:myFields/my:prodList/my:product/my:price", nsManager).InnerText;
string amount = node.SelectSingleNode("/dfs:IPDocument/my:myFields/my:prodList/my:product/my:amount", nsManager).InnerText;

SubmitToDataBase(name,price, amount);
}
}


In this method first we initialize the XMLNamespaceManager. To this manager we will have to add 2 namespace. First one is the namespace of the data source of the InfoPath document. This one we can find out in InfoPath client by navigating to Properties -> Details.

Now when InfoPath submits the form to the Web Service, it adds another namespace to the document marked as: dfs with the url http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" and you need to add the namespace to your namespace manager.

nsManager .AddNamespace("dfs", "http://schemas.microsoft.com/office/infopath/2003/dataFormSolution");


Then to get value of certain field in the XmlDocument we need to know the XPath which leads directly to the desired XmlNode. We can get the XPath by the Copy XPath option, which can be found in the context menu of desired field in the "Data Sources" tab in your InfoPath client.

For example to get the "amount" XmlNode and later the string representing a number inside this node we can use the following code:

XmlNode nAmount = node.SelectSingleNode("/dfs:IPDocument/my:myFields/my:prodList/my:product/my:amount", nsManager);

int amount = Convert.ToInt32(nAmount.InnerText);
Now in the following part I will just show a simple example of method which would store some data to the Access database.

Preparing the Accesss database connection


To connect to Access database you can use the OLE DB Provider, exactly the .NET Framework OLE DB provider, in the namespace System.Data.OleDB. First you need to specify the connection string to your database. Because we are using web service it is good idea to store it in the Web.config file. Add the following to the Web.config file.




Later, already in the code of your Web Service you can prepare yourself a property which will provide you this connection string (just not to write too long lines of code).
public String ConStr
{
get { return ConfigurationManager.ConnectionStrings["myDB"].ConnectionString; }
}

The following is a simple implementation of a method which stores the data in the database. You can made this method part of your Web Service directly or build yourself some data access class.
public void SubmitToDataBase(String name, String price, String amount)
{
OleDbConnection con = new OleDbConnection(ConStr);

String cmd = "INSERT INTO products(name,price,amount)values(?,?,?)";
OleDbCommand command = new OleDbCommand(cmd, con);

OleDbParameter pName = new OleDbParameter();
pName.Value = name;
command.Parameters.Add(pName);

OleDbParameter pPrice = new OleDbParameter();
pPrice.Value = Convert.ToInt32(price);
command.Parameters.Add(pName);

OleDbParameter pAmount = new OleDbParameter();
pAmount.Value = Convert.ToInt32(amount);
command.Parameters.Add(pAmount);

con.Open();
command.ExecuteNonQuery();
con.Close();

}

There is nothing too interesting here if you are familiar with some other ADO classes. Just notice that I am using parametrized queries. The SQL command contains question marks, which are later when the actual OleDbCommand substituted with provided parameters.
That is all about the Web Service now you need to go back and configure the InfoPath form to connect to the Web Service.

Connecting the InfoPath form to the Web Service


OK now lets go back to the InfoPath form design. To submit the document to this web service you will have to add new data source select submit data -> to Web Service. Than localize you web service and find the method that, you just created and than finally in the Data Connection Wizard select submit Entire form.



Now just to give you a complete idea, here is the XML which is submitted to the web service. However if the document is saved as XML later (eg. in the SharePoint document library), the dfs namespace is not presented.

<dfs:IPDocument xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"><?mso-infoPathSolution solutionVersion="1.0.0.4" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///C:\Users\fajfr\AppData\Local\Microsoft\InfoPath\Designer2\23fae1f325544a92\manifest.xsf" ?><?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-05-18T07:21:28" xml:lang="en-us">
<my:prodList>
<my:product>
<my:name />
<my:price xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<my:amout xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
</my:product>
</my:prodList>
</my:myFields>
</dfs:IPDocument>

pondělí 3. května 2010

SharePoint and InfoPath - Error while processing the form and Schema validation error

Lately I came across these 2 following errors:

Error 1 - There has been an error while processing the form. There is an unclosed literal string

If you will come across this issue try one of following solutions(or combination):

1) You have some secondary data sources in the form which are not used. If you need them there (maybe they get use only on click of button...) than just drag and drop this data source to form and hide it.

2) Check the names of secondary data sources for special characters ( 'ěšřž...')

3) There is a KB fix which might help:

http://support.microsoft.com/kb/949752

Error 2 - Schema validation found non-data type errors

There is a solution from MS for this issue, when you are editing InfoPath form CODE:

http://blogs.msdn.com/infopath/archive/2006/11/28/the-xsi-nil-attribute.aspx

However in my case helped just setting the PreserveWhiteSpaces property to TRUE on XmlDocument:


XmlDocument lDoc = new XmlDocument();
...
lDoc.PreserveWhitespace = true;
lDoc.Save(myOutStream);

neděle 2. května 2010

Vehicle Routing Problem

One of my school assignments this semester was to implement some of the algorithms which solve the Vehicle Routing Problem.

UPDATE: I have moved the source code to GitHub. Get it or fork the project.

In VRP you have a depot and a set of customers. You have a fleet of vehicles which can serve this customers. Each customer can have an exact demand on quantity of goods and each vehicle can have capacity which is able to serve. This is easily modeled as Graph Theory Problem. The problem is NP hard. However there are several algorithms described for this problem (heuristics,genetics). To know more about variants and different solutions check this source.

I decided to implement Clarks & Wrights and Sweep algorithm. I build my solution in JAVA but later I decided that it would be cool to have some way to visualize the results. Then I decided to use GWT-MAPS API to visualize real world problems. Soon I wanted also to visualize the random generated graph problems so I included the GWT-INCUBATOR API to show some small graphs.

Check the program here

Basic functions

  • Compute the routes using Clark & Wright or Sweep algorithm
  • Generate random VRP - including distance matrix, node positions and demands of each node
  • Compute distance matrix from real world data using Google MAPS API

Basic usage
The input for this utility to compute the vehicle rows has to be specified as follows:

[route count]
[distance matrix]
[customer demand;address;x coordinate;y coordinate]


After loading the site you can see example problem of 3 customers. You can run Clark & Wright or Sweep algorithm and you will obtained the routes of the VRP in the OUTPUT window. Before running the algorithm you should set the capacity of the vehicles - in this situation all the vehicles have the same capacity. The output has the following format:

[number of routes]
[depo city1 city2 ... cityN depo]
...
[depo->city1->city2 ... cityN->depo]
...


You can visualize the output either on Graph or on MAP - if you working with real world cities and addresses than you visualize on map. If you are working just with sample data the you can visualize on the graph.


Generating distance matrix for real world cities
To generate the distance for several cities for which you know only the addresses u will enter in the INPUT box on each line one city and then click on "Generate distance matrix". Also you can see that on the MAP tab all the routes network connecting all the cities has been visualized:

image



Now you need to modify the input - more specifically modify the amounts of goods requested by each city. The DEPOT is always in the first city, so there the demand would be zero. Let's say your customer in Milano wants 15 units, in Paris 20 units and in Prague 13 units.



Now again you can just run the desired algorithm and view the results. For example after running the Clark & Wright algorithm you should get the following output:



Now you can visualize these routes in the MAP.


Generating and visualizing random problem
To generate random GRAPH VPR Problem you have to specify:

- Number of nodes.
- Constant by which the random value(0-1) would be multiplied to obtain desired amount for each city.
- Constant by which the random value(0-1) would be multiplied to obtain random coordinates of the city.

After you will obtain the desired INPUT.



Now you can run one of the algorithms(eg. SWEEP) and you will obtain the OUTPUT - the routes of the vehicle.



Now because this is randomly generated problem it is good to visualize it in the GRAPH:


THE ISSUES
The clear method of the canvas to visualize the points is not working fine - generating new VRP problem will not erase the points of the old VRP problem. The main issue is that this application uses the GWT Canvas, which was a part of GWT incubator. It also uses the Google Maps binding for GWT.

Since I have wrote the application there have been several changes in the situation about GWT and plugins:

  • Canvas is now part of the GWT 3. So theoretically the application could be rewritten using GWT 3.
  • Google Maps are now in version 2. However the GWT binding for Maps v3 has not yet been released – there are however some open-source initiatives.

So  what to do now? I have kept the code as it is: using maps v2 and the ancient Canvas project. If anyone feels motivated for moving the application to GWT 3 (changing Canvas and Maps binding) just fork it away on GitHub.

About the source code

On GitHub you can currently download two packages:

  • Java-only package. That is a basic library and executable program which can solve the VRP without the visualization.
  • GWT packages. Contains the classes which perform the VRP solving and the visualization using GWT.

The classes from the Java-only package are copied to the GWT project. That is because they have to be presented at compile time – the classes cannot be included as a JAR file. If anyone knows how to reuse a code in GWT projects without doubling it – let me know.

The  code is of a very poor quality. At time when I have wrote it I simply did not care about the readability and also I did not know Java that well.