public void Commit(String name = "", String surname = "", String email = "", String date = "")
{
String query = "USE DB_Test;SELECT * FROM dbo.users WHERE ";
if (!name.Equals(""))
{
query = query + "name = '" + name + "' AND ";
}
if (!surname.Equals(""))
{
query = query + "surname = '" + surname + "' AND ";
}
if (!email.Equals(""))
{
query = query + "email = '" + email + "' AND ";
}
if (!date.Equals(""))
{
query = query + "date = '" + date + "' AND";
}
query = query.Remove(query.Length - 4);
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlCommand command = new SqlCommand(query, con);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine(String.Format("{0}", reader["name"])); //name
Console.WriteLine(String.Format("{0}", reader["surname"])); //surname
Console.WriteLine(String.Format("{0}", reader["email"])); //email
Console.WriteLine(String.Format("{0}", reader["pwd"])); //pwd
Console.WriteLine(String.Format("{0}", reader["sex"])); //sex
Console.WriteLine(String.Format("{0}", reader["born"])); //date
}
}
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine("Errore!");
}
}
con.Close();
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void Delete()
{
String query = null;
//Console.Clear();
Console.WriteLine("--- Deleting user ---");
Console.WriteLine("delete from:\n1)ID\n2)Name");
String choice = Console.ReadLine();
if (choice.Equals('1'))
{
query = "USE DB_Test;DELETE FROM dbo.users WHERE id = " + Int32.Parse(choice);
}
else
{
Console.WriteLine("Name: ");
String name = Console.ReadLine();
Console.WriteLine("Surname: ");
String surname = Console.ReadLine();
query = "USE DB_Test;DELETE FROM dbo.users WHERE name = '" + name + "' AND surname = '" + surname + "'";
}
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlDataAdapter adapter = newConn.funA();
adapter.InsertCommand = new SqlCommand(query, con);
adapter.InsertCommand.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine("Errore!");
}
}
Console.WriteLine("User and his posts deleted!");
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void getNotify()
{
String query = null;
Console.Clear();
Console.WriteLine("--- Get activity for user ---");
Console.WriteLine("Select user: \nName: ");
String name = Console.ReadLine();
Console.WriteLine("Surname: ");
String surname = Console.ReadLine();
query = "USE DB_Test;SELECT body, type FROM dbo.notification as n JOIN dbo.users as u ON n.id_utente = u.id WHERE u.name = '" + name + "' AND u.surname = '" + surname + "'";
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlCommand command = new SqlCommand(query, con);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(String.Format("{0} -> {1}", reader["type"], reader["body"]));
}
con.Close();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine(ex);
}
}
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void dPost(String postId)
{
String query = null;
//Console.Clear();
Console.WriteLine("--- Deleting post ---");
query = "USE DB_Test;DELETE FROM dbo.posts WHERE id = " + Int32.Parse(postId);
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlDataAdapter adapter = newConn.funA();
adapter.InsertCommand = new SqlCommand(query, con);
adapter.InsertCommand.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine("Errore!");
}
}
Console.WriteLine("Post deleted!");
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void Seeding()
{
int num, i;
String res = null;
Conn newConn = new Conn();
Console.WriteLine("Inserition:\n");
Console.WriteLine("Quanti ne vuoi inserire? ");
String n = Console.ReadLine();
Int32.TryParse(n, out num);
String q = "USE DB_Test;SELECT COUNT(*) as count FROM dbo.users";
SqlConnection con = newConn.fun();
con.Open();
SqlCommand command = new SqlCommand(q, con);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
res = String.Format("{0}", reader["count"]);
}
}
for (i = Int32.Parse(res); i < num + Int32.Parse(res); i++)
{
Console.WriteLine("Name: ");
String name = Console.ReadLine();
Console.WriteLine("Surame: ");
String surname = Console.ReadLine();
Console.WriteLine("Email: ");
String email = Console.ReadLine();
Console.WriteLine("Password: "******"Sesso: ");
String sex = Console.ReadLine();
Console.WriteLine("Data di nascita: ");
String born = Console.ReadLine();
String query = "USE DB_Test;INSERT INTO dbo.users (id,name,surname,email,pwd,sex,born) VALUES ("
+ i + ",'"
+ name + "','"
+ surname + "','"
+ email + "','"
+ pwd + "','"
+ sex + "','"
+ born + "')";
try
{
SqlDataAdapter adapter = newConn.funA();
adapter.InsertCommand = new SqlCommand(query, con);
adapter.InsertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine("Errore!");
}
}
con.Close();
}
}
public void getMessage()
{
String query1 = null;
String query2 = null;
String query_final = null;
String id1 = null;
String id2 = null;
//Console.Clear();
Console.WriteLine("--- Messages between users ---");
Console.WriteLine("Name user 1: ");
String n_usr1 = Console.ReadLine();
Console.WriteLine("Surname user 1: ");
String s_usr1 = Console.ReadLine();
query1 = "USE DB_Test;SELECT id FROM dbo.users WHERE name = '" + n_usr1 + "' AND surname = '" + s_usr1 + "'";
Console.WriteLine("Name user 2: ");
String n_usr2 = Console.ReadLine();
Console.WriteLine("Surname user 2: ");
String s_usr2 = Console.ReadLine();
query2 = "USE DB_Test;SELECT id FROM dbo.users WHERE name = '" + n_usr2 + "' AND surname = '" + s_usr2 + "'";
try
{
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
SqlCommand command1 = new SqlCommand(query1, con);
SqlDataReader reader1 = command1.ExecuteReader();
if (reader1.Read())
{
id1 = (String.Format("{0}", reader1["id"]));
}
reader1.Close();
SqlCommand command2 = new SqlCommand(query2, con);
SqlDataReader reader2 = command2.ExecuteReader();
if (reader2.Read())
{
id2 = (String.Format("{0}", reader2["id"]));
}
reader2.Close();
query_final = "USE DB_Test;SELECT body FROM dbo.messages WHERE id_u1 = " + Int32.Parse(id1) + " AND id_u2 = " + Int32.Parse(id2)
+ "OR id_u1 = " + Int32.Parse(id2) + " AND id_u2 = " + Int32.Parse(id1);
SqlCommand command = new SqlCommand(query_final, con);
SqlDataReader reader = command.ExecuteReader();
Console.WriteLine("Messages ----- ");
while (reader.Read())
{
Console.WriteLine(String.Format("-> {0}", reader["body"])); //body message
}
con.Close();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine(ex);
}
}
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void UserPosts()
{
String query = null;
String[] idPost = new String[50];
Console.WriteLine("Nome: ");
String name = Console.ReadLine();
Console.WriteLine("Procedo?[Yes/no]");
String check = Console.ReadLine();
if (check.Equals("Y") || check.Equals("y"))
{
query = "USE DB_Test;SELECT p.id as id, p.body as bp FROM dbo.posts as p JOIN dbo.users as u ON p.id_user = u.id WHERE u.name LIKE '" + name + "'";
}
else if (check.Equals("N") || check.Equals("n"))
{
Console.WriteLine("Cognome: ");
String surname = Console.ReadLine();
query = "USE DB_Test;SELECT p.id as id, p.body as bp FROM dbo.posts as p JOIN dbo.users as u ON p.id_user = u.id JOIN WHERE u.name LIKE '" + name + "' AND u.surname LIKE '" + surname + "'";
}
else
{
Console.WriteLine("Compila un campo!");
Console.Clear();
UserPosts();
}
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlCommand command = new SqlCommand(query, con);
SqlDataReader reader = command.ExecuteReader();
Console.WriteLine("[posts]");
int i = 0;
while (reader.Read())
{
Console.WriteLine(String.Format("{1}: {0}", reader["bp"], reader["id"])); //body post
idPost[i] = String.Format("{0}", reader["id"]);
i++;
}
reader.Close();
Console.WriteLine("\n[comments]");
foreach (String j in idPost)
{
if (j == null)
{
break;
}
String com_quer = "USE DB_Test;SELECT body, ip_post FROM dbo.comments WHERE ip_post = " + Int32.Parse(j);
SqlCommand command2 = new SqlCommand(com_quer, con);
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
Console.WriteLine(String.Format("{1}: {0}", reader2["body"], reader2["ip_post"])); //body comment
}
reader2.Close();
}
Console.WriteLine("[D] for delete: ");
String d = Console.ReadLine();
if (d.Equals("d") || d.Equals("D"))
{
Console.WriteLine("Select post to delete white id: ");
String p = Console.ReadLine();
tbd_project.DeletePost dp = new DeletePost();
dp.dPost(p);
}
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine(ex);
}
}
con.Close();
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
public void updatePC()
{
String query = null;
Console.Clear();
Console.WriteLine("--- Moderate posts or comments ---");
Console.WriteLine("Select what you want to moderate: \n1)Post\n2)Comment");
String choice = Console.ReadLine();
if (choice.Equals("1"))
{
Console.WriteLine("Id post: ");
String id = Console.ReadLine();
Console.WriteLine("New text: ");
String text = Console.ReadLine();
query = "USE DB_Test;UPDATE posts SET body = '" + text + "' WHERE id = " + Int32.Parse(id);
}
else if (choice.Equals("2"))
{
Console.WriteLine("Id comment: ");
String id = Console.ReadLine();
Console.WriteLine("New text: ");
String text = Console.ReadLine();
query = "USE DB_Test;UPDATE comments SET body = '" + text + "' WHERE id = " + Int32.Parse(id);
}
Conn newConn = new Conn();
SqlConnection con = newConn.fun();
con.Open();
try
{
SqlCommand command = con.CreateCommand();
command.CommandText = query;
int rows = command.ExecuteNonQuery();
con.Close();
if (rows > 0)
{
Console.WriteLine("Udated! " + rows.ToString() + " rows updated!");
}
else
{
Console.WriteLine("Ops! " + rows.ToString() + " rows updated!");
}
Console.WriteLine("Press [Enter] to continue: ");
String enter = Console.ReadLine();
}
catch (Exception ex)
{
if (ex is SqlException)
{
Console.WriteLine(ex);
}
else
{
Console.WriteLine("Errore!");
}
}
}