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