here's my final working code:
public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)
{
string header = "Yes";
string query = string.Empty;
DataTable dataTable = null;
string filePath = string.Empty;
string fileName = string.Empty;
try
{
filePath = Path.GetDirectoryName(ConfigurationManager.AppSettings["QuantumOutputFilesLocation"]);
fileName = Path.GetFileName(ConfigurationManager.AppSettings["CSVFilename"]);
query = @"SELECT * FROM [" + fileName + "]";
if (IsFirstRowHeader) header = "Yes";
using (OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Text;HDR=" + header + "\"")))
{
using (OleDbCommand command = new OleDbCommand(query, connection))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable = new DataTable();
adapter.Fill(dataTable);
try
{
OleDbConnection DBconn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Seagoe_QuantumConnectionString"].ConnectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = DBconn;
cmd.CommandType = CommandType.Text;
DBconn.Open();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
cmd.CommandText = "INSERT INTO tblQuantum (DateEntered, Series, SerialNumber, YearCode, ModelNumber, BatchNumber, DeviceType, RatedPower, EnergyStorageCapacity," +
"MaxEnergyStorageCapacity, User_IF_FWRevNo, Charge_Controller_FWRevNo, RF_Module_FWRevNo, SSEGroupNumber, TariffSetting)" +
" VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +
"','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +
"','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + dataTable.Rows[i].ItemArray.GetValue(7) + "','" + dataTable.Rows[i].ItemArray.GetValue(8) +
"','" + dataTable.Rows[i].ItemArray.GetValue(9) + "','" + dataTable.Rows[i].ItemArray.GetValue(10) + "','" + dataTable.Rows[i].ItemArray.GetValue(11) +
"','" + dataTable.Rows[i].ItemArray.GetValue(12) + "','" + dataTable.Rows[i].ItemArray.GetValue(13) + "','" + dataTable.Rows[i].ItemArray.GetValue(14) + "')";
cmd.ExecuteNonQuery();
}
DBconn.Close();
}
catch (Exception ex)
{
sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
}
}
}
}
if (Directory.Exists(filePath))
{
Directory.Delete(filePath, true);
Directory.CreateDirectory(filePath);
}
}
catch (Exception ex)
{
sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
}
return dataTable;