public bool AddUnversity(University uni)
{
try
{
using (var context = new TSMTEntities())
{
context.Universities.Add(uni);
context.SaveChanges();
return true;
}
}
catch (Exception ex)
{
return false;
}
}
public void GetUni(int uniId)
{
University = new University();
University = db.Universities.Find(uniId);
}
public ActionResult AddUniversity(FormCollection f)
{
University uni = new University();
uni.Name = f["Name"];
uni.Address = f["Address"];
uni.UniversityCode = f["UniversityCode"];
uni.Website = f["Website"];
uni.Phone = f["Phone"];
db.Universities.Add(uni);
db.SaveChanges();
return RedirectToAction("ManageUniversity");
}
public void UpdateUni(University uni, int uniId)
{
var oldUni = db.Universities.Find(uniId);
oldUni.Name = uni.Name;
oldUni.UniversityCode = uni.UniversityCode;
oldUni.Address = uni.Address;
oldUni.Website = uni.Website;
oldUni.Phone = uni.Phone;
db.SaveChanges();
}
public ActionResult Importexcel(HttpPostedFileBase file)
{
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there.
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Content/UploadedFolder"), Request.Files["FileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
System.IO.File.Delete(fileLocation);
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
List<ImportErrors> listErrors = new List<ImportErrors>();
ImportErrors imEror = new ImportErrors();
var listUni = db.Universities.ToList();
int tmp = db.Universities.Count();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
University uni = new University();
imEror = new ImportErrors();
if (ds.Tables[0].Rows[i]["Name"].ToString() != "")
{
uni.Name = ds.Tables[0].Rows[i]["Name"].ToString();
}
else
{
imEror.Name = null;
imEror.Address = ds.Tables[0].Rows[i]["Address"].ToString();
imEror.Code = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
if (ds.Tables[0].Rows[i]["UniversityCode"].ToString() != "")
{
uni.UniversityCode = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
else
{
imEror.Code = null;
imEror.Name = ds.Tables[0].Rows[i]["Name"].ToString();
imEror.Address = ds.Tables[0].Rows[i]["Address"].ToString();
}
if (ds.Tables[0].Rows[i]["Address"].ToString() != "")
{
uni.Address = ds.Tables[0].Rows[i]["Address"].ToString();
}
else
{
imEror.Address = null;
imEror.Name = ds.Tables[0].Rows[i]["Name"].ToString();
imEror.Code = ds.Tables[0].Rows[i]["UniversityCode"].ToString();
}
uni.Website = ds.Tables[0].Rows[i]["Website"].ToString() != "" ? ds.Tables[0].Rows[i]["Website"].ToString() : null;
uni.Phone = ds.Tables[0].Rows[i]["Phone"].ToString() != "" ? ds.Tables[0].Rows[i]["Phone"].ToString() : null;
bool flag = true;
if (uni.Name != null && uni.Address != null && uni.UniversityCode != null)
{
for (int j = 0; j < tmp; j++)
{
imEror = new ImportErrors();
if (ds.Tables[0].Rows[i]["Name"].ToString() == listUni[j].Name)
{
imEror.SameName = uni.Name;
imEror.Name = uni.Name;
imEror.Code = uni.UniversityCode;
imEror.Address = uni.Address;
flag = false;
}
if (ds.Tables[0].Rows[i]["UniversityCode"].ToString() == listUni[j].UniversityCode)
{
imEror.SameCode = uni.UniversityCode;
imEror.Code = uni.UniversityCode;
imEror.Name = uni.Name;
imEror.Address = uni.Address;
flag = false;
}
if (imEror.SameCode != null || imEror.SameName != null)
{
imEror.Row = i + 2;
listErrors.Add(imEror);
break;
}
}
if (flag)
{
db.Universities.Add(uni);
db.SaveChanges();
}
}
else
{
imEror.Row = i + 2;
listErrors.Add(imEror);
}
}
if (listErrors.Count > 0)
{
TempData["listerror"] = listErrors;
return RedirectToAction("ImportError");
}
return RedirectToAction("ManageUniversity");
//ViewBag.message = "Information saved successfully.";
}
ModelState.AddModelError("", "Plese select Excel File.");
}
return RedirectToAction("ManageUniversity");
}