public SQLInfo ExecuteNonQuery(string sql)
{
string connStr = ConfigurationManager.AppSettings["DBConnectionString"];
SqlConnection conn = null;
SQLInfo info = new SQLInfo();
try {
conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
info.Status = true;
}
catch (Exception exp) {
info.Status = false;
info.StatusMessage = exp.Message;
}
finally {
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return(info);
}
// Event Handler
public void File_OnChanged(object source, FileSystemEventArgs e)
{
//Pause to allow file handle to be cleaned up
System.Threading.Thread.Sleep(1000);
string filePath = e.FullPath;
//*** Validate XML document against schema
//Get schema document path
string schemaPath = ConfigurationManager.AppSettings["XmlSchemaFile"];
if (schemaPath != null)
{
//XML doc should be validated against schema
//Create XmlValidator object
XmlValidator validator = new XmlValidator();
//Build schema collection
XmlSchemaSet schemaCol = new XmlSchemaSet();
schemaCol.Add(null, schemaPath);
//Get error log file
string logFile = ConfigurationManager.AppSettings["XmlValidationErrorLogFile"];
//Validate Document
ValidationStatus validationStatus =
validator.ValidateXml <string>(filePath,
schemaCol, null, ((logFile != null)?logFile:null));
//Check validation status
if (!validationStatus.Status)
{
//Log that errors occurred during validation and
//stop processing on this document
this.WriteToLog("Validation of " + filePath + " failed. " +
"See error log file for more details.");
File.Move(filePath, filePath + "." + Guid.NewGuid().ToString() + ".notValid");
return;
}
else //Validation successful
{
this.WriteToLog(filePath + " validated successfully. ");
}
}
//Call XML import object to move XML into db
SQLGenerator gen = new SQLGenerator();
SQLInfo info = gen.CreateSQLStatement(filePath);
if (info.Status)
{
WriteToLog(filePath + " parsed successfully!");
//Execute SQL statement against database
SQLInfo dbInfo = gen.ExecuteNonQuery(info.SQL);
if (dbInfo.Status)
{
WriteToLog(filePath + " data updated successfully in database!");
}
else
{
WriteToLog(filePath + " not updated in db successfully. Error: " +
dbInfo.StatusMessage);
}
}
else
{
WriteToLog(filePath + " not parsed successfully. Error: " +
info.StatusMessage);
}
//Rename the file
File.Move(filePath, filePath + "." + Guid.NewGuid().ToString() + ".old");
}
public SQLInfo CreateSQLStatement(string xmlPath)
{
XmlTextReader reader = null;
Hashtable fieldNamesValues = new Hashtable();
StringBuilder sqlStatements = new StringBuilder();
bool error = false;
//Create Return Object
SQLInfo sqlInfo = new SQLInfo();
try {
reader = new XmlTextReader(xmlPath);
//Read through the XML stream and find proper tokens
while (reader.Read())
{
if (!error) //Stop parsing if problem is encountered
{
if (reader.NodeType == XmlNodeType.Element)
{
//Get the name of the XML token
switch (reader.Name.ToLower())
{
case "customer":
if (reader.HasAttributes)
{
string customerID = reader.GetAttribute("id");
if (customerID != String.Empty)
{
fieldNamesValues.Add("CustomerID", "'" + customerID + "'");
}
else
{
sqlInfo.Status = false;
sqlInfo.StatusMessage = "ID attribute empty on customer element";
sqlInfo.SQL = null;
error = true;
}
}
else
{
sqlInfo.Status = false;
sqlInfo.StatusMessage = "No attributes on customer element";
sqlInfo.SQL = null;
error = true;
}
break;
case "companyname":
string companyName = reader.ReadString();
if (companyName != String.Empty)
{
fieldNamesValues.Add("CompanyName", "'" + companyName + "'");
}
else
{
sqlInfo.Status = false;
sqlInfo.StatusMessage = "CompanyName element is empty.";
sqlInfo.SQL = null;
error = true;
}
break;
case "contactname":
if (reader.HasAttributes)
{
fieldNamesValues.Add("ContactName", "'" + reader.GetAttribute("name") + "'");
fieldNamesValues.Add("ContactTitle", "'" + reader.GetAttribute("title") + "'");
}
break;
case "address":
if (reader.HasAttributes)
{
fieldNamesValues.Add("Address", "'" + reader.GetAttribute("street") + "'");
fieldNamesValues.Add("City", "'" + reader.GetAttribute("city") + "'");
fieldNamesValues.Add("Region", "'" + reader.GetAttribute("state") + "'");
fieldNamesValues.Add("PostalCode", "'" + reader.GetAttribute("zip") + "'");
fieldNamesValues.Add("Country", "'" + reader.GetAttribute("country") + "'");
}
break;
case "busphone":
if (reader.HasAttributes)
{
fieldNamesValues.Add("Phone", "'" + reader.GetAttribute("busLine") + "'");
}
break;
case "busfax":
if (reader.HasAttributes)
{
fieldNamesValues.Add("Fax", "'" + reader.GetAttribute("busLine") + "'");
}
break;
} //switch
} //XmlNodeType check
}
else
{
break;
}
if (reader.NodeType == XmlNodeType.EndElement)
{
if (reader.Name.ToLower() == "customer")
{
string[] FVArray = AddSeparator(fieldNamesValues, ',');
string fields = FVArray[0];
string fieldVals = FVArray[1];
sqlStatements.Append("INSERT INTO Customers (" + fields + ") VALUES (" + fieldVals + ");");
//Clear out ArrayLists to handle multiple XML records
fieldNamesValues.Clear();
}
}
} //End While
if (!error)
{
sqlInfo.Status = true;
sqlInfo.StatusMessage = String.Empty;
sqlInfo.SQL = sqlStatements.ToString();
}
return(sqlInfo);
} catch (Exception exp) {
sqlInfo.Status = false;
sqlInfo.StatusMessage = exp.Message + "\n\n" + exp.StackTrace;
sqlInfo.SQL = null;
return(sqlInfo);
}
finally {
if (reader != null)
{
reader.Close();
}
}
}