IDataReader implementation + SqlBulkCopy

Introduction  

I will show here how to implement the IDataReader interface. I will also illustrate its usage by showing how to insert data from a file into a SQL Server table using the SqlBulkCopy class.  

Background 

The IDataReader interface has a lot of methods and also makes you implement IDataRecord ( lot-of-methodsx2  ). Maybe because of this, all the IDataReader implementations I see around are usually more complicated that it really should. 

The approach presented below implements just the methods necessary to accomplish the task of read data from a file and insert it into a table. Trying to keep the code simple and efficient.   

Using the code   

First lets illustrate the usage of the IDataReader implementation that I will call MyFileDataReader.

To do so, I will use the SqlBulkCopy class that "Lets you efficiently bulk load a SQL Server table with data from another source". The "another source" could be another Table, or, in this case, a DataReader. 

With a DataReader implementation, we are able to insert the data from the file to a sql server table doing as follows:  

using (MyFileDataReader reader = new MyFileDataReader(@"C:\myfile.txt"))
{
  SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
  bulkCopy.DestinationTableName = "[serv].[dbo].[my_test_table]";
  bulkCopy.BatchSize = 10000;

  bulkCopy.WriteToServer(reader);

  bulkCopy.Close();

}  

The reader holds a stream and other members to help in reading the data from the file:

public class MyFileDataReader : IDataReader
{
    protected StreamReader Stream { get; set; }
    protected object[] Values;
    protected bool Eof { get; set; }
    protected string CurrentRecord { get; set; }
    protected int CurrentIndex { get; set; }

    public MyFileDataReader(string fileName)
    {
        Stream = new StreamReader(fileName);
        Values = new object[this.FieldCount];
    }  

The SqlBulkCopy class just call few methods to do the job. To simplify the implementation here, I will assume that the table that will receive the data from the file have just 3 columns: the primary key, and 2 string columns. And also: the file is fixed column formatted and have two columns: the first with width 12 and the second with width 40. Said that, we can do:   

public void Close()
{
    Array.Clear(Values, 0, Values.Length);
    Stream.Close();
    Stream.Dispose();
}

public int Depth
{
    get { return 0; }
}

public DataTable GetSchemaTable()
{// avoid to implement several methods if your scenario do not demand it

    throw new NotImplementedException();
}

public bool IsClosed
{
    get { return Eof; }
}

public bool NextResult()
{
    return false;
}

public bool Read()
{
    CurrentRecord = Stream.ReadLine();
    Eof = CurrentRecord == null;

    if (!Eof)
    {
        Fill(Values);
        CurrentIndex++;
    }

    return !Eof;
}

private void Fill(object[] values)
{ // by default, the first position of the array holds the value that will be  
   // inserted at the first column of the table, and so on
   // lets assume here that the primary key is auto-generated
    values[0] = null;
    values[1] = CurrentRecord.Substring(0, 12).Trim();
    values[2] = CurrentRecord.Substring(12, 40).Trim();
} // if the file is csv we could do a Split instead of Substring operations

public int RecordsAffected
{
    get { return -1; }
} 

To implement IDataReader, is also mandatory to implement the IDisposable and the IDataRecord interfaces.   

The IDataRecord obligate you to implement a lot of methods. But in this scenario there are some methods implementations that we are not able to avoid:  

public int FieldCount
{
    get { return 3; }
}

public IDataReader GetData(int i)
{
    if (i == 0)
        return this;

    return null;
}

public string GetDataTypeName(int i)
{
    return "String";
}

public string GetName(int i)
{
    return Values[i].ToString();
}

public string GetString(int i)
{
    return Values[i].ToString();
}

public object GetValue(int i)
{
    return Values[i];
}

public int GetValues(object[] values)
{
    Fill(values);

    Array.Copy(values, Values, this.FieldCount);

    return this.FieldCount;
}

public object this[int i]
{
    get { return Values[i]; }
}  

In this case, there is no need to implement the other methods of IDataRecord. 

The IDisposable have only the Dispose method that can be used to close the reader and release all the resources used. 

Points of Interest     

In my tests, this proved an efficient way for read data from a large file and insert it's contents into a SQL Server table. The IDataReader implementation presented above is very simple. It can be improved with exception handling and more data types support for example. It can also be adapted to support other file formats (e.g., CSV or XML).