Import and export Opeartion On Database

Hello friends ...

firstly i am import data from excel shit and which are store all excel shit record into database ..but now i want perform operation like if data type is wrong then data cant be store into database it can store into another one place (invalid data item) ..where user can easily download all invalid records and check this ... source code is here ...

Database Name : DoctorManagementSystem

Data Table :Doctor

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 DoctorsManagmentSystem.Models;

using LinqToExcel;

namespace DoctorsManagmentSystem.Controllers

{

public class DoctorController : Controller

{

private DoctorsManagementSystemEntities db = new DoctorsManagementSystemEntities();

public ActionResult Index()

{

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

}

public FileResult DownloadExcel()

{

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

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

}

[HttpPost]

public ActionResult 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

{

Doctor TU = new Doctor();

TU.DoctorId = a.DoctorId;

TU.FormNumber = 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.Doctors.Add(TU);

db.SaveChanges();

}

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);

}

//yogesh

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

}

//yogesh

// 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 =

)] Doctor doctor)

{//yogesh

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);

}

Manas Mohapatra

Your right ..But give me some line code where i can perform this operation easily ...

using System.Collections.Generic;

using System.Data;

using System.Data.Entity;

using System.Data.Entity.Validation;

using System.Data.OleDb;

using System.Linq;

using System.Net;

using System.Web;

using System.Web.Mvc;

using DoctorsManagmentSystem.BusinessLogic;

using DoctorsManagmentSystem.Models;

using LinqToExcel;

namespace DoctorsManagmentSystem.Controllers

{

public class DoctorController : Controller

{

private DoctorsManagementSystemEntities db = new DoctorsManagementSystemEntities();

public ActionResult Index()

{

ViewBag.StatusMessage = "Uploaded";

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

}

//yogesh

public FileResult DownloadExcel()

{

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

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

}

//yogesh

[HttpPost]

public JsonResult UploadExcel(Doctor users, HttpPostedFileBase FileUpload)

{

var data = new List();

var validatedDoctors = new List();

var inValidatedDoctors = new List();

if (FileUpload != null)

{

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

{

var filename = FileUpload.FileName;

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

FileUpload.SaveAs(targetpath + filename);

var 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");

const string sheetName = "Sheet1";

var excelFile = new ExcelQueryFactory(pathToExcelFile);

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

foreach (var a in artistAlbums)

{

try

{

//Todo validate(doctor)

var doctor = GetFormattedDocotr(a);

validatedDoctors.Add(doctor);

}

catch (DbEntityValidationException ex)

{

foreach (var validationError in ex.EntityValidationErrors.SelectMany(e => e.ValidationErrors))

{

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

}

if (validatedDoctors.Count > 0)

{

var populateDataToDb = new PopulateDataToDatabase(db);

populateDataToDb.BatchInsertion(validatedDoctors,"doctor");

}

if (inValidatedDoctors.Count > 0)

{

var populateDataToDb = new PopulateDataToDatabase(db);

populateDataToDb.BatchInsertion(inValidatedDoctors);

ViewBag.FilePath = "";

}

//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("

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Harshal Patil9y ago
Accepted answer
Use try-catch block inside for-each loop. When any exception occurs inside loop than in catch block log error insert the current record into another table called exception table.
Nitin Sontakke9y ago
In my personal opinion, one should read the data without worrying about data types. All columns will have data type as String.

Then write a code to check data type of each column by trying to parse it. The parsing will give errors which could be handled and reported. The best way to achieve this is to use TryParse method instead of Parse method.
Yogesh Vedpathak9y ago
Manas Mohapatra
Your right ..But give me some line code where i can perform this operation easily ...
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Validation;
using System.Data.OleDb;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using DoctorsManagmentSystem.BusinessLogic;
using DoctorsManagmentSystem.Models;
using LinqToExcel;
namespace DoctorsManagmentSystem.Controllers
{
public class DoctorController : Controller
{
private DoctorsManagementSystemEntities db = new DoctorsManagementSystemEntities();
public ActionResult Index()
{
ViewBag.StatusMessage = "Uploaded";
return View(db.Doctors.ToList());
}
//yogesh
public FileResult DownloadExcel()
{
const string path = "/Doc/Users.xlsx";
return File(path, "application/vnd.ms-excel", "Users.xlsx");
}
//yogesh
[HttpPost]
public JsonResult UploadExcel(Doctor users, HttpPostedFileBase FileUpload)
{
var data = new List();
var validatedDoctors = new List();
var inValidatedDoctors = new List();
if (FileUpload != null)
{
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
var filename = FileUpload.FileName;
var targetpath = Server.MapPath("~/Doc/");
FileUpload.SaveAs(targetpath + filename);
var 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");
const string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var artistAlbums = from a in excelFile.Worksheet(sheetName) select a;
foreach (var a in artistAlbums)
{
try
{
//Todo validate(doctor)
var doctor = GetFormattedDocotr(a);
validatedDoctors.Add(doctor);
}
catch (DbEntityValidationException ex)
{
foreach (var validationError in ex.EntityValidationErrors.SelectMany(e => e.ValidationErrors))
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
if (validatedDoctors.Count > 0)
{
var populateDataToDb = new PopulateDataToDatabase(db);
populateDataToDb.BatchInsertion(validatedDoctors,"doctor");
}
if (inValidatedDoctors.Count > 0)
{
var populateDataToDb = new PopulateDataToDatabase(db);
populateDataToDb.BatchInsertion(inValidatedDoctors);
ViewBag.FilePath = "";
}
//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("