ASP.NET, C#,SQL SERVER,MYSQL,PYTHON,HTML,JAVASCRIPT,JQUERY,AJAX,ANGULARJS : How to import data from excel to sql server database using oleDB connection.

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 readDatafromExcelUsingOleDbAndInsertIntoDatabase.aspx

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

<!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 readDatafromExcelUsingOleDbAndInsertIntoDatabase.aspx.cs

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

namespace Lab1
{
public partial class readDatafromExcelUsingOleDbAndInsertIntoDatabase : System.Web.UI.Page
{
string dataFilePath_SPH = @"C:\Sample\Book1.xlsx"; //Path of your data file
protected void Page_Load(object sender, EventArgs e)
{
insertExcelData();
}
private void insertExcelData()
{
string conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
if (conStr != "")
{
conStr = String.Format(conStr, dataFilePath_SPH, "Yes");
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();

string sql_Excel = "SELECT " +
"Name," +
"Gender," +
"Department," +
"Salary " +
"From[Sheet1$]";

OleDbCommand cmd = new OleDbCommand(sql_Excel, connExcel);
OleDbDataReader oRd = cmd.ExecuteReader();
if (oRd.HasRows)
{
EmployeeBL employeeBL = new EmployeeBL();
while (oRd.Read())
{
Employee employee = new Employee();
employee.Name = oRd["Name"].ToString();
employee.Gender = oRd["Gender"].ToString();
employee.Department = oRd["Department"].ToString();
employee.Salary =float.Parse(oRd["Salary"].ToString());

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

}
}
}

Pages

Tuesday, February 25, 2020

How to import data from excel to sql server database using oleDB connection.

Featured Post

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