I found a solution using csvhelper but now I discovered another problem with formatting the date I can't format it yyyy-mm-dd to add it to the database. If I change in Windouws the date format to yyyy-mm-dd the import works but I can't change it must be dd / mm / yyyy
private void button60_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select file";
fdlg.InitialDirectory = @"c:\";
fdlg.FileName = textBox4.Text;
fdlg.Filter = "Text and CSV Files(*.txt, *.csv)|*.txt;*.csv|Text Files(*.txt)|*.txt|CSV Files(*.csv)|*.csv|All Files(*.*)|*.*";
fdlg.FilterIndex = 1;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
using (var reader = new StreamReader(fdlg.FileName))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
using (var dr = new CsvDataReader(csv))
{
var dt = new DataTable();
dt.Load(dr);
dataGridView2.DataSource = dt;
dataGridView2.Columns[0].HeaderCell.Value = "ID";
dataGridView2.Columns[1].HeaderCell.Value = "Nr.";
dataGridView2.Columns[2].HeaderCell.Value = "Serie";
dataGridView2.Columns[3].HeaderCell.Value = "CUI";
dataGridView2.Columns[4].HeaderCell.Value = "Client";
dataGridView2.Columns[5].HeaderCell.Value = "Serie SIM";
dataGridView2.Columns[6].DefaultCellStyle.Format = "yyyy-mm-dd";
dataGridView2.Columns[6].HeaderCell.Value = "Data activarii";
dataGridView2.Columns[7].HeaderCell.Value = "Status";
dataGridView2.Columns[8].HeaderCell.Value = "Data expirarii";
dataGridView2.Columns[8].DefaultCellStyle.Format = "yyyy-mm-dd";
dataGridView2.Columns[9].HeaderCell.Value = "Telefon";
dataGridView2.Columns[10].HeaderCell.Value = "Nr. activat";
dataGridView2.Columns[11].HeaderCell.Value = "Nr_zile";
dataGridView2.Columns[12].HeaderCell.Value = "Ob.";
dataGridView2.Columns[13].HeaderCell.Value = "Tip client";
dataGridView2.Columns[14].HeaderCell.Value = "Email";
dataGridView2.Columns[15].HeaderCell.Value = "Data inregistrarii";
dataGridView2.Columns[15].DefaultCellStyle.Format = "yyyy-mm-dd";
dataGridView2.Columns[16].HeaderCell.Value = "Semnatura";
dataGridView2.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(238, 239, 249);
dataGridView2.CellBorderStyle = DataGridViewCellBorderStyle.SingleHorizontal;
dataGridView2.DefaultCellStyle.SelectionBackColor = Color.DarkTurquoise;
dataGridView2.DefaultCellStyle.SelectionForeColor = Color.WhiteSmoke;
dataGridView2.BackgroundColor = Color.White;
dataGridView2.EnableHeadersVisualStyles = false;
dataGridView2.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.None;
dataGridView2.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(20, 25, 72);
dataGridView2.ColumnHeadersDefaultCellStyle.ForeColor = Color.White;
}
}
}
}
private void button58_Click(object sender, EventArgs e)
{
string constring = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
if (con.State == ConnectionState.Closed)
con.Open();
for (int i = 0; i < dataGridView2.RowCount; i++)
{
try
{
SqlCommand sqlCmd = new SqlCommand("insert into abonament (nr, serie, cui, client, sim, data_inst, activare, data_exp, telefon, nr_activat, nr_zile, ob, tip_client, email, datacurenta1, semnatura) values ( @nr, @serie, @cui, @client, @sim, @data_inst, @activare, @data_exp, @telefon, @nr_activat, @nr_zile, @ob, @tip_client, @email, @datacurenta1, @semnatura)", con);
sqlCmd.Parameters.AddWithValue("@mode", "Add");
sqlCmd.Parameters.AddWithValue("@id", 0);
sqlCmd.Parameters.AddWithValue("@nr ", dataGridView2.Rows[i].Cells[1].Value);
sqlCmd.Parameters.AddWithValue("@serie ", dataGridView2.Rows[i].Cells[2].Value);
sqlCmd.Parameters.AddWithValue("@cui ", dataGridView2.Rows[i].Cells[3].Value);
sqlCmd.Parameters.AddWithValue("@client ", dataGridView2.Rows[i].Cells[4].Value);
sqlCmd.Parameters.AddWithValue("@sim ", dataGridView2.Rows[i].Cells[5].Value);
sqlCmd.Parameters.AddWithValue("@data_inst ", dataGridView2.Rows[i].Cells[6].Value);
sqlCmd.Parameters.AddWithValue("@activare ", dataGridView2.Rows[i].Cells[7].Value);
sqlCmd.Parameters.AddWithValue("@data_exp ", dataGridView2.Rows[i].Cells[8].Value);
sqlCmd.Parameters.AddWithValue("@telefon ", dataGridView2.Rows[i].Cells[9].Value);
sqlCmd.Parameters.AddWithValue("@nr_activat ", dataGridView2.Rows[i].Cells[10].Value);
sqlCmd.Parameters.AddWithValue("@nr_zile ", dataGridView2.Rows[i].Cells[11].Value);
sqlCmd.Parameters.AddWithValue("@ob ", dataGridView2.Rows[i].Cells[12].Value);
sqlCmd.Parameters.AddWithValue("@tip_client ", dataGridView2.Rows[i].Cells[13].Value);
sqlCmd.Parameters.AddWithValue("@email ", dataGridView2.Rows[i].Cells[14].Value);
sqlCmd.Parameters.AddWithValue("@datacurenta1 ", dataGridView2.Rows[i].Cells[15].Value);
sqlCmd.Parameters.AddWithValue("@semnatura ", dataGridView2.Rows[i].Cells[16].Value);
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Datele nu au fost salvate");
}
}
MessageBox.Show("Datele au fost salvate");
}