CsvDataReader GetTypedReader()
{
var tr = File.OpenText("Data\\Types.csv");
var schema = new TypedCsvSchema();
schema.Add("Byte", typeof(byte));
schema.Add("Int16", typeof(short));
schema.Add("Int32", typeof(int));
schema.Add("Int64", typeof(long));
schema.Add("Char", typeof(char));
schema.Add("String", typeof(string));
schema.Add("Bool", typeof(bool));
schema.Add("Float", typeof(float));
schema.Add("Double", typeof(double));
schema.Add("DateTime", typeof(DateTime));
schema.Add("Decimal", typeof(decimal));
schema.Add("Guid", typeof(Guid));
var opts = new CsvDataReaderOptions()
{
Schema = schema
};
return(CsvDataReader.Create(tr, opts));
}
public void SqlTVPSample()
{
using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset
var csb =
new SqlConnectionStringBuilder
{
DataSource = @"(LocalDb)\MSSqlLocalDb",
InitialCatalog = "Test",
IntegratedSecurity = true
};
using var conn = new SqlConnection(csb.ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from Feature";
var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();
reader.Close();
var csvSchema = new Schema(tableSchema);
var options = new CsvDataReaderOptions
{
Delimiter = '|',
Schema = csvSchema,
};
DbDataReader dataReader = CsvDataReader.Create(csvText, options);
cmd.CommandText = "begin tran";
cmd.ExecuteNonQuery();
cmd.CommandText = "InsertFeatures";
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter()
{
ParameterName = "data",
SqlDbType = SqlDbType.Structured
};
dataReader = new BoundedDataReader(dataReader, 100);
var paramData = dataReader;
param.Value = paramData;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
cmd.CommandText = "commit tran";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
}
public void SylvanRead()
{
using var tr = GetData();
var options = new CsvDataReaderOptions {
Delimiter = '|'
};
using var dr = CsvDataReader.Create(tr);
while (dr.Read())
{
var id = dr.GetString(0);
}
}
public void BufferTooSmall()
{
var opts = new CsvDataReaderOptions()
{
BufferSize = 128
};
using var tr = File.OpenText("Data\\Binary.csv");
var csv = CsvDataReader.Create(tr, opts);
csv.Read();
Assert.Throws <CsvRecordTooLargeException>(() => csv.Read());
}
public void DupeHeaderFix()
{
var data = "a,b,c,d,e,e";
var fixSchema = new ExcelHeaders();
var opts = new CsvDataReaderOptions {
Schema = fixSchema
};
var csv = CsvDataReader.Create(new StringReader(data), opts);
Assert.Equal(6, csv.FieldCount);
}
public void Date1()
{
using var tr = new StringReader("Date\n20200803\n20200804\n20200805\n");
var opts = new CsvDataReaderOptions()
{
DateFormat = "yyyyMMdd"
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 3), csv.GetDateTime(0));
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 4), csv.GetDateTime(0));
Assert.True(csv.Read());
Assert.Equal(new DateTime(2020, 8, 5), csv.GetDateTime(0));
}
public async Task SylvanDeDupeReuse()
{
using var tr = TestData.GetTextReader();
var opts = new CsvDataReaderOptions {
StringFactory = pool
};
using var dr = await CsvDataReader.CreateAsync(tr, opts);
while (await dr.ReadAsync())
{
for (int i = 0; i < dr.FieldCount; i++)
{
var s = dr.GetString(i);
}
}
}
public void Boolean3()
{
using var tr = new StringReader("Bool\nT\nF\nX\n");
var opts = new CsvDataReaderOptions()
{
TrueString = null,
FalseString = "f",
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.False(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
}
public void Boolean1()
{
using var tr = new StringReader("Bool\nT\nF\nX\n");
var opts = new CsvDataReaderOptions()
{
TrueString = "t",
FalseString = "f",
};
var csv = CsvDataReader.Create(tr, opts);
Assert.True(csv.Read());
Assert.True(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.False(csv.GetBoolean(0));
Assert.True(csv.Read());
Assert.Throws <FormatException>(() => csv.GetBoolean(0));
}
public void SqlBulkLoadSample()
{
using var csvText = GetData(); // Gets a TextReader over a large-ish CSV dataset
var csb =
new SqlConnectionStringBuilder
{
DataSource = @"(LocalDb)\MSSqlLocalDb",
InitialCatalog = "Test",
IntegratedSecurity = true
};
using var conn = new SqlConnection(csb.ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from Feature";
var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();
reader.Close();
var csvSchema = new Schema(tableSchema);
var options = new CsvDataReaderOptions
{
Delimiter = '|',
Schema = csvSchema,
};
DbDataReader dataReader = CsvDataReader.Create(csvText, options);
//dataReader = new EmptyAsNullDataReader(dataReader);
var bcp = new SqlBulkCopy(conn);
bcp.BulkCopyTimeout = 0;
bcp.DestinationTableName = "Feature";
bcp.BatchSize = 50000;
bcp.WriteToServer(dataReader);
}
public void SylvanDataTable()
{
var schema = new TypedCsvSchema();
schema.Add(0, typeof(int));
schema.Add(1, typeof(string));
schema.Add(2, typeof(double?));
schema.Add(3, typeof(DateTime));
schema.Add(4, typeof(DateTime?));
var options = new CsvDataReaderOptions
{
Schema = schema
};
using var tr = GetData();
using var dr = CsvDataReader.Create(tr, options);
var dt = new DataTable();
dt.Load(dr);
}
public async Task BinaryValues()
{
using (var reader = File.OpenText("Data\\Binary.csv"))
{
var schema = new TypedCsvSchema();
schema.Add(1, typeof(byte[]));
var opts = new CsvDataReaderOptions()
{
Schema = schema
};
var csv = await CsvDataReader.CreateAsync(reader, opts);
csv.Read();
var vals = new object[2];
csv.GetValues(vals);
var expected = Encoding.ASCII.GetBytes(BinaryValue1);
Assert.Equal(expected, (byte[])vals[1]);
csv.Read();
csv.GetValues(vals);
expected = Encoding.ASCII.GetBytes(BinaryValue2);
Assert.Equal(expected, (byte[])vals[1]);
}
}
public async Task NoHeadersWithSchema()
{
var schema = new ExcelHeaders();
var opts =
new CsvDataReaderOptions
{
HasHeaders = false,
Schema = schema
};
using (var reader = File.OpenText("Data\\DataOnly.csv"))
{
var csv = await CsvDataReader.CreateAsync(reader, opts);
Assert.Equal(4, csv.FieldCount);
Assert.True(csv.HasRows);
Assert.Equal(0, csv.RowNumber);
Assert.Equal("C", csv.GetName(2));
Assert.Equal(3, csv.GetOrdinal("D"));
Assert.Equal("A", csv.GetName(0));
Assert.Throws <IndexOutOfRangeException>(() => csv.GetOrdinal("Id"));
Assert.True(await csv.ReadAsync());
Assert.Equal(1, csv.RowNumber);
Assert.Equal("1", csv[0]);
Assert.Equal("John", csv[1]);
Assert.Equal("Low", csv[2]);
Assert.Equal("2000-11-11", csv[3]);
Assert.True(await csv.ReadAsync());
Assert.Equal(2, csv.RowNumber);
Assert.Equal("2", csv[0]);
Assert.Equal("Jane", csv[1]);
Assert.Equal("High", csv[2]);
Assert.Equal("1989-03-14", csv[3]);
Assert.False(await csv.ReadAsync());
}
}