Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

IDataReader implementation + SqlBulkCopy

Rate me:
Please Sign up or sign in to vote.
4.77/5 (10 votes)
22 May 2013CPOL2 min read 52.6K   12   10
IDataReader implementation illustrated with SqlBulkCopy class usage

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  Smile | <img src=). 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:  

C#
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:

C#
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:   

C#
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:  

C#
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). 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Brazil Brazil
Yesterday C++, today C#.

Comments and Discussions

 
GeneralMy vote of 4 Pin
ankita vishal15-May-14 1:33
ankita vishal15-May-14 1:33 
QuestionThoughts Pin
PIEBALDconsult20-Jul-11 18:22
mvePIEBALDconsult20-Jul-11 18:22 
AnswerRe: Thoughts Pin
Espen Harlinn21-Jul-11 0:16
professionalEspen Harlinn21-Jul-11 0:16 
GeneralRe: Thoughts Pin
cesar_boucas21-Jul-11 6:08
cesar_boucas21-Jul-11 6:08 
GeneralRe: Thoughts Pin
Espen Harlinn21-Jul-11 9:55
professionalEspen Harlinn21-Jul-11 9:55 
GeneralRe: Thoughts Pin
cesar_boucas21-Jul-11 10:03
cesar_boucas21-Jul-11 10:03 
AnswerRe: Thoughts Pin
cesar_boucas21-Jul-11 4:25
cesar_boucas21-Jul-11 4:25 
If you are using C# you should use the SqlBulkCopy class instead of programmatically call the bcp command. Especially if your file is not well formatted and/or you need to manipulate the data before the table inserts.
"Go for it!"

GeneralRe: Thoughts Pin
PIEBALDconsult21-Jul-11 14:26
mvePIEBALDconsult21-Jul-11 14:26 
QuestionSome info needed Pin
Mycroft Holmes20-Jul-11 14:32
professionalMycroft Holmes20-Jul-11 14:32 
AnswerRe: Some info needed Pin
cesar_boucas21-Jul-11 4:16
cesar_boucas21-Jul-11 4:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.