public JsonResult MSU_Create(UsersModalModel jsonString) { string StudentNumber = jsonString.StudentNumber; string FirstName = jsonString.FirstName; string LastName = jsonString.LastName; string Email = jsonString.Email; string Notes = jsonString.Notes; //Notes field is allowed to be null, therfore account for string variation if (Notes != null) { Notes = "'" + Notes + "'"; } else { Notes = "NULL"; } bool createStatus = mugshareusers.CreateQuery(StudentNumber, FirstName, LastName, Email, Notes); IsTrueOrFalse model = new IsTrueOrFalse(createStatus); return(ResultJson(model)); }
public JsonResult MSU_Edit(UsersModalModel jsonString) { string UserKey = jsonString.UserKey; string StudentNumber = jsonString.StudentNumber; string FirstName = jsonString.FirstName; string LastName = jsonString.LastName; string Email = jsonString.Email; bool MugInUse = jsonString.MugInUse; string DateOfRental = jsonString.DateOfRental; string Notes = jsonString.Notes; //Date of rental field is allowed to be null, therfore account for string variation if (DateOfRental != null) { DateOfRental = "'" + DateOfRental + "'"; } else { DateOfRental = "NULL"; } //Notes field is allowed to be null, therfore account for string variation if (Notes != null) { Notes = "'" + Notes + "'"; } else { Notes = "NULL"; } bool editStatus = mugshareusers.EditQuery(UserKey, StudentNumber, FirstName, LastName, Email, MugInUse, DateOfRental, Notes); IsTrueOrFalse model = new IsTrueOrFalse(editStatus); return(ResultJson(model)); }
/* * Function: GetRecord * * Get data for mug share users modals. * * Returns: * * model - a UsersModalModel model of the record with the primary key equal to the UserKey */ public static UsersModalModel GetRecord(string UserKey) { SqlConnection dbconnection = new SqlConnection(); SqlCommand dbcommand = new SqlCommand(); SqlDataReader dbreader; string connectionstring = ConfigurationManager.ConnectionStrings["MugShareDB"].ToString(); string queryString = "SELECT * FROM MugShareUsers WHERE pKey = " + UserKey; UsersModalModel model = new UsersModalModel(); try { if (dbconnection.State == ConnectionState.Closed) { dbconnection.ConnectionString = connectionstring; dbconnection.Open(); } dbcommand.Connection = dbconnection; dbcommand.CommandTimeout = 600; dbcommand.CommandText = queryString; dbcommand.CommandType = CommandType.Text; dbreader = dbcommand.ExecuteReader(); if (dbreader.HasRows) { dbreader.Read(); if (!string.IsNullOrEmpty(dbreader["pKey"].ToString())) { model.UserKey = dbreader["pKey"].ToString(); } if (!string.IsNullOrEmpty(dbreader["StudentNumber"].ToString())) { model.StudentNumber = dbreader["StudentNumber"].ToString(); } if (!string.IsNullOrEmpty(dbreader["FirstName"].ToString())) { model.FirstName = dbreader["FirstName"].ToString(); } if (!string.IsNullOrEmpty(dbreader["LastName"].ToString())) { model.LastName = dbreader["LastName"].ToString(); } if (!string.IsNullOrEmpty(dbreader["Email"].ToString())) { model.Email = dbreader["Email"].ToString(); } if (dbreader["MugInUse"].ToString() == "True") { model.MugInUse = true; } else { model.MugInUse = false; } if (!string.IsNullOrEmpty(dbreader["DateOfRental"].ToString())) { string temp = dbreader["DateOfRental"].ToString(); model.DateOfRental = temp.Substring(0, temp.Length - 12); } if (!string.IsNullOrEmpty(dbreader["TotalMugsBorrowed"].ToString())) { model.TotalMugsBorrowed = dbreader["TotalMugsBorrowed"].ToString(); } if (!string.IsNullOrEmpty(dbreader["Notes"].ToString())) { model.Notes = dbreader["Notes"].ToString(); } } return(model); } catch (Exception e) { throw new Exception(@"Mug-Share Application GetRecord() failed : ", e); } finally { if (dbconnection.State == ConnectionState.Open) { dbconnection.Close(); } } }
public JsonResult MSU_Excel(string ExcelPath) { //string connectionString = @"C:\Users\Logan\Downloads\StudentInfoExample.xlsx"; string connectionString = ExcelPath; string sheetName = "Sheet1"; bool createStatus = false; //tempString is the hardcoded working string, and connectionString is the same string passed through ajax //if(tempString != connectionString) //{ // IsTrueOrFalse temp = new IsTrueOrFalse(false); // return ResultJson(temp); //} List <UsersModalModel> list = new List <UsersModalModel>(); //connect to excel document and get row data var excelFile = new ExcelQueryFactory(connectionString); var studentRecords = from a in excelFile.Worksheet(sheetName) select a; //create UsersModalModel models containing the data from excel document foreach (var a in studentRecords) { UsersModalModel model = new UsersModalModel(); model.StudentNumber = a["STUD_NO"]; model.FirstName = a["GIVEN_NAME"]; model.LastName = a["SURNAME"]; model.Email = a["EMAIL_ADDRESS"]; model.TotalMugsBorrowed = "0"; list.Add(model); } //get ride of duplicates based on student numbers list = list.GroupBy(x => x.StudentNumber).Select(y => y.First()).ToList(); //delete all records from the MugShareUsers table in database if (list.Count > 0) { string clearMSUTable = "DELETE FROM MugShareUsers"; createStatus = mugshareusers.QueryProcessor(clearMSUTable); } //make query string to input excel data into database if (createStatus) { string dataEntryQuery = "INSERT INTO MugShareUsers (StudentNumber, FirstName, LastName, Email, MugInUse, TotalMugsBorrowed) VALUES "; foreach (UsersModalModel element in list) { string recordValues = "(" + element.StudentNumber + ", '" + element.FirstName + "', '" + element.LastName + "', '" + element.Email + "', 'false', 0), "; dataEntryQuery += recordValues; } dataEntryQuery = dataEntryQuery.Substring(0, dataEntryQuery.Length - 2); createStatus = mugshareusers.QueryProcessor(dataEntryQuery); } IsTrueOrFalse boolModel = new IsTrueOrFalse(createStatus); return(ResultJson(boolModel)); }
public JsonResult MSU_GetRecord(string UserKey) { UsersModalModel model = mugshareusers.GetRecord(UserKey); return(ResultJson(model)); }