There are many ways to achieve this.
One ways can be
Step1:Try to read all your data in to a datatable ( assuming 1st line of CSV represents the ColumnName)
var values = GetAllLines();
DataTable dt = new DataTable();
foreach (var item in values.FirstOrDefault())
{
if (!dt.Columns.Contains(item))
dt.Columns.Add(item);
}
var allvalues=values.Skip(1).ToList();
for (int i = 0; i < allvalues.Count; i++)
{
dt.Rows.Add(allvalues.Take(dt.Columns.Count).ToArray());
}
Description for Get All Lines function
private IEnumerable<string[]> GetAllLines()
{
string str;
using (StreamReader rd=new StreamReader(txtFileName.Text))
{
while ((str = rd.ReadLine()) != null)
{
yield return str.Split(',');
}
}
}
STEP2Then you can use SQL Bulk Copy to Copy the Entire datatable to database. Where you need to set the destination table and Column Mapping, in my case i assume the column name is same as that of datatable
SqlConnection dbconnection = new SqlConnection("your Connection string.");
SqlBulkCopy sqlblkcpy=new SqlBulkCopy(dbconnection);
dbconnection.Open();
sqlblkcpy.DestinationTableName = "<Table Name To Which you want to Copy your Data>";
foreach (DataColumn item in dt.Columns)
{
sqlblkcpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item.ColumnName, item.ColumnName));
}
if(dt.RecordCount>0)
sqlblkcpy.WriteToServer(dt);