how to Upload a excel file to sql database table using c# windows form application

Question

I want to upload a excel file through windows form application in c# and want to import the data to database ( Mysql server). how can i do that??? I have created a form which requires me to upload a excel file into the mysql database . its an bulk insert data to database table.

My Excel File Contain columns like userid,password,first_name,last_name,user_group AND MySql Database table(aster_users) Contain many columns like userid,password,first_name,last_name,user_group,queue,active,created_date,created_by,role ..

i need to upload that excel file to my database and other columns will get empty or null that's not a matter.

My Form design is

Here is My c# Code:

using MySql.Data.MySqlClient;
using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace UploadFileToDatabase
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();       
    }

    String MyConString = "SERVER=******;" +
           "DATABASE=dbs;" +
           "UID=root;" +
           "PASSWORD=pwsd;" + "Convert Zero Datetime = True";
private void BtnSelectFile_Click(object sender, EventArgs e)
    {
        OpenFileDialog dlg = new OpenFileDialog();
        dlg.Filter = "Text files | *.csv";
        if (dlg.ShowDialog() == DialogResult.OK)
        {
            string fileName;
            fileName = dlg.FileName;
            txtfilepath.Text = fileName;
        }
      }

private void btnUpload_Click(object sender, EventArgs e)           
 {                
    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtfileparth.Text + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";

        using (OleDbConnection connection =
              new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand
                    ("Select * FROM [Sheet1$]", connection);

            connection.Open();

            using (DbDataReader dr = command.ExecuteReader())
            {
                string sqlConnectionString = MyConString;

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.ColumnMappings.Add("[userid]", "userid");
                    bulkCopy.ColumnMappings.Add("password", "password");
                    bulkCopy.ColumnMappings.Add("first_name", "first_name");
                    bulkCopy.ColumnMappings.Add("last_name", "last_name");
                    bulkCopy.ColumnMappings.Add("user_group", "user_group");
                    bulkCopy.DestinationTableName = "aster_users";
                    bulkCopy.WriteToServer(dr);
                    MessageBox.Show("Upload Successfull!");
                }
            }

        }
}

Here is how i tried.i got an error message like this

Additional information: External table is not in the expected format.

in this line connection.Open(); . How can this be Done?

Answer 1

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace IMPORT
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
    String MyConString = "SERVER=******;" +
           "DATABASE=db;" +
           "UID=root;" +
           "PASSWORD=pws;";

private void btnSelectFile_Click(object sender, EventArgs e)
    {
        OpenFileDialog openfiledialog1 = new OpenFileDialog();
        openfiledialog1.ShowDialog();
        openfiledialog1.Filter = "allfiles|*.xls";
        txtfilepath.Text = openfiledialog1.FileName;
    }
private void btnUpload_Click(object sender, EventArgs e)
{
 string path = txtfilepath.Text;

        string ConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties = Excel 8.0";

        DataTable Data = new DataTable();

        using (OleDbConnection conn =new OleDbConnection(ConnString))
        {
            conn.Open();

            OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [dataGridView1_Data$]", conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(Data);

            conn.Close();
        }
        string ConnStr = MyConString;
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnStr))
        {
            bulkCopy.DestinationTableName = "TABLE NAME";
            bulkCopy.ColumnMappings.Add("userid", "userid");
            bulkCopy.ColumnMappings.Add("password", "password");
            bulkCopy.ColumnMappings.Add("first_name", "first_name");
            bulkCopy.ColumnMappings.Add("last_name", "last_name");
            bulkCopy.ColumnMappings.Add("user_group", "user_group");
            bulkCopy.WriteToServer(Data);
            MessageBox.Show("UPLOAD SUCCESSFULLY");
        }
     }
 }

An example found http://technico.qnownow.com/bulk-copy-data-from-excel-to-destination-db-using-sql-bulk-copy/. And ERROR: Additional information: External table is not in the expected format