Cara Import Excel ke SQL Server di C#

Pada latihan ini kita akan menggunakan satu buah komponen button dan openfiledialog, untuk itu buatlah desaign form seperti gambar dibawah :

Script :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace importexel
{
public partial class Form1 : Form
{
string file;
public string database = “Data Source = ALI-PC; Initial Catalog = StagingDb; Integrated Security=True”;
OleDbConnection con;
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = “(*.xls)|*.xls|(*.xlsx)|*.xlsx|All files (*.*)|*.*”;
openFileDialog1.ShowDialog();
using (con = new OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;data source='” + openFileDialog1.FileName + “‘;Extended Properties=Excel 8.0;”))
{

con.Open();

OleDbCommand com = new OleDbCommand(“Select * from [Employee$]”, con);

OleDbDataReader dr = com.ExecuteReader();

using (SqlConnection sqlcon = new SqlConnection(database))

{

sqlcon.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))

{

bulkCopy.ColumnMappings.Add(“Name”, “Name”);

bulkCopy.ColumnMappings.Add(“Gender”, “Gender”);

bulkCopy.ColumnMappings.Add(“City”, “City”);

bulkCopy.ColumnMappings.Add(“Department”, “Department”);

bulkCopy.DestinationTableName = “tblEmployee”;

bulkCopy.WriteToServer(dr);

}

}

dr.Close();

dr.Dispose();

}
}
}


}