MS Access has fallen out of favor many years ago, especially for use on the web, but it’s still being used by offices to capture data internally. When recently asked whether that data could be exposed on a website, I thought I’d put together the following ASP.NET code to be able to do so relatively quickly.
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
using System.Web;
...
private string pathToAccessDB = HttpContext.Current.Server.MapPath("~/App_Data/mydb.accdb");
private string tableName = @"tableName";
protected void PushAccessDataToMSSQL()
{
string accessConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + pathToAccessDB;
DataTable dt = new DataTable();
// fill datatable with Access table data
using (OleDbConnection conn = new OleDbConnection(accessConnection))
{
string query = "SELECT * FROM " + tableName;
OleDbCommand cmd = new OleDbCommand(query, conn);
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
da.Dispose();
}
// if we have results from Access
if (dt.Rows.Count > 0)
{
// first check if destination table already exists
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CWIS53_ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand checkIfTableExists = new SqlCommand("IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '" + tableName + "') SELECT 1 ELSE SELECT 0", conn);
bool tableExists = checkIfTableExists.ExecuteScalar().ToString().Equals("1");
// if not, create it
if (!tableExists)
{
var createTableSB = new StringBuilder("CREATE TABLE [" + tableName + "]");
createTableSB.AppendLine("(");
// using each column of the source table to create a column in the destination table
foreach (DataColumn dc in dt.Columns)
{
createTableSB.AppendLine(" [" + dc.ColumnName + "] VARCHAR(150),");
}
createTableSB.Remove(createTableSB.Length - 1, 1);
createTableSB.AppendLine(")");
var createTableCommand = new SqlCommand(createTableSB.ToString(), conn);
createTableCommand.ExecuteNonQuery();
}
else
{
// clear all records
SqlCommand clearRecords = new SqlCommand("DELETE FROM " + tableName, conn);
clearRecords.ExecuteScalar();
}
// execute bulk copy
using (SqlBulkCopy copy = new SqlBulkCopy(conn))
{
copy.DestinationTableName = tableName;
copy.BatchSize = 1000;
copy.BulkCopyTimeout = 240;
copy.WriteToServer(dt);
copy.NotifyAfter = 1000;
}
}
}
}
...