DataStreams
Framework for reading, writing, and bulk inserting files from .Net Download Demo

SQL Data Dictionary
Quickly and easily document your SQL Server database Download Demo

Home > Posts > Creating a Generic List DataReader for SqlBulkCopy

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.

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.

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:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using DataStreams.ETL;

class Program
{
	static void Main(string[] args)
	{
		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 (IDataReader reader = candidates.GetDataReader())
		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);
		}
	}
}

This code also works for Typed DataTables, Linq to SQL objects, and Entity Framework objects because they all extend from IEnumerable<T>.

Please send any questions or comments to Bruce Dunwiddie.