public void DeleteAccidents(int AccidentID) { Transaction trans; SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("DeleteAccident", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@AccidentID", AccidentID)); cmd.ExecuteNonQuery(); } catch (Exception e) { trans.RollBack(); throw e; } trans.Commit(); }
public List<Message> GetMessage(int UserID) { trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("GetMessage", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserID", UserID)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.HasRows && rdr.Read()) { messageList = new List<Message>(); Message A = new Message( rdr["Message"].ToString(), DateTime.Parse(rdr["sendDate"].ToString()), rdr["Tittle"].ToString(), new Costumer(rdr["Name"].ToString())); messageList.Add(A); } } catch (Exception e) { trans.RollBack(); throw e; } trans.getcon().Close(); return messageList; }
public int getUserIDFromUsername(string username) { int userID = 0; trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("getIDfromUsername", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Username", username)); SqlDataReader dbr; dbr = cmd.ExecuteReader(); while (dbr.Read() == true) { if (dbr.HasRows) { userID = (int)dbr["UserID"]; } } dbr.Close(); } catch (Exception e) { trans.RollBack(); throw e; // trans.getcon().Close(); } trans.Commit(); trans.getcon().Close(); return userID; }
public List<Day> getAccidentDaysFromDB(DateTime selectStart, DateTime selectEnd, int DeviceNr) { List<Day> dayAccidents = new List<Day>(); trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("nrOfAccident", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceNr)); cmd.Parameters.Add(new SqlParameter("@StartDate", selectStart)); cmd.Parameters.Add(new SqlParameter("@EndDate", selectEnd)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { // Device D = new Device(int.Parse(rdr["DeviceNR"].ToString()), User); Day A = new Day(DateTime.Parse((rdr["TimeOfAccident"]).ToString()), int.Parse(rdr["NrOfAccident"].ToString()),int.Parse(rdr["Average"].ToString())); dayAccidents.Add(A); } rdr.Close(); } catch (Exception e) { //trans.RollBack(); throw e; //trans.getcon().Close(); } //trans.Commit(); trans.getcon().Close(); return dayAccidents; }
public void RegisterAccident( int amount, DateTime time, int Drinks, DateTime TimeSleep, DateTime TimeToilet, int ToiletVisit ) { Transaction trans; SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); int i = 101; try { cmd = new SqlCommand("RegisterBWAcc", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Amount", amount)); cmd.Parameters.Add(new SqlParameter("@TimeOfAccident", time)); cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceNumber)); cmd.Parameters.Add(new SqlParameter("@Drinks", Drinks)); cmd.Parameters.Add(new SqlParameter("@TimeSleep", TimeSleep)); cmd.Parameters.Add(new SqlParameter("@TimeToilet", TimeToilet)); cmd.Parameters.Add(new SqlParameter("@Toilet", ToiletVisit)); //cmd.Parameters.Add(new SqlParameter("@Version", (Version + 1))); i = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception e) { trans.RollBack(); throw e; } //trans.Commit(); }
public void SendMessage(string Messagtxt, DateTime senddate, string Tittle, int UserID) { Transaction trans; SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("SendMessage", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Message", Messagtxt)); cmd.Parameters.Add(new SqlParameter("@sendDate", senddate)); cmd.Parameters.Add(new SqlParameter("@Tittle", Tittle)); cmd.Parameters.Add(new SqlParameter("@UserID", UserID)); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception e) { trans.RollBack(); throw e; } }
public void UpdateAccident(int accID, DateTime time, int amount, int DeviceID) { Transaction trans; SqlCommand cmd; int versionCheck = getVersion(accID); trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("UpdateAccident", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@AccidentID", accID)); cmd.Parameters.Add(new SqlParameter("@Amount", amount)); cmd.Parameters.Add(new SqlParameter("@TimeOfAccident", time)); cmd.Parameters.Add(new SqlParameter("@DeviceID", DeviceID)); cmd.Parameters.Add(new SqlParameter("@versionCheck", versionCheck)); cmd.ExecuteNonQuery(); } catch (Exception e) { trans.RollBack(); throw e; } trans.Commit(); }
public void RegisterDevice(int userID) { Transaction trans = new Transaction(); SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("RegisterDevice", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserID", userID)); cmd.ExecuteNonQuery(); } catch (Exception e) { trans.RollBack(); throw e; } trans.Commit(); }
public List<Accident> ReadAccidents(int UserID) { trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("ReadAccidents", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@UserID", UserID)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.HasRows && rdr.Read()) { Accident A = new Accident(int.Parse(rdr["AccidentID"].ToString()), int.Parse(rdr["Amount"].ToString()), DateTime.Parse(rdr["TimeOfAccident"].ToString()), new Device(int.Parse(rdr["DeviceID"].ToString()), User)); A.Version = int.Parse(rdr["Version"].ToString()); tempList.Add(A); } } catch (Exception e) { trans.RollBack(); throw e; } trans.getcon().Close(); return tempList; }
public int getVersion(int accID) { trans = new Transaction(); trans.BegindTransactions(); SqlDataReader dbr; int version = 0; try { cmd = new SqlCommand("getVersion", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@AccidentID", accID)); cmd.ExecuteNonQuery(); dbr = cmd.ExecuteReader(); while (dbr.Read() == true) { if (dbr.HasRows) { version = (int)dbr["Version"]; } } dbr.Close(); } catch (Exception) { throw; } trans.getcon().Close(); return version; }
public List<Accident> getListByDateFromDB(DateTime Date, int DeviceNr) { List<Accident> dayAccidents = new List<Accident>(); DateTime newDate = DateTime.Parse(Date.ToShortDateString()); trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("getInfoFromDate" , trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@Date", newDate)); cmd.Parameters.Add(new SqlParameter("@DeviceNr", DeviceNr)); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.HasRows && rdr.Read()) { // Device D = new Device(int.Parse(rdr["DeviceNR"].ToString()), User); Accident acc = new Accident(int.Parse((rdr["AccidentID"]).ToString()), int.Parse((rdr["Amount"]).ToString()), DateTime.Parse((rdr["TimeOfAccident"]).ToString()),int.Parse((rdr["DeviceID"]).ToString()), int.Parse((rdr["Drinks"]).ToString()), DateTime.Parse((rdr["TimeSleep"]).ToString()), DateTime.Parse((rdr["TimeToilet"]).ToString()), int.Parse((rdr["Toilet"]).ToString()), int.Parse((rdr["Version"]).ToString())); dayAccidents.Add(acc); } } catch (Exception e) { trans.RollBack(); throw e; // trans.getcon().Close(); } //trans.Commit(); trans.getcon().Close(); return dayAccidents; }
public void SignUp(string type, string birthday, string UserName, string PassWord, string Name, int deviceID) { Transaction trans; SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); try { cmd = new SqlCommand("NewSignUp", trans.getcon(), trans.GetTransaction()); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@type", type)); cmd.Parameters.Add(new SqlParameter("@birthday", birthday)); cmd.Parameters.Add(new SqlParameter("@UserName", UserName)); cmd.Parameters.Add(new SqlParameter("@PassWord", PassWord)); cmd.Parameters.Add(new SqlParameter("@Name", Name)); cmd.Parameters.Add(new SqlParameter("@deviceID", deviceID)); cmd.ExecuteNonQuery(); } catch (Exception e) { trans.RollBack(); throw e; } trans.Commit(); }
public int login(string Username, string Password) { int Perm = 3; Transaction trans; SqlCommand cmd; trans = new Transaction(); trans.BegindTransactions(); String query = "Select UserName ,PassWord ,TypeOfUser from LogIn1 where UserName = '******'and PassWord = '******'"; cmd = new SqlCommand(query, trans.getcon(), trans.GetTransaction()); SqlDataReader dbr; dbr = cmd.ExecuteReader(); while (dbr.Read() == true) { if (dbr.HasRows) { Perm = (int)dbr["TypeOfUser"]; } } dbr.Close(); trans.Commit(); trans.GetConnection().Close(); return Perm; }