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; }
public Row(string key, string value) { _columns = new Dictionary<string, string>(); _columns.AddOrOvewrite(key, value); }