import csv file into database table in asp.net

i make a code for import csv file but there is table column and csv file header are same order like

table column: and csv file header order are:

1.name 1.name

2.address 2.address

3.email 3.email

but i want to import with any order like

table column: 1st csv file header order are: and 2nd csv file header are:

1.name 1.address 1.email

2.address 2.name 2.address

3email 3.email 3.name

how can it possible?

my code is bellow please modify my code

csvupload.aspx code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="csvupload.aspx.cs" Inherits="csvfileupload2.csvupload" %>

Well Come To WildCard Techno Services Pvt Ltd

File Upload
Select File	ToolTip="Select Only Excel File" Height="27px" />

CaptionAlign="Top" HorizontalAlign="Justify"

DataKeyNames="file_id"

ToolTip="Excel FIle DownLoad Tool" CellPadding="4" ForeColor="#333333"

GridLines="None">

Download

HeaderText="phone_number" ItemStyle-Width = "150" />

and my csvupload.aspx.cs code is bellow

?using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace csvfileupload2

{

public partial class csvupload : System.Web.UI.Page

{

string consString = @"Data Source=ANIRUDDHA-PC;Initial Catalog=sark;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)

{

BindGrid();

}

protected void Upload(object sender, EventArgs e)

{

//Upload and save the file

string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string csvPath = Server.MapPath("~/Files/") + fileName;

FileUpload1.SaveAs(csvPath);

int file_id = 0;

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "insert into files (filename,created_date) values('" + fileName + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

int i = cmd.ExecuteNonQuery();

con.Close();

con.Open();

SqlCommand cmd1 = con.CreateCommand();

cmd1.CommandText = "SELECT TOP 1 * FROM files ORDER BY file_id DESC";

SqlDataReader rdr = cmd1.ExecuteReader();

if (rdr.HasRows)

{

rdr.Read();

file_id = Convert.ToInt32(rdr[0]);

}

con.Close();

}

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[16] {

new DataColumn("file_id", typeof(int)),

new DataColumn("phone_number"),

new DataColumn("title"),

new DataColumn("first_name"),

new DataColumn("middle_initial"),

new DataColumn("last_name"),

new DataColumn("address"),

new DataColumn("city"),

new DataColumn("state"),

new DataColumn("province"),

new DataColumn("postal_code"),

new DataColumn("country_code"),

new DataColumn("gender"),

new DataColumn("date_of_birth"),

new DataColumn("alt_phone"),

new DataColumn("email")});

string csvData = File.ReadAllText(csvPath);

foreach (string row in csvData.Split('\n'))

{

if (!string.IsNullOrEmpty(row))

{

dt.Rows.Add();

dt.Rows[dt.Rows.Count - 1][0] = Convert.ToInt32(file_id);

int i = 1;

foreach (string cell in row.Split(','))

{

dt.Rows[dt.Rows.Count - 1][i] = cell;

i++;

}

using (SqlConnection con = new SqlConnection(consString))

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

{

//Set the database table name

sqlBulkCopy.DestinationTableName = "dbo.profilesrr";

sqlBulkCopy.ColumnMappings.Add("file_id", "file_id");

sqlBulkCopy.ColumnMappings.Add("phone_number", "phone_number");

sqlBulkCopy.ColumnMappings.Add("title", "title");

sqlBulkCopy.ColumnMappings.Add("first_name", "first_name");

sqlBulkCopy.ColumnMappings.Add("middle_initial", "middle_initial");

sqlBulkCopy.ColumnMappings.Add("last_name", "last_name");

sqlBulkCopy.ColumnMappings.Add("address", "address");

sqlBulkCopy.ColumnMappings.Add("city", "city");

sqlBulkCopy.ColumnMappings.Add("state", "state");

sqlBulkCopy.ColumnMappings.Add("province", "province");

sqlBulkCopy.ColumnMappings.Add("postal_code", "postal_code");

sqlBulkCopy.ColumnMappings.Add("country_code", "country_code");

sqlBulkCopy.ColumnMappings.Add("gender", "gender");

sqlBulkCopy.ColumnMappings.Add("date_of_birth", "date_of_birth");

sqlBulkCopy.ColumnMappings.Add("alt_phone", "alt_phone");

sqlBulkCopy.ColumnMappings.Add("email", "email");

con.Open();

sqlBulkCopy.WriteToServer(dt);

con.Close();

}

public void BindGrid()

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "select * from files";

SqlDataReader rdr = cmd.ExecuteReader();

DataTable dt = new DataTable();

dt.Load(rdr);

GV_List.DataSource = dt;

GV_List.DataBind();

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

DataSet ds = new DataSet();

SqlCommand cmd = new SqlCommand("select * from profilesrr", con);

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

da.Fill(ds);

da.Dispose();

cmd.Dispose();

con.Close();

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

}

please modify my code

OLEDB Connection could me made by using the below code:

where:

strFilePath = Path of the file

strSheetName = Name of the sheet in that file

System.Data.OleDb.OleDbConnection MyConnection;

System.Data.DataSet DtSet;

System.Data.OleDb.OleDbDataAdapter MyCommand;

MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + strFilePath + "';Extended Properties=Excel 12.0;");

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strSheetName + "$]", MyConnection);

MyCommand.TableMappings.Add("Table", "TestTable");

DtSet = new System.Data.DataSet();

MyCommand.Fill(DtSet);

System.Data.DataTable dt = DtSet.Tables[0];

MyConnection.Close();

return dt;

will share the EP Plus code in a few hours