Import Excel data with Foreign key instead of Name using C# and VB.Net in ASP.Net

Last Reply 3 months ago By dharmendr

Posted 3 months ago

I have two table ,one table is item master File and second table is Order table,

here is my table structure

Item Master File

Item Code Item Name
1 Pen
2 Pencil
3 Apple
4 Car

Order Table

Order ID Item Code QTY
1 1 2
1 2 3
1 3 5

i have excel file in this format

Order ID Item Name QTY
1 Pen 2
1 Pencil 3
1 Apple 5
     

Please Guide ,during import data in table ,it also check that order ID and item Name exit in a table or not....Thanks

Posted 3 months ago

Hi akhter,

For importing excel in Database refer below article.

Import Excel data to SQL Server Database in ASP.Net using C# and VB.Net

For comparing two datatable refer below link.

Compare Rows of Two DataTables and copy matching rows in Third DataTable using C#

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

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" Text="Upload" OnClick="Upload" runat="server" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" />
        <asp:BoundField DataField="ItemCode" HeaderText="Item Code" />
        <asp:BoundField DataField="QTY" HeaderText="QTY" />
    </Columns>
</asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Upload(object sender, EventArgs e)
{
    DataTable dtItemMaster = new DataTable();
    dtItemMaster.Columns.Add("ItemCode", typeof(int));
    dtItemMaster.Columns.Add("ItemName", typeof(string));
    dtItemMaster.Rows.Add(1, "Pen");
    dtItemMaster.Rows.Add(2, "Pencil");
    dtItemMaster.Rows.Add(3, "Apple");
    dtItemMaster.Rows.Add(4, "Car");

    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 dtExcelData = new DataTable();
        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
        {
            oda.Fill(dtExcelData);
        }
        excel_con.Close();

        for (int i = 0; i < dtExcelData.Rows.Count; i++)
        {
            string itemName = dtExcelData.Rows[i]["ItemName"].ToString();
            int itemCode = dtItemMaster.Select("ItemName='" + itemName + "'")[0].Field<int>("ItemCode");
            dtExcelData.Rows[i]["ItemName"] = itemCode;
        }
        dtExcelData.Columns["ItemName"].ColumnName = "ItemCode";
        GridView1.DataSource = dtExcelData;
        GridView1.DataBind();

        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                sqlBulkCopy.DestinationTableName = "dbo.Order";

                //[OPTIONAL]: Map the Excel columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("OrderID", "Order ID");
                sqlBulkCopy.ColumnMappings.Add("ItemCode", "Item Code");
                sqlBulkCopy.ColumnMappings.Add("QTY", "QTY");
                con.Open();
                sqlBulkCopy.WriteToServer(dtExcelData);
                con.Close();
            }
        }
    }
}

VB.Net

Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
    Dim dtItemMaster As DataTable = New DataTable()
    dtItemMaster.Columns.Add("ItemCode", GetType(Integer))
    dtItemMaster.Columns.Add("ItemName", GetType(String))
    dtItemMaster.Rows.Add(1, "Pen")
    dtItemMaster.Rows.Add(2, "Pencil")
    dtItemMaster.Rows.Add(3, "Apple")
    dtItemMaster.Rows.Add(4, "Car")
    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 dtExcelData As DataTable = New DataTable()

        Using oda As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" & sheet1 & "]", excel_con)
            oda.Fill(dtExcelData)
        End Using
        excel_con.Close()
        For i As Integer = 0 To dtExcelData.Rows.Count - 1
            Dim itemName As String = dtExcelData.Rows(i)("ItemName").ToString()
            Dim itemCode As Integer = dtItemMaster.[Select]("ItemName='" & itemName & "'")(0).Field(Of Integer)("ItemCode")
            dtExcelData.Rows(i)("ItemName") = itemCode
        Next
        dtExcelData.Columns("ItemName").ColumnName = "ItemCode"
        GridView1.DataSource = dtExcelData
        GridView1.DataBind()
        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.Order"
                sqlBulkCopy.ColumnMappings.Add("OrderID", "Order ID")
                sqlBulkCopy.ColumnMappings.Add("ItemCode", "Item Code")
                sqlBulkCopy.ColumnMappings.Add("QTY", "QTY")
                con.Open()
                sqlBulkCopy.WriteToServer(dtExcelData)
                con.Close()
            End Using
        End Using
    End Using
End Sub

Screenshots

The Excel

DataTable Binded to Gridview after importing Excel