Home > Posts > Creating a Validating DataReader for SqlBulkCopy
The ValidatingDataReader class is intended to provide as detailed error messages as possible for SqlBulkCopy in both invalid mappings and when data is too large for the destination column
Download ValidatingDataReader.cs (Right Click->Save Target As...).
The most frustrating thing about working with SqlBulkCopy is the lack of useful error messages:
- Received an invalid column length from the bcp client for colid N.
- The given ColumnName 'X' does not match up with any column in data source.
- The given ColumnMapping does not match up with any column in the source or destination.
I get asked about these messages all the time and how to go about tracking down the offending piece of data or mapping.
It even gives you suggestions in the case where capitalization may be a factor and if you choose to, giving you the opportunity to truncate offending data to prevent an exception. Compare the previous error messages to these:
- Column value "X" with length N from source column X in record N does not fit in destination column X with length N in table X in database X on server X.
- Source column x does not exist in source. Column name mappings are case specific and best found match is X.
- Destination column x does not exist in destination table X in database X on server X. Column name mappings are case specific and best found match is X.
The usage of the ValidatingDataReader class is simple. You just pass an instance of IDataReader into the constructor, along with the SqlBulkCopy object and the SqlConnection, and optionally the SqlTransaction, and you pass the ValidatingDataReader instance into SqlBulkCopy's WriteToServer method instead of the original IDataReader. ValidatingDataReader will query the destination table to pull metadata information and check mappings. It will then monitor each record passed from the original IDataReader and check the values against the destination table column lengths.
using (CsvDataReader reader = CsvDataReader.Parse("id,name\r\n1,Bruce Dunwiddie"))
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Database))
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
conn.Open();
using (SqlCommand createTable = new SqlCommand(@"
IF EXISTS
(
SELECT *
FROM
sys.tables t
INNER JOIN sys.schemas s ON
s.schema_id = t.schema_id
WHERE
t.[name] = 'Test' AND
s.[name] = 'dbo'
)
BEGIN
DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test
(
[id] INT,
[name] VARCHAR(10) -- this is obviously too short
);", conn))
{
createTable.ExecuteNonQuery();
}
reader.Settings.HasHeaders = true;
reader.Columns.Add("int");
reader.Columns.Add("varchar");
bcp.DestinationTableName = "dbo.Test";
bcp.ColumnMappings.Add("id", "[id]");
bcp.ColumnMappings.Add("name", "[name]");
using (ValidatingDataReader validator = new ValidatingDataReader(reader, conn, bcp))
{
bcp.WriteToServer(validator);
}
}
Please send any questions or comments to Bruce Dunwiddie.
|
