using window application to import csv data to sql server.here the code i tried. Not get any error but the import data cannot save in database.
code:
private void btnselect_Click(object sender, EventArgs e)
{
//open serach file to recognise by the
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = ".csv";
ofd.Filter = "Comma Separated(*.csv)|*.csv";
ofd.ShowDialog();
textfilename.Text = ofd.FileName;
}
private DataTable GetDataTabletFromCSVFile(string csv_file_path)
{
DataTable csvData = new DataTable();
try
{
using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
{
csvReader.SetDelimiters(new string[] { "," });
csvReader.HasFieldsEnclosedInQuotes = true;
string[] colfields = csvReader.ReadFields();
foreach(string column in colfields)
{
while (! csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
for (int i = 0; i < fieldData.Length; i++)
{
if (fieldData[i] == "")
{
fieldData[i] = null;
}
}
csvData.Rows.Add(fieldData);
}
}
}
}
catch (Exception ex)
{
return null;
}
return csvData;
}
private void btnImport_Click(object sender, EventArgs e)
{
{
Cursor = Cursors.WaitCursor;
DataTable dt = GetDataTabletFromCSVFile(csv_file_path);
if (dt == null) return;
SaveImportDataToDatabase(dt);
MessageBox.Show("Data Import success!");
textfilename.Text = string.Empty;
Cursor = Cursors.Default;
}
}
private void SaveImportDataToDatabase(DataTable SS)
{
using (SqlConnection conn = new SqlConnection())
using (SqlConnection dbConnection = new SqlConnection("Data Source=ytl//bku; Initial Catalog=databse; User Id=***; Password=*****;"))
{
dbConnection.Open();
using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
{
s.DestinationTableName = "SS";
foreach (var column in SS.Columns)
s.ColumnMappings.Add(column.ToString(), column.ToString());
s.WriteToServer(SS);
}
}
}