Microsoft .NET Framework 2.0 introduces a new class called SqlBulkCopy. As, the name suggest this class is used to perform massive data copy operations from one source to the other. Let’s take a look at the simple copy operation from table Customers to CustomersArchive. Both the tables contain the following columns:
CustomerID: Stores the ID of the customer
FirstName: Stores the first name of the customer
LastName: Stores the last name of the customer
The most basic copy operation is implemented as follows:
string connectionString = "Server=localhost;Database=EasyShopping;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("select * from customers", myConnection);
myConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
using (SqlBulkCopy bulk = new SqlBulkCopy(connectionString))
{
bulk.BatchSize = 500;
bulk.DestinationTableName = "CustomersArchive";
bulk.NotifyAfter = 500;
bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
bulk.WriteToServer(reader);
}
myConnection.Close();
The bulk.WriteToServer method takes various overloads but I am using the overload which takes the IDataReader as the parameter. This is because IDataReader will allow me to iterate over the records without having to store all the records in memory. This will be a much faster operation then loading the records into DataTable or DataRows.
In the above code the schema for both the tables was same. If you have tables with different schemas you can use the SqlBulkCopy.
bulk.ColumnMappings.Add("FirstName", "FirstNameArchive");
bulk.ColumnMappings.Add("LastName", "LastNameArchive");
How about importing data from a large XML file? Well, you can always read the XML file into the DataSet and then use DataSet.Tables[0] to provide the SqlBulkCopy.WriteToServer method with the DataTable. But this approach is not good since now we are loading the data into the memory collection. The collection in this case is DataSet.
You might be thinking that you can get away by using XmlTextReader! Unfortunately no since WriteToServer method only takes in the object which inherits the IDataReader interface. This means you have to create a custom reader which inherits from the IDataReader.
Here is the implementation of the XmlDataReader:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
namespace SqlBulkInsertDemo
{
public class XmlDataReader : IDataReader
{
private string _path;
private XmlTextReader _reader;
private int _oridinalPosition = -1;
private string[] _columns;
public XmlDataReader(string path, string[] columns)
{
_path = path;
_columns = columns;
_reader = new XmlTextReader(path);
}
public void Close()
{
_reader.Close();
}
public int Depth
{
get { throw new NotImplementedException(); }
}
public DataTable GetSchemaTable()
{
throw new NotImplementedException();
}
public bool IsClosed
{
get { throw new NotImplementedException(); }
}
public bool NextResult()
{
throw new NotImplementedException();
}
public bool Read()
{
return _reader.Read();
}
public int RecordsAffected
{
get { throw new NotImplementedException(); }
}
public void Dispose()
{
throw new NotImplementedException();
}
public int FieldCount
{
get { return 2; }
}
public bool GetBoolean(int i)
{
throw new NotImplementedException();
}
public byte GetByte(int i)
{
throw new NotImplementedException();
}
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public char GetChar(int i)
{
throw new NotImplementedException();
}
public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
{
throw new NotImplementedException();
}
public IDataReader GetData(int i)
{
throw new NotImplementedException();
}
public string GetDataTypeName(int i)
{
throw new NotImplementedException();
}
public DateTime GetDateTime(int i)
{
throw new NotImplementedException();
}
public decimal GetDecimal(int i)
{
throw new NotImplementedException();
}
public double GetDouble(int i)
{
throw new NotImplementedException();
}
public Type GetFieldType(int i)
{
throw new NotImplementedException();
}
public float GetFloat(int i)
{
throw new NotImplementedException();
}
public Guid GetGuid(int i)
{
throw new NotImplementedException();
}
public short GetInt16(int i)
{
throw new NotImplementedException();
}
public int GetInt32(int i)
{
throw new NotImplementedException();
}
public long GetInt64(int i)
{
throw new NotImplementedException();
}
public string GetName(int i)
{
throw new NotImplementedException();
}
public int GetOrdinal(string name)
{
_oridinalPosition++;
return _oridinalPosition;
}
public string GetString(int i)
{
throw new NotImplementedException();
}
public object GetValue(int i)
{
string value = String.Empty;
try
{
while (_reader.Read())
{
if (_reader.NodeType == XmlNodeType.Element)
{
if (!String.IsNullOrEmpty(_reader.Name) && _columns.Contains(_reader.Name))
{
value = _reader.ReadElementString(_reader.Name);
break;
}
}
}
}
catch (Exception ex) {
// log the exception!
}
return value;
}
public int GetValues(object[] values)
{
throw new NotImplementedException();
}
public bool IsDBNull(int i)
{
throw new NotImplementedException();
}
public object this[string name]
{
get { return _reader[name]; }
}
public object this[int i]
{
get { return _reader[i]; }
}
}
}
I only implemented the methods which were necessary for the bulk operation. Now, you can easily use the new custom XmlDataReader class for your bulk copy operations.
private static void ImportDataFromXmlFile()
{
string path = @"C:\Customers.xml";
IDataReader reader = new XmlDataReader(path,new string[] {"FirstName","LastName"});
try
{
using (SqlBulkCopy copy = new SqlBulkCopy(_connectionString))
{
copy.ColumnMappings.Add("FirstName", "FirstName");
copy.ColumnMappings.Add("LastName", "LastName");
copy.DestinationTableName = "CustomersArchive";
copy.BatchSize = 500;
copy.SqlRowsCopied += new SqlRowsCopiedEventHandler(copy_SqlRowsCopied);
copy.WriteToServer(reader);
}
}
catch (Exception ex)
{
}
}
I hope you liked this post! I will write an article on this very soon which will be published on GridViewGuy. So, stay tuned!