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
