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