Introduction
This is a very simple Excel 2007 data loader to SQL 2008 database. I am using SqlBulkCopy
class to make data load fast. (10K record loaded in less than 3s).
One catch: You will need existing table in SQL DB with matching columns. I skipped dynamic table creation to keep the code simple.
Using the Code
This is the most basic version, without error checking to make the code clear. Below is the completed code behind the form.
Button 1 - Opens the Excel file.
Button 2 - Uploads data to SQL Server.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
namespace SimpleExcelLoader
{
public partial class Form1 : Form
{
string filename="";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "Excel 2007 files
(*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
filename = openFileDialog1.FileName;
textBox1.Text = filename;
button2.Enabled = true;
toolStripStatusLabel1.Text = "File Selected.
Enter DB Info and click Upload Data";
}
}
private void button2_Click(object sender, EventArgs e)
{
string server = textBox2.Text;
string db = textBox3.Text;
string user = textBox4.Text;
string pass = textBox5.Text;
string tableName=textBox6.Text;
string serverConnectionString="Server="+server+";
Database="+db+";Uid="+user+";Pwd="+pass+";";
string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";
string strSQL = "SELECT * FROM [Sheet1$]";
OleDbConnection excelConnection = new OleDbConnection(connectionString);
excelConnection.Open();
OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
dataAdapter.Fill(dTable);
using (SqlConnection destinationConnection =
new SqlConnection(serverConnectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = tableName;
try
{
bulkCopy.WriteToServer(dTable);
toolStripStatusLabel1.Text="Data Uploaded";
}
catch (Exception ex)
{
toolStripStatusLabel1.Text=ex.Message;
}
destinationConnection.Close(); }}
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
excelConnection.Close();
excelConnection.Dispose();
}}}
Points of Interest
The code was tested with Excel 2007 and SQL Server 2008.
History
- 9th December, 2008: Initial post
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.