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