Importing data from excel into database using stored procedure

Kmandapalli
Posted by Kmandapalli under ASP.NET category on | Points: 40 | Views : 4370
Importing data from excel into database using stored procedure :

Step 1:
Create a new MVC Application and name it as ImportExcel.

Step 2:
Right-click on the controllers folder, select add new controller and name it as Home.
Write an ActionResult method as follows:
 public ActionResult ExcelImport()
{
return View();
}


Step 3:
Create a View for the ActionResult method ExcelImport.
Write the below code:
 @{
ViewBag.Title = "ExcelImport";
}
<h2>CSV Bulk Upload</h2>
<div style="font-weight: bold; color: Red; background-color: Lime">
@ViewData["Message"]
</div>
@using (Html.BeginForm("ExcelImport", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" name="FileUpload" />
<input type="submit" name="Submit" id="Submit" value="Upload" />
}
<p>@Html.Encode(ViewData["Feedback"])
</p>


Step 4:
Create a table ExcelData in the databse with the column names same as that of Excel sheet.
eg:
IF OBJECT_ID('SalesHistory')>0    
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO


Write a proc for inserting data from DataTable into database as below:
eg:
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO

CREATE PROCEDURE usp_InsertBigScreenProducts
(
@datatable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory VALUES(@datatable )
END
GO


Step 5:
Right-click on the models folder, select add new item and add ADO.NET Entity data model and then add your stored procedure.
Create object for the Entities:
1. Open Web.Config file and look into connectionString, you can find a new connectionString being created.
eg:
<add name="primedb03Entities1" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=PRIMEDB01\SQL2008;initial catalog=primedb03;user id=fresher;password=fresher@prime;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

2. create object for this connectionString in the controller:

primedb03Entities1 db = new primedb03Entities1();


Step 6:
Open the Home controller and write the HttpPost ActionResult method for ExcelImport as follows:
[HttpPost]
public ActionResult AmazonOrders(HttpPostedFileBase FileUpload)
{

var fileName = Path.GetFileName(FileUpload.FileName);
var path = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
FileUpload.SaveAs(path);
var excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName) + ";Extended Properties=Excel 12.0;");
OleDbConnection objOlecon = new OleDbConnection();
objOlecon.ConnectionString = excelConnectionString;
objOlecon.Open();
OleDbDataAdapter objOleDa = new OleDbDataAdapter("Select * from [Amazon-orders$]", objOlecon);//Here [Amazon-orders] is the excel sheet name.
DataTable objdt = new DataTable();
objOleDa.Fill(objdt);
SqlConnection objsqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);
objsqlCon.Open();
SqlCommand cmd = new SqlCommand("primedb03..[ImportOrders]");//Here primedb03 is the database name
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = objsqlCon;
cmd.Parameters.AddWithValue("@datatable ", objdt);
cmd.ExecuteNonQuery();
objsqlCon.Close();
ViewData["message"] = "Records Imported Successfully";
db.GenerateOrder();
objOlecon.Close();

return View();

}

That's it, run ur code and check, all the data present in the excel sheet gets imported into database table.
There is no need to use SqlBulk also.

Mark as Answer if satisfied.

Regards,
Shree M

Comments or Responses

Login to post response