public SqlTests()
{
var opts = new DataCopier.Options();
opts.outputTable = "testTable";
formatter = new dcp.lib.Formatters.Sql(opts, schema);
}
public Csv(Stream stream, DataCopier.Options opts)
{
this.opts = opts;
this.stream = stream;
this.textWriter = new StreamWriter(this.stream);
this.csv = new CsvWriter(textWriter);
csv.Configuration.HasHeaderRecord = false;
}
public void MakeReaderReturnsXlsx()
{
var opts = new DataCopier.Options {
};
using (var reader = (new DataCopier()).makeReader(tests.XlsxTests.FILENAME, opts))
{
Assert.True(reader is dcp.lib.Readers.Xlsx);
}
}
public void MakeWriterReturnsSqlServer()
{
var opts = new DataCopier.Options {
};
using (var writer = (new DataCopier()).makeWriter($"sql://{tests.SqlServerWriterTests.CONNECTIONSTRINGWITHOUTDATABASE}", opts))
{
Assert.True(writer is dcp.lib.Writers.SqlServer);
}
}
public void ReadsFirstRowValue()
{
var opts = new DataCopier.Options();
using (var reader = new Xlsx(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal(1.1, rows[0][0]);
}
}
public void ReadsFirstColumn()
{
var opts = new DataCopier.Options();
using (var reader = new Csv(stream, opts))
{
var rows = reader.Next(1);
Assert.Equal("1", rows[0][0]);
}
}
public void ReadsLastRowValue()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(1);
Assert.Equal(new DateTime(2017, 2, 1), rows[0][2]);
}
}
public void ReturnsShortListIfNotEnoughRows()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(9);
Assert.Equal(3, rows.Count);
}
}
public void ReturnsCorrectNumberOfRows()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(3);
Assert.Equal(3, rows.Count);
}
}
public void LeavesRows()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
opts.leaveRows = 1;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(9);
Assert.Equal(2, rows.Count);
}
}
public void SkipsColumns()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
opts.skipColumns = 1;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(1);
Assert.Equal("$$1", rows[0][0]);
}
}
public void UsesMaxColumns()
{
var opts = new DataCopier.Options();
opts.inputTable = TABLENAME;
opts.maxColumns = 1;
using (var reader = new SqlServer(CONNECTIONSTRINGWITHDATABASE, opts))
{
var rows = reader.Next(1);
Assert.Equal(91, rows[0][0]);
Assert.Single(rows[0]);
}
}
// private readonly int lastRowNumber;
public Csv(Stream stream, DataCopier.Options opts)
{
this.opts = opts;
this.stream = stream;
this.textReader = new StreamReader(this.stream);
this.csv = new CsvReader(textReader);
csv.Configuration.HasHeaderRecord = false;
// skiprows
while ((rowsRead < this.opts.skipRows) && csv.Read())
{
rowsRead += 1;
}
}
public void ConvertReturnsCorrectRowCount()
{
var opts = new DataCopier.Options {
};
opts.outputTable = tests.SqlServerWriterTests.TABLENAME;
var count = DataCopier.Convert(
opts,
tests.XlsxTests.FILENAME,
$"sql://{tests.SqlServerWriterTests.CONNECTIONSTRINGWITHDATABASE}"
);
Assert.Equal(3, count);
}
public SqlServer(string connectionString, DataCopier.Options opts)
{
if (String.IsNullOrWhiteSpace(opts.outputTable))
{
throw new ArgumentException("output table not specified");
}
this.connection = new SqlConnection(connectionString);
this.connection.Open();
this.opts = opts;
if (this.opts.truncate)
{
runQuery($"TRUNCATE TABLE {this.opts.outputTable};");
}
this.formatter = new Formatters.Sql(this.opts, getSchema());
}
public Xlsx(Stream stream, DataCopier.Options opts)
{
this.opts = opts;
this.stream = stream;
this.excel = new ExcelPackage(stream);
this.worksheet = excel.Workbook.Worksheets[this.opts.worksheetNumber];
if (this.worksheet.Dimension == null)
{
throw new Exception($"no data in worksheet {this.opts.worksheetNumber}");
}
nextRowNumber = this.opts.skipRows + 1;
lastRowNumber = this.worksheet.Dimension.End.Row - this.opts.leaveRows;
startColumnNumber = this.opts.skipColumns + 1;
endColumnNumber = this.worksheet.Dimension.End.Column;
if (this.opts.maxColumns != null)
{
endColumnNumber = Math.Min((int)this.opts.maxColumns, endColumnNumber);
}
}
public SqlServer(string connectionString, DataCopier.Options opts)
{
this.opts = opts;
if (String.IsNullOrWhiteSpace(opts.inputTable))
{
throw new ArgumentException("input table not specified");
}
this.connection = new SqlConnection(connectionString);
this.connection.Open();
nextRowNumber = this.opts.skipRows + 1;
var maxRows = getRowCount();
lastRowNumber = maxRows - this.opts.leaveRows;
startColumnNumber = this.opts.skipColumns + 1;
endColumnNumber = getColumnCount();
if (this.opts.maxColumns != null)
{
endColumnNumber = Math.Min((int)this.opts.maxColumns, endColumnNumber);
}
}
static void Main(string[] args)
{
CommandLine.Parser.Default.ParseArguments <cliOptions>(args)
.WithParsed <cliOptions>(cliOpts =>
{
var opts = new DataCopier.Options {
bufferRows = cliOpts.bufferRows,
//writer options
truncate = cliOpts.truncate,
inputTable = String.IsNullOrWhiteSpace(cliOpts.inputTable) ? null : cliOpts.inputTable.Trim(),
// reader options
outputTable = String.IsNullOrWhiteSpace(cliOpts.outputTable) ? null : cliOpts.outputTable.Trim(),
skipRows = cliOpts.skipRows,
leaveRows = cliOpts.leaveRows,
skipColumns = cliOpts.skipColumns,
maxColumns = cliOpts.maxColumns,
worksheetNumber = cliOpts.worksheetNumber
};
var startTime = DateTime.Now;
int lines = 0;
try {
lines = DataCopier.Convert(opts, cliOpts.readerUrl.Trim(), cliOpts.writerUrl.Trim());
}
catch (ArgumentException e) {
Console.Error.WriteLine(e.Message);
}
var endTime = DateTime.Now;
var elapsedSeconds = (endTime - startTime).TotalMilliseconds / 1000F;
Console.WriteLine($"{lines} lines copied in {elapsedSeconds} seconds ({lines/elapsedSeconds} lines per second).");
// Console.WriteLine($"{.readerUrl} {opts.writerUrl}");
})
.WithNotParsed <cliOptions>((errs) =>
{
// HelpText.AutoBuild<cliOptions>()
});
}
public Sql(DataCopier.Options opts, List <Column> schema)
{
this.tableName = opts.outputTable;
this.schema = schema;
}
