public SheetWrapper(ExcelWorksheet sheet,bool revriteExising=false)
        {
            ColumnMap = new Dictionary<string, int>();
            Sheet = sheet;

            if (sheet.Dimension == null || revriteExising)
            {
                RowCursor = 2;
                return;
                
            }

            if (sheet.Dimension.Rows != 0)
            {
                RowCursor = sheet.Dimension.Rows + 1;
            }

            if (sheet.Dimension.Columns != 0)
            {
                for (int i = 1; i < sheet.Dimension.Columns; i++)
                {
                    ColumnMap.AddOrOvewrite(sheet.Cells[1, i].Value.ToString(), i);
                }
            }
        }
        public int AddRow(Row externalRow)
        {
            var row = externalRow.Columns.Select(x => new KeyValuePair<string, string>(x.Key.ToColumnName(), x.Value));
            foreach (var item in row)
            {
                GetOrCreateColumn(item.Key.ToColumnName());
            }

            //Execute insert statement

            //Row with properly escaped column names
            var pureRow = row.Select(x => new KeyValuePair<string, string>(commandBuilder.QuoteIdentifier(x.Key.ToColumnName()), x.Value)).ToList();

            var valueByKey = pureRow.ToDictionary(x => x.Key, x => x.Value);
            var parameterNamesByKey = new Dictionary<string, string>();
            int i = 1;
            foreach (var pair in pureRow)
            {
                parameterNamesByKey.AddOrOvewrite(pair.Key, string.Format("@a{0}", i));
                i++;
            }

            string pureTableName = commandBuilder.QuoteIdentifier(_tableName);

            var columNames = parameterNamesByKey.ToList()
                                                .Select(x => x.Key)
                                                .Aggregate((l, r) => string.Format("{0},{1}", l, r));

            var valueParameters = parameterNamesByKey.ToList()
                                                          .Select(x => x.Value)
                                                          .Aggregate((l, r) => string.Format("{0},{1}", l, r));

            var insertComand = string.Format(@"INSERT INTO {0} ({1})
                                 VALUES({2})",pureTableName, columNames,valueParameters);

            using (var cmd = _connection.CreateCommand())
            {
                cmd.CommandText = insertComand;
                foreach (var pair in parameterNamesByKey)
                {
                    var value = valueByKey[pair.Key];
                    var paramName = pair.Value;
                    if (string.IsNullOrEmpty(value))
                    {
                        cmd.Parameters.AddWithValue(paramName, DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(paramName, value);
                    }
                }
                ExecuteCmd(() => cmd.ExecuteNonQuery());
            }
                //TODO return our new autoincremented default identity
                return 1;
        }
Example #3
0
 public Row(string key, string value)
 {
     _columns = new Dictionary<string, string>();
     _columns.AddOrOvewrite(key, value);
 }