In desktop application used in technologies like C#, .Net and MySQL I import data from large file (which is about 37 MB) to datagridview. The table from Excel file is in below:
After loading data from Excel to datagridview I'm inserting that data to MySQL database.
Later when I executing this importing code, that problem is began.
Despite of switching off ContextSwitchDeadlock exception (of only 1 table to insert) inserting this data takes about... 10 minutes.
Despite of not calling any error or exception in program it inserts not all data. In that case I saw I have imported only 8 statuses not all (11 statuses).
And I have a question: why inserting to database takes so long and doesn't save all data? How to reduce inserting data to MySQL db and save all data?
I send a link to shared file to download: https://drive.google.com/file/d/1LE7phZwyT7VR3NJc6bA-n1reCJ_-X3u9/view?usp=sharing
Maybe it helps.
All code snippets with codes modifications are in 1 code snippet.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
namespace ControlDataBase
{
public partial class New_Tables : Form
{
public New_Tables()
{
InitializeComponent();
}
Form1 frm1 = (Form1)Application.OpenForms["Form1"];
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
private void ImportData_Click(object sender, EventArgs e)
{
using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "Excel Files|*.xlsx;*.xlsm;*.xlsb;*.xltx;*.xltm;*.xls;*.xlt;*.xls;*.xml;*.xml;*.xlam;*.xla;*.xlw;*.xlr;", ValidateNames = true })
{
if (ofd.ShowDialog() == DialogResult.OK)
{
FileInfo fi = new FileInfo(ofd.FileName);
string FileName1 = ofd.FileName;
string excel = fi.FullName;
if (ofd.FileName.EndsWith(".xlsx"))
{
StrConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=\"Excel 12.0;\"";
}
if (ofd.FileName.EndsWith(".xls"))
{
StrConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excel + ";Extended Properties=\"Excel 1.0;HDR=Yes;IMEX=1\"";
}
OleDbConnection oledbconn = new OleDbConnection(StrConn);
OleDbDataAdapter dta5 = new OleDbDataAdapter("SELECT * FROM [Order_status$]", oledbconn);
oledbconn.Open();
DataSet dsole5 = new DataSet();
dta5.Fill(dsole5, "Order_status$");
datagrdStatus_order.DataSource = dsole5.Tables["Order_status$"];
oledbconn.Close();
foreach (DataGridViewRow row in datagrdStatus_order.Rows)
{
string constring = "datasource = localhost; port = 3306; username = root; password = ";
using (MySqlConnection con = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand("INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.DESC_ORDER = @DESC_ORDER AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME", con))
{
cmd.Parameters.AddWithValue("@ID_WORKER", row.Cells["ID_WORKER"].Value);
cmd.Parameters.AddWithValue("@FNAME", row.Cells["FNAME"].Value);
cmd.Parameters.AddWithValue("@LNAME", row.Cells["LNAME"].Value);
cmd.Parameters.AddWithValue("@DESC_ORDER", row.Cells["DESC_ORDER"].Value);
cmd.Parameters.AddWithValue("@ORDER_NUMBER", row.Cells["ORDER_NUMBER"].Value);
cmd.Parameters.AddWithValue("@MODULES_NAME", row.Cells["NAME"].Value);
cmd.Parameters.AddWithValue("@PROJECT_NAME", row.Cells["PROJECT_NAME"].Value);
cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", row.Cells["AMOUNT_OF_PRODUCTS"].Value);
cmd.Parameters.AddWithValue("@BEGIN_DATE", row.Cells["BEGIN_DATE"].Value);
cmd.Parameters.AddWithValue("@END_DATE", row.Cells["END_DATE"].Value);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
connection.Close();
MessageBox.Show("The data are imported correctly");
loaddataalldatagridview();
}
}
}
private void loaddataalldatagridview()
{
frm1.loaddata5();
}
}
}
// Modification inserting excel data with for loop:
string constring = "datasource = localhost; port = 3306; username = root; password = ";
string query5 = "INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.DESC_ORDER = @DESC_ORDER AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME";
using (MySqlConnection con = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand(query5, con))
{
cmd.Parameters.Add("@ID_WORKER", MySqlDbType.Int32);
cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@LNAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@DESC_ORDER", MySqlDbType.VarChar);
cmd.Parameters.Add("@ORDER_NUMBER", MySqlDbType.VarChar);
cmd.Parameters.Add("@MODULES_NAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@PROJECT_NAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@AMOUNT_OF_PRODUCTS", MySqlDbType.Int32);
cmd.Parameters.Add("@BEGIN_DATE", MySqlDbType.DateTime);
cmd.Parameters.Add("@END_DATE", MySqlDbType.DateTime);
con.Open();
for (int i = 0; i < datagrdStatus_order.Rows.Count + 1; i++)
{
cmd.Parameters["@ID_WORKER"].Value = datagrdStatus_order.Rows[i].Cells[0].Value;
cmd.Parameters["@FNAME"].Value = datagrdStatus_order.Rows[i].Cells[1].Value;
cmd.Parameters["@LNAME"].Value = datagrdStatus_order.Rows[i].Cells[2].Value;
cmd.Parameters["@DESC_ORDER"].Value = datagrdStatus_order.Rows[i].Cells[3].Value;
cmd.Parameters["@ORDER_NUMBER"].Value = datagrdStatus_order.Rows[i].Cells[4].Value;
cmd.Parameters["@MODULES_NAME"].Value = datagrdStatus_order.Rows[i].Cells[5].Value;
cmd.Parameters["@PROJECT_NAME"].Value = datagrdStatus_order.Rows[i].Cells[6].Value;
cmd.Parameters["@AMOUNT_OF_PRODUCTS"].Value = datagrdStatus_order.Rows[i].Cells[7].Value;
cmd.Parameters["@BEGIN_DATE"].Value = datagrdStatus_order.Rows[i].Cells[8].Value;
cmd.Parameters["@END_DATE"].Value = datagrdStatus_order.Rows[i].Cells[9].Value;
cmd.ExecuteNonQuery();
}
con.Close();
}
}
MessageBox.Show("Imported correctly");
loaddataalldatagridview();
// Modification inserting excel data with foreach loop.
string constring = "datasource = localhost; port = 3306; username = root; password = ";
string query5 = "INSERT IGNORE INTO try1.order_status(ID_WORKER, ID_ORDER, ID_MODULE, ID_PROJECT, AMOUNT_OF_PRODUCTS, BEGIN_DATE, END_DATE) SELECT workers.ID_WORKER, orders.ID_ORDER, module.ID_MODULE, projects.ID, @AMOUNT_OF_PRODUCTS, @BEGIN_DATE, @END_DATE FROM try1.workers INNER JOIN try1.orders INNER JOIN try1.modules INNER JOIN try1.projects WHERE workers.FNAME = @FNAME AND workers.LNAME = @LNAME AND workers.ID_WORKER = @ID_WORKER AND orders.DESC_ORDER = @DESC_ORDER AND orders.ORDER_NUMBER = @ORDER_NUMBER AND modules.NAME = @MODULES_NAME AND projects.PROJECT_NAME = @PROJECT_NAME";
using (MySqlConnection con = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand(query5, con))
{
cmd.Parameters.Add("@ID_WORKER", MySqlDbType.Int32);
cmd.Parameters.Add("@FNAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@LNAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@DESC_ORDER", MySqlDbType.VarChar);
cmd.Parameters.Add("@ORDER_NUMBER", MySqlDbType.VarChar);
cmd.Parameters.Add("@MODULES_NAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@PROJECT_NAME", MySqlDbType.VarChar);
cmd.Parameters.Add("@AMOUNT_OF_PRODUCTS", MySqlDbType.Int32);
cmd.Parameters.Add("@BEGIN_DATE", MySqlDbType.DateTime);
cmd.Parameters.Add("@END_DATE", MySqlDbType.DateTime);
con.Open();
foreach (DataGridViewRow row in datagrdStatus_order.Rows)
{
cmd.Parameters.AddWithValue("@ID_WORKER", row.Cells["ID_WORKER"].Value);
cmd.Parameters.AddWithValue("@FNAME", row.Cells["FNAME"].Value);
cmd.Parameters.AddWithValue("@LNAME", row.Cells["LNAME"].Value);
cmd.Parameters.AddWithValue("@DESC_ORDER", row.Cells["DESC_ORDER"].Value);
cmd.Parameters.AddWithValue("@ORDER_NUMBER", row.Cells["ORDER_NUMBER"].Value);
cmd.Parameters.AddWithValue("@MODULES_NAME", row.Cells["NAME"].Value);
cmd.Parameters.AddWithValue("@PROJECT_NAME", row.Cells["PROJECT_NAME"].Value);
cmd.Parameters.AddWithValue("@AMOUNT_OF_PRODUCTS", row.Cells["AMOUNT_OF_PRODUCTS"].Value);
cmd.Parameters.AddWithValue("@BEGIN_DATE", row.Cells["BEGIN_DATE"].Value);
cmd.Parameters.AddWithValue("@END_DATE", row.Cells["END_DATE"].Value);
cmd.ExecuteNonQuery();
}
con.Close();
}
}
MessageBox.Show("Imported correctly");
loaddataalldatagridview();