Updating a Secondary Data Source in InfoPath Using AJAX
page 5 of 6
by S.Y.M. Wong-A-Ton
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 42935/ 171

Creating an ASP.NET Page to Update the Secondary Data Source

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.


View Entire Article

User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-08-18 10:15:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search