/// <summary> /// This function when the service accedentally fail or closed than we need the foollwing function to get the last record /// </summary> /// <param name="element_id"></param> private static void CheckLastRecordInsterted(long element_id) { DatawarehouseConnectionContext ctx = new DatawarehouseConnectionContext(); if (ctx.Point_Measure_Fact_Entities.Any(element => element.Point_ID == element_id)) { default_from_dt = Convert.ToDateTime(ctx.Point_Measure_Fact_Entities.Where(element => element.Point_ID == element_id).OrderByDescending(t => t.Timestamp_From).First().Timestamp_From.AddMilliseconds(2).ToString("yyyy-MM-dd,HH:mm:ss.fff")); } else { default_from_dt = Convert.ToDateTime(default_to_dt.AddYears(-1).ToShortDateString() + " 00:00:00"); } }
public void Save_Point_Measure_Fact_Service(IList <RawDataDTO> list_raw_data_dto, ElementDTO element) { try { DatawarehouseConnectionContext datawarehousecontext = new DatawarehouseConnectionContext(); Point_Measure_Fact point_measure_fact; foreach (RawDataDTO raw_dto in list_raw_data_dto) { point_measure_fact = new Point_Measure_Fact(); try { point_measure_fact.ID = 1; point_measure_fact.Client_ID = 0; point_measure_fact.DataRecordingFrequency = element.Recorded_Freq; point_measure_fact.Date_ID = System.DateTime.Now.Date; point_measure_fact.External_ID = raw_dto.ID.ToString(); point_measure_fact.Hour_ID = System.DateTime.Now.Date; point_measure_fact.Last_Update_Time = System.DateTime.Now; point_measure_fact.Max_Value = element.Max_Value; point_measure_fact.Min_Value = element.Min_Value; point_measure_fact.Point_ID = element.ID; point_measure_fact.Schedule_ID = 0; point_measure_fact.Timestamp_From = Convert.ToDateTime(raw_dto.TIMESTAMP); point_measure_fact.Timestamp_To = Convert.ToDateTime(raw_dto.TIMESTAMP); point_measure_fact.Unit_Of_Measurement = element.Unit.Unit_Name; point_measure_fact.Value = Convert.ToDouble(raw_dto.VALUE); datawarehousecontext.Point_Measure_Fact_Entities.Add(point_measure_fact); } catch (Exception ex) { throw ex; } } datawarehousecontext.SaveChanges(); } catch (Exception ex) { throw ex; } //datawarehousecontext.Point_Measure_Fact_Entities.Add() }
private static void Delete_Duplicate(ElementDTO element) { try { Console.WriteLine("delete duplicate data " + element.ID + "--" + element.Source_Element_Name_History); DatawarehouseConnectionContext ctx_delete_duplicate = new DatawarehouseConnectionContext(); ctx_delete_duplicate.Database.CommandTimeout = 300; ctx_delete_duplicate.Database.ExecuteSqlCommand("EXEC Delete_Duplicate_Data " + element.ID); ctx_delete_duplicate.Database.Connection.Close(); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] remove duplicate data from error " + ex.Message + " -- " + element.ID + "--" + element.Source_Element_Name_History); } }
/// <summary> /// Datawarehouse finalinsert into the Point_Mesaure_Fact /// </summary> /// <param name="point_measure_facts"></param> private static void FinalInsert(IList <Point_Measure_Fact> point_measure_facts) { try { BlockingCollection <Point_Measure_Fact> entityQueue = new BlockingCollection <Point_Measure_Fact>(); List <Task> writers = new List <Task>(); if (point_measure_facts.Count > 0) { Action writerAction = new Action(() => { DatawarehouseConnectionContext ctx = new DatawarehouseConnectionContext(); ctx.Database.CommandTimeout = 300; int totalAdded = 0; try { while (true) { Point_Measure_Fact my = entityQueue.Take(); my.Last_Update_Time = System.DateTime.Now; ctx.Point_Measure_Fact_Entities.Add(my); totalAdded++; if (totalAdded > 500) { //Console.WriteLine("Saving changes, TID: " + Thread.CurrentThread.ManagedThreadId); try { ctx.SaveChanges(); } catch (Exception ex) { Helper.WriteToFile("Error in saving: " + ex.InnerException.Message); } ctx.Database.Connection.Close(); ctx = new DatawarehouseConnectionContext(); } } } catch (InvalidOperationException inv) { //WriteToFile("Saving final changes --- " + inv.Message); //Console.WriteLine("Saving final changes, TID: " + Thread.CurrentThread.ManagedThreadId); // Console.WriteLine("Error Saving final " + inv.Message); ctx.SaveChanges(); // Save any pending changes ctx.Database.Connection.Close(); totalAdded = 0; } }); for (int i = 0; i < 1000; i++) { writers.Add(Task.Factory.StartNew(writerAction)); } foreach (Point_Measure_Fact point_measure_fact in point_measure_facts) { entityQueue.Add(point_measure_fact); } } entityQueue.CompleteAdding(); //Console.WriteLine("Done generating data."); Task.WaitAll(writers.ToArray()); Console.WriteLine("Finished"); } catch (Exception ex) { string innerexp = (ex.InnerException != null) ? ex.InnerException.Message : ""; Helper.WriteToFile("Error in the FinalInsert " + ex.Message + "--" + innerexp); Console.Write("Done with errors Press any key"); Console.ReadKey(); } }
private async static void GetRowsForEachTable(ElementDTO element, ConnectorDTO _dto_connector, int index) { try { string information_of_element = element.ID + "--" + element.Source_Element_Name_History + " Connection " + _dto_connector.DatabaseName; InnonAnalyticsEngineEntities _dbContext2 = new InnonAnalyticsEngineEntities(); _dbContext2.Database.CommandTimeout = 300; //Console.WriteLine("From Date " + default_from_dt); //Console.WriteLine("To Date " + default_to_dt); IRawDataRepository _rawDataRepository = new RawDataRepository(); // Console.ReadLine(); //IList<RawDataDTO> rawdata = Point_Measure_Fact_Service point_measure_fact_service; tblDatawareHouseMigrationLog data_migration_log = new tblDatawareHouseMigrationLog(); await Task.Run(() => { IList <RawDataDTO> list_RawDataDTO = new List <RawDataDTO>(); try { Console.WriteLine("Start For Table " + element.ID + "--" + element.Source_Element_Name_History + " -- From dt " + default_from_dt + " -- To date " + default_to_dt); list_RawDataDTO = _rawDataRepository.GetRowData(element.Source_Element_Name_History, Convert.ToDateTime(default_from_dt.ToString("yyyy-MM-dd HH:mm:ss.fff")), Convert.ToDateTime(default_to_dt.ToString("yyyy-MM-dd HH:mm:ss.fff")), _dto_connector, _dbContext2); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] First Step fetching the bulk data from error " + ex.Message + " -- " + information_of_element); } return(list_RawDataDTO); }) .ContinueWith((k) => { if (k.Result.Count > 0) { try { data_migration_log = _dbContext2.tblDatawareHouseMigrationLogs.Add( new tblDatawareHouseMigrationLog { Element_ID = element.ID, Last_Run_Start = System.DateTime.Now, Total_Record_Fetch = 0, Timestamp_From = default_from_dt, }); _dbContext2.SaveChangesAsync(); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] Sec Step saving in to the tblDatawareHouseMigrationLogs data from error " + ex.Message + " -- " + information_of_element); } } return(k.Result); }) .ContinueWith((x) => { IList <Point_Measure_Fact> point_measure_fact_list_dto = new List <Point_Measure_Fact>(); try { // x is last continue result (GetRowData returns the IList<RawData>) //Save Final insert in to the database InnonAnalyticsWarehouse table Point_Mesaure_Fact Console.WriteLine("Get Rows for Table " + element.Source_Element_Name_History + " - " + x.Result.Count(), EventLogEntryType.Information); point_measure_fact_service = new Point_Measure_Fact_Service(); point_measure_fact_service.List_Point_Measure_Fact_Service(x.Result, element); point_measure_fact_list_dto = point_measure_fact_service.List_Point_Measure_Fact_Service(x.Result, element); FinalInsert(point_measure_fact_list_dto); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] Third Step final saving the data in to the point measure facts from error " + ex.Message + " -- " + information_of_element); } return(point_measure_fact_list_dto); }) .ContinueWith((y) => { // y is last continue result //update DatawareHouseMigrationLog table for the next run if (y.Result.Count > 0) { data_migration_log.Last_Run_End = System.DateTime.Now; data_migration_log.Total_Record_Fetch = y.Result.Count; data_migration_log.Timestamp_To = Convert.ToDateTime(y.Result.LastOrDefault().Timestamp_To); _dbContext2.SaveChangesAsync(); } return(y.Result); }).ContinueWith((z) => { IList <RawDataDTO> rawdata = new List <RawDataDTO>(); try { //Checking the missing data DatawarehouseConnectionContext ctx = new DatawarehouseConnectionContext(); ctx.Database.CommandTimeout = 300; rawdata = ctx.Database.SqlQuery <RawDataDTO>("EXEC GetRawMissingData '" + _dto_connector.ServerOrIP + "', '" + _dto_connector.ServerUserName + "' , '" + _dto_connector.ServerPassword + "', '" + _dto_connector.DatabaseName + "' , '" + element.Source_Element_Name_History + "' ,'" + Convert.ToDateTime(default_to_dt.AddYears(-1).ToShortDateString() + " 00:00:00").ToString("yyyy-MM-dd HH:mm:ss.fff") + "','" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," + element.ID).ToList(); ctx.Database.Connection.Close(); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] Fourth getting missing data from error " + ex.Message + " -- " + information_of_element); } return(rawdata); }).ContinueWith((t) => { //Saving the missing data IList <RawDataDTO> missingrawdata = new List <RawDataDTO>(); missingrawdata = t.Result; try { Console.WriteLine("Missing " + element.Source_Element_Name_History + "- Total:-" + missingrawdata.Count()); point_measure_fact_service = new Point_Measure_Fact_Service(); point_measure_fact_service.List_Point_Measure_Fact_Service(missingrawdata, element); IList <Point_Measure_Fact> point_measure_fact_list_dto = point_measure_fact_service.List_Point_Measure_Fact_Service(missingrawdata, element); FinalInsert(point_measure_fact_list_dto); } catch (Exception ex) { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] Fifth saving missing data from error " + ex.Message + " -- " + information_of_element); } }).ContinueWith((r) => { Task.Factory.StartNew((x) => { Helper.WriteToFile(" [ " + System.DateTime.Now + " ] Deleting for " + +element.ID + "--" + element.Source_Element_Name_History); Console.WriteLine(index + " delete duplicate data " + element.ID + "--" + element.Source_Element_Name_History); DatawarehouseConnectionContext ctx_delete_duplicate = new DatawarehouseConnectionContext(); ctx_delete_duplicate.Database.CommandTimeout = 300; ctx_delete_duplicate.Database.ExecuteSqlCommand("EXEC Delete_Duplicate_Data " + element.ID); ctx_delete_duplicate.Database.Connection.Close(); Console.WriteLine(index + " Finished duplicate data delete "); }, TaskCreationOptions.DenyChildAttach); }); //this.EventLog.WriteEntry("Get Rows for Table " + element.Source_Element_Name_History + " - " + rawdata.Count(), EventLogEntryType.Information); } catch (Exception ex) { string innerexp = (ex.InnerException != null) ? ex.InnerException.Message: ""; Helper.WriteToFile("Error while getting the GetRowsForEachTable with table " + element.ID + "--" + element.Source_Element_Name_History + " Connection " + _dto_connector.DatabaseName + " - Error" + ex.Message + " - Inner exception " + innerexp); } }