In a previous post, I discussed how to use the SqlBulkCopy class along with a SqlDataReader to quickly stream data from one database to another.  The reason that you can use a SqlDataReader as a parameter in the WriteToServer method of SqlBulkCopy is because it implements the IDataReader interface.  The great thing about this, is that any object that implements the IDataReader interface can be used in the WriteToServer method as long as it provides all of the necessary functionality.  In this post, we’ll go step by step through the process of creating your own custom IDataReader that can stream data in bulk into your database.

The first thing we need to do is create the class that represents our custom object. For this example, we’ll use an object that contains a unique ID and an integer. I’m doing this because both values can be generated randomly with ease. This class looks like the following:

public class CustomObject
{
    public Guid UniqueId { get; set; }
    public int RandomNumber { get; set; }
}

Next, we have to create our custom data reader class.  I’ve named the class CustomObjectDataReader:

public class CustomObjectDataReader : IDataReader
{
}

As you can see, this class implements the IDataReader interface. When you choose to implement this interface in Visual Studio, you will see lots and lots of methods are added to your class. Luckily, only a subset of these need to be implemented to make your class compatible with SqlBulkCopy. The members that need to be implemented are:

Properties

  • FieldCount

Methods

  • GetName
  • GetOrdinal
  • GetValue
  • Read

I’ll go through each of these one by one.

The FieldCount property is the number of data fields in each record. In our case, we have two properties in our class. Therefore, we can implement the FieldCount property like so:

public int FieldCount
{
    get { return 2; }
}

Next, we will implement the GetName method. This method is provided an ordinal which represents the column number in the database table we will be copying data to.  The method returns the name of the column for the specified ordinal.  Below is the implementation:

public string GetName(int i)
{
    switch (i)
    {
        case 0:
            return "UniqueId";
        case 1:
            return "RandomNumber";
        default:
            return string.Empty;
    }
}

Next, let’s implement the GetOrdinal method. This method is basically the opposite of the GetName method. This method returns the ordinal for the specified column name.

public int GetOrdinal(string name)
{
    switch (name)
    {
        case "UniqueId":
            return 0;
        case "RandomNumber":
            return 1;
        default:
            return -1;
    }
}

The GetValue method is up next. This method returns a value of the field for the supplied ordinal. For this method to work properly, you must maintain an index to the position in the data you are reading. I maintain this index in the _currentIndex field. You will see how the index is maintained when we implement the Read method. Until then, here is the implementation of GetValue:

public object GetValue(int i)
{
    switch (i)
    {
        case 0:
            return _objectList[_currentIndex].UniqueId;
        case 1:
            return _objectList[_currentIndex].RandomNumber;
        default:
            return null;
    }
}

The Read method returns a boolean value which signifies whether there is more data available for the IDataReader to read. In my implementation, this is determined by comparing the _currentIndex field against the length of the _objectList field. The implementation of Read follows:

public bool Read()
{
    if ((_currentIndex + 1) < _objectList.Count)
    {
        _currentIndex++;
        return true;
    }
    else
    {
        return false;
    }
}

The only other code necessary in our custom IDataReader is a constructor and a few field declarations:

private List<CustomObject> _objectList = null;
private int _currentIndex = -1;
public CustomObjectDataReader(List<CustomObject> o)
{
    _objectList = o;
}

So that is the required code to create your custom IDataReader class. You should also have a whole bunch of unimplemented methods that we didn’t write implementations for. That’s fine as they aren’t necessary for this purpose.

The final thing we need to do, is go over how to use this object. The code to use the object is pretty simple:

List<CustomObject> list = new List<CustomObject>();
Random r = new Random();
for (int i = 0; i < 10000; i++)
{
    CustomObject t = new CustomObject();
    t.UniqueId = Guid.NewGuid();
    t.RandomNumber = r.Next();
    list.Add(t);
}

CustomObjectDataReader reader = new CustomObjectDataReader(list);
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (SqlBulkCopy cpy = new SqlBulkCopy(conn))
    {
        cpy.DestinationTableName = "RandomNumbers";
        cpy.WriteToServer(reader);
    }
}

The code is quite simple. First, we randomly generate data to create a list of 10,000 CustomObject objects. Next, we instantiate a new CustomObjectDataReader object and pass it a reference to the list of data. Next, we create an open a SqlConnection and then do the same for a SqlBulkCopy object. We set the DestinationTableName property and call the WriteToServer method along with passing it a reference to our CustomObjectDataReader. This code should successfully copy the 10,000 objects into the specified database table rather quickly.

That’s all that is required to create your own IDataReader object that is ready to use in SqlBulkCopy. I’ve had tons of success using this method during batch processing where millions of results need to be saved back to a database. Give it a shot and let me know if you find any other interesting uses for this technique!

Sample code can be downloaded here. The sample code contains a complete working example of what was discussed here as well as code that compares the performance of this methodology vs. using SqlCommand objects.

Using a Custom IDataReader to Stream Data Into a Database
Tagged on:     

Leave a Reply