using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Text;
namespace testRDP.Models
{
public class ExceltoDB
{
OleDbConnection Econ;
MySqlConnection con;
string constr, Query, sqlconn;
public void ExcelConn(string FilePath)
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
Econ = new OleDbConnection(constr);
}
public void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["ConfigDB"].ConnectionString;
con = new MySqlConnection(sqlconn);
}
public void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);
Query = string.Format("Select [FirstName],[LastName], [Age], [Position], [Office], [Salary] FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
connection();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
BulkInsertMySQL(Exceldt);
con.Close();
}
public void BulkInsertMySQL(DataTable dtData)
{
StringBuilder sCommand = new StringBuilder("INSERT INTO Employee (FirstName, LastName) VALUES ");
using (MySqlConnection mConnection = new MySqlConnection(sqlconn))
{
List<string> Rows = new List<string>();
for (int i=0; i<dtData.Rows.Count; i++)
{
Rows.Add(string.Format("('{0}','{1}')", dtData.Rows[i]["FirstName"], dtData.Rows[i]["LastName"]));
}
sCommand.Append(string.Join(",", Rows));
sCommand.Append(";");
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
}
}
}
}
