Since persisting changes made to a secondary data source
means updating the data store underlying the secondary data source, you can
write code within an ASP.NET page to save changes back to the database table
pertaining to the secondary data source.
Open Microsoft Visual Studio and create a new web site. Make
sure that the web site is located at the URL you specified in the calls to the
web server in the InfoPath form; in our case, http://localhost/UpdateDSwithAjax/Default.aspx
would be the full URL to the ASP.NET page performing the updates to the
underlying data of the secondary data source.
Delete the HTML content that Microsoft Visual Studio adds
for you by default from within the Default.aspx page.
Open the code-behind file for the Default.aspx page
and add a using statement for the System.Data.SqlClient
namespace. Then add the code listed in Listing 4 to the Page_Load
event handler for the web page. Do not forget to change the SQL
connection string to match the settings for your own SQL Server
database.
Listing 4
- Code in the Page_Load event to update the
database table
Response.ContentType = "text/xml";
string paramValue = Request.Form["q"];
string action = Request.Form["a"];
using (SqlConnection conn = new SqlConnection(
"Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=True"))
{
if (action != null && action != string.Empty
&& paramValue != null && paramValue != string.Empty)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
switch (action)
{
case "a":
cmd.CommandText = "INSERT INTO Properties (Name) VALUES (@Name)";
cmd.Parameters.Add ("@Name", paramValue);
break;
case "d":
cmd.CommandText = "DELETE FROM Properties WHERE Value = @Value";
cmd.Parameters.Add ("@Value", paramValue);
break;
default:
break;
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
string result = "<?xml version=\"1.0\" ?><result/>";
Response.Write(result);
First you set the content type of the web page to text/xml so that the web page can return XML data to your
InfoPath form. You are not going to do anything with this XML, but if you
wanted to you could use it to dynamically populate controls on the InfoPath form
or the entire form itself. The latter is beyond the scope of this article.
Response.ContentType = "text/xml";
Next, you retrieve the parameters that were passed to the
web page.
string paramValue = Request.Form["q"];
string action = Request.Form["a"];
You then create a connection to the server and database
where the underlying store of the secondary data source is located-
SqlConnection conn = new SqlConnection(
"Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=True")
and based on the action requested (an addition or deletion
of an item) generate an INSERT or DELETE statement to execute.
switch (action)
{
case "a":
cmd.CommandText = "INSERT INTO Properties (Name) VALUES (@Name)";
cmd.Parameters.Add ("@Name", paramValue);
break;
case "d":
cmd.CommandText = "DELETE FROM Properties WHERE Value = @Value";
cmd.Parameters.Add ("@Value", paramValue);
break;
default:
break;
}You then open the connection to the database, execute the
query, and close the connection.
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
The web page then returns an XML string to the caller, your
InfoPath form, to indicate that the update has been performed.
string result = "<?xml version=\"1.0\" ?><result/>";
Response.Write(result);
The InfoPath form will receive this XML string in its onReadyState() event handler and can use it as a trigger to
refresh the list of items in the drop-down list box that is bound to the
secondary data source.
Tip: Test whether the web page is
working before calling it from the InfoPath form. You will need to set proper security
permissions on the database and Properties table for
the identity under which the web site is running.
