public List <string> GetColumnsFromCsv() { var firstLine = ""; using (StreamReader reader = new StreamReader(FileName)) { firstLine = reader.ReadLine(); } var columns = StaticHelperClass.SplitString(Configuration.Delimiter, firstLine, 1); System.Diagnostics.Debug.WriteLine(columns?.Count); return(columns); }
public void CreateSingleTable(IColumn[] columns, int ChunkSize) { FileStream stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true); using (StreamReader reader = new StreamReader(stream)) { reader.ReadLine(); var table = MakeSingleTable(columns); int counter = 1; string line; Task worker = null; string[] _columns = columns.Select(c => c.ColumnName).ToArray(); string[] _values = new string[columns.Length]; List <string> queries = new List <string>(ChunkSize); string[] cachedQueries; using (var database = new Database(Configuration.DatabasePath)) { if (!database.HasTables) { database.CreateTables(new List <ITable>() { table }); } List <IData> row = new List <IData>(columns.Length); while ((line = reader.ReadLine()) != null) { var l = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0); for (var y = 0; y < l.Count; y++) { if (row.Count != columns.Length) { row.Add(new Data() { ColumnIndex = y, Value = l[y] }); } else { row[y] = new Data() { ColumnIndex = y, Value = l[y] } }; } queries.Add(database.InsertDataIntoMainTable(row, table, columns, _columns, _values)); if (queries.Count == ChunkSize) { if (worker != null && !worker.IsCompleted) { worker.Wait(); } PushNotification($"Saving {ChunkSize} {counter++}"); cachedQueries = new string[queries.Count]; for (int i = 0; i < queries.Count; i++) { cachedQueries[i] = queries[i]; } worker = Task.Factory.StartNew(() => { database.ExecuteMultipleNonQueriesInATranscaction(cachedQueries); }); queries = new List <string>(ChunkSize); } } if (worker != null && !worker.IsCompleted) { worker.Wait(); } if (queries.Count > 0) { database.ExecuteMultipleNonQueriesInATranscaction(queries); } } } stream.Dispose(); }
private ColumnTypeSQLLITE[] GetDataTypeFromCsvColumns() { var result = new List <ColumnTypeSQLLITE>(); var flagDictionary = new Dictionary <int, List <ColumnTypeSQLLITE> >(); using (StreamReader reader = new StreamReader(FileName)) { reader.ReadLine(); string line; bool isFirstLine = true; int accuracy = 0; if (Configuration.ColumnTypeAccuracy == 0) { Configuration.ColumnTypeAccuracy = 1; } while ((line = reader.ReadLine()) != null) { if (Configuration.ColumnTypeAccuracy != 11) { if (Configuration.ColumnTypeAccuracy == accuracy) { break; } else { accuracy++; } } var nonDateIndexes = new List <int>(); var cultureInfo = new CultureInfo("en-US"); var dummyValueDateTime = new DateTime(); var values = StaticHelperClass.SplitString(Configuration.Delimiter, line, 2); if (values != null) { for (int y = 0; y < values.Count; y++) { try { if (!isFirstLine) { if (IsDateTime(values[y], cultureInfo, dummyValueDateTime) && !nonDateIndexes.Contains(y)) { if (result[y] != ColumnTypeSQLLITE.Numeric) { result[y] = ColumnTypeSQLLITE.Numeric; if (flagDictionary.ContainsKey(y)) { if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Numeric)) { flagDictionary[y].Add(ColumnTypeSQLLITE.Numeric); } } else { flagDictionary.Add(y, new List <ColumnTypeSQLLITE>() { ColumnTypeSQLLITE.Numeric }); } } } else if (int.TryParse(values[y], out int res)) { if (result[y] != ColumnTypeSQLLITE.Integer) { result[y] = ColumnTypeSQLLITE.Integer; if (flagDictionary.ContainsKey(y)) { if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Integer)) { flagDictionary[y].Add(ColumnTypeSQLLITE.Integer); } } else { flagDictionary.Add(y, new List <ColumnTypeSQLLITE>() { ColumnTypeSQLLITE.Integer }); } } } else if (double.TryParse(values[y], out double res1)) { if (result[y] != ColumnTypeSQLLITE.Real) { result[y] = ColumnTypeSQLLITE.Real; if (flagDictionary.ContainsKey(y)) { if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Real)) { flagDictionary[y].Add(ColumnTypeSQLLITE.Real); } } else { flagDictionary.Add(y, new List <ColumnTypeSQLLITE>() { ColumnTypeSQLLITE.Real }); } } } else { if (result[y] != ColumnTypeSQLLITE.Text) { result[y] = ColumnTypeSQLLITE.Text; if (flagDictionary.ContainsKey(y)) { if (!flagDictionary[y].Contains(ColumnTypeSQLLITE.Text)) { flagDictionary[y].Add(ColumnTypeSQLLITE.Text); } } else { flagDictionary.Add(y, new List <ColumnTypeSQLLITE>() { ColumnTypeSQLLITE.Text }); } } } } else { if (IsDateTime(values[y], cultureInfo, dummyValueDateTime)) { result.Add(ColumnTypeSQLLITE.Numeric); } if (int.TryParse(values[y], out int res)) { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Integer); } else if (double.TryParse(values[y], out double res1)) { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Real); } else { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Text); } } } catch (ArgumentOutOfRangeException) { if (IsDateTime(values[y], cultureInfo, dummyValueDateTime)) { result.Add(ColumnTypeSQLLITE.Numeric); } if (int.TryParse(values[y], out int res)) { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Integer); } else if (double.TryParse(values[y], out double res1)) { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Real); } else { nonDateIndexes.Add(y); result.Add(ColumnTypeSQLLITE.Text); } } } } isFirstLine = true; } } // var lines = File.ReadAllLines(csvFilePath); //flags changes in types if (result.Count > 0) { return(FinesseTypes(result, flagDictionary).ToArray()); } else { return(null); } }
//public void CreateMultiTableDatabase(IColumn[] columns, int chunkSize) //{ // using (var stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true)) // { // using (var reader = new StreamReader(stream)) // { // reader.ReadLine(); // string line; // Task worker = null; // var data = new List<IData>(chunkSize); // bool isRelationshipDone = false; // var relationshipBuilder = new AutoRelationshipBuilder(); // ICollection<ITable> tables = null; // Dictionary<IColumn, ITable> relationshipsResult = null; // var idDictionary = new ConcurrentDictionary<RelationshipKey, int>(); // int id = 0; // var queries = new List<string>(); // using (var database = new Database(Configuration.DatabasePath)) // { // while (true) // { // if (data.Count < chunkSize) // { // line = reader.ReadLine(); // if (line == null) // break; // var d = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0); // for (var i = 0; i < d.Count; i++) // data.Add(new Data() { ColumnIndex = i, Value = d[i] }); // } // else if (!isRelationshipDone) // { // relationshipsResult = relationshipBuilder // .MakeRelationships(columns, data, out tables); // if (tables != null) // database.CreateTables(tables); // isRelationshipDone = true; // if (relationshipsResult != null) // queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns); // database.ExecuteMultipleNonQueriesInATranscaction(queries); // var transferList = new List<IData>(data.Count); // transferList.AddRange(data); // worker = Task.Factory.StartNew(() => // { // MakeSQlTable_MultipleTables(transferList, tables.Last(), idDictionary, database, columns); // }); // data = new List<IData>(chunkSize); // queries = new List<string>(); // } // else // { // if (worker != null && !worker.IsCompleted) // worker.Wait(); // worker = Task.Factory.StartNew(() => // { // if (relationshipsResult != null) // queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns); // database.ExecuteMultipleNonQueriesInATranscaction(queries); // queries = new List<string>(); // var transferList = new List<IData>(data.Count); // transferList.AddRange(data); // MakeSQlTable_MultipleTables(transferList, tables.Last(), idDictionary, database, columns); // }); // data = new List<IData>(chunkSize); // } // } // if (data.Count > 0) // { // if (relationshipsResult != null) // queries = MakeRelationshipTableQueries(data, relationshipsResult, idDictionary, ref id, columns); // database.ExecuteMultipleNonQueriesInATranscaction(queries); // queries = new List<string>(); // MakeSQlTable_MultipleTables(data, tables.Last(), idDictionary, database, columns); // } // } // } // } //} public void CreateMultiTableDatabase(IColumn[] columns, int chunkSize) { using (var stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read, 32768, true)) { using (var reader = new StreamReader(stream)) { reader.ReadLine(); string line; var calcedChunkSize = (int)Math.Floor((double)chunkSize / columns.Length); Task worker = null; var data = new List <IData>(chunkSize); List <IData> transferData; bool RelationshipDone = false; var relationshipBuilder = new AutoRelationshipBuilder(); ICollection <ITable> tables = null; Dictionary <IColumn, ITable> relationshipsResult = null; var idSaver = new ConcurrentDictionary <RelationshipKey, int>(); int id = 0; var queries = new List <string>(); // copy of queries for the task to work with List <string> q; using (var database = new Database(Configuration.DatabasePath)) { while (true) { if (data.Count < chunkSize) { line = reader.ReadLine(); if (line == null) { break; } var d = StaticHelperClass.SplitString(Configuration.Delimiter, line, 0); for (var i = 0; i < d.Count; i++) { data.Add(new Data() { ColumnIndex = i, Value = d[i] }); } } else { transferData = new List <IData>(chunkSize); if (!RelationshipDone) { relationshipsResult = relationshipBuilder .MakeRelationships(columns, data, out tables); if (tables != null) { database.CreateTables(tables); } RelationshipDone = true; } if (relationshipsResult != null) { var cacheTask = Task.Factory.StartNew(() => { transferData.AddRange(data); }); queries = MakeRelationshipTableQueries(data, relationshipsResult, idSaver, ref id, columns); if (!cacheTask.IsCompleted) { cacheTask.Wait(); } } q = new List <string>(queries.Count); q.AddRange(queries); if (worker != null) { worker.Wait(); } worker = Task.Factory.StartNew(() => { database.ExecuteMultipleNonQueriesInATranscaction(q); MakeSQlTable_MultipleTables(transferData, tables.Last(), idSaver, database, columns); }); data = new List <IData>(chunkSize); queries = new List <string>(); } } if (data.Count > 0) { if (relationshipsResult != null) { var relationshipWorkers = new List <Task <List <string> > >(); queries = MakeRelationshipTableQueries(data, relationshipsResult, idSaver, ref id, columns); } if (worker != null && !worker.IsCompleted) { worker.Wait(); } database.ExecuteMultipleNonQueriesInATranscaction(queries); MakeSQlTable_MultipleTables(data, tables.Last(), idSaver, database, columns); } else if (worker != null && !worker.IsCompleted) { worker.Wait(); } } //PushNotification("Cycle"); } } }