Hi telldurges,
Refer below link for file upload.
https://www.c-sharpcorner.com/article/upload-download-files-in-asp-net-core-2-0/
And after file upload use below code for reading file and save to databse.
Namespaces
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports ClosedXML.Excel
Imports System.Data.SqlClient
Code
C#
protected void ImportExcel(object sender, EventArgs e)
{
string query = "";
string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(filePath);
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
DataTable dt = new DataTable();
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
query += "IF OBJECT_ID('dbo." + Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName) + "', 'U') IS NULL ";
query += "BEGIN ";
query += "CREATE TABLE [dbo].[" + Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName) + "](";
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
query += cell.Value.ToString() + " VARCHAR(MAX),";
}
firstRow = false;
query = query.TrimEnd(',');
query += ")";
query += " END";
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con);
sqlBulkCopy.DestinationTableName = "dbo." + Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
VB.Net
Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim query As String = ""
Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(filePath)
Using workBook As XLWorkbook = New XLWorkbook(filePath)
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
Dim dt As DataTable = New DataTable()
Dim firstRow As Boolean = True
For Each row As IXLRow In workSheet.Rows()
If firstRow Then
query += "IF OBJECT_ID('dbo." & Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName) & "', 'U') IS NULL "
query += "BEGIN "
query += "CREATE TABLE [dbo].[" & Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName) & "]("
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
query += cell.Value.ToString() & " VARCHAR(MAX),"
Next
firstRow = False
query = query.TrimEnd(","c)
query += ")"
query += " END"
Else
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As IXLCell In row.Cells()
dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
i += 1
Next
End If
Next
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim con As SqlConnection = New SqlConnection(constr)
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
Dim sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
sqlBulkCopy.DestinationTableName = "dbo." & Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName)
sqlBulkCopy.WriteToServer(dt)
con.Close()
End Using
End Sub
