/// <summary> /// Clean up all the data for categorization and content filter. /// </summary> private static void cleanupAllData(ISACFDataContext db) { //ISACFDataContext db = new ISACFDataContext(); object[] fakeParam = new object[0]; if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Closed) { db.Connection.Open(); } db.ExecuteCommand("TRUNCATE TABLE ccfDatosGen", fakeParam); ForeignKeys[] foreignKeys = new ForeignKeys[4]; foreignKeys[0].foreignKeyName = "FK_ccfCategoryName_es_ccfCategory"; foreignKeys[0].tableName = "ccfCategoryName_es"; foreignKeys[1].foreignKeyName = "FK_ccfDomain_ccfCategory"; foreignKeys[1].tableName = "ccfDomain"; foreignKeys[2].foreignKeyName = "FK_ccfUrl_ccfCategory"; foreignKeys[2].tableName = "ccfUrl"; foreignKeys[3].foreignKeyName = "FK_ccfIPv4_ccfCategory"; foreignKeys[3].tableName = "ccfIPv4"; for (int i = 0; i < foreignKeys.Length; ++i) { db.ExecuteCommand(string.Format("TRUNCATE TABLE {0}", foreignKeys[i].tableName), fakeParam); } // DROP Foreign Keys Constraints for (int i = 0; i < foreignKeys.Length; ++i) { string dropFKCommand = string.Format("IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[{0}]') AND parent_object_id = OBJECT_ID(N'[{1}]')) " + "ALTER TABLE [{1}] DROP CONSTRAINT [{0}]", foreignKeys[i].foreignKeyName, foreignKeys[i].tableName); db.ExecuteCommand(dropFKCommand, fakeParam); } // Now we can TRUNCATE the "ccfCategory" table. db.ExecuteCommand("TRUNCATE TABLE ccfCategory", fakeParam); // CREATE Foreign Keys Constraints for (int i = 0; i < foreignKeys.Length; ++i) { string dropFKCommand = string.Format("ALTER TABLE [{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) " + "REFERENCES [{3}] ([ID]) " + "ON DELETE CASCADE " + "ALTER TABLE [{0}] CHECK CONSTRAINT [{1}]", foreignKeys[i].tableName, foreignKeys[i].foreignKeyName, "id_Category", "ccfCategory"); db.ExecuteCommand(dropFKCommand, fakeParam); } fakeParam = null; if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); db = null; } GC.Collect(); return; }
/// <summary> /// Update the general data: dateCreated, source. /// </summary> private static void updateDatosGen(ISACFDataContext db) { XDocument xDocumentIndex; ccfDatosGen dbDatosGen = new ccfDatosGen(); FileInfo indexFile = new FileInfo(Path.Combine(clsSettings.serviceSettings.dataDirectory.FullName, string.Format("{0}{1}{2}", xmlDestinationFolderName, Path.DirectorySeparatorChar, indexFileName))); // Load the XML document containing the CategoryIndex. xDocumentIndex = XDocument.Load(indexFile.FullName); ccfDatosGen xmlDatosGen = new ccfDatosGen(); xmlDatosGen = (from e in xDocumentIndex.Descendants("CategoryIndex") where ((string)e.Attribute("CategoryIndex")) != string.Empty select new ccfDatosGen { dateCreated = (DateTime)e.Attribute("DateCreated"), source = (string)e.Attribute("source"), }).Single(); dbDatosGen.dateCreated = xmlDatosGen.dateCreated; dbDatosGen.source = xmlDatosGen.source; if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Closed) { db.Connection.Open(); } db.ccfDatosGens.InsertOnSubmit(dbDatosGen); db.SubmitChanges(); if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); db = null; } // Do some Cleanup. if (dbDatosGen != null) { dbDatosGen = null; } if (xDocumentIndex != null) { xDocumentIndex = null; } if (indexFile != null) { indexFile = null; } GC.Collect(); }
internal bool updateSqlDataBase() { // Set the currentOperation clsSettings.serviceSettings.currentOperation = operationList.updatingSqlDataBase; ISACFDataContext db = new ISACFDataContext(); try { if (clsSettings.serviceSettings.useSqlTransaction) { Decimal AvailablePhysicalMemoryMB = clsStatus.getAvailablePhysicalMemoryMB(); if (AvailablePhysicalMemoryMB < 500.00M) { string Message = string.Format("El proceso \"{0}\" requiere que, en el momento de su ejecución, el valor de la memoria física disponible (RAM) " + "(actualmente: {1} MB) sea mayor que {2} MB para llevar a cabo los procesos correspondientes en modo transacción. Por tanto, no se realizarán " + "las actualizaciones de la base de datos en modo de transacción en esta oportunidad. Si este mensaje aparece repetidamente en su sistema, es " + "posible que se requiera aumentar la memoria física total (RAM) del mismo o disminuir su carga de trabajo actual.", operationList.updatingSqlDataBase, AvailablePhysicalMemoryMB, 500.00M); clsLogProcessing.WriteToEventLog(Message, EventLogEntryType.Warning); } else { currentSubOperation = sqlOperationList.preparingTransaction; db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted); } } currentSubOperation = sqlOperationList.cleaningupAllData; cleanupAllData(db); currentSubOperation = sqlOperationList.updatingDatosGen; updateDatosGen(db); currentSubOperation = sqlOperationList.updatingCategories; updateCategories(db); currentSubOperation = sqlOperationList.updatingDomains; updateDomains(db); currentSubOperation = sqlOperationList.updatingUrls; updateUrls(db); if (db.Transaction != null) { currentSubOperation = sqlOperationList.committingTransaction; db.Transaction.Commit(); } } catch (System.Exception Ex) { clsLogProcessing.WriteToEventLog(Ex.Message, System.Diagnostics.EventLogEntryType.Error); if (db != null) { if (db.Transaction != null) { db.Transaction.Rollback(); db = null; } } return(false); } finally { if (db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); } if (db != null) { db = null; } GC.Collect(); } return(true); }
} // End of updateDomains() private static void updateUrls(ISACFDataContext db) { if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Closed) { db.Connection.Open(); } List <ccfCategory> lstCategory = (from e in db.ccfCategories select e).ToList(); if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); } FileInfo UrlsFile = null; foreach (ccfCategory Category in lstCategory) { // Try to free some memory when Category changes. GC.Collect(); UrlsFile = new FileInfo(Path.Combine(clsSettings.serviceSettings.dataDirectory.FullName, string.Format("{0}{1}{2}{1}{3}", xmlDestinationFolderName, Path.DirectorySeparatorChar, Category.name, urlsFileName))); if (!File.Exists(UrlsFile.FullName)) { continue; } bool onceAgain = true; int s = 0; const int rowsToTake = 5000; while (onceAgain) { List <string> urls = (from e in XDocument.Load(UrlsFile.FullName).Descendants("url") select(string) e.Value).Distinct().Skip(s).Take(rowsToTake).ToList(); s += rowsToTake; onceAgain = urls.Count == rowsToTake; if (db == null) { db = new ISACFDataContext(); } if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Closed) { db.Connection.Open(); } foreach (string url in urls) { if (!isIPAddress(url)) { ccfUrl newUrl = new ccfUrl(); newUrl.id_Category = Category.ID; newUrl.url = url; db.ccfUrls.InsertOnSubmit(newUrl); newUrl = null; } else { ccfIPv4 newIPv4 = new ccfIPv4(); newIPv4.id_Category = Category.ID; newIPv4.IP = url; db.ccfIPv4s.InsertOnSubmit(newIPv4); newIPv4 = null; } } db.SubmitChanges(); // Do some Cleanup. if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); db = null; } if (urls != null) { urls = null; } // Try to free some memory. GC.Collect(); } // Do some Cleanup. if (UrlsFile != null) { UrlsFile = null; } // Try to free some memory. GC.Collect(); } if (lstCategory != null) { lstCategory = null; } if (db != null) { if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); db = null; } } // Try to free some memory. GC.Collect(); } // End of updateUrls()
private static void updateCategories(ISACFDataContext db) { FileInfo indexFile = new FileInfo(Path.Combine(clsSettings.serviceSettings.dataDirectory.FullName, string.Format("{0}{1}{2}", xmlDestinationFolderName, Path.DirectorySeparatorChar, indexFileName))); XDocument xDocumentIndex; xDocumentIndex = XDocument.Load(indexFile.FullName); // Read only the Attribute name of each Category Element. var Categories = (from e in xDocumentIndex.Elements("CategoryIndex").Elements("Category") where ((string)e.Element("name")) != string.Empty select e).ToArray(); if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Closed) { db.Connection.Open(); } var CategoriesToBlock = (from e in db.ccfCategoryToBlocks select e.name).ToArray(); ccfCategory newCat = null; ccfCategoryName_es newCatNameEs = null; foreach (XElement Category in Categories) { newCat = new ccfCategory(); newCat.name = Category.Attribute("name").Value; newCat.default_type = Category.Element("default-type").Value; newCat.name_en = Category.Element("name-en").Value; newCat.desc_en = Category.Element("desc-en").Value; newCat.processForISARule = CategoriesToBlock.Contains(newCat.name); if (!string.IsNullOrEmpty(Category.Element("name-es").Value)) { newCatNameEs = new ccfCategoryName_es(); newCatNameEs.id_Category = newCat.ID; newCatNameEs.name_es = Category.Element("name-es").Value; newCatNameEs.desc_es = string.IsNullOrEmpty(Category.Element("desc-es").Value) ? DBNull.Value.ToString() : Category.Element("desc-es").Value; newCat.ccfCategoryName_es = newCatNameEs; } db.ccfCategories.InsertOnSubmit(newCat); // Do cleanup. if (newCatNameEs != null) { newCatNameEs = null; } if (newCat != null) { newCat = null; } } db.SubmitChanges(); if (db.Transaction == null & db.Connection.State == System.Data.ConnectionState.Open) { db.Connection.Close(); db = null; } // Do some Cleanup. if (indexFile != null) { indexFile = null; } if (xDocumentIndex != null) { xDocumentIndex = null; } if (Categories != null) { Categories = null; } if (CategoriesToBlock != null) { CategoriesToBlock = null; } GC.Collect(); }