public static void Create()
{
Console.WriteLine("Creating A New Quote");
Console.Write("Enter first name: ");
string user = Console.ReadLine();
Console.Write("Enter a quote: ");
string quote = Console.ReadLine();
string query = $"INSERT INTO quotes (user, quote, created_at) VALUES('{user}', '{quote}', NOW())";
DbConnector.ExecuteQuery(query);
Console.WriteLine("The quote has been added to the db!");
}
// Create Method
public static void Create()
{
Console.WriteLine("New Person is going to be Created");
Console.Write("user's first name: ");
string FirstName = Console.ReadLine();
Console.Write("user's last name: ");
string LastName = Console.ReadLine();
Console.Write("User's Fav Number");
string Favourite_Number = Console.ReadLine();
int Fav_Number = Int32.Parse(Favourite_Number); // Converting into integer
string query = $"INSERT INTO users (FirstName, LastName, FavouriteNumber) VALUES('{FirstName}', '{LastName}', '{Fav_Number}')";
DbConnector.ExecuteQuery(query);
}
public static void Update()
{
Console.WriteLine("Enter ID of user you wish to update: ");
string userId = Console.ReadLine();
int user_id = Int32.Parse(userId);
Console.WriteLine("Update user's first name: ");
string NAME = Console.ReadLine();
Console.WriteLine("Update user's quote: ");
string Quote = Console.ReadLine();
string query = $"UPDATE users SET Name = '{NAME}', Quote = '{Quote}', updated_at = NOW() WHERE id = {user_id}";
DbConnector.ExecuteQuery(query);
Console.WriteLine($"{NAME}'s info has been updated!");
}
//Update Rows
public static void Update()
{
Console.WriteLine("What is the Id of the user that you want to edit");
string userId = Console.ReadLine();
int user_id = Int32.Parse(userId);
Console.WriteLine("Users new First name");
string First_Name = Console.ReadLine();
Console.WriteLine("Users new Lastname");
string Last_Name = Console.ReadLine();
Console.WriteLine("user's new favourite number");
string fav_num = Console.ReadLine();
int favourite_number = Int32.Parse(fav_num);
string query = $"UPDATE users SET FirstName = '{First_Name}',LastName = '{Last_Name}', FavouriteNumber = '{fav_num}' WHERE id = {user_id}";
DbConnector.ExecuteQuery(query);
}
public DojoModule()
{
Get("/", args =>
{
return(View["dojo.sshtml"]);
});
Post("/quotes", args =>
{
Console.WriteLine("****************************");
Console.WriteLine("quotes route check");
Console.WriteLine("****************************");
string NAME = Request.Form["NAME"];
string Quote = Request.Form["Quote"];
Console.WriteLine("****************************");
Console.WriteLine("Creating A New User");
Console.WriteLine("****************************");
string query = $"INSERT INTO users (NAME, Quote, created_at) VALUES('{NAME}', '{Quote}', NOW())";
DbConnector.ExecuteQuery(query);
return(Response.AsRedirect("/quotes"));
});
Get("/quotes", args =>
{
@ViewBag.quotes = "";
List <Dictionary <string, object> > myresults = DbConnector.ExecuteQuery("SELECT * FROM users");
myresults.Reverse();
foreach (Dictionary <string, object> item in myresults)
{
@ViewBag.quotes += "<p>" + item["Quote"] + " " + "<br>" + "-" + item["NAME"] + " " + item["created_at"] + "</p>" + "<hr>";
}
return(View["quotes.sshtml", myresults]);
});
}
private void button3_Click(object sender, EventArgs e)
{
DbConnector.ExecuteQuery($"INSERT INTO Workers(Position, Description, Exp, idUser) VALUES('{textBox1.Text}', '{textBox2.Text}', '{textBox3.Text}', '{comboBox2.SelectedValue}') ");
}
private void button2_Click(object sender, EventArgs e)
{
DbConnector.ExecuteQuery($"DELETE FROM Workers WHERE idWorkers = '{comboBox1.SelectedValue}'");
}
private void button1_Click(object sender, EventArgs e)
{
DbConnector.ExecuteQuery($"UPDATE Workers SET Position = '{textBox1.Text}', Description = '{textBox2.Text}', Exp = '{textBox3.Text}', idUser = '******' WHERE idWorkers = '{comboBox1.SelectedValue}'");
}
private void button1_Click(object sender, EventArgs e)
{
DbConnector.ExecuteQuery($"UPDATE Users SET FirstName = '{textBox1.Text}', MidName = '{textBox2.Text}', LastName = '{textBox3.Text}', Date = '{textBox4.Text}', Number = '{textBox5.Text}', idRole = '{comboBox2.SelectedValue}' WHERE idUsers = '{comboBox1.SelectedValue}' ");
}
public LoginModule()
{
Get("/", args =>
{
List <string> errors = new List <string>();
if (Session["errors"] != null)
{
errors = (List <string>)Session["errors"];
ViewBag.errors = true;
Session.DeleteAll();
}
return(View["login.sshtml", errors]);
});
Post("/login", args =>
{
string email = (string)Request.Form.login_email;
string password = (string)Request.Form.login_password;
string Query = $"SELECT id, password FROM users WHERE email= '{email}'";
List <Dictionary <string, object> > result = DbConnector.ExecuteQuery(Query);
if (result.Count < 1)
{
List <string> errors = new List <string>();
errors.Add("The username or password does not match one on file");
Session["errors"] = errors;
return(Response.AsRedirect("/"));
}
string encryptedString = (string)result[0]["password"];
if (Crypto.VerifyHashedPassword(encryptedString, password))
{
Session["id"] = (int)result[0]["id"];
ViewBag.id = (int)Session["id"];
return(View["success"]);
}
else
{
List <string> errors = new List <string>();
errors.Add("The username or password does not match one on file");
Session["errors"] = errors;
return(Response.AsRedirect("/"));
}
});
Post("/register", args =>
{
List <string> errors = new List <string>();
if (((string)Request.Form.first_name).Length < 2)
{
errors.Add("First Name not long enough");
}
if (((string)Request.Form.last_name).Length < 2)
{
errors.Add("Last Name not long enough");
}
if (((string)Request.Form.password).Length < 8)
{
errors.Add("Password not long enough");
}
if (((string)Request.Form.confirm_password) != ((string)Request.Form.password))
{
errors.Add("Passwords do not match!");
}
if (Regex.IsMatch(((string)Request.Form.first_name), @"^[\p{L}]+$") == false && ((string)Request.Form.first_name).Length > 0)
{
errors.Add("Name Cannot contain numbers!");
}
if (Regex.IsMatch(((string)Request.Form.last_name), @"^[\p{L}]+$") == false && ((string)Request.Form.last_name).Length > 0)
{
errors.Add("Name Cannot contain numbers!");
}
if (Regex.IsMatch(((string)Request.Form.email), @"^(?("")("".+?(?<!\\)""@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))" +
@"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9][\-a-z0-9]{0,22}[a-z0-9]))$",
RegexOptions.IgnoreCase) == false)
{
errors.Add("Enter a valid email address!");
}
if (((string)Request.Form.email).Length < 1)
{
errors.Add("Email is required.");
}
if (errors.Count > 0)
{
Session["errors"] = errors;
return(Response.AsRedirect("/"));
}
else
{
string EncryptedPassword = Crypto.HashPassword(((string)Request.Form.confirm_password));
string first_name = ((string)Request.Form.first_name);
string last_name = ((string)Request.Form.last_name);
string email = ((string)Request.Form.email);
string Query = $"INSERT INTO users (first_name, last_name, email, password, created_at, updated_at) VALUES ('{first_name}', '{last_name}', '{email}', '{EncryptedPassword}', NOW(), NOW())";
DbConnector.ExecuteQuery(Query);
string getId = $"SELECT id FROM users WHERE email= '{email}'";
List <Dictionary <string, object> > result = DbConnector.ExecuteQuery(getId);
Session["id"] = (int)result[0]["id"];
ViewBag.id = (int)Session["id"];
return(View["success"]);
}
});
}
private void OnExecuteClick()
{
WorkUnit = new UnitOfWork(() => CurrentData = DbConnector.ExecuteQuery(queryString), true);
WorkUnit.StatusChanged += WorkUnit_StatusChanged;
WorkUnit.DoWork();
}
private static void _transferTable(DbConnector dbConn, string tableName, string whereClause) {
var q = "";
q += "IF EXISTS( ";
q += " SELECT sc.column_id ";
q += " FROM sys.columns sc, ";
q += " sys.tables st ";
q += " WHERE sc.object_id = st.object_id AND ";
q += " st.name = '{0}' AND ";
q += " sc.is_identity = 1) ";
q += " select 1 AS BPL_RETURNED_SCALAR ";
q += "else ";
q += " select 0 AS BPL_RETURNED_SCALAR";
var isIdentity = dbConn.ExecuteQueryScalar<bool>(string.Format(q, tableName));
if (isIdentity) {
dbConn.ExecuteQuery(string.Format("SET IDENTITY_INSERT [{0}].[dbo].[{1}] ON", AnonDBName, tableName));
}
q = "";
q += "SELECT sc.name AS ColumnName, ";
q += " CAST(ISNULL(ep.value, '0') AS bit) AS IsAnonimize ";
q += "FROM syscolumns sc ";
q += "INNER JOIN sysobjects so ON sc.id = so.id ";
q += "LEFT JOIN sys.extended_properties ep ON so.id = ep.major_id AND sc.colorder = ep.minor_id AND ep.name = 'Anon' ";
q += "WHERE so.type = 'U' AND ";
q += " so.name = '{0}'";
q = string.Format(q, tableName);
var columns = dbConn.ExecuteQueryResultSet<TableColumn>(q);
q = string.Format("INSERT INTO [{0}].[dbo].[{1}] (", AnonDBName, tableName);
var comma = "";
foreach (var column in columns) {
q += comma;
q += string.Format("[{0}]", column.ColumnName);
comma = ",";
}
q += ") SELECT ";
comma = "";
foreach (var column in columns) {
q += comma;
if (column.IsAnonimize) {
q += string.Format("CASE WHEN {0} IS NULL THEN NULL ELSE dbo.F_BIN_TO_HEXSTR(CONVERT(VARBINARY(4), CHECKSUM(HASHBYTES('SHA1',[{1}])))) END AS {2}",
column.ColumnName, column.ColumnName, column.ColumnName);
} else {
q += string.Format("[{0}]", column.ColumnName);
}
comma = ",";
}
q += string.Format(" FROM [{0}] {1}", tableName, (whereClause != null) ? whereClause : "");
dbConn.ExecuteQuery(q);
if (isIdentity) {
dbConn.ExecuteQuery(string.Format("SET IDENTITY_INSERT [{0}].[dbo].[{1}] OFF", AnonDBName, tableName));
}
}
public LoginModule()
{
Get("/", args =>
{
// displaying all our forms
return(View["index.sshtml"]);
});
Post("/register", args =>
{
//actually registering the user
string FirstName = Request.Form["first_name"];
string LastName = Request.Form["last_name"];
string Email = Request.Form["email"];
string Password = Request.Form["password"];
string Confirm = Request.Form["confirm"];
// running validations
if (FirstName.Length == 0)
{
@ViewBag.FirstName = true;
}
if (LastName.Length == 0)
{
@ViewBag.LastName = true;
}
if (Email.Length == 0)
{
@ViewBag.Email = true;
}
if (Password.Length < 8)
{
@ViewBag.Password = true;
}
if (Confirm.Length == 0)
{
@ViewBag.Confirm = true;
}
if (Confirm != Password)
{
@ViewBag.match = true;
}
// if user passes all validations
if (FirstName.Length > 0 && LastName.Length > 0 && Email.Length > 0 && Password.Length > 7 && Password == Confirm)
{
// add user to database
string hash = Crypto.HashPassword(Password);
string query = $"INSERT INTO users (first_name, last_name, email, hash, created_at) VALUES('{FirstName}', '{LastName}', '{Email}', '{hash}', NOW())";
DbConnector.ExecuteQuery(query);
// selecting all from our users db and displaying them in descending order
query = "SELECT * FROM users ORDER BY id DESC LIMIT 1";
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
//setting the object to equal the first user returned
Dictionary <string, object> new_user = user[0];
//storing the current user
Session["current_user"] = (int)new_user["id"];
Console.WriteLine(Session["current_user"]);
//redirects to success page
return(Response.AsRedirect("/users"));
}
else
{
//if errors return the index page
return(View["index.sshtml"]);
}
});
Post("/login", args =>
{
string email = Request.Form["email"];
string password = Request.Form["password"];
//find the specific user that matches this email in the database
string query = $"SELECT * FROM users WHERE email = '{email}' LIMIT 1";
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
// if we did not find the user display error message
if (user.Count == 0)
{
@ViewBag.noUser = true;
return(View["index.sshtml"]);
}
//if no password was entered display error message
if (password.Length == 0)
{
@ViewBag.noPass = true;
return(View["index.sshtml"]);
}
else
{
//set the match_user object to be equal the the user returned
Dictionary <string, object> match_user = user[0];
// check the password entered with the password in the database, hashed one
bool match = Crypto.VerifyHashedPassword((string)match_user["hash"], password);
// if we find a match
if (match)
{
//store the users id in the session and redirects us to the success page
Session["current_user"] = (int)match_user["id"];
return(Response.AsRedirect("/users"));
}
//if the pws do not match display our error and return to the index
else
{
@ViewBag.wrongPass = true;
return(View["index.sshtml"]);
}
}
});
Get("/users", args =>
{
//display all users in the database
//empty viewbag
@ViewBag.users = "";
//set an empty list to equal the returned info from the database
List <Dictionary <string, object> > results = DbConnector.ExecuteQuery("SELECT * FROM users");
//set to be newest on top
results.Reverse();
//looping through the list of users and appending them to our view bag
foreach (Dictionary <string, object> item in results)
{
@ViewBag.users += "<p>" + "<b>" + item["first_name"] + " " + item["last_name"] + "</b>" + " " + "registered at " + item["created_at"] + "</p>" + "<hr>";
}
// set query to find the users where the id == the first name and set it to be the session user
string query = $"SELECT first_name FROM users WHERE id = {Session["current_user"]} LIMIT 1";
//set the list user to be equal to what our query returns
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
//loop through the list and find the user
foreach (Dictionary <string, object> item in user)
{
//set the item first name to be that of the current user
@ViewBag.current_user = item["first_name"];
}
//render the template
return(View["users.sshtml"]);
});
Post("/logout", args =>
{
//deletes the user from session and redirects back to index
Session.DeleteAll();
return(Response.AsRedirect("/"));
});
}
public LRModule()
{
Get("/", args =>
{
//------displays login and reg forms-----//
return(View["index.sshtml"]);
});
Post("/register", args =>
{
//-------storing user input-------//
string first_name = Request.Form["first_name"];
string last_name = Request.Form["last_name"];
string email = Request.Form["email"];
string password = Request.Form["password"];
string confirm = Request.Form["confirm"];
//------validating user input------//
if (first_name.Length == 0)
{
@ViewBag.first_name = true;
}
if (last_name.Length == 0)
{
@ViewBag.last_name = true;
}
if (email.Length == 0)
{
@ViewBag.email = true;
}
if (password.Length < 8)
{
@ViewBag.password = true;
}
if (confirm.Length == 0)
{
@ViewBag.confirm = true;
}
else if (confirm != password)
{
@ViewBag.match = true;
}
//-------if user input passes all validations-------//
if (first_name.Length > 0 && last_name.Length > 0 && email.Length > 0 && password.Length > 7 && password == confirm)
{
//---------store the input in the database----------//
string hash = Crypto.HashPassword(password);
string query = $"INSERT INTO users (first_name, last_name, email, hash, created_at) VALUES('{first_name}', '{last_name}', '{email}', '{hash}', NOW())";
DbConnector.ExecuteQuery(query);
//--------query the data again in descending order so the newest user is the first result-------//
query = "SELECT * FROM users ORDER BY id DESC LIMIT 1";
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
//---set an object to be == first user that was returned from the query (should be the only user that was returned)---//
Dictionary <string, object> new_user = user[0];
//--------store the user's unique id in session--------//
Session["current_user"] = (int)new_user["id"];
Console.WriteLine(Session["current_user"]);
//-------redirect the new user to the success page-------//
return(Response.AsRedirect("/users"));
}
else
{
//-------if there are errors, redirect the user to "/"-------//
return(View["index.sshtml"]);
}
});
Post("/login", args =>
{
string email = Request.Form["email"];
string password = Request.Form["password"];
//------query the db to find the user that matches the email that the user inputs-----//
string query = $"SELECT * FROM users WHERE email = '{email}' LIMIT 1";
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
//-------if no user was returned from the query, redirect to "/" with errors-------//
if (user.Count == 0)
{
@ViewBag.noUser = true;
return(View["index.sshtml"]);
}
//-------if no password was input, redirect to "/" with errors-------//
if (password.Length == 0)
{
@ViewBag.noPass = true;
return(View["index.sshtml"]);
}
else
{
//---set an object to be == first user that was returned from the query (should be the only user that was returned)---//
Dictionary <string, object> match_user = user[0];
//------verify that the password given matches the hashed version of the password associated with the found user------//
bool match = Crypto.VerifyHashedPassword((string)match_user["hash"], password);
//------if the passwords match------//
if (match)
{
//------store the user's unique id in session & redirect to success page------//
Session["current_user"] = (int)match_user["id"];
return(Response.AsRedirect("/users"));
}
//------if the passwords do not match, redirect to "/" with errors------//
else
{
@ViewBag.wrongPass = true;
return(View["index.sshtml"]);
}
}
});
Get("/users", args =>
{
//---------displaying all users in the db----------//
//------------create an empty view bag-------------//
@ViewBag.users = "";
//--------set an empty list to be == all users returned from querying the db--------//
List <Dictionary <string, object> > results = DbConnector.ExecuteQuery("SELECT * FROM users");
//---------reverse the list so that we can display the newest user on top-----------//
results.Reverse();
//------loop through the list of users and append each of their table data to the empty view bag------//
foreach (Dictionary <string, object> item in results)
{
@ViewBag.users += "<p>" + "<b>" + item["first_name"] + " " + item["last_name"] + "</b>" + " " + "registered at " + item["created_at"] + "</p>" + "<hr>";
}
//-----query the db to find the first name whose id matches the current user's id who is in session-----//
string query = $"SELECT first_name FROM users WHERE id = {Session["current_user"]} LIMIT 1";
//-----set an empty list to be == the user that is returned from the above query-----//
List <Dictionary <string, object> > user = DbConnector.ExecuteQuery(query);
//-----loop through the list(will only loop once because the list only contains one user)-----//
foreach (Dictionary <string, object> item in user)
{
//-----set a view bag == the first name of the user-----//
//-----feel free to set more view bags for the rest of the user's data if you want to display it in the views-----//
@ViewBag.current_user = item["first_name"];
}
//-----now that we have all the user data we need to display in our views, we can render the users template-----//
return(View["users.sshtml"]);
});
Post("/logout", args =>
{
//-----this route will delete all current sesssions and redirect to "/"-----//
Session.DeleteAll();
return(Response.AsRedirect("/"));
});
}
