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] + "'");
}
}
}
}
}