Example #1
0
        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);
            }
        }
Example #2
0
        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);
            }
        }