public Corresponsal obtenerCorresponsalOficinaMaxLong(int corresponsalID) { try { string sql = "SELECT TOP 1 " + "C.COR_CORRESPONSAL_ID, C.COR_NOMBRE, O.OFI_NOMBRE " + "FROM Corresponsales AS C " + "INNER JOIN OFICINAS AS O " + "ON C.COR_CORRESPONSAL_ID = O.OFI_CORRESPONSAL_ID " + "WHERE LEN(OFI_NOMBRE) = (SELECT MAX(LEN(OFI_NOMBRE)) FROM OFICINAS WHERE OFICINAS.OFI_CORRESPONSAL_ID = O.OFI_CORRESPONSAL_ID )" + "AND C.COR_CORRESPONSAL_ID = " + corresponsalID.ToString(); using (SqlConnection cnn = new SqlConnection(cnnString)) { cnn.Open(); SqlCommand cmd = new SqlCommand(sql, cnn); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); /////////////////////////////////////////////////////////// Corresponsal corr = new Corresponsal() { id = Convert.ToInt32(dr["COR_CORRESPONSAL_ID"]), nombre = dr["COR_NOMBRE"].ToString(), ofiNombre = dr["OFI_NOMBRE"].ToString() }; return(corr); } } catch { return(null); } }
public List <Corresponsal> obtenerCorresponsales() { string sql = "SELECT COR_CORRESPONSAL_ID, COR_NOMBRE, COUNT(O.OFI_CORRESPONSAL_ID) AS COR_NRO_OFI " + "FROM CORRESPONSALES AS C " + "INNER JOIN OFICINAS AS O " + "ON O.OFI_CORRESPONSAL_ID = C.COR_CORRESPONSAL_ID " + "GROUP BY COR_CORRESPONSAL_ID, COR_NOMBRE ORDER BY COR_NOMBRE;"; using (SqlConnection cnn = new SqlConnection(cnnString)) { cnn.Open(); SqlCommand cmd = new SqlCommand(sql, cnn); SqlDataReader dr = cmd.ExecuteReader(); List <Corresponsal> Lista = new List <Corresponsal>(); while (dr.Read()) { Corresponsal corr = new Corresponsal() { id = Convert.ToInt32(dr["COR_CORRESPONSAL_ID"]), nombre = dr["COR_NOMBRE"].ToString(), // ofiNombre = dr["OFI_NOMBRE"].ToString() nroOfi = Convert.ToInt32(dr["COR_NRO_OFI"]) }; Lista.Add(corr); } return(Lista); } }