Import Excel Data To Database MVC Entity Frame

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Entity;

using System.Data.Entity.Validation;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Net;

using System.Text.RegularExpressions;

using System.Web;

using System.Web.Mvc;

using ExcelImport.Models;

using LinqToExcel;

using DoctorsManagmentSystem.Models;

namespace DoctorsManagmentSystem.Controllers

{

public class DoctorController : Controller

{

private DoctorsManagementSystemEntities db = new DoctorsManagementSystemEntities();

public ActionResult Index()

{

return View(db.Doctors.ToList());

}

//yogesh

public FileResult DownloadExcel()

{

string path = "/Doc/Users.xlsx";

return File(path, "application/vnd.ms-excel", "Users.xlsx");

}

//yogesh

[HttpPost]

public JsonResult UploadExcel(Doctor users, HttpPostedFileBase FileUpload)

{

List data = new List();

if (FileUpload != null)

{

// tdata.ExecuteCommand("truncate table OtherCompanyAssets");

if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

{

string filename = FileUpload.FileName;

string targetpath = Server.MapPath("~/Doc/");

FileUpload.SaveAs(targetpath + filename);

string pathToExcelFile = targetpath + filename;

var connectionString = "";

if (filename.EndsWith(".xls"))

{

connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);

}

else if (filename.EndsWith(".xlsx"))

{

connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);

}

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);

var ds = new DataSet();

adapter.Fill(ds, "ExcelTable");

DataTable dtable = ds.Tables["ExcelTable"];

string sheetName = "Sheet1";

var excelFile = new ExcelQueryFactory(pathToExcelFile);

var artistAlbums = from a in excelFile.Worksheet(sheetName) select a;

foreach (var a in artistAlbums)

{

try

{

if (a.Doctorid != "" && a.FormNumber != "" && a.Date != "" && a.Title !="" && a.FullName != "" &&a.Gender !="" &&a.DateOfBirth !="" &&a.MobileNumber !="" && a.LandlineNumber !="" && a.Qualifications !="" && a.Specaility !="" && a.Expertise !="" && a.RegistrationNumber !="" &&a.YearsOfExperience != "" && a.ShortProfile != "" && a.Email !="" != "" && a.Website != "" && a.Subscription !=""&&a.smartPhone !="")

{

User TU = new User();

TU.DoctorId = a.DoctorId;

TUFormNumber = a.FormNumber;

TU.Date = a.Date;

TU.Title = a.Title;

TU.FullName = a.FullName;

TU.Gender = a.Gender;

TU.DateoFBirth = a.DateOfBirth;

TU.MobileNumber = a.MobileNumber;

TU.LandLineNumber = a.LandLineNumber;

TU.Qualifications = TU.Qualifications;

TU.Speciality =TU.Speciality;

TU.Expertise =TU.Expertise;

TU.RegistrationNumber= TU.RegistrationNumber;

TU.YearsOfExperience=TU.YearsOfExperience;

TU.ShortProfile =TU.ShortProfile;

TU.Email=TU.Email;

TU.Website=TU.Website;

TU. Subscription=TU.Subscription;

TU.SmartPhone=TU.SmartPhone;

db.Doctor.Add(TU);

db.SaveChanges();

}

else

{

data.Add("

");

if (a.FormNumber == "" || a.Name == null) data.Add("

FormNumber is required

");

if (a.Date == "" || a.Date == null) data.Add("

Date is required

");

if (a.Title == "" || a.Title == null) data.Add("

Title is required

");

if (a.FullName == "" || a.FullName == null) data.Add("

fullName is required

");

if (a.Gender == "" || a.Gender == null) data.Add("

Gender is required

");

if (a.DateOfBirth == "" || a.DateOfBirth == null) data.Add("

DateOfBirth is required

");

if (a.MobileNumber == "" || a.MobileNumber == null) data.Add("

ContactNo is required

");

if (a.LandlineNumber == "" || a.LandlineNumber == null) data.Add("

Landlinenumber is required

");

if (a.Qualifications == "" || a.Qualifications == null) data.Add("

Qualifications is required

");

if (a.Speciality == "" || a.Speciality == null) data.Add("

Speciality is required

");

if (a.Expertise == "" || a.Expertise == null) data.Add("

Expertise is required

");

if (a.RegistrationNumber == "" || a.RegistrationNumber == null) data.Add("

RegistrationNumber is required

");

if (a.Qualifications == "" || a.Qualifications == null) data.Add("

Qualifications is required

");

if (a.YearsOfExperience == "" || a.YearsOfExperience == null) data.Add("

YearsOfExperience is required

");

if (a.ShortProfile == "" || a.ShortProfile == null) data.Add("

ShortProfile is required

");

if (a.Email == "" || a.Email == null) data.Add("

Email is required

");

if (a.Website == "" || a.Website == null) data.Add("

Website is required

");

if (a.Subscription == "" || a.Subscription == null) data.Add("

Subscription is required

");

if (a.SmartPhone == "" || a.SmartPhone == null) data.Add("

SmartPhone is required

");

data.Add("");

data.ToArray();

return Json(data, JsonRequestBehavior.AllowGet);

}

}

catch (DbEntityValidationException ex)

{

foreach (var entityValidationErrors in ex.EntityValidationErrors)

{

foreach (var validationError in entityValidationErrors.ValidationErrors)

{

Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);

}

}

}

}

//deleting excel file from folder

if ((System.IO.File.Exists(pathToExcelFile)))

{

System.IO.File.Delete(pathToExcelFile);

}

return Json("success", JsonRequestBehavior.AllowGet);

}

else

{

//alert message for invalid file format

data.Add("

");

data.Add("

Only Excel file format is allowed

");

data.Add("");

data.ToArray();

return Json(data, JsonRequestBehavior.AllowGet);

}

}

else

{

data.Add("

");

if (FileUpload == null) data.Add("

Please choose Excel file

");

data.Add("");

data.ToArray();

return Json(data, JsonRequestBehavior.AllowGet);

}

}

}

}

// GET: /Doctor/Details/5

public ActionResult Details(int? id)

{

if (id == null)

{

return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

}

}

Doctor doctor = db.Doctors.Find(id);

if (doctor == null)

{

return HttpNotFound();

}

return View(doctor);

}

// GET: /Doctor/Create

public ActionResult Create()

{

return View();

}

// POST: /Doctor/Create

// To protect from overposting attacks, please enable the specific properties you want to bind to, for

// more details see http://go.microsoft.com/fwlink/?LinkId=317598.

[HttpPost]

[ValidateAntiForgeryToken]

public ActionResult Create([Bind(Include="DoctorId,FormNumber,Date,Title,FullName,Gender,DateOfBirth,MobileNumber,LandLineNumber,Qualifications,Speciality,Expertise,RegistrationNumber,YearsOfExperience,ShortProfile,Email,Website,Subscription,SmartPhone")] Doctor doctor)

{

if (ModelState.IsValid)

{

db.Doctors.Add(doctor);

db.SaveChanges();

return RedirectToAction("Index");

}

return View(doctor);

}

// GET: /Doctor/Edit/5

public ActionResult Edit(int? id)

{

if (id == null)

{

return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

}

Doctor doctor = db.Doctors.Find(id);

if (doctor == null)

{

return HttpNotFound();

}

return View(doctor);

}

// POST: /Doctor/Edit/5

// To protect from overposting attacks, please enable the specific properties you want to bind to, for

// more details see http://go.microsoft.com/fwlink/?LinkId=317598.

[HttpPost]

[ValidateAntiForgeryToken]

public ActionResult Edit([Bind(Include="DoctorId,FormNumber,Date,Title,FullName,Gender,DateOfBirth,MobileNumber,LandLineNumber,Qualifications,Speciality,Expertise,RegistrationNumber,YearsOfExperience,ShortProfile,Email,Website,Subscription,SmartPhone")] Doctor doctor)

{

if (ModelState.IsValid)

{

db.Entry(doctor).State = EntityState.Modified;

db.SaveChanges();

return RedirectToAction("Index");

}

return View(doctor);

}

// GET: /Doctor/Delete/5

public ActionResult Delete(int? id)

{

if (id == null)

{

return new HttpStatusCodeResult(HttpStatusCode.BadRequest);

}

Doctor doctor = db.Doctors.Find(id);

if (doctor == null)

{

return HttpNotFound();

}

return View(doctor);

}

// POST: /Doctor/Delete/5

[HttpPost, ActionName("Delete")]

[ValidateAntiForgeryToken]

public ActionResult DeleteConfirmed(int id)

{

Doctor doctor = db.Doctors.Find(id);

db.Doctors.Remove(doctor);

db.SaveChanges();

return RedirectToAction("Index");

}

protected override void Dispose(bool disposing)

{

if (disposing)

{

db.Dispose();

}

base.Dispose(disposing);

}

}

}