Using SqlBulkCopy to move data from VistaDB to SQL Server

Bulk copy from VistaDB to SQL Server

Did you know you can use the SqlBulkCopy object to move data from VistaDB to SQL Server?  The SqlBulkCopy object allows you to take any IDataReader object to copy. 

Source and Destination must match

The only trick is that the source and destination tables must be setup in advance to match each other. They must have equal datatypes and column names.   This is very easy with VistaDB since we already have matching data types.  You can upsize any table to SQL Server using this method very easily.

Quick Sample

For this example I took the Shippers table from the Northwind database. I think created the same table in SQL Server.

Then you open the VistaDB database and table and pass the resulting VistaDBDataReader to the SqlBulkCopy call.  That's about it.  The only real drawback to this method is a lack of progress.  There is no way I could find to get a progress feedback of any type, so a long operation may appear to have timed out.

The only real part to look at here is line 32:  bulkCopy.WriteToServer(vdbReader);  The SqlBulkCopyOptions.KeepIdentity option when setting up the object tells SQL Server you want to preserve existing Identity values when transferring the data, otherwise new identity values will be used when inserted.

static string SqlConnectionString = 
        "Data Source=d400sc1;database=dcs-masterdb;user id=sa;password=pass.word";
static string VDBConnectionString = 
        "Data Source=c:\\vistadb3\\data\\northwind.vdb3;OpenMode=NonExclusiveReadWrite";

static VistaDBConnection vdbconn = null;
static SqlConnection sqlconn = null;

static void Main( string[] args )
{
    try
    {

        vdbconn = new VistaDBConnection(VDBConnectionString);
        vdbconn.Open();

        sqlconn = new SqlConnection(SqlConnectionString);
        sqlconn.Open();

        using( VistaDBCommand vdbCommand = new VistaDBCommand("Select * from Shippers", vdbconn) )
        {
            using( VistaDBDataReader vdbReader = vdbCommand.ExecuteReader() )
            {

                // SQLBULKCOPY
                using( SqlBulkCopy bulkCopy = 
                   new SqlBulkCopy(SqlConnectionString, SqlBulkCopyOptions.KeepIdentity) )
                {
                    bulkCopy.DestinationTableName = "dbo.Shippers";

                    bulkCopy.BulkCopyTimeout = 0;

                    try
                    {
                        bulkCopy.WriteToServer(vdbReader);
                    }
                    catch( System.Exception e )
                    {
                        Console.WriteLine("EXCEPTION: " + e.ToString());
                    }
                }
            }
        }
    }
    catch( System.Exception e )
    {
        Console.WriteLine("EXCEPTION: " + e.ToString());
    }
    finally
    {
        if( sqlconn != null )
        {
            sqlconn.Close();
            sqlconn.Dispose();
            sqlconn = null;
        }


        if( vdbconn != null )
        {
            vdbconn.Close();
            vdbconn.Dispose();
            vdbconn = null;
        }
    }
}