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); } }