private string getDegreeID(string DegreeName)
{
Query = "select degree_id from Degree where degree_name='" + DegreeName + "'";
degree_id = DB_Connection.ExecuteScalarQueries(Query);
return(degree_id);
}
private string getDepartmentID(string DepartmentName)
{
Query = "select department_id from Department where department_name='" + DepartmentName + "'";
value = DB_Connection.ExecuteScalarQueries(Query);
return(value);
}
private void degreeComoboxData(ComboBox comboBox)
{
try
{
DB_Connection.OpenConection();
Query = "select degree_name from StudentRegisration.dbo.Degree";
//SqlDataReader sqlDataReader = DB_Connection.DataReader(Query);
SqlDataAdapter sqlDataAdapter = DB_Connection.DataAdapter(Query);
DataTable degreeDataTable = new DataTable();
sqlDataAdapter.Fill(degreeDataTable);
DB_Connection.CloseConnection();
for (int i = 0; i < degreeDataTable.Rows.Count; i++)
{
comboBox.Items.Add(degreeDataTable.Rows[i]["degree_name"]);
}
comboBox_Degree.SelectedIndex = 0;
}
catch (SqlException)
{
MessageBox.Show("Conneciton is not Allowed.");
}
}
private void Button_StudentSearch_Click(object sender, EventArgs e)
{
if (Constants.Activity_Check == "UpdateStudent")
{
Query = "Select * from StudentInformation where student_id='" + textBox_SearchStudent.Text + "' OR student_FName = '" + textBox_SearchStudent.Text + "' ";
}
else if (Constants.Activity_Check == "UpdateInstallments" || Constants.Activity_Check == "PrintChallan")
{
Query = "Select * from InstallmentInformation where student_id='" + textBox_SearchStudent.Text + "' ";
}
else if (Constants.Activity_Check == "DeleteStudent")
{
Query = "Select * from StudentInformation where student_id='" + textBox_SearchStudent.Text + "' OR student_FName = '" + textBox_SearchStudent.Text + "' ";
}
try
{
DB_Connection.OpenConection();
sqlDataAdapter = DB_Connection.DataAdapter(Query);
dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
dataGridView1.DataSource = bindingSource;
}
catch (SqlException)
{
MessageBox.Show("Connection Problem.");
}
}
private string getFee()
{
DB_Connection.OpenConection();
Query = "select fee from Fee where department_id='" + department_id + "' AND degree_id='" + degree_id + "' ";
value = DB_Connection.ExecuteScalarQueries(Query);
return(value);
}
public int Get(string team_id, string USID)
{
if (!GF.session_check())
{
return(5);
}
if (!GF.Check_if_Admin(team_id))
{
return(0);
}
if (USID.Equals(GF.get_usid()))
{
return(2);
}
paralist = new List <SqlParameter> {
new SqlParameter("@usid", USID),
new SqlParameter("@team_id", team_id)
};
try
{
DB_Connection.sql_insert_update(query_leave_team, paralist);
return(1);
}
catch
{
return(4);
}
}
public Get_TeamMembers_Result Get(string Team_ID)
{
if (!GF.session_check())
{
return(new Get_TeamMembers_Result(5));
}
if (!GF.Check_if_Admin(Team_ID))
{
return(new Get_TeamMembers_Result(0));
}
usid = GF.get_usid();
paralist = new List <SqlParameter>
{
new SqlParameter("@team_id", Team_ID),
new SqlParameter("@usid2", usid)
};
if (!DB_Connection.O_reader.IsClosed)
{
DB_Connection.O_reader.Close();
}
DB_Connection.O_reader = DB_Connection.sql_reader(query, paralist);
if (!DB_Connection.O_reader.HasRows)
{
DB_Connection.O_reader.Close();
return(new Get_TeamMembers_Result(new List <Team_Member>()));
}
members = new List <Team_Member>();
while (DB_Connection.O_reader.Read())
{
members.Add(new Team_Member(DB_Connection.O_reader.GetString(0), DB_Connection.O_reader.GetString(1) + " " + DB_Connection.O_reader.GetString(2), DB_Connection.O_reader.GetString(3)));
}
DB_Connection.O_reader.Close();
return(new Get_TeamMembers_Result(members));
}
private List <Announcment> Get_Ann(string Team_ID)
{
AnnTeams = new List <Announcment>();
paralist = new List <SqlParameter>
{
new SqlParameter("@team_id", Team_ID),
new SqlParameter("@usid", User_Id)
};
try
{
if (!DB_Connection.O_reader.IsClosed)
{
DB_Connection.O_reader.Close();
}
DB_Connection.O_reader = DB_Connection.sql_reader(query, paralist);
}
catch
{
DB_Connection.O_reader.Close();
return(new List <Announcment>());
}
if (DB_Connection.O_reader.HasRows)
{
while (DB_Connection.O_reader.Read())
{
AnnTeams.Add(new Announcment(DB_Connection.O_reader.GetString(3), DB_Connection.O_reader.GetString(0), DB_Connection.O_reader.GetString(1), DB_Connection.O_reader.GetDateTime(2)));
}
DB_Connection.O_reader.Close();
return(AnnTeams);
}
DB_Connection.O_reader.Close();
return(new List <Announcment>());
}
private void textBoxBD_TextChanged(object sender, EventArgs ev, DB_Manager dB_Manager, string dB_Name)
{
// llena el cuadro de tablas con las tablas de una bd
SqlConnection conexion = DB_Connection.DB_Connexion_Open(dB_Name);
try
{
// recogemos los nombres de las tablas en un objeto
DataTable dt = conexion.GetSchema("Tables");
// eliminamos el contenido de la lista
dB_Manager.listBoxTabla.Items.Clear();
// por cada nombre en el objeto
foreach (DataRow row in dt.Rows)
{
// printamos
dB_Manager.listBoxTabla.Items.Add((string)row[2]);
}
}
catch (Exception e)
{
MessageBox.Show(Convert.ToString(e));
}
finally
{
conexion.Close();
}
}
private List <Day> GetDays(string Report_ID)
{
paralist = new List <SqlParameter>
{
new SqlParameter("@r_id", Report_ID)
};
try
{
if (!DB_Connection.O_reader.IsClosed)
{
DB_Connection.O_reader.Close();
}
DB_Connection.O_reader = DB_Connection.sql_reader(get_content, paralist);
}
catch { }
List <Day> g = new List <Day>();
if (DB_Connection.O_reader.HasRows)
{
while (DB_Connection.O_reader.Read())
{
g.Add(new Day(DB_Connection.O_reader.GetDateTime(1), DB_Connection.O_reader.GetFieldValue <int>(0)));
}
DB_Connection.O_reader.Close();
return(g);
}
DB_Connection.O_reader.Close();
return(g);
}
private void GET_REPORTS( )
{
paralist = new List <SqlParameter>
{
new SqlParameter("@usid", USID),
new SqlParameter("@team_id", Team_ID),
new SqlParameter("@usid2", usid)
};
try
{
if (!DB_Connection.O_reader.IsClosed)
{
DB_Connection.O_reader.Close();
}
DB_Connection.O_reader = DB_Connection.sql_reader(get_Reports, paralist);
}catch (SqlException e)
{
}
if (DB_Connection.O_reader.HasRows)
{
while (DB_Connection.O_reader.Read())
{
Reports_list.Add(new Report(DB_Connection.O_reader.GetString(0), DB_Connection.O_reader.GetString(4), DB_Connection.O_reader.GetString(3), DB_Connection.O_reader.GetFieldValue <int>(2), DB_Connection.O_reader.GetDateTime(1)));
}
DB_Connection.O_reader.Close();
foreach (Report x in Reports_list)
{
x.Content = GetDays(x.Report_ID);
}
}
}
public int Get(string team_id)
{
if (HttpContext.Current.Session["USID"] == null || HttpContext.Current.Session["USID"].ToString().Equals(""))
{
return(5);
}
usid = HttpContext.Current.Session["USID"].ToString();
paralist = new List <SqlParameter> {
new SqlParameter("@usid", usid),
new SqlParameter("@team_id", team_id)
};
try
{
DB_Connection.sql_insert_update(query_leave_team, paralist);
return(1);
}
catch
{
return(4);
}
}
public override bool CheckRepeatedTest()
{
try
{
if (DB_Connection != null && DB_Connection.State == ConnectionState.Open)
{
MySqlCommand command = DB_Connection.CreateCommand();
command.CommandText = "SELECT * FROM " + SqlTable + " WHERE sn=" + SerialNumber + " AND test_flag=1;";
MySqlDataReader reader = command.ExecuteReader();
if (reader.Read() && reader["sn"].ToString().Contains(SerialNumber))
{
DB_Connection.Close();
return(true);
}
DB_Connection.Close();
return(false);
}
else
{
return(false);
}
}
catch (Exception e)
{
Debug.WriteLine("Exception: " + e.Message);
return(false);
}
}
public returned Get(string Team_ID)
{
if (!GF.session_check())
{
return(new returned(5));
}
if (!GF.Check_if_Joined(Team_ID))
{
return(new returned(4));
}
paralist = new List <SqlParameter>
{
new SqlParameter("@teamid", Team_ID)
};
DB_Connection.O_reader = DB_Connection.sql_reader(Admin_NameQ, paralist);
if (DB_Connection.O_reader == null)
{
return(new returned(404));
}
DB_Connection.O_reader.Read();
String A_NM = DB_Connection.O_reader.GetString(0) + " " + DB_Connection.O_reader.GetString(1);
DB_Connection.O_reader.Close();
return(new returned(A_NM));
}
public string Get(string Team_ID)
{
if (!GF.session_check())
{
return("Error:5");
}
if (!GF.Check_if_Admin(Team_ID))
{
return("Error:4");
}
paralist = new List <SqlParameter>
{
new SqlParameter("@teamid", Team_ID)
};
if (!DB_Connection.O_reader.IsClosed)
{
DB_Connection.O_reader.Close();
}
DB_Connection.O_reader = DB_Connection.sql_reader(pass_query, paralist);
if (DB_Connection.O_reader == null)
{
return("Error:404");
}
if (DB_Connection.O_reader.HasRows)
{
DB_Connection.O_reader.Read();
string s = DB_Connection.O_reader.GetString(0);
return(s);
}
else
{
return("Error:0");
}
}
//checks for database connection
//logs request to database once the token has been verified
//and the request has gone to an endpoint
public async Task InvokeAsync(HttpContext context)
{
//check if DB is connected and try to connect if it isnt
if (DB_Connection.conn == null || DB_Connection.conn.State != ConnectionState.Open)
{
DB_Connection.Connect();
}
//check for db connection
if (DB_Connection.conn == null)
{
context.Response.StatusCode = 400;
await context.Response.Body.WriteAsync(Encoding.ASCII.GetBytes("No database connection"));
return;
}
await _next(context); //wait for request to finish and come back with log information
//log the results of this request
var log = (TransactionLog)context.Items["Log"];
log.RequestType = context.Request.Method;
log.Origin = context.Request.Host.Value;
log.LogToDatabase();
}
// AGREGAR CATEGORÍA
public void AddSubCategory(string description, int idCategory)
{
var data = new DB_Connection();
try
{
if (ValidateSubCategory(description) == false) // si no existe, agregamos!
{
data.setStoreProcedure("SP_InsertSubCategory");
data.addParameters("@description", description);
data.addParameters("@idCategory", idCategory);
data.executeAction();
}
else
{
data.closeConnection();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
public string AuthUser(IFormCollection value)
{
var email = value["signInEmail"];
var password = value["signInPassword"];
var db = new DB_Connection();
try
{
db.OpenConnection();
string query = $"SELECT * FROM users WHERE email = '{email}' AND password = '******'";
MySqlCommand cmd = new MySqlCommand(query, db.Connection);
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
if (dataReader.HasRows)
{
while (dataReader.Read())
{
var user = new UserModel(dataReader);
dataReader.Close();
db.CloseConnection();
return(user.ToString());
}
}
return("The User email or pass not exist or doesn't match");
}
catch (MySqlException)
{
return("The User email or pass not exist or doesn't match");
}
}
// VALIDACIONES //
public bool ValidateSubCategory(string description)
{
var data = new DB_Connection();
try
{
string consulta = "SELECT COUNT(*) FROM SUBCATEGORIES WHERE Description = @desc";
data.connection.Open();
SqlCommand cmd = new SqlCommand(consulta, data.connection);
cmd.Parameters.AddWithValue("@desc", description);
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count == 0)
{
return(false); // si count es igual 0, es porque no existe un registro con la misma descripción y devuelve un false;
}
else
{
return(true);
} //caso contrario, sí existe y devuelve un true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
//LISTAR CATEGORÍAS
public List <SubCategory> GetSubCategories(bool?state = true)
{
SubCategory aux;
var lista = new List <SubCategory>();
var data = new DB_Connection();
try
{
data.setQuery("SELECT Id, Description, Status FROM SUBCATEGORIES" + (state ?? false ? " WHERE Status = 1" : "") + " ORDER BY Description");
data.executeReader();
while (data.reader.Read())
{
aux = new SubCategory(Convert.ToInt32(data.reader["Id"]),
Convert.ToString(data.reader["Description"]),
Convert.ToBoolean(data.reader["Status"]));
lista.Add(aux);
}
return(lista);
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
private void BtnDesLike_Click(object sender, RoutedEventArgs e)
{
Icon1.Kind = MaterialDesignThemes.Wpf.PackIconKind.LikeOutline;
Icon2.Kind = MaterialDesignThemes.Wpf.PackIconKind.Dislike;
Comentarios comentario = new Comentarios();
comentario.comentario = txtComentarios.Text;
comentario.id_imagem = _window.idImg;
int?cont = 0;
using (DB_Connection con = new DB_Connection())
{
cont = con.Imagens.Where(x => x.id == _window.idImg).Max(x => x.like_negativo);
if (cont == null)
{
cont = 0;
}
}
Imagens imagem = new Imagens();
imagem.like_negativo = cont + 1;
imagem.id = _window.idImg;
_service.UpdateTable(comentario);
ExecuteUpdate(cont + 1, imagem.id, "like_negativo");
ClearField();
EnableFields();
btnLike.IsEnabled = false;
}
// AGREGAR PAGO
public void AddPayment(string description) /*(Persona persona)*/
{
var data = new DB_Connection();
try
{
if (ValidatePayment(description) == false) // si no existe, agregamos!
{
data.setStoreProcedure("SP_InsertPayment");
data.addParameters("@description", description);
data.executeAction();
}
else
{
data.closeConnection();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
public int Get(string team_id)
{
if (!GF.session_check())
{
return(5);
}
usid = GF.get_usid();
if (!GF.Check_if_Admin(team_id))
{
return(0);
}
paralist = new List <SqlParameter> {
new SqlParameter("@team_id", team_id)
};
try
{
DB_Connection.sql_insert_update(query_leave_team, paralist);
}
catch
{
return(44);
}
return(1);
}
private void ExecuteUpdate(int?valor, int id, string field)
{
using (DB_Connection con = new DB_Connection())
{
con.Database.ExecuteSqlCommand($"UPDATE Imagens SET {field} = {valor} WHERE id = {id}");
}
}
public int Get(string USID, string Team_ID, string New_Title)
{
if (!GF.session_check())
{
return(5);
}
this.USID = GF.get_usid();
if (!GF.Check_if_Admin(Team_ID))
{
return(4);
}
paralist = new List <SqlParameter>
{
new SqlParameter("@ntitle", New_Title),
new SqlParameter("@usid", USID),
new SqlParameter("@teamid", Team_ID)
};
try
{
DB_Connection.sql_insert_update(query, paralist);
}
catch
{
return(0);
}
return(1);
}
public List <User> listarPersonas()
{
List <User> lista = new List <User>();
//User aux;
DB_Connection data = new DB_Connection();
try
{
data.setQuery("SELECT *FROM View_Personas");
data.executeReader();
while (data.reader.Read())
{
//aux = new User((int)(data.reader["Id"]),
// Convert.ToString(data.reader["Nombre"]),
// Convert.ToDateTime(data.reader["Fecha_Nacimiento"]),
// new Pais((int)data.reader["Id"],
// (string)data.reader["Descripcion"]),
// Convert.ToDecimal(data.reader["Credito_Maximo"]));
//lista.Add(aux);
}
return(lista);
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
public void addPersona(string nombre, DateTime fecha, string pais, decimal credito)
{
DB_Connection data = new DB_Connection();
try
{
int IdPais = obtenerIdPais(pais);
data.setQuery("INSERT INTO PERSONAS(Nombre, Fecha_Nacimiento, Id_Pais, Credito_Maximo) VALUES ('" + nombre + "','" + fecha + "', '" + IdPais + "','" + credito + "');");
//data.addParameters("@nombre" , persona.Nombre);
//data.addParameters("@fecha" , persona.Fecha_Nacimiento);
//data.addParameters("@idpais" , persona.Pais.Id);
//data.addParameters("@credito", persona.Credito_Maximo);
data.executeAction();
}
catch (Exception ex)
{
throw ex;
}
finally
{
data.closeConnection();
}
}
//Delete Student
private void deleteStudent(string student_id)
{
string deleteStudent_Query = " delete from StudentInformation where student_id='" + student_id + "' ";
DB_Connection.OpenConection();
DB_Connection.ExecuteNoNQueries(deleteStudent_Query);
DB_Connection.CloseConnection();
}
public void UpdateTable(Comentarios comentario)
{
using (DB_Connection con = new DB_Connection())
{
con.Comentarios.Add(comentario);
con.SaveChanges();
}
}
public void Configuration(IAppBuilder app)
{
ConfigureAuth(app);
gh = new DB_Connection();
allPara = new List <SqlParameter>();
DB_Connection.O_reader = DB_Connection.sql_reader(query, allPara);
DB_Connection.O_reader.Close();
}
