public string loadFileInfo(string path, string fileName, string userID) { int lineCount = System.IO.File.ReadLines(path).Count(); using (var streamReader = System.IO.File.OpenText(path)) { string source = streamReader.ReadLine(); //confirm filetype and detect seperator LoadViewModel LoadView = new LoadViewModel(); string fileType = LoadView.DetectFileType(source); char seperator = LoadView.DetectDelimeter(source); string loadid; LoadedFiles ctx = new LoadedFiles(); LoadedFile loadedfile = new LoadedFile { LoadedFileID = Guid.NewGuid(), FileName = fileName, FileType = fileType, FileImportDate = DateTime.Now, UserID = userID, LineCount = lineCount }; ctx.DBLoadedFiles.Add(loadedfile); ctx.SaveChanges(); loadid = loadedfile.LoadedFileID.ToString(); return(loadid); } }
public void progressupdate(Guid id) { using (var context = new LoadedFiles()) { var result = context.DBLoadedFiles.SingleOrDefault(b => b.LoadedFileID == id); if (result != null) { result.Progress = 100; context.SaveChanges(); } } }
public string filename(Guid id) { using (var context = new LoadedFiles()) { // Query for all blogs with names starting with B var user = context.DBLoadedFiles.Find(id); string file = user.FileName; return(file); } }
public List <Guid> unprocessedids() { List <Guid> list = new List <Guid>(); using (var context = new LoadedFiles()) { var unprocessedfiles = context.DBLoadedFiles.Where(b => b.Progress == null); foreach (var row in unprocessedfiles) { list.Add(row.LoadedFileID); } } return(list); }
public void SetupColumnsDataTypes(List <string> fields, string loadid) { string fieldsql; Type dataType; string sqlDataType; List <string> sqlDataTypeList = new List <string>(); foreach (string item in fields) { string field = item.Replace("\"", ""); //identify columns datatype using function GetColumnType() based on first 50 records fieldsql = "SELECT TOP(50) " + field + " FROM table_load_" + loadid + " WHERE " + field + " IS NOT NULL AND " + field + " != ''"; using (var newTableCtx = new LoadedFiles()) { var values = newTableCtx.Database.SqlQuery <string>(fieldsql).ToList(); //building sqlDataTypeList based on function GetColumnType() results dataType = GetColumnType(values); if (dataType.ToString() == "System.String") { sqlDataType = "varchar"; } //else if (dataType.ToString() == "System.Boolean") //{ // sqlDataType = "bit"; //} else if (dataType.ToString() == "System.Int32") { sqlDataType = "int"; } else if (dataType.ToString() == "System.Int64") { sqlDataType = "bigint"; } else if (dataType.ToString() == "System.Double") //later on is converting value back to nvarchar when consists character "," { //We will have to implement a tool for the user to then define if char"," represents separator for decimal places or if sqlDataType = "float"; //is used to separate for example thousands (1,000) or millions(1,000,000) } else if (dataType.ToString() == "System.DateTime") { sqlDataType = "datetime"; } else { sqlDataType = "nvarchar"; } sqlDataTypeList.Add(sqlDataType); } } // setting appropriate Data Types and Size for each column in database + Updates for table [dbo].Analysis with data counts that are used in analysis Chrts for (int x = 0; x < sqlDataTypeList.Count; x++) { using (var newTableCtx = new LoadedFiles()) { int alterRowIdDataType = newTableCtx.Database.ExecuteSqlCommand("ALTER TABLE table_load_" + loadid + " ALTER COLUMN DIRowID uniqueidentifier NOT NULL"); //unifying Date Format (yyyy-mm-dd) to be able to alter column and set as "datetime" data type. If column has invalid date it will be set as '1753-01-01' to pointed out wrong value and differentiate from not populated(NULL) - we must somehow catch this and reported if (sqlDataTypeList[x] == "datetime") { int unifyDates = newTableCtx.Database.ExecuteSqlCommand("Update table_load_" + loadid + " Set " + fields[x] + " = COALESCE( TRY_CONVERT(DATE, " + fields[x] + ", 103), TRY_CONVERT(DATE, " + fields[x] + ", 102), TRY_CONVERT(DATE, " + fields[x] + ", 101));"); int alterColumnsDataType = newTableCtx.Database.ExecuteSqlCommand("ALTER TABLE table_load_" + loadid + " ALTER COLUMN " + fields[x] + " " + sqlDataTypeList[x] + ";"); int updateTblAnalysisFieldType = newTableCtx.Database.ExecuteSqlCommand("UPDATE [dbo].[Analysis] SET [Type] = '" + sqlDataTypeList[x] + "' WHERE [LoadedFileID] = '" + loadid.ToString().Replace("_", "-") + "' AND [FieldName] = '" + fields[x] + "'"); //int updateTblAnalysisFieldType = newTableCtx.Database.ExecuteSqlCommand("UPDATE [dbo].[Analysis] SET [Type] = '" + sqlDataTypeList[x] + "', [MinValue] = (Select Min(" + fields[x] + ") from [dbo].table_load_" + loadid.ToString() + "), [MaxValue] = (Select Max(" + fields[x] + ") from [dbo].table_load_" + loadid.ToString() + ") WHERE [LoadedFileID] = '" + loadid.ToString().Replace("_", "-") + "' AND [FieldName] = '" + fields[x] + "'"); } else // means sqlDataTypeList[x] is not a datetime { try //if sqlDataTypeList[x] is anything else but not varchar, nvarchar { if (sqlDataTypeList[x] != "varchar" || sqlDataTypeList[x] != "nvarchar") { int alterColumnsDataType = newTableCtx.Database.ExecuteSqlCommand("ALTER TABLE table_load_" + loadid + " ALTER COLUMN " + fields[x] + " " + sqlDataTypeList[x] + ";"); int updateTblAnalysisFieldType = newTableCtx.Database.ExecuteSqlCommand("UPDATE [dbo].[Analysis] SET [Type] = '" + sqlDataTypeList[x] + "', [MinValue] = (Select Min(" + fields[x] + ") from [dbo].table_load_" + loadid.ToString() + "), [MaxValue] = (Select Max(" + fields[x] + ") from [dbo].table_load_" + loadid.ToString() + "), [AvgValue] = (Select Avg(" + fields[x] + ") from [dbo].table_load_" + loadid.ToString() + ") WHERE [LoadedFileID] = '" + loadid.ToString().Replace("_", "-") + "' AND [FieldName] = '" + fields[x] + "'"); } } catch // anything that failed in "Try" + type varchar and nvarchar { if (sqlDataTypeList[x] != "varchar" || sqlDataTypeList[x] != "nvarchar") // anything that failed in "Try" but is not "varchar" or "nvarchar" will be converted to nvarchar(e.g. at this moment numbers that have "," instead of "." to separate decimal points ) { sqlDataTypeList[x] = "nvarchar"; } string sql = "select max(len(" + fields[x] + ")) from table_load_" + loadid + ";"; try { var size = newTableCtx.Database.SqlQuery <Int64>(sql).ToList(); int alterColumnsTypeSize = newTableCtx.Database.ExecuteSqlCommand("ALTER TABLE table_load_" + loadid + " ALTER COLUMN " + fields[x] + " " + sqlDataTypeList[x] + "(" + size[0] + ");"); } catch { var size = newTableCtx.Database.SqlQuery <Int32>(sql).ToList(); int alterColumnsTypeSize = newTableCtx.Database.ExecuteSqlCommand("ALTER TABLE table_load_" + loadid + " ALTER COLUMN " + fields[x] + " " + sqlDataTypeList[x] + "(" + size[0] + ");"); } int updateTblAnalysisFieldType = newTableCtx.Database.ExecuteSqlCommand("UPDATE [dbo].[Analysis] SET [Type] = '" + sqlDataTypeList[x] + "' WHERE [LoadedFileID] = '" + loadid.ToString().Replace("_", "-") + "' AND [FieldName] = '" + fields[x] + "'"); } } } } }