Hi All of you..
I am gonna give an idea on Bulk data Insert into Database using EntityFramework with help of Excel sheet.
1> Add EPPlus.4.0.1.1 DLL file to the References.
2> Add CSS and JS file in Kendo Folder of Kendo UI
3>Add Style Sheet and JS file Link to _Layout.cshtml According to given order.
Path :- Project ->Content Folder.
I am gonna give an idea on Bulk data Insert into Database using EntityFramework with help of Excel sheet.
1> Add EPPlus.4.0.1.1 DLL file to the References.
2> Add CSS and JS file in Kendo Folder of Kendo UI
Path :- Project ->Content Folder.
4> Create a Table for a Database
name of table is Experience.
CREATE TABLE [dbo].[Experience] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Title] NVARCHAR (100) NULL,
[Description] NVARCHAR (MAX) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);
Now dont Insert a Value.
5> Now create Model class for table name it Experience.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
namespace ItsMe.Areas.Admin.Models
{
[Table("Experience")]
public class Experience
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
}
}
6>Map the model with DbSet<Experience>experienceContext{get;set;}
7>Now we will Create Export function for Excel sheet , The Excel sheet will be Empty and Column name will there, According to table field (Column name)the data will insert.
8> Create ExportManager.cs for Experience model
Code here:-
using ItsMe.Areas.Admin.Models;
using ItsMe.Models;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
namespace ItsMe.BL
{
public partial class ExportManager
{
DefaultConnection db = new DefaultConnection();
#region Experience Download/Export
public virtual void ExportTestExperienceToXlsx(Stream stream, IList<Experience> experience)
{
if (stream == null)
throw new ArgumentNullException("stream");
// ok, we can run the real code of the sample now
using (var xlPackage = new ExcelPackage(stream))
{
// uncomment this line if you want the XML written out to the outputDir
//xlPackage.DebugMode = true;
// get handle to the existing worksheet
var worksheet = xlPackage.Workbook.Worksheets.Add("Experiences");
//Create Headers and format them
var properties = new[]
{
"Title",
"Description"
};
for (int i = 0; i < properties.Length; i++)
{
worksheet.Cells[1, i + 1].Value = properties[i];
worksheet.Cells[1, i + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells[1, i + 1].Style.Font.Bold = true;
}
int row = 2;
foreach (var p in experience)
{
int col = 1;
worksheet.Cells[row, col].Value = p.Title;
col++;
worksheet.Cells[row, col].Value = p.Description;
col++;
row++;
}
xlPackage.Save();
}
}
#endregion
}
}
9> Create a Controller so that we can write Export and Emport logic.
ExpenseController
using ItsMe.Areas.Admin.Models;
using ItsMe.BL;
using ItsMe.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace ItsMe.Areas.Admin.Controllers
{
[Authorize]
public class ExperienceController : Controller
{
DefaultConnection db = new DefaultConnection();
private readonly ExportManager _exportmanager;
public ExperienceController()
{
_exportmanager = new ExportManager();
}
#region Export/Import Excel
public virtual IList<Experience> GetExperienceIds(int[] experienceIds)
{
if (experienceIds == null || experienceIds.Length == 0)
return new List<Experience>();
var query = db.experienceContext;
var experiences = query.ToList();
//sort by passed identifiers
var sortedexperiences = new List<Experience>();
foreach (int id in experienceIds)
{
var experience = experiences.Find(x => x.Id == id);
if (experience != null)
sortedexperiences.Add(experience);
}
return sortedexperiences;
}
[ValidateAntiForgeryToken]
public ActionResult ExportExcelSelected(string selectedIds)
{
var experience = new List<Experience>();
#region Idwise
if (selectedIds != null)
{
var ids = selectedIds
.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
.Select(x => Convert.ToInt32(x))
.ToArray();
experience.AddRange(GetExperienceIds(ids));
}
#endregion
byte[] bytes = null;
using (var stream = new MemoryStream())
{
_exportmanager.ExportTestExperienceToXlsx(stream, experience);
bytes = stream.ToArray();
}
return File(bytes, "text/xls", "TestQuestion-[" + System.DateTime.Today.ToString("dd-MMM-yyyy") + "].xlsx");
}
public ActionResult Index()
{
return View();
}
#endregion
}
}
10> Now we create View page for Index Action .
Here we place the Export to Excel button with help of javascript query.
Now e write a logic for Import data from excel.
To be Continue................