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 previouslywritten 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 thepreviously 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s