Home > Posts > Creating a Generic List DataReader for SqlBulkCopy

Download GenericListDataReader.cs (Right Click->Save Target As...).

One of the downfalls of using custom collections based on generic lists is that SqlBulkCopy does not support writing generic list objects. It only supports bulk inserting from DataTables, DataRow arrays, or implementations of IDataReader. Fortunately, creating a custom IDataReader implementation enough so that SqlBulkCopy can use it is fairly simple. The GenericListDataReader class implements a simple IDataReader using reflection to allow SqlBulkCopy access to all public properties that are castable to SQL Server data types.

Given the custom collection objects below:
public class Candidate
{
	private int id;
	private string name;
	private DateTime createDate;

	public int ID
	{
		get
		{
			return id;
		}

		set
		{
			id = value;
		}
	}

	public string Name
	{
		get
		{
			return name;
		}

		set
		{
			name = value;
		}
	}

	public DateTime CreateDate
	{
		get
		{
			return createDate;
		}

		set
		{
			createDate = value;
		}
	}
}

public class Candidates : List<Candidate>
{

}
The code below dynamically drops and creates a destination table based on the public properties and bulk inserts the records to the table:
Candidates candidates = new Candidates()
	{
		new Candidate()
		{
			ID = 1,
			Name = "John",
			CreateDate = new DateTime(1997, 10, 24)
		},
		new Candidate()
		{
			ID = 2,
			Name = "Joe",
			CreateDate = new DateTime(2009, 1, 1)
		}
	};

using (GenericListDataReader<Candidate> reader = new GenericListDataReader<Candidate>(candidates))
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Database))
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
{
	conn.Open();

	bcp.DestinationTableName = "candidates";

	string createTableSql = "";

	createTableSql += "IF EXISTS(SELECT * FROM sys.tables t WHERE t.name = '" 
		+ bcp.DestinationTableName + "') DROP TABLE " + bcp.DestinationTableName + ";";
	createTableSql += "CREATE TABLE dbo." + bcp.DestinationTableName + "(";

	for (int column = 0; column < reader.FieldCount; column++)
	{
		if (column > 0)
		{
			createTableSql += ",";
		}

		createTableSql += "[" + reader.GetName(column) + "]" + " VARCHAR(MAX) NULL";
	}

	createTableSql += ");";

	using (SqlCommand createTable = new SqlCommand(createTableSql, conn))
	{
		createTable.ExecuteNonQuery();
	}

	bcp.WriteToServer(reader);
}
Please send any questions or comments to Bruce Dunwiddie.