Hi BugHunter,

In order to do this add a Column as per your database column name to the DataTable at the end position with default value. So that you can insert it while using BulkCopy.

Refer below article for BulkCopy.

Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net

Check this example. Now please take its reference and correct your code.

HTML

<asp:GridView runat="server" ID="GridView1" />

Namespaces

C#

using System.Data;
using System.Data.OleDb;
using System.IO;

VB.Net

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        Session["UserName"] = "Test";
        string excelPath = Server.MapPath("~/Files/Excel07.xlsx");
        string conString = string.Empty;
        string extension = Path.GetExtension(excelPath);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
                break;
            case ".xlsx": //Excel 07 or higher
                conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
                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 dtExcelData = new DataTable();
            // Adding column with default value to hold Session Data.
            dtExcelData.Columns.Add("UserName").DefaultValue = Session["UserName"];
            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            // Moving the added column to the end position.
            dtExcelData.Columns["UserName"].SetOrdinal(dtExcelData.Columns.Count - 1);
            excel_con.Close();
            GridView1.DataSource = dtExcelData;
            GridView1.DataBind();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Session("UserName") = "Test"
        Dim excelPath As String = Server.MapPath("~/Files/Excel07.xlsx")
        Dim conString As String = String.Empty
        Dim extension As String = Path.GetExtension(excelPath)
        Select Case extension
            Case ".xls"
                conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"
            Case ".xlsx"
                conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"
        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 dtExcelData As DataTable = New DataTable()
            ' Adding column with default value to hold Session Data.
            dtExcelData.Columns.Add("UserName").DefaultValue = Session("UserName")
            Using oda As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" & sheet1 & "]", excel_con)
                oda.Fill(dtExcelData)
            End Using
            ' Moving the added column to the end position.
            dtExcelData.Columns("UserName").SetOrdinal(dtExcelData.Columns.Count - 1)
            excel_con.Close()
            GridView1.DataSource = dtExcelData
            GridView1.DataBind()
        End Using
    End If
End Sub

Screenshot