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();
}
}
}