public InsertObject GetInsertStatement(string fullPath, string tableGuid) { StringBuilder allInserts = new StringBuilder(""); Dictionary <string, int> map = CreateColNameToIndexMap(fullPath); Dictionary <string, Column> colMap = CreateColNameToColumnMap(); Dictionary <string, string> commandParameters = new Dictionary <string, string>(); int count = 0; using (StreamReader sr = new StreamReader(fullPath)) using (var csv = new CsvReader(sr)) { csv.Configuration.BadDataFound = context => { //skip }; csv.Configuration.PrepareHeaderForMatch = (string header, int index) => header.ToLower(); try { csv.Read(); csv.ReadHeader(); while (csv.Read()) { string insertStatement = "INSERT INTO " + "[" + tableGuid + "]" + " ( " + String.Join(",", Columns.Select(x => "[" + x.Pseudonym + "]")) + ") VALUES (" + String.Join(",", Columns.Select(x => "@" + count.ToString() + x.Pseudonym.Replace("-", ""))) + ");"; for (var i = 0; i < Columns.Count; i++) { commandParameters["@" + count.ToString() + Columns[i].Pseudonym.Replace("-", "")] = csv.GetField(Columns[i].ColumnName); } allInserts.Append(insertStatement); count += 1; } } catch (Exception ex) { //do something } } string allInsertStatements = allInserts.ToString(); InsertObject insertObject = new InsertObject(); insertObject.ParameterMap = commandParameters; insertObject.InsertStatements = allInsertStatements.TrimEnd(';'); insertObject.RowCount = count; return(insertObject); }
public async void InsertDataRows(string[] splitStatements, Dal dal, IHubContext <SignalRHub> signalHubContext, InsertObject insertObject) { int maxRows = (int)((decimal)2000 / Columns.Count); int numStatements = splitStatements.Length - 1; int numToSkip = 0; int numToProcess = Math.Min(3000, maxRows); while (numStatements > 0) { string insert = string.Join("", splitStatements.Skip(numToSkip).Take(numToProcess)); await dal.ExecuteInsertObject(this, insert, insertObject.ParameterMap, ConnectionSettings.InsertConnString, numToProcess, numToSkip, splitStatements.Length - 1); await signalHubContext.Clients.All.SendAsync("row update", numToSkip); numToSkip += maxRows; numStatements -= maxRows; } }