Add additional column in SqlBulkCopy to save in database using C# and VB.Net in ASP.Net

Hi Amol111,

Refer below sample.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
<br />
<asp:GridView runat="server" ID="gvEmployees" />

Namespaces

C#

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration

Code

C#

protected void Upload(object sender, EventArgs e)
{
    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(excelPath);

    string conString = string.Empty;
    string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    switch (extension)
    {
        case ".xls":
            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx":
            conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
            break;

    }
    conString = string.Format(conString, excelPath);
    using (OleDbConnection excel_con = new OleDbConnection(conString))
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("Id", typeof(int)),
        new DataColumn("Name", typeof(string)),
        new DataColumn("Salary",typeof(decimal)),
        new DataColumn("FileName",typeof(string)),
        new DataColumn("Uploaded_By",typeof(int)),
        new DataColumn("Uploaded_Date",typeof(DateTime)) });
        DataTable dtExcelData = new DataTable();
        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);
            for (int i = 0; i < dtExcelData.Rows.Count; i++)
            {
                dt.Rows.Add(dtExcelData.Rows[i][0].ToString(), dtExcelData.Rows[i][1].ToString(), dtExcelData.Rows[i][2].ToString(), Path.GetFileName(FileUpload1.PostedFile.FileName), dtExcelData.Rows[i][0].ToString(), DateTime.Today);
            }
        }
        excel_con.Close();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                sqlBulkCopy.DestinationTableName = "dbo.tblEmployees";
                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                con.Close();
                gvEmployees.DataSource = dt;
                gvEmployees.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(excelPath)
    Dim conString As String = String.Empty
    Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

    Select Case extension
        Case ".xls"
            conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
        Case ".xlsx"
            conString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
    End Select

    conString = String.Format(conString, excelPath)
    Using excel_con As OleDbConnection = New OleDbConnection(conString)
        excel_con.Open()
        Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Salary", GetType(Decimal)), New DataColumn("FileName", GetType(String)), New DataColumn("Uploaded_By", GetType(Integer)), New DataColumn("Uploaded_Date", GetType(DateTime))})
        Dim dtExcelData As DataTable = New DataTable()
        Using oda As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" & sheet1 & "]", excel_con)
            oda.Fill(dtExcelData)
            For i As Integer = 0 To dtExcelData.Rows.Count - 1
                dt.Rows.Add(dtExcelData.Rows(i)(0).ToString(), dtExcelData.Rows(i)(1).ToString(), dtExcelData.Rows(i)(2).ToString(), Path.GetFileName(FileUpload1.PostedFile.FileName), dtExcelData.Rows(i)(0).ToString(), DateTime.Today)
            Next
        End Using

        excel_con.Close()
        Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(consString)
            Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
                sqlBulkCopy.DestinationTableName = "dbo.tblEmployees"
                sqlBulkCopy.ColumnMappings.Add("Id", "PersonId")
                sqlBulkCopy.ColumnMappings.Add("Name", "Name")
                sqlBulkCopy.ColumnMappings.Add("Salary", "Salary")
                con.Open()
                sqlBulkCopy.WriteToServer(dt)
                con.Close()
                gvEmployees.DataSource = dt
                gvEmployees.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot