public override DataGrid Import()
{
if (!Settings.FormatterType.IsDefined()) throw new InvalidOperationException("A Table Name or Sql Query was not specified.");
DataGrid dataGrid = new DataGrid();
Columns.Where(match => !Excluded.Contains(match.ColumnName)).ForEach(c => dataGrid.Columns.Add(c));
dataGrid.Read(MediaConfiguration.Reader);
Log.Info(String.Format("Importing {0} Rows to Database...", dataGrid.Rows.Count));
string sqlQuery;
if (Settings.FormatterType.IsSqlTable())
{
sqlQuery = String.Format("INSERT INTO {0} ({1}) VALUES ({2})"
, Table()
, Columns.Select(c => c.DbColumnName()).JoinStrings(",")
, Columns.Select(c => c.DbParamName()).JoinStrings(","));
}
else if (Settings.FormatterType.IsSqlQuery())
{
sqlQuery = Settings.SqlQuery;
}
else
{
throw new NotSupportedException(String.Format("Formatter Type '{0}' Is Not Supported.", Settings.FormatterType.DisplayName()));
}
using (DbContext context = new DbContext(Settings.ConnectionString))
{
context.Database.CommandTimeout = 300;
using (DbContextTransaction transaction = context.Database.BeginTransaction())
{
if (Settings.FormatterType.IsSqlTable() && Settings.AlwaysTruncate)
{
Log.Info("Truncating Table...");
context.Database.ExecuteSqlCommand(String.Format("TRUNCATE TABLE {0}", Table()));
Log.Info("Done Truncating Table.");
}
if (!Settings.PreEvent.IsNullOrBlank())
{
Log.Info("Executing Pre-event...", new LogAttachment("Sql", Settings.PreEvent));
Log.Trace(Settings.PreEvent);
context.Database.ExecuteSqlCommand(Settings.PreEvent);
Log.Info("Done Executing Pre-event.");
}
foreach (DataGridRow row in dataGrid.Rows)
{
int rowIndex = row.Index() + 1;
SqlParameter[] sqlParams = null;
try
{
Log.Trace(String.Format("Importing Row '{0}' to Database.", rowIndex));
sqlParams = Columns.Select(c => c.DbParam(row[c.ColumnName])).ToArray();
context.Database.ExecuteSqlCommand(sqlQuery, sqlParams);
}
catch (Exception exception)
{
string error = String.Format("An error ocurred while importing Row '{0}' to Database.", rowIndex);
LogAttachments attachments = new LogAttachments();
attachments.Add(new LogAttachment("Sql", sqlQuery));
if (sqlParams != null)
{
attachments.AddRange(sqlParams.Select(p => new LogAttachment(p.ParameterName, p.Value.FormatString())));
}
Log.Error(error, exception, attachments.ToArray());
throw new AbortException(error, exception);
}
}
if (!Settings.PostEvent.IsNullOrBlank())
{
Log.Info("Executing Post-event...");
Log.Trace(Settings.PostEvent);
context.Database.ExecuteSqlCommand(Settings.PostEvent);
Log.Info("Done Executing Post-event.");
}
transaction.Commit();
}
}
Log.Info("Done importing to Database.");
return dataGrid;
}
