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 loadFile(string path, string fileName, string loadid)
{
List <string> headers = null;
int lineCount = System.IO.File.ReadLines(path).Count() - 1;
using (var streamReader = System.IO.File.OpenText(path))
{
MySplit split = new MySplit();
string source = streamReader.ReadLine();
//confirm filetype and detect seperator
LoadViewModel LoadView = new LoadViewModel();
string fileType = LoadView.DetectFileType(source);
char seperator = LoadView.DetectDelimeter(source);
//Bulk Load
List <string> fields = split.mySplit(source, seperator);
if (headers == null)
{
headers = fields;
}
string sql;
string sql2;
string sqlproofloadid = loadid.Replace('-', '_');
sql = LoadView.GenerateCreateTableSql(fields, sqlproofloadid);
sql2 = UpdateAnalysisTablePopuilationPerCent(headers, loadid, lineCount)[0];
string ConnStr = ConfigurationManager.ConnectionStrings["LoadedFiles"].ConnectionString;
var Conn = new SqlConnection(ConnStr);
var CreateTable = new SqlCommand(sql, Conn);
var analysisUpdate = new SqlCommand(sql2, Conn);
Conn.Open();
CreateTable.ExecuteNonQuery();
analysisUpdate.ExecuteNonQuery();
var tempDataTable = new DataTable();
tempDataTable.Columns.Add(new DataColumn("DIRowID"));
for (int i = 0; i < fields.Count; i++)
{
tempDataTable.Columns.Add(new DataColumn());
}
while (!streamReader.EndOfStream)
{
DataRow row = tempDataTable.NewRow();
source = streamReader.ReadLine();
List <string> values = new List <string>();
values.Add(Guid.NewGuid().ToString());
values.AddRange(split.mySplit(source, seperator));
row.ItemArray = values.ToArray();
tempDataTable.Rows.Add(row);
//int currentProgress = tempDataTable.Rows.Count;
//perCent = (currentProgress / lineCount) * 100;
//sql = LoadView.GenerateInsertInToTableSql(values, sqlproofloadid);
//ViewBag.Message = sql;
//using (var newTableCtx = new LoadedFiles())
//{
// int noOfRecordsInserted = newTableCtx.Database.ExecuteSqlCommand(sql);
//}
}
streamReader.Close();
var bc = new SqlBulkCopy(Conn, SqlBulkCopyOptions.TableLock, null)
{
DestinationTableName = "table_load_" + sqlproofloadid,
BatchSize = tempDataTable.Rows.Count
};
//Conn.Open();
bc.WriteToServer(tempDataTable);
bc.Close();
for (int i = 1; i <= UpdateAnalysisTablePopuilationPerCent(headers, loadid, lineCount).Count() - 1; i++)
{
sql2 = UpdateAnalysisTablePopuilationPerCent(headers, loadid, lineCount)[i];
var analysisUpdate2 = new SqlCommand(sql2, Conn);
analysisUpdate2.ExecuteNonQuery();
}
Conn.Close();
// identifying data types and altering table columns
LoadView.SetupColumnsDataTypes(fields, sqlproofloadid);
}
}