Import Excel data to Database using C# and VB.Net in Windows Application

Last Reply 2 months ago By pandeyism

Posted 2 months ago

I have a two table.

table1 DefClass is as follow

ClassID

                     ClassName

195

One

196

Two

and table2 tblStu structure is like this.

StuID

SName

ClassID

Phone

now i want to import data in tblStu like this way

StuID

SName

ClassName

Phone

1

ABC

Two

123456789

2

XYZ

Two

123456789

3

SKY

Two

123456789

and it must be store in the tblStu like this way

StuID

SName

ClassID

Phone

1

ABC

196

123456789

2

XYZ

196

123456789

3

SKY

196

123456789

Point to Note: In excel file i m using classname, and in backend at tblStu, I want to store ClassID from tblDefClass which is coressponding to ClassName.

how to do like this????

You are viewing reply posted by: pandeyism 2 months ago.
Posted 2 months ago

Hi smile,

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

For importing excel refer below article.

Import data from Excel file to Windows Forms DataGridView using C# and VB.Net

Form Design

In Windows Form i have taken a Button for Upload excel, OpenFileDialog to select file and DataGridView for displaying the uploaded record.

Namespaces

C#

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

VB.Net

Imports System.ComponentModel
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

Code

C#

private void btnUpload_Click(object sender, EventArgs e)
{
    openFileDialog1.ShowDialog();
}

private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
    DataTable dtDb = new DataTable();
    dtDb.Columns.Add("ClassID", typeof(int));
    dtDb.Columns.Add("ClassName", typeof(string));
    dtDb.Rows.Add(195, "One");
    dtDb.Rows.Add(196, "Two");

    string excelPath = openFileDialog1.FileName;
    string conString = string.Empty;
    string extension = Path.GetExtension(excelPath);
    switch (extension)
    {
        case ".xls":
            conString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", excelPath);
            break;
        case ".xlsx":
            conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", excelPath);
            break;
    }
    DataTable dtExcelData = new DataTable();
    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();
        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 className = dtExcelData.Rows[i]["ClassName"].ToString();
            int classId = dtDb.Select("ClassName='" + className + "'")[0].Field<int>("ClassID");
            dtExcelData.Rows[i]["ClassName"] = classId;
        }
        dtExcelData.Columns["ClassName"].ColumnName = "ClassID";
    }
    dataGridView1.DataSource = dtExcelData;
    dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
    string consString = "Data Source=.;Initial Catalog=Test;Integrated Security = true";
    using (SqlConnection con = new SqlConnection(consString))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.tblStu";
            //[OPTIONAL]: Map the Excel columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("StuID", "StuID");
            sqlBulkCopy.ColumnMappings.Add("SName", "SName");
            sqlBulkCopy.ColumnMappings.Add("ClassID", "ClassID");
            sqlBulkCopy.ColumnMappings.Add("Phone", "Phone");
            con.Open();
            sqlBulkCopy.WriteToServer(dtExcelData);
            con.Close();
        }
    }
}

VB.Net

Private Sub btnUpload_Click(sender As System.Object, e As System.EventArgs) Handles btnUpload.Click
    openFileDialog1.ShowDialog()
End Sub
Private Sub openFileDialog1_FileOk(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles openFileDialog1.FileOk
    Dim dtDb As DataTable = New DataTable()
    dtDb.Columns.Add("ClassID", GetType(Integer))
    dtDb.Columns.Add("ClassName", GetType(String))
    dtDb.Rows.Add(195, "One")
    dtDb.Rows.Add(196, "Two")
    Dim excelPath As String = openFileDialog1.FileName
    Dim conString As String = String.Empty
    Dim extension As String = Path.GetExtension(excelPath)
    Select Case extension
        Case ".xls"
            conString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", excelPath)
        Case ".xlsx"
            conString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", excelPath)
    End Select

    Dim dtExcelData As DataTable = New DataTable()
    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()
        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 className As String = dtExcelData.Rows(i)("ClassName").ToString()
            Dim classId As Integer = dtDb.[Select]("ClassName='" & className & "'")(0).Field(Of Integer)("ClassID")
            dtExcelData.Rows(i)("ClassName") = classId
        Next
        dtExcelData.Columns("ClassName").ColumnName = "ClassID"
    End Using

    dataGridView1.DataSource = dtExcelData
    dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
    Dim consString As String = "Data Source=.;Initial Catalog=Test;Integrated Security = true"
    Using con As SqlConnection = New SqlConnection(consString)
        Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
            'Set the database table name.
            sqlBulkCopy.DestinationTableName = "dbo.tblStu"
            '[OPTIONAL]: Map the Excel columns with that of the database table.
            sqlBulkCopy.ColumnMappings.Add("StuID", "StuID")
            sqlBulkCopy.ColumnMappings.Add("SName", "SName")
            sqlBulkCopy.ColumnMappings.Add("ClassID", "ClassID")
            sqlBulkCopy.ColumnMappings.Add("Phone", "Phone")
            con.Open()
            sqlBulkCopy.WriteToServer(dtExcelData)
            con.Close()
        End Using
    End Using
End Sub

Screenshots

The Excel

The imported record displayed in DataGridView