How to add columns to DataReader

Question

My goal is to retrieve data from a data source, add some metadata to it and insert it to another target.

The target has schema with four more columns then the source (calculated columns).

I am using SqlBulkCopy, which requires a reader with all columns (including the 4 calculated).

Is there a way to add columns to the DataReader manually? or if it's not possible what alternatives i have for the data insertion?

Answer 1

A DataReader is a read-only construct and so it can't be modified. You can use a DataTable instead.

Answer 2

It is possible

• Create your own class that implements the IDataReader interface
• Add an existing DataReader in your class constructor
• override the interface as needed to return the result from your Base DataReader or return your own calculated values

Just to get an idea, this could be a simple implementation (I skipped most methods)

public class WrapperDataReader : IDataReader
{
    private IDataReader reader;

    public WrapperDataReader(IDataReader reader)
    {
        this.reader = reader;
    }

    public void Close()
    {
        reader.Close();
    }

    public int Depth
    {
        get { return reader.Depth; }
    }

    public DataTable GetSchemaTable()
    {
        var schemaTable = reader.GetSchemaTable();
        // add your computed column to the schema table
        schemaTable.Rows.Add(...);
        return schemaTable;
    }

    public bool GetBoolean(int i)
    {
        return reader.GetBoolean(i);
    }

    public int GetOrdinal(string name)
    {
        if (name.Equals("displayName", StringComparison.InvariantCultureIgnoreCase))
            return 15;
        return reader.GetOrdinal(name);
    }

    public string GetString(int i)
    {
        if (i == 15)
            return String.Format("{0}, {1}", GetString(1), GetString(2)); // lastname, firstname
        return reader.GetString(i);
    }

}

Update

Since you are propably using the WriteToServer method, you can use the overload that takes a DataTable instead.

        var connectionString = "...";
        var copy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Default);
        copy.DestinationTableName = "Customers";

        var reader = new SqlDataReader();
        var table = new DataTable();
        table.Load(reader);
        table.Columns.Add("DisplayName", typeof(string), "lastname, firstname");
        table.Columns.Add("CustomerCode", typeof(string));

        foreach (DataRow row in table.Rows)
            row["CustomerCode"] = ((int)row["id"] + 10000).ToString();

        copy.WriteToServer(table);