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 = 1;
sqlInfo.StatusMessage = "ID attribute empty on customer element";
sqlInfo.SQL = null;
error = true;
}
} else {
sqlInfo.Status = 1;
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 = 1;
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 = 0;
sqlInfo.StatusMessage = String.Empty;
sqlInfo.SQL = sqlStatements.ToString();
}
return sqlInfo;
} catch (Exception exp) {
sqlInfo.Status = 1;
sqlInfo.StatusMessage = exp.Message + "\n\n" + exp.StackTrace;
sqlInfo.SQL = null;
return sqlInfo;
}
finally {
if (reader != null) reader.Close();
}
}
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 = 1;
sqlInfo.StatusMessage = "ID attribute empty on customer element";
sqlInfo.SQL = null;
error = true;
}
}
else
{
sqlInfo.Status = 1;
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 = 1;
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 = 0;
sqlInfo.StatusMessage = String.Empty;
sqlInfo.SQL = sqlStatements.ToString();
}
return(sqlInfo);
} catch (Exception exp) {
sqlInfo.Status = 1;
sqlInfo.StatusMessage = exp.Message + "\n\n" + exp.StackTrace;
sqlInfo.SQL = null;
return(sqlInfo);
}
finally {
if (reader != null)
{
reader.Close();
}
}
}