Exemple #1
0
        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);
        }
Exemple #2
0
        // 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");
        }
Exemple #3
0
        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();
                }
            }
        }