// THIS ReadAllUsersFromDB METHOD SELECTS ALL THE FIELDS FROM logins DATABASE and
// STORES THEM INTO A LIST OF USERS MODELS
static List <TaxSystemOperationMODEL> ReadAllUsersFromDB()
{
List <TaxSystemOperationMODEL> myUsers = new List <TaxSystemOperationMODEL>();
SqlConnection myConnSRFN = new SqlConnection();
try
{
myConnSRFN.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
myConnSRFN.Open();
string queryString = "SELECT loginId,userName,passWord,description,isAdmin FROM Nutella.logins;";
SqlCommand commandSRFN = new SqlCommand(queryString, myConnSRFN);
SqlDataReader myUsersResults = commandSRFN.ExecuteReader();
while (myUsersResults.Read())
{
TaxSystemOperationMODEL newUser = new TaxSystemOperationMODEL();
newUser.loginId = int.Parse(myUsersResults["loginId"].ToString());
newUser.userName = myUsersResults["userName"].ToString();
newUser.passWord = myUsersResults["passWord"].ToString();
newUser.description = myUsersResults["description"].ToString();
newUser.isAdmin = myUsersResults["isAdmin"].ToString();
myUsers.Add(newUser);
}
}
catch (Exception ex)
{
return(null);
}
finally
{
myConnSRFN.Close();
}
return(myUsers);
}
public ActionResult Edit(int id, FormCollection collection)
{
SqlConnection connEDITpost = new SqlConnection();
connEDITpost.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
TaxSystemOperationMODEL ModifiedUserToUpload = new TaxSystemOperationMODEL();
try
{
connEDITpost.Open();
string queryEDITpost = "UPDATE Nutella.logins SET " +
"userName = '******', " +
"passWord = '******', " +
"description = '" + collection["description"] + "', " +
"isAdmin = '" + collection["isAdmin"] + "' " +
"WHERE loginId = " + id + ";";
SqlCommand commandEDITpost = new SqlCommand(queryEDITpost, connEDITpost);
commandEDITpost.ExecuteNonQuery();
}
catch (Exception ex)
{
return(null);
}
finally
{
connEDITpost.Close();
}
return(RedirectToAction("Manage"));
}
public ActionResult Create(FormCollection collection)
{
TaxSystemOperationMODEL CreateNewUser = new TaxSystemOperationMODEL();
SqlConnection myconnCREATE = new SqlConnection();
try
{ // TODO: Add insert logic here
myconnCREATE.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
myconnCREATE.Open();
CreateNewUser.userName = collection["userName"];
CreateNewUser.passWord = collection["passWord"];
CreateNewUser.description = collection["description"];
CreateNewUser.isAdmin = collection["isAdmin"];
//INSERT INTO Contact(ID, FirstName, LastName) VALUES(1, 'serafin', 'g');
string queryCREATE = "INSERT INTO Nutella.logins (userName,passWord,description,isAdmin) VALUES ('" +
CreateNewUser.userName + "','" +
CreateNewUser.passWord + "','" +
CreateNewUser.description + "','" +
CreateNewUser.isAdmin + "');";
SqlCommand commCREATE = new SqlCommand(queryCREATE, myconnCREATE);
int numRowsAffected = commCREATE.ExecuteNonQuery();
myconnCREATE.Close();
return(RedirectToAction("Manage"));
}
catch
{
return(View());
}
}
public ActionResult Edit(int id, FormCollection collection)
{
SqlConnection connEditPostOp = new SqlConnection();
connEditPostOp.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
TaxSystemOperationMODEL OperationModified = new TaxSystemOperationMODEL();
try
{
connEditPostOp.Open();
OperationModified.isin = collection["isin"];
string queryEDITpostOp = "UPDATE Nutella.operations SET " +
"isin = '" + collection["isin"] + "', " +
"purchaseDate = '" + collection["purchaseDate"] + "', " +
"sellDate = '" + Convert.ToDateTime(collection["sellDate"]) + "', " +
"amount = '" + collection["amount"] + "', " +
"description = '" + collection["description"] + "' " +
"WHERE operationId = " + id + ";";
SqlCommand commandEDITpostOp = new SqlCommand(queryEDITpostOp, connEditPostOp);
commandEDITpostOp.ExecuteNonQuery();
}
catch (Exception ex)
{
return(null);
}
finally
{
connEditPostOp.Close();
}
return(RedirectToAction("Manage"));
}
// GET: TaxSystemOperation/Edit/5
public ActionResult Edit(int id)
{
SqlConnection connEditGetOp = new SqlConnection();
connEditGetOp.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
TaxSystemOperationMODEL OperationToEdit = new TaxSystemOperationMODEL();
try
{
connEditGetOp.Open();
string queryEDITgetOp = "SELECT * FROM Nutella.operations WHERE operationId =" + id + ";";
SqlCommand commEditGetOperations = new SqlCommand(queryEDITgetOp, connEditGetOp);
SqlDataReader DReditGetOp = commEditGetOperations.ExecuteReader();
while (DReditGetOp.Read())
{
OperationToEdit.isin = DReditGetOp["isin"].ToString();
OperationToEdit.purchaseDate = Convert.ToDateTime(DReditGetOp["purchaseDate"]);
OperationToEdit.sellDate = Convert.ToDateTime(DReditGetOp["sellDate"]);
OperationToEdit.amount = DReditGetOp["amount"].ToString();
OperationToEdit.description = DReditGetOp["description"].ToString();
}
}
catch
{
return(null);
}
finally
{
connEditGetOp.Close();
}
return(View(OperationToEdit));
}
public ActionResult Create(FormCollection collection)
{
TaxSystemOperationMODEL newOperation = new TaxSystemOperationMODEL();
SqlConnection connCREATEpost = new SqlConnection();
try
{
connCREATEpost.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
newOperation.isin = collection["isin"];
newOperation.purchaseDate = Convert.ToDateTime(collection["purchaseDate"].ToString());
newOperation.sellDate = Convert.ToDateTime(collection["sellDate"].ToString());
//newOperation.sellDate = collection["sellDate"].ToString("YYYY-MM-dd");
newOperation.amount = collection["amount"];
newOperation.description1 = collection["description"];
string queryCREATE = "INSERT INTO Nutella.operations (isin,purchaseDate,sellDate,amount,description)" +
" VALUES ('" +
newOperation.isin + "','" +
newOperation.purchaseDate + "','" +
newOperation.sellDate + "','" +
newOperation.amount + "','" +
newOperation.description1 + "');";
connCREATEpost.Open();
SqlCommand commCREATEpost = new SqlCommand(queryCREATE, connCREATEpost);
commCREATEpost.ExecuteNonQuery();
return(RedirectToAction("Manage"));
}
catch
{
return(View());
}
finally
{
connCREATEpost.Close();
}
}
static List <TaxSystemOperationMODEL> ReadAllOperationsFromDB()
{
List <TaxSystemOperationMODEL> myOperations = new List <TaxSystemOperationMODEL>();
SqlConnection myConnSRFN2 = new SqlConnection();
try
{
myConnSRFN2.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
myConnSRFN2.Open();
string queryString = "SELECT operationId,isin,purchaseDate,sellDate,amount,description FROM Nutella.operations;";
SqlCommand commandReadAll = new SqlCommand(queryString, myConnSRFN2);
SqlDataReader myUsersResults = commandReadAll.ExecuteReader();
while (myUsersResults.Read())
{
TaxSystemOperationMODEL newOperation = new TaxSystemOperationMODEL();
newOperation.operationId = int.Parse(myUsersResults["operationId"].ToString());
newOperation.isin = myUsersResults["isin"].ToString();
newOperation.purchaseDate = (DateTime)myUsersResults["purchaseDate"];
newOperation.sellDate = Convert.ToDateTime(myUsersResults["sellDate"]);
//the most correct one: the selldate one BUT with Tostring("YYYY-MM-dd") at the end
newOperation.amount = myUsersResults["amount"].ToString();
newOperation.description = myUsersResults["description"].ToString();
myOperations.Add(newOperation);
}
}
catch (Exception ex)
{
return(null);
}
finally
{
myConnSRFN2.Close();
}
return(myOperations);
}
// GET: TaxSystemUsers/Edit/5
public ActionResult Edit(int id)
{
//I configure the connection to the DB
SqlConnection connEDITget = new SqlConnection();
connEDITget.ConnectionString = ConfigurationManager.ConnectionStrings["SRFNconnection"].ConnectionString;
TaxSystemOperationMODEL UserToEdit = new TaxSystemOperationMODEL();
try
{
string queryEDITget = "SELECT userName,passWord,description,isAdmin FROM Nutella.logins WHERE loginId=" + id + ";";
SqlCommand commandEDITget = new SqlCommand(queryEDITget, connEDITget);
connEDITget.Open();
SqlDataReader UserWeWannaEdit = commandEDITget.ExecuteReader();
//now I assign the result of the select to the Database to each element of the object
//UserToEdit, recently created above
while (UserWeWannaEdit.Read())
{
UserToEdit.userName = UserWeWannaEdit["userName"].ToString();
UserToEdit.passWord = UserWeWannaEdit["passWord"].ToString();
UserToEdit.description = UserWeWannaEdit["description"].ToString();
UserToEdit.isAdmin = UserWeWannaEdit["isAdmin"].ToString();
}
}
catch
{
return(null);
}
finally
{
connEDITget.Close();
}
return(View(UserToEdit));
}
