private bool fillArrayList(ArrayList a, string query) { try { conn.getConnection(); DR = SqlManager.getQuery(sqlString, conn); if (DR != null) { while (DR.Read()) { for (int i = 0; i < DR.FieldCount; i++) { a.Add(DR[i].ToString()); } } } DR.Close(); } catch (DbException ex) { MessageBox.Show("No se pudo Llenar el Arraylist:\n " + ex); } if (a.Count > 0) { return(true); } else { return(false); } }
private void fillForm() { String q = "SELECT name,lastname,address,phone,cellphone,emailPersonal,password FROM users WHERE username='******';"; SqlDataReader info = null; info = SqlManager.getQuery(q, this.conn); if (info.HasRows) { while (info.Read()) { this.name = info[0].ToString(); this.lastname = info[1].ToString(); this.address = info[2].ToString(); this.phone = info[3].ToString(); this.cellphone = info[4].ToString(); this.emailPersona = info[5].ToString(); this.password = info[6].ToString(); } } info.Close(); if (this.name != "") { ultraTextEditor_Nombres.Text = this.name; } if (this.lastname != "") { ultraTextEditor_Apellidos.Text = this.lastname; } if (this.address != "") { ultraTextEditor_Direccion.Text = this.address; } if (this.phone != "") { ultraTextEditor_Telefono.Text = this.phone; } if (this.cellphone != "") { ultraTextEditor_Celular.Text = this.cellphone; } if (this.emailPersona != "") { ultraTextEditor_EmailPersonal.Text = this.emailPersona; } ultraTextEditor_Contrasenia.Text = ""; }
private void ultraButton1_Click(object sender, EventArgs e) { String q = "SELECT (us.name + us.lastname) AS 'Nombres', c.name AS 'Carrera' FROM dbo.users us JOIN dbo.career c ON us.careerId=c.id;"; String val; int b = 0; if (radioButton_cedcourse.Checked) { val = ultraTextEditor_searchcourse.Value.ToString(); ced = val; q = "SELECT (us.name + us.lastname) AS 'Nombres', c.name AS 'Carrera' FROM dbo.users us JOIN dbo.career c ON us.careerId=c.id AND us.id='" + val + "';"; } else if (radioButton_matcourse.Checked) { val = ultraTextEditor_searchcourse.Value.ToString(); q = "SELECT (us.name + us.lastname) AS 'Nombres', c.name AS 'Carrera', us.id as 'Cédula' FROM dbo.users us JOIN dbo.career c ON us.careerId=c.id AND us.registrationNumber='" + val + "';"; } else { b = 1; } if (b == 0) { SqlDataReader myReader = SqlManager.getQuery(q, conn); if (myReader.HasRows) { myReader.Read(); ultraLabel_namecourse.Text = "Nombre: " + myReader[0].ToString(); ultraLabel_careercourse.Text = "Carrera: " + myReader[1].ToString(); if (radioButton_matcourse.Checked) { ced = myReader[2].ToString(); } } else { MessageBox.Show("No se ha encontrado estudiante."); } myReader.Close(); } else { MessageBox.Show("Debe elegir un parámetro de búsqueda."); } }
public User(Connector conn, String username) { SqlDataReader myReader = null; String q = "SELECT id,name,lastname,username FROM dbo.users WHERE username='******';"; myReader = SqlManager.getQuery(q, conn); if (myReader.HasRows) { myReader.Read(); id = myReader[0].ToString(); name = myReader[1].ToString(); lastname = myReader[2].ToString(); username = myReader[3].ToString(); myReader.Close(); } q = "SELECT directorshipId FROM dbo.directorshipHistorial WHERE chargeTakenDate=(" + "SELECT MAX(chargeTakenDate) FROM dbo.directorshipHistorial WHERE usersId='" + id + "')"; myReader = SqlManager.getQuery(q, conn); myReader.Read(); if (myReader.HasRows) { directorship = Int32.Parse(myReader[0].ToString()); myReader.Close(); q = "SELECT name FROM dbo.directorship WHERE id='" + directorship + "';"; myReader = SqlManager.getQuery(q, conn); myReader.Read(); this.directorshipName = myReader[0].ToString(); myReader.Close(); } else { this.directorshipName = "NINGUNO"; } myReader.Close(); }
private String myReader(String query) { SqlDataReader myreader = null; String var = ""; myreader = SqlManager.getQuery(query, conn); if (myreader.HasRows) { while (myreader.Read()) { var = myreader[0].ToString(); } } myreader.Close(); return(var); }
/**<summary>Metodo verifica si la rama a registrar no existe en el sistema</summary> */ /**<return> Devuelve la rama encontrada en caso de que exista en el sistema. * Devuelve null en caso de que no exista</return>*/ /**<param name="sender">Objeto</param>*/ /**<param name="e">Evento</param>*/ private Branch isExitsBranch(Branch br) { Branch brx = new Branch(); sqlString = "select name,shortName from branch where name like '%" + br.getName() + "%' OR shortName like '%" + br.getShortName() + "%';"; DR = SqlManager.getQuery(sqlString, conn); if (DR != null) { while (DR.Read()) { brx.setName((String)(DR["name"])); brx.setShortName((String)(DR["shortName"])); } } DR.Close(); return(brx); }
/*************************************************************************************************************************************************************/ private void cmbEncargadoR_BeforeDropDown(object sender, CancelEventArgs e) { ArrayList encargados = new ArrayList(); if (this.cmbRamaEncargadaR.Text == "") { MessageBox.Show("Seleccione La Rama Encargada", "Rama Ecargada Inválida", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { this.cmbEncargadoR.Items.Clear(); //sqlString = "select name + ' ' + lastname as 'fullName' from SACMEDB.dbo.users where (SACMEDB.dbo.users.id = (select userId from userBranch where branchId = (select SACMEDB.dbo.branch.id from branch where name = '" + this.cmbRamaEncargadaR.Text + "')))"; sqlString = "select userId from userBranch where branchId = (select id from branch where name = '" + this.cmbRamaEncargadaR.Text + "')"; try { DR = SqlManager.getQuery(sqlString, conn); if (DR != null) { while (DR.Read()) { encargados.Add(DR["userId"].ToString()); idEncargado.Items.Add(DR["userId"].ToString()); } } DR.Close(); for (int i = 0; i < encargados.Count; i++) { DR = SqlManager.getQuery("select name +' '+ lastname as 'fullName' from users where id = '" + encargados[i] + "'", conn); if (DR != null) { while (DR.Read()) { this.cmbEncargadoR.Items.Add(DR["fullName"]); } } DR.Close(); } } catch (DbException ex) { MessageBox.Show("No se pudo Obtener El Encargado:\n " + ex); } } }
/**<summary>Metodo que maneja el evento de clic en el boton consultar/actualizar rama. * Toma el id de la fila del GridLayout y segun eso realiza la busqueda de la rama que coincida con el id. * Muestra los datos en una nueva ventana</summary> */ /**<return> No devuelve nada </return>*/ /**<param name="sender">Objeto</param>*/ /**<param name="e">Evento</param>*/ private void btnConsult_Click(object sender, EventArgs e) { Int32 selectedRowCount = dataGridView_Branch.Rows.GetRowCount(DataGridViewElementStates.Selected); if (selectedRowCount > 0) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); int numFila; try{ String fila = this.dataGridView_Branch.SelectedRows[selectedRowCount - 1].Index.ToString(); numFila = Int32.Parse(fila); string idRama = this.dataGridView_Branch[0, numFila].Value.ToString(); int idBranch = Int32.Parse(idRama); sqlString = "select name,shortName, isEnable, created from branch where id='" + idBranch + "';"; DR = SqlManager.getQuery(sqlString, conn); Branch br = new Branch(); br.setId(idBranch); if (DR != null) { while (DR.Read()) { br.setName((String)(DR["name"])); br.setShortName((String)(DR["shortName"])); br.setIsEnable((bool)(DR["isEnable"])); br.setCreated((DateTime)(DR["created"])); } } DR.Close(); Form_uploadDataBranch consulta = new Form_uploadDataBranch(br, conn); consulta.Show(); } catch (Exception ex) { MessageBox.Show("Disculpe las molestias.Se produjo un error inesperado:\n " + ex); } } else { MessageBox.Show("Para consultar debe elegir una rama de la lista"); } }
/*************************************************************************************************************************************************************/ public void fillComboBox(UltraComboEditor u, string sqlString, string campo) { u.Items.Clear(); try { conn.getConnection(); DR = SqlManager.getQuery(sqlString, conn); if (DR != null) { while (DR.Read()) { u.Items.Add(DR[campo]); } } DR.Close(); } catch (DbException ex) { MessageBox.Show("No se pudo Obtener las Ramas:\n " + ex); } }
private void addItemtoComboEditor(Infragistics.Win.UltraWinEditors.UltraComboEditor uce, String query) { String item; SqlDataReader myreader = null; myreader = SqlManager.getQuery(query, conn); while (uce.Items.Count > 0) { uce.Items.RemoveAt(0); } if (myreader.HasRows) { while (myreader.Read()) { item = myreader[0].ToString(); uce.Items.Add(item); } } myreader.Close(); }
/*************************************************************************************************************************************************************/ private void btnModificar_Click(object sender, EventArgs e) { ArrayList evento = getSelectedItem(this.dataGridView_Eventos); if (SelectedItems) { string idEvento = null; this.formMain.Enabled = false; if (evento.Count > 0) { sqlString = "select id from [SACMEDB].[dbo].[event] where name = '" + evento[0] + "' and [SACMEDB].[dbo].[event].[description] = '" + evento[1] + "'"; try { DR = SqlManager.getQuery(sqlString, conn); if (DR != null) { while (DR.Read()) { idEvento = DR["id"].ToString(); } } DR.Close(); } catch (DbException ex) { MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex); } formMain.Enabled = false; Form_modifyEvent modify = new Form_modifyEvent(idEvento, formMain, this, DR, conn); modify.Show(); } else { MessageBox.Show("No se encontró el evento a buscar:\n "); } } }
/*************************************************************************************************************************************************************/ private void btnGuardarEvento_Click(object sender, EventArgs e) { int branchId = 0; int eventTypeId = 0; if (isValidRegistryForm()) { try { conn.getConnection(); DR = SqlManager.getQuery("select id from SACMEDB.dbo.branch where name = '" + this.cmbRamaEncargadaR.Text + "'", conn); if (DR != null) { while (DR.Read()) { branchId = Convert.ToInt32(DR["id"].ToString()); } } DR.Close(); } catch (DbException ex) { MessageBox.Show("No Se Pudo Obtener El Id De La Rama:\n " + ex); } try { conn.getConnection(); DR = SqlManager.getQuery("select id from [SACMEDB].[dbo].[eventType] where name = '" + this.cmbTipoR.Text + "'", conn); if (DR != null) { while (DR.Read()) { eventTypeId = Convert.ToInt32(DR["id"].ToString()); } } DR.Close(); } catch (DbException ex) { MessageBox.Show("No Se Pudo Obtener El Id De La Rama:\n " + ex); } Event ev = new Event(); ev.setName(this.txtNombreR.Text); ev.setDescription(this.txtDescripcionR.Text); this.idEncargado.SelectedIndex = this.cmbEncargadoR.SelectedIndex; ev.setResponsible(idEncargado.SelectedItem.ToString()); ev.setDuration(this.NumericEditorDuracionR.Value.ToString() + " " + this.cmbDuracion.Text); ev.setStart((DateTime)this.dateFechaDeInicioR.Value); ev.setEventTypeId(eventTypeId); ev.setBranchId(branchId); ev.setCreatedBy(eventCreator); insertEvent(ev); for (int i = 0; i < this.cmbOrganizadorR.Items.Count; i++) { sqlString = "insert into [SACMEDB].[dbo].[eventOrganizer] (eventId,organizerId) values ((select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreR.Text + "' and description = '" + this.txtDescripcionR.Text + "'),(select id from organizers where name = '" + this.cmbOrganizadorR.Items[i].ToString() + "'))"; SqlManager.executeQuery(sqlString, conn); } for (int i = 0; i < this.cmbParticipantesR.Items.Count; i++) { sqlString = "insert into [SACMEDB].[dbo].[eventParticipant] (userId,eventId,rol) values ('" + this.idParticipants.Items[i].ToString() + "',(select id from [SACMEDB].[dbo].[event] where name = '" + this.txtNombreR.Text + "' and description = '" + this.txtDescripcionR.Text + "'),'Participante Evento " + this.txtNombreR.Text + "') "; SqlManager.executeQuery(sqlString, conn); } clearRegistryForm(); MessageBox.Show("Evento Creado Satisfactoriamente"); } }
private void ultraButton_Ingresar_Click(object sender, EventArgs e) { int flag = 0; String query = ""; SqlDataReader user = null; Form_usersPassword fm; this.usuario = ultraTextEditor_Usuario.Text; this.contrasenia = ultraTextEditor_Contrasenia.Text; if (this.usuario == "" || this.contrasenia == "") { MessageBox.Show("Ingrese correctamente sus datos"); } else { query = "SELECT id FROM users WHERE username='******';"; user = SqlManager.getQuery(query, sacmeDbConn); if (!user.HasRows) { MessageBox.Show("Ud no pertenece al Sistema"); user.Close(); } else { user.Close(); if (ultraCheckEditor_PrimeraVez.Checked) { DirectorioEspol.directorioEspolSoapClient wservice = new DirectorioEspol.directorioEspolSoapClient(); if (wservice.autenticacion(usuario, contrasenia)) { flag = 1; } else { MessageBox.Show("Ud no pertenece a la ESPOL, no podrá ingresar a SACME"); } } else { query = "SELECT id FROM users WHERE username='******' AND password='******';"; user = SqlManager.getQuery(query, sacmeDbConn); if (!user.HasRows) { MessageBox.Show("El usuario o contraseña es inválido. Intente nuevamente"); user.Close(); } else { user.Close(); Form_main formmain = new Form_main(this, sacmeDbConn, this.usuario); this.Hide(); formmain.Show(); } user.Close(); } if (flag == 1) { fm = new Form_usersPassword(ultraTextEditor_Usuario.Text, sacmeDbConn, this); this.Hide(); fm.Show(); } } } }
public Form_academicControlRegistry(Connector conn, User userSession, String id, String typeAC) { String sql; this.conn = conn; InitializeComponent(); this.userSession = userSession; this.referenceId = id; SqlDataReader DR; if (typeAC == "course") { this.typeAC = typeAC; ultraTabControl1.SelectedTab = ultraTabPageControl2.Tab; ultraTabPageControl1.Enabled = false; sql = "SELECT id,name,gainedPoints,academicTerm,institution,duration,userId FROM " + "dbo.course WHERE id=" + id; try { DR = SqlManager.getQuery(sql, conn); if (DR != null) { DR.Read(); ultraTextEditor_nameofcourse.Value = DR["name"].ToString(); ultraTextEditor_institutioncourse.Value = DR["institution"].ToString(); ultraTextEditor_pointgetcourse.Value = DR["gainedPoints"].ToString(); String acTerm = DR["academicTerm"].ToString(); String duration = DR["duration"].ToString(); String[] academicT = acTerm.Split(' ', '-'); ultraTextEditor_anio1course.Value = academicT[1]; ultraTextEditor_anio2course.Value = academicT[2]; ultraComboEditor_termcurso.SelectedText = academicT[0]; String[] timeDuration = duration.Split(' '); ultraComboEditor_typedurationcourse.Value = timeDuration[1]; numericUpDown_durationcountcourse.Value = Int32.Parse(timeDuration[0]); ultraTextEditor_searchcourse.Value = DR["userId"].ToString(); radioButton_cedcourse.Checked = true; } DR.Close(); } catch (DbException ex) { MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex); } ultraButton_savecourse.Text = "ACTUALIZAR"; ultraButton_savecourse.Size = new Size(92, 24); } else if (typeAC == "espol") { this.typeAC = typeAC; ultraTabControl1.SelectedTab = ultraTabPageControl1.Tab; ultraTabPageControl2.Enabled = false; sql = "SELECT id,userId,gainedPoints,totalPoints,academicTerm,espolAverage,observation " + "FROM dbo.academicPerformance WHERE id=" + id; try { DR = SqlManager.getQuery(sql, conn); if (DR != null) { DR.Read(); radioButton_ced.Checked = true; ultraTextEditor_search.Text = DR["userId"].ToString(); String acTerm = DR["academicTerm"].ToString(); String[] academicT = acTerm.Split(' ', '-'); ultraTextEditor_anio1.Value = academicT[1]; ultraTextEditor_anio2.Value = academicT[2]; ultraComboEditor_term.SelectedText = academicT[0]; ultraTextEditor_gainedPoints.Text = DR["gainedPoints"].ToString(); bool parse = Int32.TryParse(ultraTextEditor_gainedPoints.Text, out gainedPointsLast); ultraTextEditor_espolAvg.Text = DR["espolAverage"].ToString(); ultraTextEditor_observation.Text = DR["observation"].ToString(); } DR.Close(); } catch (DbException ex) { MessageBox.Show("No se pudo Obtener el Id del Evento:\n " + ex); } ultraButton_saveEspol.Text = "ACTUALIZAR"; ultraButton_saveEspol.Size = new Size(92, 24); } }
private void ultraButton2_Click(object sender, EventArgs e) { String academicTerm = null, observation = null, espolAvg = null, ced = null; int gainedPoints = 0, totalPoints = 0, totalPoints_update = 0; float espolAvg_float; academicTerm = ultraComboEditor_term.Text + " " + ultraTextEditor_anio1.Text + "-" + ultraTextEditor_anio2.Text; ced = ultraTextEditor_search.Text; observation = ultraTextEditor_observation.Text; espolAvg = ultraTextEditor_espolAvg.Text; bool parse = Int32.TryParse(ultraTextEditor_gainedPoints.Text, out gainedPoints); if (false == parse) { MessageBox.Show("Formato puntos ganados incorrecto.\nDebe ser un entero."); } else { String qTotalP = "SELECT acper.totalPoints FROM dbo.academicPerformance acper WHERE acper.userId='" + ced + "'" + "AND acper.inserted = (SELECT MAX(ap.inserted) FROM dbo.academicPerformance ap WHERE ap.userId='" + ced + "');"; SqlDataReader myReader = SqlManager.getQuery(qTotalP, conn); if (myReader.HasRows) { myReader.Read(); totalPoints = Int32.Parse(myReader[0].ToString()); totalPoints_update = totalPoints - gainedPointsLast + gainedPoints; totalPoints = totalPoints + gainedPoints; } else { totalPoints = gainedPoints; } myReader.Close(); } parse = float.TryParse(espolAvg, NumberStyles.Any, CultureInfo.InvariantCulture, out espolAvg_float); if (parse == true) { String insertedBy = null; if (ultraButton_saveEspol.Text == "GUARDAR") { String burnAPquery = "INSERT INTO dbo.academicPerformance (userId,academicTerm,gainedPoints,totalPoints,espolAverage,observation,insertedBy)" + "VALUES ('" + ced + "','" + academicTerm + "'," + gainedPoints + "," + totalPoints + "," + espolAvg_float + ",'" + observation + "','" + insertedBy + "');"; bool burn = SqlManager.executeQuery(burnAPquery, conn); if (burn) { MessageBox.Show("Registro se ha guardado exitosamente."); this.Close(); } else { MessageBox.Show("Su registro no ha podido ser guardado."); } } else if (ultraButton_saveEspol.Text == "ACTUALIZAR") { String updateAPquery = "UPDATE dbo.academicPerformance SET userId='" + ced + "',academicTerm='" + academicTerm + "',gainedPoints=" + gainedPoints + ",totalPoints=" + totalPoints_update + ",espolAverage=" + espolAvg_float + ",observation='" + observation + "' " + "WHERE id=" + referenceId; MessageBox.Show(updateAPquery); bool update = SqlManager.executeQuery(updateAPquery, conn); if (update) { MessageBox.Show("Registro ha sido actualizado exitosamente."); this.Close(); } else { MessageBox.Show("Su registro no ha podido ser actualizado."); } } } else { MessageBox.Show("El formato ingresado de promedio ESPOL es incorrecto."); } }
private void ultraButton_Buscar_Click(object sender, EventArgs e) { int rowCount; String query = "SELECT Cargo= " + "(SELECT dd.name " + "FROM directorship dd, directorshipHistorial ddhh " + "WHERE ddhh.usersId=u.id AND ddhh.directorshipId=dd.id AND ddhh.chargeTakenDate=(" + "SELECT MAX(chargeTakenDate) " + "FROM dbo.directorshipHistorial " + "WHERE usersId=u.id ))," + "Estado=" + "(SELECT us.id " + "FROM userState us " + "WHERE us.id=u.userStateId), " + "Miembro=" + "(SELECT isMember " + "FROM userTreatment ut " + "WHERE ut.id=u.userTreatmentId AND ut.isMember=1), " + "u.name as Nombres,u.lastname as Apellidos, u.id as Cédula, u.birthDate as 'Fecha de Nacimiento'," + "u.address as Dirección, u.phone as Teléfono, u.cellphone as Celular," + "u.registrationNumber as Matrícula,c.name as Carrera, u.emailESPOL as 'Correo de ESPOL'," + "u.username as Usuario,u.emailPersonal as 'Correo Alterno' " + "FROM users u,career c " + "WHERE (c.id=u.careerId AND "; String cont = " ;"; String q = ""; String search = ultraTextEditor_Buscar.Text; int flag = 0; if (search == "") { MessageBox.Show("Escriba algún dato en la caja de texto"); } else { if (radioButton_Cedula.Checked) { search = ultraTextEditor_Buscar.Text; q = query + "u.id='" + search + "')" + cont; flag = 1; } else if (radioButton_Matricula.Checked) { search = ultraTextEditor_Buscar.Text; q = query + "u.registrationNumber='" + search + "')" + cont; flag = 1; } else if (radioButton_Nombre.Checked) { search = ultraTextEditor_Buscar.Text; q = query + "u.name like '%" + search + "%')" + cont; flag = 1; } else if (radioButton_Apellido.Checked) { search = ultraTextEditor_Buscar.Text; q = query + "u.lastname like '%" + search + "%')" + cont; flag = 1; } else { MessageBox.Show("Seleccione una opción para continuar"); } if (flag == 1) { SqlManager.loadDataGridView(dataGridView_Users, q, conn); dataGridView_Users.Columns[1].Visible = false; dataGridView_Users.Columns[0].Width = 60; dataGridView_Users.Columns[0].Visible = true; dataGridView_Users.Columns[0].DisplayIndex = 14; if (dataGridView_Users.Columns.Count == 16) { dataGridView_Users.Columns.Remove("ColumnEstado"); } DataGridViewColumn newCol = new DataGridViewColumn(); // add a column to the grid DataGridViewCell cell = new DataGridViewTextBoxCell(); //Specify which type of cell in this column newCol.CellTemplate = cell; newCol.HeaderText = "Estado"; newCol.Name = "ColumnEstado"; newCol.Visible = true; newCol.Width = 50; dataGridView_Users.Columns.Add(newCol); dataGridView_Users.Columns[0].Visible = true; dataGridView_Users.Columns[0].Width = 100; rowCount = 0; String a; while (rowCount < dataGridView_Users.Rows.Count - 1) { a = dataGridView_Users.Rows[rowCount].Cells[1].Value.ToString(); String qry = "SELECT us.isActive,us.isInactive,us.isPassive,us.isRemoved,us.isTesting FROM dbo.userState us WHERE us.id=" + a + ";"; SqlDataReader mr = SqlManager.getQuery(qry, conn); if (mr.HasRows) { mr.Read(); if (mr[0].ToString() == "True") { dataGridView_Users.Rows[rowCount].Cells[15].Value = "Activo"; } else if (mr[1].ToString() == "True") { dataGridView_Users.Rows[rowCount].Cells[15].Value = "Inactivo"; } else if (mr[2].ToString() == "True") { dataGridView_Users.Rows[rowCount].Cells[15].Value = "Pasivo"; } else if (mr[3].ToString() == "True") { dataGridView_Users.Rows[rowCount].Cells[15].Value = "Removido"; } else if (mr[4].ToString() == "True") { dataGridView_Users.Rows[rowCount].Cells[15].Value = "A prueba"; } else { dataGridView_Users.Rows[rowCount].Cells[15].Value = "Sin información"; } } mr.Close(); rowCount++; } } } }