Professional InfoPath 2003 [Electronic resources] نسخه متنی

Saving the Stories

Once the meta data of the database is defined, you can write the code to save all stories inside it. As for the database choices, you have several strategies to save XML data into the database. You can use a SQL Server stored procedure that gets the XML document to do everything using OPENXML rowset provider, or you can write all code using ADO.

For portability reasons, we would adopt a saving strategy that is independent of the database server. Then, all code will be written in InfoPath using ADO components. Before you proceed writing code, you have to consider two factors:

Atomicity

Double insert

Suppose that the editor is reviewing five stories and decides to save them on his company database storage. For some reason the third story raises an exception, and the editor tries saving again. If you don’t manage that kind of issue, you could find in your database seven stories instead of five, since in the first try, two stories were saved successfully and in the second try all five stories were saved again. To solve this kind of issue, you have to save in a transactional (atomic) connection that guarantees that if one error is raised for any reason, no records are saved to the database. To inhibit a double insert of the same story, before you save, you have to verify that the story isn’t already present in the database. If it is, you have to update it.

The saving process is then organized in the manner shown in Figure 16-2.

Figure 16-2: Saving process.

Loading and Saving the Stories

The first step of the saving process is to load all stories entered and save them one by one:

function Save()
{
 var metaNodes = XDocument.DOM.selectNodes("//meta");
 for(var i = 0; i < metaNodes.length; i++)
  SaveSingleStory(metaNodes.item(i));
}

SaveSingleStory is the function that saves a single meta element into the database. Since we must work on a single atomic transaction, we have to open the connection before the cycle starts and close it after all stories are saved:

function Save()
{
 var adoCn = null;
 var metaNodes = XDocument.DOM.selectNodes("//meta");
 
 // Check the node precence
 if(metaNodes.length > 1)
  return;
 
 try
 {
  // Connection string
  var strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" +
     "Persist Security Info=False;Initial Catalog=NewsLine;Data Source=.";
  adoCn = new ActiveXObject("ADODB.Connection");
  
  adoCn.Open(strConn);
  adoCn.BeginTrans();
  
  for(var i = 0; i < metaNodes.length; i++)
   SaveSingleStory(metaNodes.item(i), adoCn);
   
  adoCn.CommitTrans();
 }
 catch(e)
 {
  XDocument.UI.Alert("An error occurred during the saving.");
  if(adoCn != null && adoCn.State == 1)
  {
   adoCn.RollbackTrans();
   adoCn.Close();
  }
 }
}

When the connection is opened, a new transaction starts. Then, all database operations done with that connection object are under the same transaction. If one of the operations fails, the rollback is invoked and no operations are persisted. If all saving proceeds correctly, the transaction is then committed.

Saving a Story

SaveSingleStory is the function that saves a single story into the database. Before that, you must collect all indexed values with the correct identifier:

function SaveSingleStory(meta, cn)
{
 var metaID = meta.selectSingleNode("@id").text;
 var creatorID = meta.selectSingleNode("creator/@userID").text;
 var status = meta.selectSingleNode("status").text;
 var category = meta.selectSingleNode("category/@name").text;
 var pubTime = meta.selectSingleNode("publicationTime");
 var type = meta.selectSingleNode("type/@type").text;
 
 // code continue...
}

If all values are valid, you can proceed in getting the correct lookup IDs. To do so, you have to implement some functions that get the id from the database. For example, to get the id of the status, you can query the Status table as follows:

function getStatusID(status)
{
 var adoCn = null;
 var id = 0;
 try
 {
  // Connection string
  var strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" +
     "Persist Security Info=False;Initial Catalog=NewsLine;Data Source=.";
  adoCn = new ActiveXObject("ADODB.Connection");
  var adoCmd = new ActiveXObject("ADODB.Command");
  
  adoCmd.CommandText = "SELECT StatusID FROM Status WHERE Status = ‘" + status + "‘";
  adoCmd.CommandType = 1; //adCmdText
  
  adoCn.Open(strConn);
  adoCmd.ActiveConnection = adoCn;
  
  var rs = adoCmd.Execute();
  rs.MoveFirst();
  id = rs("StatusID");
 }
 catch(e)
 {
  if(adoCn != null && adoCn.State == 1)
   adoCn.Close();
  throw e;
 }
 
 return id;
}

By the way, you can ensure that all lookup IDs query the lookup data tables the same way. Once you have collected all IDs, you can check to see whether or not the meta data has been saved already:

function IsMetaPresent(metaID, cn)
{
 var present = false;
 
 try
 {
  var adoCmd = new ActiveXObject("ADODB.Command");
  
  adoCmd.CommandText = "SELECT COUNT(MetaID) AS NR FROM Meta WHERE MetaID = ‘" +
      metaID + "‘";
  adoCmd.CommandType = 1; //adCmdText
  adoCmd.ActiveConnection = cn;
  
  var rs = adoCmd.Execute();
  rs.MoveFirst();
  if(rs("NR") > 0)
   present = true;
 }
 catch(e)
 {
  throw e;
 }
 
 return present;
}

If the preceding function returns false, that meta ID isn’t present in the data table and you can save it. Otherwise, you can only update the record with new information. The new story is inserted into the meta table with an insert SQL statement:

function NewMeta(metaID, contributorID, statusID, categoryID, subjectTypeID, 
    subjectNameID, publicationTime, resourceTypeID, meta, cn)
{
 try
 {
  var adoCmd = new ActiveXObject("ADODB.Command");
  
  adoCmd.CommandText = "INSERT INTO Meta VALUES( ‘" + metaID + "‘, " +
   contributorID + "," + statusID + "," + categoryID + "," + subjectTypeID + "," +
   subjectNameID + ",’" + publicationTime + "‘," + resourceTypeID + ",’" + meta  + "‘)";
  adoCmd.CommandType = 1; //adCmdText
  adoCmd.ActiveConnection = cn;
  
  adoCmd.Execute();
 }
 catch(e)
 {
  throw e;
 }
}

If the story is already present, you have to update the record content:

function UpdateMeta(metaID, contributorID, statusID, categoryID, subjectTypeID, 
    subjectNameID, publicationTime, resourceTypeID, meta, cn)
{
 try
 {
  var adoCmd = new ActiveXObject("ADODB.Command");
  
  adoCmd.CommandText = "UPDATE Meta SET ContributorID = " +
   contributorID + ", StatusID = " + statusID + ", CategoryID = " + categoryID + 
   ", SubjectTypeID = " + subjectTypeID + ", SubjectNameID = " +
   subjectNameID + ", PublicationTime = ‘" + publicationTime + "‘, ResourceTypeID = " + 
   resourceTypeID + ", WholeStory = ‘" + meta  + "‘" +
   " WHERE MetaID = ‘" + metaID + "‘";
  adoCmd.CommandType = 1; //adCmdText
  adoCmd.ActiveConnection = cn;
  
  adoCmd.Execute();
 }
 catch(e)
 {
  throw e;
 }
}

As a final step, you can complete the SaveSingleStory function:

function SaveSingleStory(meta, cn)
{
 var metaID = meta.selectSingleNode("@id").text;
 var creatorID = meta.selectSingleNode("creator/@userID").text;
 var status = meta.selectSingleNode("status").text;
 var category = meta.selectSingleNode("category/@name").text;
 var pubTime = meta.selectSingleNode("publicationTime");
 var type = meta.selectSingleNode("type/@type").text;
 
 var statusID = getStatusID(status);
 var contributorID = getContributorID(creatorID);
 var categoryID = getStatusID(category);
 var subjectTypeID = getStatusID(subjectType);
 var subjectNameID = getStatusID(subjectName);
 var resourceTypeID = getResourceType(resourceType);
 
 if(IsMetaPresent(metaID, cn))
  UpdateMeta(metaID, contributorID, statusID, categoryID, subjectTypeID, 
     subjectNameID, pubTime, resourceTypeID, meta.xml, cn);
 else
  NewMeta(metaID, contributorID, statusID, categoryID, subjectTypeID, 
     subjectNameID, pubTime, resourceTypeID, meta.xml, cn);
}