public void uTable() { connect = new SqlConnection("Data Source=HP;Integrated Security=SSPI;Initial Catalog=Usersf"); Console.Clear(); while (true) { TRepository.Table(); Menu choice = RenderMenu(); switch (choice) { case Menu.Insert: { Add(); break; } case Menu.Update: { Update(); break; } case Menu.Delete: { Delete(); break; } case Menu.Exit: { return; } } } }
public static void Add() { Console.Clear(); TRepository.Table(); Console.WriteLine(" Add User: "******""); Console.Write(" ID : "); string ID = Console.ReadLine(); int value; while (!int.TryParse(ID, out value)) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(" Invalid ID."); Console.ForegroundColor = ConsoleColor.White; Console.ReadKey(true); Console.Clear(); return; } ; Console.Write(" Name: "); string username = Convert.ToString(Console.ReadLine()); Console.Write(" Password: "******" Email: "); string email = Convert.ToString(Console.ReadLine()); SqlCommand command = new SqlCommand("Select * From Users where ID = @id", connect); SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@id"; parameter.Value = ID.ToString(); command.Parameters.Add(parameter); connect.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(" ID already exists."); Console.ForegroundColor = ConsoleColor.White; Console.ReadKey(true); Console.Clear(); reader.Close(); connect.Close(); return; } else { try { SqlCommand aCommand = new SqlCommand("INSERT INTO [Users] ([ID], [Username], [Password], [Email]) VALUES('" + ID + "','" + username + "','" + password + "','" + email + "')", connect); aCommand.ExecuteNonQuery(); connect.Close(); } catch (SqlException e) { Console.WriteLine("Error: {0}", e.Errors[0].Message); connect.Close(); } Console.Clear(); Console.WriteLine(""); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine(" User with ID {0} was successfully added to UserTable", ID); Console.ForegroundColor = ConsoleColor.White; Console.WriteLine(""); return; } }
public static void Delete() { Console.Clear(); TRepository.Table(); Console.WriteLine(" Delete User"); Console.WriteLine(":________________:"); Console.WriteLine(""); Console.Write(" Choose ID to be deleted: "); int ID = Convert.ToInt32(Console.ReadLine()); SqlCommand command = new SqlCommand("Select * From Users where ID = @id", connect); SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@id"; parameter.Value = ID.ToString(); command.Parameters.Add(parameter); connect.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(" You are about to delete user with ID = {0}", ID); Console.ForegroundColor = ConsoleColor.White; Console.WriteLine(" [Y]es [N]o"); string key = Console.ReadLine(); if (key == "y" || key == "Y") { try { SqlCommand aCommand = new SqlCommand("Delete from users where id = " + ID, connect); aCommand.ExecuteNonQuery(); reader.Close(); connect.Close(); } catch (SqlException e) { Console.WriteLine("Error: {0}", e.Errors[0].Message); reader.Close(); connect.Close(); } Console.Clear(); Console.WriteLine(""); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine(" User with ID {0} was successfully deleted from UserTable", ID); Console.ForegroundColor = ConsoleColor.White; Console.WriteLine(""); return; } else { Console.Clear(); reader.Close(); connect.Close(); return; } } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(" ID does not exist."); Console.ForegroundColor = ConsoleColor.White; Console.ReadKey(true); Console.Clear(); reader.Close(); connect.Close(); return; } }
public static void Update() { Console.Clear(); TRepository.Table(); Console.WriteLine(" Update User"); Console.WriteLine(":________________:"); Console.WriteLine(""); Console.Write(" Choose ID to be updated: "); int ID = Convert.ToInt32(Console.ReadLine()); Console.Write(" Name: "); string username = Convert.ToString(Console.ReadLine()); Console.Write(" Password: "******" Email: "); string email = Convert.ToString(Console.ReadLine()); SqlCommand command = new SqlCommand("Select * From Users where ID = @id", connect); SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@id"; parameter.Value = ID.ToString(); command.Parameters.Add(parameter); connect.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { try { SqlCommand aCommand = new SqlCommand("Update [Users] SET [Username]='" + username + "',[Password]='" + password + "',[email]='" + email + "'where [id]=" + ID, connect); aCommand.ExecuteNonQuery(); connect.Close(); } catch (SqlException e) { Console.WriteLine("Error: {0}", e.Errors[0].Message); connect.Close(); } Console.Clear(); Console.WriteLine(""); Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine(" User with ID {0} was successfully updated in UserTable", ID); Console.ForegroundColor = ConsoleColor.White; Console.WriteLine(""); return; } else { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(" ID does not exist."); Console.ForegroundColor = ConsoleColor.White; Console.ReadKey(true); Console.Clear(); reader.Close(); connect.Close(); return; } }