"Gil Lapid Shafriri" <e_la***@hotmail.com> wrote in message
news:OMJ984WQGHA.3924@TK2MSFTNGP14.phx.gbl...
> 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>");
>
>        }
>
>
>
>    }
>
> }
>
>
>
>