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:

 12345678910111213141516171819202122
 
 [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

  • Anonymous
    October 25, 2007
    PingBack from http://www.soundpages.net/computers/?p=4662

  • Anonymous
    October 25, 2007
    http://wffrdwbfasqfs.host.com <a href="http://wffddwbfasqfs.host.com">desk3</a>

  • Anonymous
    October 29, 2007
    The latest news and gossip from SharePoint-Land :-) OT aber eigentlich doch nicht Microsoft kauft sich

  • Anonymous
    November 07, 2007
    Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  • Anonymous
    November 07, 2007
    Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  • Anonymous
    November 14, 2007
    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) { }

  • Anonymous
    November 21, 2007
    The comment has been removed

  • Anonymous
    December 10, 2007
    If you have following setup... You need to create InfoPath Form that you're going to use in Forms Server

  • Anonymous
    December 10, 2007
    If you have following setup... You need to create InfoPath Form that you&#39;re going to use in Forms

  • Anonymous
    January 09, 2008
    Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  • Anonymous
    March 06, 2008
    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?

  • Anonymous
    June 26, 2008
    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

  • Anonymous
    July 29, 2008
    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!

  • Anonymous
    June 08, 2009
    Direkter Download: SPPD-080-2007-11-08 Aktuell E-Mail Records Retention in SharePoint Server 2007 MSDN

  • Anonymous
    September 28, 2009
    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

  • Anonymous
    October 07, 2010
    Thanks man,  This  was  really helpful

  • Anonymous
    November 10, 2010
    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??