There are lot of solutions on the internet. I found one solution for easy understanding. Please make sure your columns in the CSV file as same as MySql database columns.
Method 1:
Using SqlBulkCopy
const string CSV_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
string CSVpath = "C:\\CSVFiles";
var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;
string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
for (int i = 0; i < AllFiles.Length; i++)
{
try
{
File_Name = AllFiles[i].Name;
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
{
using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + File_Name + "]", con))
{
da.Fill(dt);
}
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConStr))
{
bulkCopy.ColumnMappings.Add(0, "Column1");
bulkCopy.ColumnMappings.Add(1, "Column2");
bulkCopy.ColumnMappings.Add(2, "Column3");
bulkCopy.DestinationTableName = "myTable";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
Method 2:
Using MySqlBulkLoader
static void Main(string[] args)
{
string connStr = "server=localhost;user id=user id;password=password;database=database";
MySqlConnection conn = new MySqlConnection(connStr);
string file = @"filepath";
MySqlBulkLoader bl = new MySqlBulkLoader(conn);
bl.TableName = "tablename";
bl.FieldTerminator = "|"; This can be {comma,tab,semi colon, or other character}
bl.LineTerminator = "\n";
bl.FileName =file;
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
int count = bl.Load();
Console.WriteLine(count + " lines uploaded.");
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.WriteLine("Done.");
Console.ReadLine();
}
Conclusion:
MySqlBulkLoader is very faster than the SqlBulkCopy choice is yours!