public ActionResult Add([Bind(Include = "name")] Soruce model)
{
if (!ModelState.IsValid)
{
return(RedirectToAction("Index", "Source", new { success = "failed" }));
}
try
{
Soruce s = new Soruce {
name = model.name
};
db.sources.Add(s);
db.SaveChanges();
}
catch (Exception ex)
{
if (!ModelState.IsValid)
{
return(RedirectToAction("Index", "Source", new { success = "failed" }));
}
}
return(RedirectToAction("Index", "Source", new { success = "ok" }));
}
public ActionResult Delete(int?id)
{
try
{
Soruce s = db.sources.Where(src => src.id == id).FirstOrDefault();
if (s == null)
{
return(new HttpStatusCodeResult(404));
}
db.sources.Remove(s);
db.SaveChanges();
return(new HttpStatusCodeResult(200));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(500));
throw;
}
}
public ActionResult Update([Bind(Include = "id,name")] Soruce model)
{
if (!ModelState.IsValid)
{
return(RedirectToAction("Index", "Source", new { success = "failed" }));
}
try
{
Soruce s = db.sources.Where(src => src.id == model.id).FirstOrDefault();
if (s == null)
{
return(new HttpStatusCodeResult(404));
}
s.name = model.name;
db.SaveChanges();
return(new HttpStatusCodeResult(200));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(500));
throw;
}
}
public ActionResult ImportExcelFileToDatabase(HttpPostedFileBase FileUpload)
{
int total = 0;
int used_phones = 0;
try
{
DataSet ds = new DataSet();
if (Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + 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);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
excelConnection.Close();
excelConnection.Dispose();
excelConnection1.Close();
excelConnection1.Dispose();
}
total = ds.Tables[0].Rows.Count;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string source_name = ds.Tables[0].Rows[i][5].ToString();
Soruce s = db.sources.Where(sc => sc.name == source_name).FirstOrDefault();
if (s == null)
{
Soruce n_sc = new Soruce
{
name = ds.Tables[0].Rows[i][5].ToString()
};
db.sources.Add(n_sc);
db.SaveChanges();
}
var date = String.IsNullOrEmpty(ds.Tables[0].Rows[i][6].ToString()) ? DateTime.Now : Convert.ToDateTime(ds.Tables[0].Rows[i][6].ToString());
string phone = ds.Tables[0].Rows[i][1].ToString();
string phone2 = ds.Tables[0].Rows[i][2].ToString();
bool used_phone_status = false;
if (!String.IsNullOrEmpty(phone))
{
if (db.questions.Any(qu => qu.phone.Trim() == phone || qu.phone2.Trim() == phone))
{
used_phone_status = true;
used_phones = used_phones + 1;
}
}
if (!String.IsNullOrEmpty(phone2))
{
if (db.questions.Any(qu => qu.phone.Trim() == phone2 || qu.phone2.Trim() == phone2))
{
used_phone_status = true;
if (!used_phone_status)
{
used_phones = used_phones + 1;
}
}
}
if (!used_phone_status)
{
Question q = new Question
{
fullname = ds.Tables[0].Rows[i][0].ToString(),
phone = ds.Tables[0].Rows[i][1].ToString(),
phone2 = ds.Tables[0].Rows[i][2].ToString(),
question = ds.Tables[0].Rows[i][3].ToString(),
note = ds.Tables[0].Rows[i][4].ToString(),
source = ds.Tables[0].Rows[i][5].ToString(),
added = date
};
db.questions.Add(q);
}
db.SaveChanges();
total = (total - used_phones) < 0 ? 0 : total - used_phones;
}
return(RedirectToAction("Import", new RouteValueDictionary(new { controller = "Question", action = "Import", success = "ok", added = total, unadded = used_phones })));
}
else
{
return(RedirectToAction("Import", new RouteValueDictionary(new { controller = "Question", action = "Import", success = "failed" })));
}
}
catch (Exception ex)
{
return(RedirectToAction("Import", new RouteValueDictionary(new { controller = "Question", action = "Import", success = "failed", msg = ex.Message.ToString() })));
}
}
