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