private XmlDocument SqlVerification(DataPool pool)
{
Annotation verify = pool.RootNode.Annotations[ScriptingUtilities.SqlVerifyAnnotationName];
if (verify != null) // is sql verification demanded?
{
pool.RootNode.Annotations.Remove(verify); // reset annotation, it should be done only for this step
SqlVerificationSepc spec = specs.Where(n => n.Name == verify.Value).FirstOrDefault();
if (spec == null) // do we have this SqlSpec not yet in cache?
{
// Read SqlSpec from annotation
Annotation specsAnnotation = pool.RootNode.Annotations[ScriptingUtilities.SqlVerificationSpecsAnnotationName];
if (specsAnnotation == null)
{
throw new Exception("No SqlVerificationSpecs found");
}
specs = SIEESerializer.StringToObject(specsAnnotation.Value) as List <SqlVerificationSepc>;
spec = specs.Where(n => n.Name == verify.Value).FirstOrDefault();
if (spec == null)
{
throw new Exception("SqlVerificationSpec " + verify.Value + " not found");
}
}
sqlVerify(pool, spec); // do it...
}
return(pool.XmlDocument);
}
// Add an Sql verification specification to the annotation at the batch.
public static void AddSqlVerificationSpec(DataPool pool, SqlVerificationSepc spec)
{
// Read existing annotation from batch or create new one
List <SqlVerificationSepc> specs;
Annotation SqlVerificationSpecAnnotation = pool.RootNode.Annotations[SqlVerificationSpecsAnnotationName];
if (SqlVerificationSpecAnnotation == null)
{
specs = new List <SqlVerificationSepc>();
}
else
{
specs = SIEESerializer.StringToObject(SqlVerificationSpecAnnotation.Value) as List <SqlVerificationSepc>;
}
// If this annotation does not already exists add the current one
if (specs.Where(n => n.Name == spec.Name).Count() == 0)
{
specs.Add(spec);
// Create new annotation if none had been there
if (SqlVerificationSpecAnnotation == null)
{
SqlVerificationSpecAnnotation = new Annotation(pool, SqlVerificationSpecsAnnotationName);
pool.RootNode.Annotations.Add(SqlVerificationSpecAnnotation);
}
// Store all SqlSpecs at the annotation
SqlVerificationSpecAnnotation.Value = SIEESerializer.ObjectToString(specs);
}
}
private void sqlVerify(DataPool pool, SqlVerificationSepc spec)
{
// Do verification for all documents if document class matches.
spec.TableSpec.Connect();
foreach (Document doc in pool.RootNode.Documents)
{
if (doc.Name == spec.Classname)
{
sqlVerify(doc, spec);
}
}
}
private void sqlVerify(Document doc, SqlVerificationSepc spec)
{
// Create the Sql statement
string tmp = string.Empty;
foreach (SqlFieldMapping fh in spec.FieldMap)
{
if (tmp != string.Empty)
{
tmp += ", ";
}
tmp += ecscapeQuoteCharacters(fh.Column);
}
string sqlString = "Select " + tmp + " From " + spec.TableSpec.Table + " Where ";
tmp = string.Empty;
foreach (SqlFieldMapping fh in spec.FieldMap)
{
string fieldValue = doc.Fields[fh.OCCField].Value;
if (string.IsNullOrEmpty(fieldValue))
{
continue;
}
if (tmp != string.Empty)
{
tmp += " AND ";
}
tmp +=
ecscapeQuoteCharacters(fh.Column) + " = '" +
ecscapeQuoteCharacters(fieldValue) + "'";
}
sqlString += tmp;
// Shoot sql statement off, leave confirmation field in case of error
bool success = spec.TableSpec.Read(sqlString, spec.FieldMap);
// Transfer results to data pool
foreach (SqlFieldMapping fh in spec.FieldMap)
{
if (success)
{
doc.Fields[fh.OCCField].Value = fh.Value;
doc.Fields[fh.OCCField].State = DataState.Ok;
}
else
{
doc.Fields[fh.OCCField].State = DataState.Error;
}
}
}
