Moving data from Access to SQL Server using ADO.NET

[Gil Lapid Shafriri]

Hi,

I was looking for long time for a fast and reliable way to upload Access
table into an empty SQL Server table. Finaly I came with a way that seems to
be working fine but I'd like to
get confirmation from this group that there are no hidden problem going that
way. In short I used system.data.OledbConnection with Jet provider to get
DataReader to the Access source
and fed the system.data.SqlClient.BulkCopy object with that reader to bulk
upload the changes.

Here is a sample code:


Can someone point out if this method will work in "all" cases ? What are the
hidden problems I should expect ?

Thanks,

Einat


using System;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Runtime.InteropServices;





namespace AccessDataTransfer

{



class Program

{



static void Main(string[] args)

{

String Sourcedb = "c:\\1.mdb";

String DestConnectionStr = "Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=gil2";

String TableName = "Foo";



if (args.Length >= 1)

{

if (args[0] == "/?")

{

PrintHelp();

return;

}



Sourcedb = args[0];

}



if (args.Length >= 2)

{

DestConnectionStr = args[1];

}



if (args.Length >= 3)

{

TableName = args[2];

}



TransferTableData(

Sourcedb,

DestConnectionStr,

TableName

);



}



//

// Transfer data deom Access table to SQL server table. The table is
asummed to be created on the sql server side.

//

//

static void TransferTableData(

String AccessSourcedb,

String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();



// Reader to source

//

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();



//bulk upload to destination

//

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);





int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();









static private void PrintHelp()

{

System.Console.WriteLine("AccessDartaTransfer.exe
<AccessSourcefile> <SQL server connection string> <TableDef to transfer>");

}



}

}

 

[William \(Bill\) Vaughn]

Nope, this approach is fine. Go for it. Yes, there are always going to be
issues when moving data that have more to do with the logistics and data
content, but this is a viable (and recommended) strategy.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi,

I was looking for long time for a fast and reliable way to upload Access
table into an empty SQL Server table. Finaly I came with a way that seems
to be working fine but I'd like to
get confirmation from this group that there are no hidden problem going
that way. In short I used system.data.OledbConnection with Jet provider
to get DataReader to the Access source
and fed the system.data.SqlClient.BulkCopy object with that reader to
bulk upload the changes.

Here is a sample code:


Can someone point out if this method will work in "all" cases ? What are
the hidden problems I should expect ?

Thanks,

Einat


using System;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Runtime.InteropServices;





namespace AccessDataTransfer

{



class Program

{



static void Main(string[] args)

{

String Sourcedb = "c:\\1.mdb";

String DestConnectionStr = "Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=gil2";

String TableName = "Foo";



if (args.Length >= 1)

{

if (args[0] == "/?")

{

PrintHelp();

return;

}



Sourcedb = args[0];

}



if (args.Length >= 2)

{

DestConnectionStr = args[1];

}



if (args.Length >= 3)

{

TableName = args[2];

}



TransferTableData(

Sourcedb,

DestConnectionStr,

TableName

);



}



//

// Transfer data deom Access table to SQL server table. The table
is asummed to be created on the sql server side.

//

//

static void TransferTableData(

String AccessSourcedb,

String DestinationConnectionStr,

String TableName

)

{

int Start = GetTickCount();



//Connection to the destination

//

SqlConnection DestConnection = new
SqlConnection(DestinationConnectionStr);

DestConnection.Open();

SqlCommand Cmd = DestConnection.CreateCommand();



//Connection to source

//

OleDbConnection OledbSrcConnection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + AccessSourcedb);

OledbSrcConnection.Open();



// Reader to source

//

OleDbCommand OleDbCommand = new OleDbCommand("SELECT * FROM " +
TableName);

OleDbCommand.Connection = OledbSrcConnection;

OleDbDataReader OleDbDataReader = OleDbCommand.ExecuteReader();



//bulk upload to destination

//

SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnection,
System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity,null);

bulkCopy.BulkCopyTimeout = 100000000;

bulkCopy.DestinationTableName = TableName;

bulkCopy.WriteToServer(OleDbDataReader);





int End = GetTickCount();



Console.WriteLine("all rows were uploaded in {0} miliseconds",
End - Start);

}



[DllImportAttribute("kernel32.dll", SetLastError = true)]

private static extern int GetTickCount();









static private void PrintHelp()

{

System.Console.WriteLine("AccessDartaTransfer.exe
<AccessSourcefile> <SQL server connection string> <TableDef to
transfer>");

}



}

}