how to apply csvbulkcopy to import csv data into sql server using window application form.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;using System.Windows.Forms;
private void btnbrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = ".csv";
ofd.Filter = "Comma Separated (*.csv)|*.csv";
ofd.ShowDialog();
txtfilename.Text = ofd.FileName;
}
private void btnimport_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
DataTable dt = GetDataFromFile();
if (dt == null) return;
SaveImportDataToDatabase(dt);
MessageBox.Show("Data Import success!");
txtfilename.Text = string.Empty;
Cursor = Cursors.Default;
}
private DataTable GetDataFromFile()
{
DataTable dt = new DataTable();
try
{
using (StreamReader sr = new StreamReader(txtfilename.Text))
{
string header = sr.ReadLine();
if (string.IsNullOrEmpty(header))
{
MessageBox.Show("no file data");
return null;
}
string[] headerColumns = header.Split(',');
foreach (string headerColumn in headerColumns)
{
dt.Columns.Add(headerColumn);
}
while (!sr.EndOfStream)
{
string line = sr.ReadLine();
if (string.IsNullOrEmpty(line)) continue;
string[] fields = line.Split(',');
DataRow importedRow = dt.NewRow();
for (int i = 0; i < fields.Count(); i++)
{
importedRow[i] = fields[i];
}
dt.Rows.Add(importedRow);
}
}
}
catch (Exception e)
{
Console.WriteLine("the file could not be read:");
Console.WriteLine(e.Message);
}
return dt;
}
private void SaveImportDataToDatabase(DataTable test)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=Biiuty; Initial Catalog=Hbi; User Id=ida; Password=***"))
{
conn.Open();
foreach (DataRow importRow in test.Rows)
{
SqlCommand cmd = new SqlCommand
("INSERT INTO test (DateTime,Milliseconds,MachineAutoStartStop,Pressure,Batch,)" + "VALUES (@DateTime,@Milliseconds,@MachineAutoStartStop,@Pressure,@Batch)", conn);
DateTime rowDate;
if (DateTime.TryParse((string)importRow["DateTime"], out rowDate))
{
cmd.Parameters.AddWithValue("@DateTime", rowDate);
}
else
{
cmd.Parameters.AddWithValue("@DateTime", DBNull.Value);
}
cmd.Parameters.AddWithValue("@Milliseconds", importRow["Milliseconds"]);
cmd.Parameters.AddWithValue("@MachineAutoStartStop", importRow["MachineAutoStartStop"]);
cmd.Parameters.AddWithValue("@Pressure", importRow["Pressure"]);
cmd.Parameters.AddWithValue("@Batch", importRow["Batch"]);
cmd.ExecuteNonQuery();
}
}
}
Amit MohantyPosted Dec 19, 2022, 10:19 AM
Check your datatable has data or not.
dhivyah jaiyasangerPosted Dec 19, 2022, 9:05 AM
After changed the code based on the suggestion i get this error:
System.ArgumentNullException: 'Value cannot be null.
Parameter name: reader'
code :
Pankajkumar PatelPosted Dec 13, 2022, 9:11 AM
Hi Dhivyah Jaiyasanger,
Try this:
Hope this will help you!
Amit MohantyPosted Dec 12, 2022, 10:01 AM
DataTable dataTable = new DataTable();
Chage the above line.
Here you have to stored your csv data that you wanr to post to database.
Column mappings
I assumed your DataTable has the exact same schema as the target table. What if the “Name” column in the source is actually called “FullName”. Here is how we can create a mapping between the columns in the source and target table.
dhivyah jaiyasangerPosted Dec 12, 2022, 9:33 AM
after I apply the suggested code i get this error
Get error : System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination
code :
Amit MohantyPosted Dec 9, 2022, 10:10 AM
Try this