#pragma warning disable CA1822 // Mark members as static
public DbDataReader UnpivotAsDataReader(object dataSource, string[] primaryColumns, string unpivotColumnName, Type unpivotColumnType,
#pragma warning restore CA1822 // Mark members as static
string unpivotValueColumnName, Type unpivotValueType, UnpivotOptions options = null)
{
options ??= new UnpivotOptions();
if (primaryColumns == null || primaryColumns.Length <= 0)
{
throw new ArgumentNullException(nameof(primaryColumns));
}
if (string.IsNullOrWhiteSpace(unpivotColumnName))
{
throw new ArgumentNullException(nameof(unpivotColumnName));
}
if (string.IsNullOrWhiteSpace(unpivotValueColumnName))
{
throw new ArgumentException(nameof(unpivotValueColumnName));
}
if (unpivotValueType == null)
{
throw new ArgumentNullException(nameof(unpivotValueType));
}
var reader = ScriptHostObject.GetDataSourceReader(dataSource,
new DataSourceParameters()
{
IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
});
if (reader == null)
{
throw new Exception("Input data are not provided.");
}
try
{
var readerParameters = new UnpivotDataReader.Parameters()
{
DataSource = reader,
PrimaryColumns = primaryColumns,
IgnoreColumns = options.IgnoreColumns,
IgnoreErrors = options.IgnoreErrors,
UnPivotColumnName = unpivotColumnName,
UnpivotColumnType = unpivotColumnType,
UnPivotValueColumnName = unpivotValueColumnName,
UnPivotValueType = unpivotValueType,
SkipValues = options.SkipValues
};
var result = new UnpivotDataReader(readerParameters);
return(result);
}
catch (Exception)
{
reader.Close();
reader.Dispose();
throw;
}
}
#pragma warning disable CA1822 // Mark members as static
public DataTable Pivot(object dataSource, string[] primaryColumns, string pivotColumn, string pivotValueColumn,
#pragma warning restore CA1822 // Mark members as static
PivotAggregateFunction AggregateFunction = PivotAggregateFunction.First, PivotOptions options = null)
{
options ??= new PivotOptions();
if (primaryColumns == null || primaryColumns.Length <= 0)
{
throw new ArgumentNullException(nameof(primaryColumns));
}
if (string.IsNullOrWhiteSpace(pivotColumn))
{
throw new ArgumentNullException(nameof(pivotColumn));
}
if (string.IsNullOrWhiteSpace(pivotValueColumn))
{
throw new ArgumentException(nameof(pivotValueColumn));
}
using var reader = ScriptHostObject.GetDataSourceReader(dataSource,
new DataSourceParameters()
{
IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
});
if (reader == null)
{
throw new Exception("Input data are not provided.");
}
using var sourceTable = new DataTable("Source");
sourceTable.Load(reader);
var primColumns = primaryColumns.Select(pc => sourceTable.Columns[pc]).ToArray();
var pivColumn = sourceTable.Columns[pivotColumn];
var pivValueColumn = sourceTable.Columns[pivotValueColumn];
if (primColumns.Length <= 0 || primColumns.Contains(null))
{
throw new ArgumentException("Cannot find one or more of primary columns.");
}
if (pivColumn == null)
{
throw new ArgumentException($"Cannot find column '{pivotColumn}'.");
}
if (pivValueColumn == null)
{
throw new ArgumentException($"Cannot find column '{pivotValueColumn}'.");
}
var result = PivotToTable(sourceTable, primColumns, pivColumn, pivValueColumn, AggregateFunction);
return(result);
}
#pragma warning disable CA1822 // Mark members as static
public DbDataReader ConvertToDataReader(object dataSource, ConvertToDataReaderOptions options = null)
#pragma warning restore CA1822 // Mark members as static
{
options ??= new ConvertToDataReaderOptions();
var reader = ScriptHostObject.GetDataSourceReader(dataSource,
new DataSourceParameters()
{
IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
});
return(reader);
}
public void ExportTableToDatabase(object dataSource, Connection connection, string tableName, ExportTableToDatabaseOptions options = null)
{
options ??= new ExportTableToDatabaseOptions();
const string progressDescription = "Exporting table into database";
var dataReader = ScriptHostObject.GetDataSourceReader(dataSource,
new DataSourceParameters()
{
IgnoreErrors = options.IgnoreErrors, Columns = options.SelectColumns, SkipColumns = options.SkipColumns
});
if ((connection.DbConnection?.State ?? ConnectionState.Closed) != ConnectionState.Open)
{
connection.Open();
}
if (options.ReportProgress)
{
UpdateProgress(ProgressKind.Value, 0, 100, progressDescription);
}
if (!string.IsNullOrWhiteSpace(options.PreScript))
{
ExecuteScript(connection, options.PreScript, options);
}
var exporter = DbExporter.GetDbExporter(connection.FactoryInvariantName);
if (exporter == null)
{
throw new Exception("Cannot configure DB exporter for selected connection.");
}
exporter.SkipAutoID = options.SkipAutoID;
if (options.Replace)
{
exporter.DropTable(connection.DbConnection, options.TableSchema, tableName);
}
if (!string.IsNullOrWhiteSpace(options.CreateTableScript))
{
ExecuteScript(connection, options.CreateTableScript, options);
}
if (options.BatchSize.HasValue)
{
exporter.BatchSize = options.BatchSize.Value;
}
if (options.ReportProgress)
{
exporter.Progress += (s, e) =>
{
var percent = (e.Max > 0 && e.Progress <= e.Max) ? Convert.ToInt32(Convert.ToDouble(e.Progress) / Convert.ToDouble(e.Max)) : 50;
percent = Utils.ValueInRange(percent, 0, 100);
UpdateProgress(ProgressKind.Value, percent, 100, progressDescription);
};
}
try
{
bool needCreateTable = !options.UseExistingTable && string.IsNullOrWhiteSpace(options.CreateTableScript);
exporter.ExportDataTable(connection.DbConnection, dataReader,
options.TableSchema, tableName, needCreateTable, CancellationToken.None);
}
finally
{
if (options.ReportProgress)
{
UpdateProgress(ProgressKind.Value, 0, 100, string.Empty);
}
}
if (!string.IsNullOrWhiteSpace(options.PostScript))
{
ExecuteScript(connection, options.PostScript, options);
}
}