/*Crear un nuevo usuario*/ public void CreateUser(string name, string last_name, string addr, string email, string phone, string gender) { try { myCommand = new SqlCommand("SP_CreateUser", MyConnection.GetConnection()); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = name; myCommand.Parameters.Add("@last_name", SqlDbType.VarChar).Value = last_name; myCommand.Parameters.Add("@addr", SqlDbType.VarChar).Value = addr; myCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = email; myCommand.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone; myCommand.Parameters.Add("@gender", SqlDbType.Char).Value = gender; myCommand.ExecuteNonQuery(); if (myCommand.Connection.State == ConnectionState.Open) { myCommand.Connection.Close(); } MessageBox.Show("El Usuario: " + UpperFirstLetter(name) + " " + UpperFirstLetter(last_name) + " ha sido creado satisfactoriamente", "Nuevo usuario creado", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
/*Busca los usuarios*/ public void FindUser(DataGridView grid, string name) { myDataTable = new DataTable(); myCommand = new SqlCommand("SP_SearchUser", MyConnection.GetConnection()); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = name; mySqlAdapter = new SqlDataAdapter(myCommand); mySqlAdapter.Fill(myDataTable); if (myCommand.Connection.State == ConnectionState.Open) { myCommand.Connection.Close(); } if (myDataTable.Rows.Count == 0) { MessageBox.Show("Lo sentimos el usuario no ha sido encontrado", "Usuario no encontrado", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { for (int i = 0; i < myDataTable.Rows.Count; i++) { myDataTable.Rows[i]["Nombre"] = UpperFirstLetter(myDataTable.Rows[i]["Nombre"].ToString()); myDataTable.Rows[i]["Apellido"] = UpperFirstLetter(myDataTable.Rows[i]["Apellido"].ToString()); } grid.DataSource = myDataTable; } }
/*Actualizar informacion de usuario*/ public void UpdateUser(string name, string last_name, string addr, string email, string phone, string gender, int id) { try { myCommand = new SqlCommand("SP_UpdateUser", MyConnection.GetConnection()); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = name; myCommand.Parameters.Add("@last_name", SqlDbType.VarChar).Value = last_name; myCommand.Parameters.Add("@addr", SqlDbType.VarChar).Value = addr; myCommand.Parameters.Add("@email", SqlDbType.VarChar).Value = email; myCommand.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone; myCommand.Parameters.Add("@gender", SqlDbType.VarChar).Value = gender; myCommand.Parameters.Add("@id", SqlDbType.Int).Value = id; myCommand.ExecuteNonQuery(); if (myCommand.Connection.State == ConnectionState.Open) { myCommand.Connection.Close(); } MessageBox.Show("La información del usuario ha sido actualizado satisfactoriamente", "Actualizado", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
/*Seleccionar usuario por id*/ public DialogResult SelectUserById(int id) { string name = null, last_name = null, addr = null, email = null, phone = null, gender = null; try { myCommand = new SqlCommand("SP_ListUsersById", MyConnection.GetConnection()); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@id", SqlDbType.Int).Value = id; myDataReader = myCommand.ExecuteReader(); while (myDataReader.Read()) { name = UpperFirstLetter(myDataReader["Nombre"].ToString()); last_name = UpperFirstLetter(myDataReader["Apellido"].ToString()); addr = myDataReader["Dirección"].ToString(); email = myDataReader["Email"].ToString(); phone = myDataReader["Teléfono"].ToString(); gender = myDataReader["Género"].ToString(); } UserClass user = new UserClass(name, last_name, addr, email, phone, gender, id); FormEditOrDeleteUser myFormEditOrDeleteUser = new FormEditOrDeleteUser(user); DialogResult result = myFormEditOrDeleteUser.ShowDialog(); return(result); } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(DialogResult.Cancel); } }
/*Muestra los registros de la tabla en un gridview*/ public void DisplayUserList(DataGridView grid) { myDataTable = new DataTable(); myCommand = new SqlCommand("SP_ListUsers", MyConnection.GetConnection()); mySqlAdapter = new SqlDataAdapter(myCommand); mySqlAdapter.Fill(myDataTable); if (myCommand.Connection.State == ConnectionState.Open) { myCommand.Connection.Close(); } for (int i = 0; i < myDataTable.Rows.Count; i++) { myDataTable.Rows[i]["Nombre"] = UpperFirstLetter(myDataTable.Rows[i]["Nombre"].ToString()); myDataTable.Rows[i]["Apellido"] = UpperFirstLetter(myDataTable.Rows[i]["Apellido"].ToString()); } grid.DataSource = myDataTable; }
/*Eliminar usuario*/ public void DeleteUser(int id) { try { myCommand = new SqlCommand("SP_DeleteUser", MyConnection.GetConnection()); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add("@id", SqlDbType.Int).Value = id; myCommand.ExecuteNonQuery(); if (myCommand.Connection.State == ConnectionState.Open) { myCommand.Connection.Close(); } MessageBox.Show("El usuario ha sido eliminado satisfactoriamente", "Eliminado", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }