CODEWITHSUNDEEP
c#sqlms-access
mitoKon
mitoKon

Reputation: 61

How to copy Access table to SQL table in C#?

As title says I've used odbcconnection to sqlconnection and for the life of me cant get it to work.. Copied a bunch of code from this site but cant get them both to work.

The program just hangs so maybe I am doing something wrong, but would appreciate maybe a bare bones template that i could just fill in the connection details and bulk copy the table to table..

 using (OdbcConnection myConnection = new OdbcConnection())
            {
                string myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" +
 "Dbq=//####/data/Toronto/wrkgrp/wrkgrp30/Business Risk Oversight and Control/DATA INTEGRITY/CDE/CDE Testing Portal Requirements/Migration Requirements/RCM/Mutual Funds/Mutual Funds.mdb;";

                myConnection.ConnectionString = myConnectionString;
                myConnection.Open();

                //execute queries, etc
                OdbcCommand cmd = myConnection.CreateCommand();

                cmd.CommandText = "SELECT * FROM RCM_MF_New_Accounts_Samples";
                OdbcDataReader reader = cmd.ExecuteReader(); // close conn after complete

                DataTable myDataTable = new DataTable();
                myDataTable.Load(reader);
                //myConnection.Close();

                string destinationConnectionString = "Data Source=####;Initial Catalog=DYOF_STAGING_BROC;User ID=;Password=;Connection Timeout=999";

                SqlConnection destination = new SqlConnection(destinationConnectionString);
                SqlBulkCopy bulkData;
                //destination.Open();

                Exception ex = null;
                try
                {
                    Console.WriteLine("step1");
                    bulkData = new SqlBulkCopy(destinationConnectionString, SqlBulkCopyOptions.FireTriggers);
                    bulkData.BulkCopyTimeout = 1;
                    bulkData.DestinationTableName = "Load_CTR_Sample_Account_Opening2";
                    bulkData.WriteToServer(myDataTable);
                    bulkData.Close();
                    Console.WriteLine("moved from here to there");
                    reader.Close();

                    //destination.Close();


                }
                catch (Exception e)
                {
                    ex = e;
                }

Upvotes: 0

Views: 696

Answers (4)

ASH
ASH

Reputation: 20342

Read the data from Access into a DataTable:

string strConnect = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=D:\Temp\MyDB.accdb";

DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(strConnect))
    {
    OleDbCommand cmd = new OleDbCommand("SELECT * FROM MyTable", con);
    con.Open();
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    da.Fill(dt);
    }

Then use SqlBulkCopy to update SQL:

string strConnect = @"Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True";
using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlBulkCopy bulk = new SqlBulkCopy(con))
        {
        bulk.DestinationTableName = "Test";
        bulk.WriteToServer(dt);
        }
    }

Of course, there is a much easier way to go straight from Access to SQL Server, using VBA, SQL , or other methods.

https://support.office.com/en-us/article/import-or-link-to-data-in-an-sql-server-database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

https://www.sqlshack.com/six-different-methods-to-copy-tables-between-databases-in-sql-server/

Upvotes: 1

Jonathan Allen
Jonathan Allen

Reputation: 70337

Here's a basic example of bulk insert. 

public void BulkInsert(DataTable employees)
{
    if (employees == null)
        throw new ArgumentNullException(nameof(employees), $"{nameof(employees)} is null.");

    using (var con = OpenConnection())
    using (var sbc = new SqlBulkCopy(con))
    {
        sbc.DestinationTableName = "HR.Employee";
        foreach (DataColumn column in employees.Columns)
            sbc.ColumnMappings.Add(column!.ColumnName, column.ColumnName);
        sbc.WriteToServer(employees);
    }
}

Note the foreach (DataColumn column in employees.Columns) loop. This is required so that it knows the column names are the same in the source and the target table. (If they're not the same, manually map them in the same fashion.)

Source: https://grauenwolf.github.io/DotNet-ORM-Cookbook/BulkInsert.htm#ado.net

Upvotes: 1

Jonathan Allen
Jonathan Allen

Reputation: 70337

I actually wrote an ORM to make this kind of task easier. 

var accessDS = new AccessDataSource(connectionString1);
var dt = accessDS.From("RCM_MF_New_Accounts_Samples").ToDataTable().Execute();

var sqlDS = new SqlServerDataSource(connectionString2);
sqlDS.InsertBulk("Load_CTR_Sample_Account_Opening2", dt).Execute();

This does not work for .NET Core.

Packages:

Upvotes: 2

Jain
Jain

Reputation: 45

Following option need to verify 1) Column Name should be the same. 2) verify the column length. 3) verify the data type.

Upvotes: 0

Related Questions