Click here to Skip to main content
15,887,083 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
how to apply csvbulkcopy to import csv data into sql server using window application form.

What I have tried:

C#
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 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("@mili", importRow["mili"]);
			cmd.Parameters.AddWithValue("@Machine", importRow["Machine"]);
			

			cmd.ExecuteNonQuery();
		}
	}
}
Posted
Updated 19-Dec-22 18:43pm
v3
Comments
Graeme_Grant 9-Dec-22 4:40am    
What error are you seeing? Please update your question with:
1. Which line is throwing the error
2. A copy of the exact error
3. If no error, explain where you are stuck.
dhivyah jaiya 19-Dec-22 4:21am    
Get error : System.ArgumentNullException: 'Value cannot be null.
Parameter name: reader'

code:
DataTable dataTable = new DataTable();
string dbconnection = @"Data Source=Biiuty; Initial Catalog=Hbi; User Id=ida; Password=***";
using (SqlConnection sqlConnection = new SqlConnection(dbconnection))
using (var sqlBulk = new SqlBulkCopy(dbconnection))
{
sqlBulk.DestinationTableName = "S2P5";
sqlBulk.ColumnMappings.Add("DateTime", "DateTime");
sqlBulk.ColumnMappings.Add("Milliseconds", "Milliseconds");
sqlBulk.ColumnMappings.Add("MachineAutoStartStop", "MachineAutoStartStop");
sqlBulk.ColumnMappings.Add("Pressure", "Pressure");

sqlBulk.WriteToServer(dt);
}
Graeme_Grant 20-Dec-22 4:48am    
Yep, you are trying to pass a null value. You need to identify which line is throwing the error, then why nulls are either being used or are not supported, then write code to handle the condition so that nulls do not throw errors. This is called data validation before use.

1 solution

I did a quick GOOGLE SEARCH: How to apply csvbulkcopy for import csv data to SQL server C# - Google Search[^]

And here are some results returned:
* Bulk Import CSV file data into database using SqlBulkCopy in ASP.Net[^]
* Bulk Insert CSV File into SQL Server in C#[^]
* How To Import CSV File Into SQL Server[^]

If these are not suitable, there are many other answers to look at in that search.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900