From InfoPath to Database via Web Service


I have been asked to build this kind of example sooooo many times that now I really need to write this down 🙂 This stuff isn't rocket science but I have got so many emails about this so I just want to answer all those questions at once.

So I have previously written about getting data from web service to the InfoPath. Now I'm going to show you how can you create web service that stores the data from InfoPath to the database. Nothing fancy but just to give you some starting points if your planning to do this.

I'm not going to create new InfoPath form for this so I'll just re-use the previously created example.

I just added new method to my web service to handle insert of new employee:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[WebMethod]
public int AddNewEmployee(Employee employee)
{
  int rowsAffected = 0;
  using (SqlConnection conn = new SqlConnection("..."))
  {
    SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
      "(EmployeeNumber, Title, FirstName, LastName, Salary) " +
      "VALUES(@EmployeeNumber, @Title, @FirstName, @LastName, @Salary)", conn);

    cmd.Parameters.AddWithValue("@EmployeeNumber", employee.EmployeeNumber);
    cmd.Parameters.AddWithValue("@Title", employee.Title);
    cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
    cmd.Parameters.AddWithValue("@LastName", employee.LastName);
    cmd.Parameters.AddWithValue("@Salary", employee.Salary);

    conn.Open();
    rowsAffected = cmd.ExecuteNonQuery();
  }

  return rowsAffected;
}

It accepts the previously defined struct as parameter and it then just puts it into the database. It can't be any simpler right 🙂 Just add your own connections string and create new database+table and you're good to go.

In my InfoPath form I change the label of button from Get employee data to Insert new employee. Then I added new web service of type "Retrieve data"... and not type "Submit data". Why? Well because "Retrieve data" has more ways to modify the parameters. I think that "Submit data" type is quite limited on that.

Of course I also needed to change the rule of the button too:

Notice that last action is Show dialog box expression.. I just that to display the return value but of course that isn't necessary (and it's not supported by InfoPath Form Services anyway).

Now I'm ready to take this for a test spin:


Pressing button will then give me this dialog:

And if I go to the database I'll see something like this:

Database verifies our story. So 1 new row with the data that I have typed into the InfoPath form has come up...

This was quick intro how you can create whole chain from InfoPath to web service and then to database.

Anyways... Happy hacking!

J

Comments (17)

  1. The latest news and gossip from SharePoint-Land 🙂 OT aber eigentlich doch nicht Microsoft kauft sich

  2. Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  3. Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  4. link says:

    question,

    I currently testing the blog with the Expense Report form template that comes with infopath.

    In the form there is a repeating section item whats the best way to submit this using webservice?

    do you need to make an new webservice to submit this or can you do this with the,

    [WebMethod(Description="Submit an Expense Report form here")]

    public void PostExpenseReport(InfoPathService.expenseReport expenses)

    {

    }

  5. Matt Faus says:

    Hi Janne,

    This is a cool example, and something that is surely done a lot in the InfoPath development community.  My company does tons of InfoPath consulting and we had requests to go to the database from InfoPath so much that we just made a generic web service that abstracts mapping the XML into SQL via a graphical mapping tool.  The web service is static, and you just use our admin tool to define mappings for each new form template that you create.  This eliminates the need to constantly change the web service for every form template, and it works quite well.  Like I said, we’ve been using it on VERY large InfoPath projects (i.e. tens of thousands of users submitting forms every day) and it works great!!

    Besides the core mapping functionality, there are also a load of other features such as Active Directory integration, and querying, sorting, and filtering data out of the database to populate drop-downs in the UI.

    Anyway, check it out here: http://www.qdabra.com/proddetail.asp?prod=QDBXL1

    Drop me a line via the Contact Us page on the site, or on the forums at InfoPathDev.com.  Thanks!

    Matt Faus

    Qdabra Software

  6. If you have following setup… You need to create InfoPath Form that you’re going to use in Forms Server

  7. If you have following setup… You need to create InfoPath Form that you're going to use in Forms

  8. Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  9. Nilesh B says:

    Steps written here are very good and working nicely, but i want to get data from database instead of hard coded value.

    how to do that?

    will you please explain this?

  10. sweth says:

    Hi there,

    I have a problem concerning Submitting to a SQL Database (via Web Services) in InfoPath web-enabled forms.

    I already followed the instructions of the following TechArticle .

    http://msdn2.microsoft.com/en-us/library/aa192516(office.11).aspx

    I am using Sql 2005 VS2005 and Infopath 2007. I created a webservice

    connecting to a database table of the Sql 2005 server. I created a web-

    enabled form in InfoPath to submit and receive data to this webservice.

    If I try to use this form, I am able to query the database and it shows me content of the table. But I am not able to submit data to sql. If I hit submit, Infopath confirms the submission successful but sql doesn’t get any data. It seems, that the sqldataadapter.update command does not really update the table.

    When I create the same form with a database connection instead of a webservice connection, receiving AND submitting data works fine and sql table is updating or inserting new data.

    I need this working real bad. Pls Help.

    Swetha

  11. Pepe says:

    Dear Janne,

    This is an excellent tutorial.  I am new creating web service applications. In fact, this is my first attempt. I was wondering if you could provide me a little bit more detailed explanation on where the "connections string". This is the one I found:

    private const string _ADOConnectionString =

           @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhostsqlexpress;Initial Catalog=WhitePaperSamples";

    I amn’t being able to see my SQL fields’ values to set them equal to my InfoPath’s fields values. Thank you!

  12. Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  13. pench says:

    Hello,

    I am posting a little bit late..

    I want to do exactly the same thing : inserting date into oracle from infopath but using user defined type. It means complex type that you are creating in one hand as a type in visual studio, and on the other hand as a type (or object ) in oracle.

    In fact, to talk on your example, I have a repeating table of employees, and i want to insert into Oracle database but only calling the inserted sql once, that means by using a stored procedure. The store procedure would have as parameter an array of Employee..

    Could you help?

    Thanks

  14. Angel says:

    Thanks man,  This  was  really helpful

  15. George says:

    Hi there,

    I follow these instruction but I met to one error when I want to submit the data to database via webservice. That error is 'The query cannot be run for the following DataObject: AddNewEmployee

    InfoPath cannot run the specified query.

    Access is denied.

    'Can Someone help me??

Skip to main content