ASP.NET, C#,SQL SERVER,MYSQL,PYTHON,HTML,JAVASCRIPT,JQUERY,AJAX,ANGULARJS : How to read data from excel file and insert into the sql server data using c# asp.net.

In this article we have upload bulk data from excel to sql server using asp.net c#. we have use the following sample data.

Now after creating the sample excel data file. we need to create data table in sql server for storing this data.

CREATE TABLE [dbo].[tblEmployee](
[id] [int] IDENTITY(1,1) NOT NULL,
[emp_Name] [nvarchar](200) NULL,
[Gender] [nvarchar](200) NULL,
[Department] [nvarchar](200) NULL,
[Salary] [float] NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Store procedure for insert data

CREATE proc [dbo].[SP_InsertEmployee](
@emp_Name nvarchar(200),
@Gender nvarchar(200),
@Department nvarchar(200),
@Salary float
)
as
begin
insert into tblEmployee values(@emp_Name,@Gender,@Department,@Salary)
end

After creating data table now add the following code in web.config file of your asp.net project.

<appSettings>
<add key="ConnectionString" value="Data Source=LAPTOP-ISNBQ76D;Initial Catalog=tutorial;Integrated Security=True"/>
</appSettings>

It is for database connection.

<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
 Extended Properties='Excel 8.0;HDR={1}'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
 Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings>

It is for Excel connection.

Now create Employee class

public class Employee
{
public int id { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public string Department { get; set; }
public float Salary { get; set; }
}

Now create EmployeeBL class for business logic

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace Lab1
{

public class EmployeeBL
{
public static string StringCon = null;
SqlConnection conn = null;
SqlCommand cmd = null;

public EmployeeBL()
{
StringCon = ConfigurationManager.AppSettings["ConnectionString"];
conn = new SqlConnection(StringCon);
}
public bool insertEmployee(Employee employee)
{
try
{
conn.Open();
cmd = new SqlCommand("SP_InsertEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@emp_Name", employee.Name);
cmd.Parameters.AddWithValue("@Gender", employee.Gender);
cmd.Parameters.AddWithValue("@Department", employee.Department);
cmd.Parameters.AddWithValue("@Salary", employee.Salary);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
return true;
}
}catch(Exception ex)
{

}
finally
{
conn.Close();
}
return false;
}
}
}

Now add the web form in your project insertDataFromExcelToDatabase.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="insertDataFromExcelToDatabase.aspx.cs" Inherits="Lab1.insertDataFromExcelToDatabase" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>

</html>

Then add the following code in code bind file insertDataFromExcelToDatabase.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

namespace Lab1
{
public partial class insertDataFromExcelToDatabase : System.Web.UI.Page
{
string dataFilePath_SPH = @"C:\Sample\Book1.xlsx";
protected void Page_Load(object sender, EventArgs e)
{
insertExcelDataToDatabase(dataFilePath_SPH,".xlsx");
}
public void insertExcelDataToDatabase(string filePath, string extension)
{
try
{
string conStr = "";
switch (extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
default:
break;
}
if (conStr != "")
{
conStr = String.Format(conStr, filePath, "No");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From ["+ SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);

EmployeeBL employeeBL = new EmployeeBL();
for (int i = 1; i < dt.Rows.Count; i++)
{
Employee employee = new Employee();

employee.Name = dt.Rows[i][1].ToString();
employee.Gender = dt.Rows[i][2].ToString();
employee.Department = dt.Rows[i][3].ToString();
employee.Salary = float.Parse(dt.Rows[i][4].ToString());

if (employeeBL.insertEmployee(employee))
{
Response.Write(" Record inserted for employee " + employee.Name +"<br>");
}

}
}
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{

}
}

}
}

Pages

Tuesday, February 25, 2020

How to read data from excel file and insert into the sql server data using c# asp.net.

Featured Post

What is JavaScript? What is the role of JavaScript engine?