/// <summary> /// Puts info into DB. /// </summary> /// <param name="info">Record information to submit</param> /// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param> /// <returns>Identity ID for just created record.</returns> public int crearTeatro(TeatroDto info, int op) { if (log.IsDebugEnabled) { log.Debug("crearTeatro Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; int rslt = 0; try { List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() { ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@id", Value = info.idTeatro, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@idCine", Value = info.idCine, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@nombreTeatro", Value = info.nombreTeatro.ToString(), SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@telefono1Teatro", Value = info.telefono1Teatro.ToString(), SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@telefono2Teatro", Value = info.telefono2Teatro.ToString(), SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@telefono3Teatro", Value = info.telefono3Teatro.ToString(), SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@idMunicipioTeatro", Value = info.idMunicipioTeatro, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@idDepartamentoTeatro", Value = info.idDepeartamentoTeatro, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@direccionTeatro", Value = info.direccionTeatro, SqlDbType = SqlDbType.VarChar } }; String sql = "sp_crearActualizarTeatro @operacion, @id, @idCine, @nombreTeatro, @telefono1Teatro, @telefono2Teatro, @telefono3Teatro, @idMunicipioTeatro, @idDepartamentoTeatro, @direccionTeatro"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("crearCine"); rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray()); } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Returns -1"); } rslt = -1; } finally { try { if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Returns -1"); } rslt = -1; } } if (log.IsDebugEnabled) { log.Debug("rslt=[" + rslt + "]"); log.Debug("crearTeatro Ends"); } return rslt; }
/// <summary> /// Puts info into DB. /// </summary> /// <param name="info">Record information to submit</param> /// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param> /// <returns>Identity ID for just created record.</returns> public int createCine(CineDto info, int op) { if (log.IsDebugEnabled) { log.Debug("createCine Starts"); } SqlTransaction transaction = null; HandleDatabase hdb = null; int rslt = 0; try { List<SqlParameter> paramList = new List<SqlParameter>(); paramList.Add(new SqlParameter() { ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int }); paramList.Add(new SqlParameter() { ParameterName = "@id", Value = info.idCine, SqlDbType = SqlDbType.Int }); paramList.Add(new SqlParameter() { ParameterName = "@nombreCine", Value = info.nombreCine.ToString(), SqlDbType = SqlDbType.VarChar }); paramList.Add(new SqlParameter() { ParameterName = "@fechaCreacionCine", Value = info.fechaCreacionCine, SqlDbType = SqlDbType.DateTime }); paramList.Add(new SqlParameter() { ParameterName = "@nit", Value = info.nit, SqlDbType = SqlDbType.VarChar }); String sql = "sp_crearActualizarCine @operacion, @id, @nombreCine, @fechaCreacionCine, @nit"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("crearCine"); rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray()); if (log.IsDebugEnabled) { log.Debug("createCine Ends"); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Returns 0"); } rslt = 0; } finally { try { if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Returns 0"); } rslt = 0; } } log.Debug("Rslt=[" + rslt + "]"); if (log.IsDebugEnabled) { log.Debug("createCine Ends"); } return rslt; }
/// <summary> /// Gets a list of active movies to work. /// </summary> /// <returns>A list of DetallePeliculaDto objects</returns> public List<DetallePeliculaDto> getPeliculasActivas() { if (log.IsDebugEnabled) { log.Debug("getPeliculasActivas Starts"); } SqlDataReader rdr = null; SqlTransaction transaction = null; HandleDatabase hdb = null; List<DetallePeliculaDto> lstPelicula = new List<DetallePeliculaDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); String sql = "sp_obtenerPeliculasActivas"; if (log.IsDebugEnabled) { log.Debug("Sql=[" + sql + "]"); } transaction = hdb.BeginTransaction("getPeliculasActivas"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql); while (rdr.Read()) { lstPelicula.Add(new DetallePeliculaDto() { idPelicula = Convert.ToInt32(rdr["idPelicula"]), idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]), nombrePelicula = rdr["nombrePelicula"].ToString(), idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString()), fechaCreacionPelicula = Convert.ToDateTime(rdr["fechaCreacionPelicula"].ToString()), idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString()), sinopsis = rdr["sinopsis"].ToString(), urlArticuloEc = rdr["urlArticuloEC"].ToString(), enCartelera = (rdr["activo"].ToString() == "S" ? "Si" : "No") }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Return sets to emtpy list"); } lstPelicula = new List<DetallePeliculaDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Return sets to emtpy list"); } lstPelicula = new List<DetallePeliculaDto>(); } } if (log.IsDebugEnabled) { log.Debug("getPeliculasActivas Ends"); } return lstPelicula; }
/// <summary> /// Puts info into DB. /// </summary> /// <param name="info">Record information to submit</param> /// <param name="op">Which kind to operation to make. 1:Insert, 2:update, 3:delete</param> /// <returns>Identity ID for just created record.</returns> public int createEntidad(EntidadDto info, int op) { if (log.IsDebugEnabled) { log.Debug("createEntidad Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; int rslt = 0; try { List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() { ParameterName = "@operacion", Value = op, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@id", Value = info.idEntidad, SqlDbType = SqlDbType.Int }, new SqlParameter() { ParameterName = "@codigo", Value = info.codEntidad.ToString(), SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@nombre", Value = info.nombreEntidad, SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@valor", Value = info.valorEntidad, SqlDbType = SqlDbType.VarChar }, new SqlParameter() { ParameterName = "@descripcion", Value = info.descripcionEntidad, SqlDbType = SqlDbType.VarChar } }; String sql = "sp_crearActualizarEntidad @operacion, @id, @codigo, @nombre, @valor, @descripcion"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("crearEntidad"); rslt = hdb.ExecuteSelectSQLStmtAsScalar(transaction, sql, paramList.ToArray()); } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Returns -1"); } rslt = -1; } finally { try { if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Returns -1"); rslt = -1; } } if (log.IsDebugEnabled) { log.Debug("Rslt=[" + rslt + "]"); log.Debug("createEntidad Ends"); } return rslt; }
/// <summary> /// Retrieves all values for table CINE. /// </summary> /// <returns>List of CineDto objects</returns> public List<CineDto> getCines() { if (log.IsDebugEnabled) { log.Debug("getCines Starts"); } HandleDatabase hdb = null; SqlDataReader rdr = null; SqlTransaction transaction = null; List<CineDto> movieList = new List<CineDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); string sql = "sp_obtenerCines"; if (log.IsDebugEnabled) { log.Debug("Sql=[" + sql + "]"); } transaction = hdb.BeginTransaction("getCines"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql); while (rdr.Read()) { movieList.Add(new CineDto() { idCine = Convert.ToInt32(rdr["idCine"]), nit = rdr["nit"].ToString(), fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]), nombreCine = rdr["nombreCine"].ToString() }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Empty list returned"); } movieList = new List<CineDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Empty list returned"); } movieList = new List<CineDto>(); } } if (log.IsDebugEnabled) { log.Debug("getCines Ends"); } return movieList; }
/// <summary> /// Queries database to locate hours for a time period of time for a movie. /// </summary> /// <param name="id">Record to locate</param> /// <returns>List of hours for movie.</returns> public List<MovieShowHour> getMovieShowHoursFor(int id) { if (log.IsDebugEnabled) { log.Debug("getMovieShowHoursFor Starts"); } string sql = "select * from tbl_hora where idHorarioPelicula = @id order by horaPelicula, minutoPelicula "; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); } List<MovieShowHour> movieHoursList = new List<MovieShowHour>(); HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("getMovieHoursFor"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, new SqlParameter() { ParameterName = "@id", Value = id, SqlDbType = SqlDbType.BigInt }); while (rdr.Read()) { movieHoursList.Add(new MovieShowHour() { id = Convert.ToInt32(rdr["idHora"]), timeHour = Convert.ToInt32(rdr["horaPelicula"]), timeMinute = Convert.ToInt32(rdr["minutoPelicula"]) }); } // Let's fill field timeFull movieHoursList.ForEach(h => { string ampm = "am"; int hour = h.timeHour; if (hour > 12) { ampm = "pm"; hour -= 12; } if (hour == 12) { ampm = "pm"; } h.timeFull = hour + ":" + h.timeMinute.ToString().PadLeft(2, '0') + " " + ampm; }); } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Empty list returned"); } movieHoursList = new List<MovieShowHour>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Empty list returned"); } movieHoursList = new List<MovieShowHour>(); } } if (log.IsDebugEnabled) { log.Debug("getMovieShowHoursFor Ends"); } return movieHoursList; }
/// <summary> /// A core method in application. It has two tasks to make. First to update the premiere status, and second to /// retrieve all records scheduled for this week for billboard. /// </summary> /// <returns>A list of records for this week billboard.</returns> public List<MovieFullInfo> updateBillboardAndGetMovieFullInfo() { if (log.IsDebugEnabled) { log.Debug("updateBillboardAndGetMovieFullInfo Starts"); } string sql = ""; List<MovieFullInfo> movieFullList = new List<MovieFullInfo>(); sql = "sp_actualizarcartelera"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("updBillboardAndGetMovieFullinfo"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql); while (rdr.Read()) { movieFullList.Add(new MovieFullInfo() { id = Convert.ToInt32(rdr["idPelicula"]), name = rdr["nombrePelicula"].ToString(), nameFull = rdr["nombrePelicula"].ToString() + " " + rdr["nombreFormato"].ToString(), img = rdr["imagenCartelera"].ToString(), url = rdr["urlArticuloEC"].ToString(), active = rdr["activo"].ToString(), premiere = rdr["premiere"].ToString(), idGenre = Convert.ToInt32(rdr["idGeneroPelicula"]), genre = rdr["nombreGenero"].ToString(), idLocation = Convert.ToInt32(rdr["idTeatro"]), nameLocation = rdr["nombreCine"].ToString(), branchName = rdr["nombreTeatro"].ToString(), nameFullLocation = rdr["nombreCine"].ToString() + " " + rdr["nombreTeatro"].ToString(), address = rdr["direccionTeatro"].ToString(), idFormat = Convert.ToInt32(rdr["idFormato"]), nameFormat = rdr["nombreFormato"].ToString(), idShow = Convert.ToInt32(rdr["idHorarioPelicula"]), createDate = Convert.ToDateTime(rdr["fechaCreacionPelicula"]), dt = new DateTime(Convert.ToInt32(rdr["annoHorarioPelicula"]), Convert.ToInt32(rdr["mesHorarioPelicula"]), Convert.ToInt32(rdr["diaHorarioPelicula"])) }); } // Before returning records it needs to normalize field MovieFullInfo.url movieFullList.ForEach(m => { if (!m.url.Contains(Settings.UrlMilleniumPrefix)) { m.url = Settings.UrlMilleniumPrefix + m.url; } }); } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Empty list returned"); } movieFullList = new List<MovieFullInfo>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Empty list returned"); } movieFullList = new List<MovieFullInfo>(); } } if (log.IsDebugEnabled) { log.Debug("updateBillboardAndGetMovieFullInfo Ends"); } return movieFullList; }
/// <summary> /// Create or updates a schedule programming. /// </summary> /// <param name="datosProgramacion">What to update/create</param> /// <returns>0: Success, -1: Failure</returns> public int createUpdateProgramacionPelicula(ProgramacionPeliculaDto datosProgramacion) { if (log.IsDebugEnabled) { log.Debug("createUpdateProgramacionPelicula Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; int rslt = 0; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() {ParameterName = "@IDHORARIOPELICULA", Value = datosProgramacion.idHorarioPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@IDFORMATO", Value = datosProgramacion.idFormato, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@IDPELICULA", Value = datosProgramacion.idPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@IDTEATRO", Value = datosProgramacion.idTeatro, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@ANNOHORARIO", Value = datosProgramacion.annoHorarioPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@MESHORARIO", Value = datosProgramacion.mesHorarioPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@DIAHORARIO", Value = datosProgramacion.diaHorarioPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() {ParameterName = "@HORAMINUTO", Value = datosProgramacion.horaMinutoPelicula, SqlDbType = SqlDbType.VarChar}, new SqlParameter() {ParameterName = "@SALA", Value = datosProgramacion.sala, SqlDbType = SqlDbType.VarChar}, new SqlParameter() {ParameterName = "@NOMBREDIA", Value = datosProgramacion.nombreDiaSemanaHorarioPelicula, SqlDbType = SqlDbType.VarChar}, new SqlParameter() {ParameterName = "@FRECUENCIA", Value = datosProgramacion.frecuencia, SqlDbType = SqlDbType.Int} }; String sql = "SP_CREARACTUALIZARPROGRAMACIONPELICULA @IDHORARIOPELICULA,@IDFORMATO,@IDPELICULA,@IDTEATRO,@ANNOHORARIO,@MESHORARIO, @DIAHORARIO,@HORAMINUTO,@SALA, @NOMBREDIA,@FRECUENCIA"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } transaction = hdb.BeginTransaction("crUpdProgramacionPelicula"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray()); rslt = 1; } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Return sets to -1"); } rslt = -1; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Return sets to -1"); } rslt = -1; } } if (log.IsDebugEnabled) { log.Debug("rslt=[" + rslt + "]"); log.Debug("createUpdateProgramacionPelicula Ends"); } return rslt; }
/// <summary> /// Extends the data transfer object to expose the theater name and movie name /// </summary> /// <returns>A list of TeatroExDto objects</returns> public List<TeatroExDto> getTeatrosEx() { if (log.IsDebugEnabled) { log.Debug("getTeatrosEx Starts"); } SqlDataReader rdr = null; SqlTransaction transaction = null; HandleDatabase hdb = null; List<TeatroExDto> lstTeatros = new List<TeatroExDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); String sql = "sp_obtenerExTeatros"; if (log.IsDebugEnabled) { log.Debug("Sql=[" + sql + "]"); } transaction = hdb.BeginTransaction("getTeatrosEx"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql); while (rdr.Read()) { lstTeatros.Add(new TeatroExDto() { idTeatro = Convert.ToInt32(rdr["idteatro"]), idCine = Convert.ToInt32(rdr["idcine"]), nombreTeatro = rdr["nombreteatro"].ToString(), telefono1Teatro = rdr["telefono1teatro"].ToString(), telefono2Teatro = rdr["telefono2teatro"].ToString(), telefono3Teatro = rdr["telefono3teatro"].ToString(), idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]), idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]), direccionTeatro = rdr["direccionteatro"].ToString(), nombreCine = rdr["nombreCine"].ToString(), nombreMunicipio = rdr["nombreMunicipio"].ToString(), nombreDepartamento = rdr["nombreDepartamento"].ToString() }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Result sets to empty"); } lstTeatros = new List<TeatroExDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Result sets to empty"); } lstTeatros = new List<TeatroExDto>(); } } if (log.IsDebugEnabled) { log.Debug("getTeatrosEx Ends"); } return lstTeatros; }
/// <summary> /// Creates a Movie in DB. /// </summary> /// <param name="teatro">The theater to reference</param> /// <param name="peliculas">The list of ids for movies referenced in this theater.</param> /// <returns>0 if successful, -1 if error</returns> public int createPeliculaTeatro(int teatro, string peliculas) { if (log.IsDebugEnabled) { log.Debug("createPeliculaTeatro Starts"); } SqlTransaction transaction = null; SqlDataReader rdr = null; HandleDatabase hdb = null; int rslt = 0; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() {ParameterName = "@peliculas", Value = peliculas, SqlDbType = SqlDbType.VarChar}, new SqlParameter() {ParameterName = "@teatro", Value = teatro, SqlDbType = SqlDbType.Int} }; String sql = "sp_crearPeliculasPorTeatro @teatro,@peliculas"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } transaction = hdb.BeginTransaction("crearPeliculaPorTeatro"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray()); rslt = 1; } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Result is set to -1"); } rslt = -1; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Result is set to -1"); } rslt = -1; } } if (log.IsDebugEnabled) { log.Debug("r=[" + rslt + "]"); log.Debug("createPeliculaTeatro Ends"); } return rslt; }
/// <summary> /// Retrieves which movies are in which thater. /// </summary> /// <param name="teatro">The theater id to retrieve</param> /// <returns>A list of PeliculaDto objects</returns> public List<PeliculaDto> getPeliculasPorTeatro(int teatro) { if (log.IsDebugEnabled) { log.Debug("getPeliculasPorTeatro Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; List<PeliculaDto> lstPeliculasCine = new List<PeliculaDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); String sql = "sp_obtenerPeliculasPorTeatro @TEATRO"; SqlParameter param = new SqlParameter() { ParameterName = "@TEATRO", Value = teatro, SqlDbType = SqlDbType.Int }; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("PeliculasPorTeatro"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); while (rdr.Read()) { lstPeliculasCine.Add(new PeliculaDto() { idPelicula = Convert.ToInt32(rdr["idPelicula"]), idTeatro = Convert.ToInt32(rdr["idTeatro"]), nombrePelicula = rdr["nombrePelicula"].ToString(), nombreTeatro = rdr["nombreTeatro"].ToString() }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("List is empty"); } lstPeliculasCine = new List<PeliculaDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("List is empty"); } lstPeliculasCine = new List<PeliculaDto>(); } } if (log.IsDebugEnabled) { log.Debug("getPeliculasPorTeatro Ends"); } return lstPeliculasCine; }
/// <summary> /// Retrieves a record from DB about a PeliculaDto object. /// </summary> /// <param name="idPelicula">Parameter to retrieve.</param> /// <returns>One record with data or NULL if none found</returns> public DetallePeliculaDto getPelicula(int idPelicula) { if (log.IsDebugEnabled) { log.Debug("getPelicula Starts"); } SqlTransaction transaction = null; SqlDataReader rdr = null; HandleDatabase hdb = null; DetallePeliculaDto r = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); String sql = "sp_obtenerPelicula @IDPELICULA"; SqlParameter param = new SqlParameter() { ParameterName = "@IDPELICULA", Value = idPelicula, SqlDbType = SqlDbType.Int }; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("getPelicula"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); if (rdr.HasRows) { rdr.Read(); r = new DetallePeliculaDto() { idPelicula = Convert.ToInt32(rdr["idPelicula"]), idDetallePelicula = Convert.ToInt32(rdr["idDetallePelicula"]), nombrePelicula = rdr["nombrePelicula"].ToString(), idUsuarioCreador = Convert.ToInt32(rdr["idUsuarioCreador"].ToString()), fechaCreacionPelicula = Convert.ToDateTime(rdr["fechaCreacionPelicula"].ToString()), idGeneroPelicula = Convert.ToInt32(rdr["idGeneroPelicula"].ToString()), imagenCartelera = rdr["imagenCartelera"].ToString(), sinopsis = rdr["sinopsis"].ToString(), urlArticuloEc = rdr["urlArticuloEC"].ToString(), enCartelera = rdr["activo"].ToString(), premiere = rdr["premiere"].ToString() }; } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Record return is null"); } r = null; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Record return is null"); } r = null; } } if (log.IsDebugEnabled) { if (r == null) { log.Debug("Result is NULL"); } else { log.Debug("Result sets to [" + r.ToString() + "]"); } log.Debug("getPelicula Ends"); } return r; }
/// <summary> /// Retrieves one record from DB. /// </summary> /// <param name="nombreParametro">Filter to use</param> /// <returns>NULL if no record found.</returns> public ParametroSistemaDto getValorParametroSistema(string nombreParametro) { if (log.IsDebugEnabled) { log.Debug("getValorParametroSistema Starts"); } SqlTransaction transaction = null; SqlDataReader rdr = null; HandleDatabase hdb = null; ParametroSistemaDto r = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); SqlParameter param = new SqlParameter() { ParameterName = "@np", Value = nombreParametro, SqlDbType = SqlDbType.VarChar }; string sql = "sp_obtenerValorParametroSistema @np"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("ObtenerValorParametroSistema"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); if (rdr.HasRows) { rdr.Read(); r = new ParametroSistemaDto() { idParametro = Convert.ToInt32(rdr["idParametro"]), nombreParametro = rdr["nombreParametro"].ToString(), valorParametro = rdr["valorParametro"].ToString(), descValorParametro = rdr["descValorParametro"].ToString(), visible = Convert.ToChar(rdr["visible"]) }; } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Return null"); } r = null; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Return null"); } r = null; } } if (log.IsDebugEnabled) { if (r == null) { log.Debug("Result is NULL"); } else { log.Debug("Result sets to [" + r.ToString() + "]"); } log.Debug("getValorParametroSistema Ends"); } return r; }
/// <summary> /// Retrieves all values for table PARAMETROSISTEMA. /// </summary> /// <returns>List of ParametroSistemaDto objects</returns> public List<ParametroSistemaDto> getValoresParametroSistema() { if (log.IsDebugEnabled) { log.Debug("getValoresParametroSistema Starts"); } SqlDataReader rdr = null; SqlTransaction transaction = null; HandleDatabase hdb = null; List<ParametroSistemaDto> sysParamList = new List<ParametroSistemaDto>(); try { string sql = "sp_obtenerValoresParametroSistema"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); } hdb = new HandleDatabase(Settings.Connection); hdb.Open(); transaction = hdb.BeginTransaction("obtenerValoresParametroSistema"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql); while (rdr.Read()) { sysParamList.Add(new ParametroSistemaDto() { idParametro = Convert.ToInt32(rdr["idParametro"]), nombreParametro = rdr["nombreParametro"].ToString(), valorParametro = rdr["valorParametro"].ToString(), descValorParametro = rdr["descValorParametro"].ToString(), visible = Convert.ToChar(rdr["visible"]) }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("List returned is empty"); } sysParamList = new List<ParametroSistemaDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("List returned is empty"); } sysParamList = new List<ParametroSistemaDto>(); } } if (log.IsDebugEnabled) { log.Debug("getValoresParametroSistema Ends"); } return sysParamList; }
/// <summary> /// Retrieves one record from DB given id. /// </summary> /// <param name="id">id to match</param> /// <returns>NULL if not found, else record information</returns> public EntidadDto getValorEntidad(int id) { if (log.IsDebugEnabled) { log.Debug("getValorEntidad Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; EntidadDto r = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); SqlParameter param = new SqlParameter() { ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int }; String sql = "sp_obtenerValorEntidad @id"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("obtenerValorEntidad"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); if (rdr.HasRows) { rdr.Read(); r = new EntidadDto(); r.idEntidad = Convert.ToInt32(rdr["IDENTIDAD"]); r.codEntidad = Convert.ToInt32(rdr["CODENTIDAD"]); r.nombreEntidad = rdr["NOMBREENTIDAD"].ToString(); r.valorEntidad = rdr["VALORENTIDAD"].ToString(); r.descripcionEntidad = rdr["DESCRIPCIONENTIDAD"].ToString(); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Returns null"); } r = null; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); } r = null; } } if (log.IsDebugEnabled) { if (r == null) { log.Debug("Result is NULL"); } else { log.Debug("Result sets to [" + r.ToString() + "]"); } log.Debug("getCine Ends"); } return r; }
/// <summary> /// Retrieves one record from DB. /// </summary> /// <param name="id">Filter to use</param> /// <returns>NULL if no record found.</returns> public CineDto getCine(int id) { if (log.IsDebugEnabled) { log.Debug("getCine Starts"); log.Debug("id=[" + id + "]"); } SqlDataReader rdr = null; SqlTransaction transaction = null; HandleDatabase hdb = null; CineDto r = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); SqlParameter param = new SqlParameter() { ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int}; string sql = "sp_obtenerCine @id"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("getCine"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); if (rdr.HasRows) { rdr.Read(); r = new CineDto() { idCine = Convert.ToInt32(rdr["idCine"]), nit = rdr["nit"].ToString(), fechaCreacionCine = Convert.ToDateTime(rdr["fechaCreacionCine"]), nombreCine = rdr["nombreCine"].ToString() }; if (log.IsDebugEnabled) { log.Debug("Record retrieved =[" + r.ToString() + "]"); } } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); } r = null; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); } r = null; } } if (log.IsDebugEnabled) { if (r == null) { log.Debug("Result is NULL"); } else { log.Debug("Result sets to [" + r.ToString() + "]"); } log.Debug("getCine Ends"); } return r; }
/// <summary> /// Retrieves one record from DB. /// </summary> /// <param name="id">Filter to use</param> /// <returns>NULL if no record found.</returns> public TeatroDto getTeatro(int id) { if (log.IsDebugEnabled) { log.Debug("getTeatro Starts"); } SqlTransaction transaction = null; SqlDataReader rdr = null; HandleDatabase hdb = null; TeatroDto r = null; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); SqlParameter param = new SqlParameter() { ParameterName = "@id", Value = id, SqlDbType = SqlDbType.Int }; string sql = "sp_obtenerTeatro @id"; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); var paramValues = "ParameterName=[" + param.ParameterName + "], Value=[" + param.Value + "], SqlDbType=[" + param.SqlDbType + "]"; log.Debug("Parameter val=[" + paramValues + "]"); } transaction = hdb.BeginTransaction("getTeatro"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); if (rdr.HasRows) { rdr.Read(); r = new TeatroDto() { idTeatro = Convert.ToInt32(rdr["idteatro"]), idCine = Convert.ToInt32(rdr["idcine"]), nombreTeatro = rdr["nombreteatro"].ToString(), telefono1Teatro = rdr["telefono1teatro"].ToString(), telefono2Teatro = rdr["telefono2teatro"].ToString(), telefono3Teatro = rdr["telefono3teatro"].ToString(), idMunicipioTeatro = Convert.ToInt32(rdr["idMunicipioTeatro"]), idDepeartamentoTeatro = Convert.ToInt32(rdr["idDepartamentoTeatro"]), direccionTeatro = rdr["direccionteatro"].ToString() }; } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Result sets to null"); } r = null; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Result sets to null"); } r = null; } } if (log.IsDebugEnabled) { if (r == null) { log.Debug("Result is NULL"); } else { log.Debug("Result sets to [" + r.ToString() + "]"); } log.Debug("getTeatro Ends"); } return r; }
/// <summary> /// Given a movie Id and a theater Id, retrieves the schedule for those params. /// </summary> /// <param name="idPelicula">Which movie to retrieve</param> /// <param name="idTeatro">Which theater to retrieve</param> /// <returns>A list of PeliculaFullInfoDto objecs </returns> public List<PeliculaFullInfoDto> getProgramacionPelicula(int idPelicula, int idTeatro) { if (log.IsDebugEnabled) { log.Debug("getProgramacionPelicula Starts"); } HandleDatabase hdb = null; SqlDataReader rdr = null; SqlTransaction transaction = null; List<PeliculaFullInfoDto> lstResultado = new List<PeliculaFullInfoDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() { ParameterName = "@idPelicula", Value = idPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() { ParameterName = "@idTeatro", Value = idTeatro,SqlDbType = SqlDbType.Int} }; String sql = "sp_obtenerProgramacionPelicula @idPelicula,@idTeatro"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } transaction = hdb.BeginTransaction("obtenerProgramacionPelicula"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray()); while (rdr.Read()) { lstResultado.Add(new PeliculaFullInfoDto() { idFormato = Convert.ToInt32(rdr["idFormato"]), nombreFormato = rdr["nombreFormato"].ToString(), idPelicula = Convert.ToInt32(rdr["idPelicula"]), idHorarioPelicula = Convert.ToInt32(rdr["idHorarioPelicula"]), annoHorarioPelicula = rdr["annoHorarioPelicula"].ToString(), mesHorarioPelicula = rdr["mesHorarioPelicula"].ToString(), diaHorarioPelicula = rdr["diaHorarioPelicula"].ToString(), nombreDiaSemanaHorarioPelicula = rdr["nombreDiaSemanaHorarioPelicula"].ToString(), idTeatro = Convert.ToInt32(rdr["idTeatro"]), nombreTeatro = rdr["nombreTeatro"].ToString(), frecuencia = Convert.ToInt32(rdr["frecuencia"]), horaPelicula = rdr["horaPelicula"].ToString(), minutoPelicula = rdr["minutoPelicula"].ToString(), sala = Convert.ToInt32(rdr["sala"]), }); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Empty list returned"); } lstResultado = new List<PeliculaFullInfoDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Empty list returned"); } lstResultado = new List<PeliculaFullInfoDto>(); } } if (log.IsDebugEnabled) { log.Debug("getProgramacionPelicula Ends"); } return lstResultado; }
/// <summary> /// Creates a new record in DB. /// </summary> /// <param name="pelicula">Movie details</param> /// <param name="operacion">Operation to accomplish: 1:create, 2:update</param> /// <returns></returns> public int createPelicula(DetallePeliculaDto pelicula, int operacion) { if (log.IsDebugEnabled) { log.Debug("createPelicula Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; int rslt = 0; try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); List<SqlParameter> paramList = new List<SqlParameter>() { new SqlParameter() {ParameterName = "@OPERACION", Value = operacion, SqlDbType = SqlDbType.Int}, new SqlParameter() { ParameterName = "@PELICULA", Value = pelicula.idPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() { ParameterName = "@NOMBRE", Value = pelicula.nombrePelicula, SqlDbType = SqlDbType.VarChar}, new SqlParameter() { ParameterName = "@USUARIO", Value = pelicula.idUsuarioCreador, SqlDbType = SqlDbType.Int}, new SqlParameter() { ParameterName = "@GENERO", Value = pelicula.idGeneroPelicula, SqlDbType = SqlDbType.Int}, new SqlParameter() { ParameterName = "@SINOPSIS", Value = pelicula.sinopsis, SqlDbType = SqlDbType.VarChar}, new SqlParameter() { ParameterName = "@IMAGEN", Value = pelicula.imagenCartelera, SqlDbType = SqlDbType.VarChar}, new SqlParameter() { ParameterName = "@URL", Value = pelicula.urlArticuloEc, SqlDbType = SqlDbType.VarChar}, new SqlParameter() { ParameterName = "@ACTIVO", Value = pelicula.enCartelera, SqlDbType = SqlDbType.VarChar} }; String sql = "sp_crearActualizarPelicula @OPERACION,@PELICULA,@NOMBRE,@USUARIO,@GENERO,@SINOPSIS,@IMAGEN,@URL,@ACTIVO"; var i = 1; if (log.IsDebugEnabled) { log.Debug("SQL=[" + sql + "]"); paramList.ForEach(p => { var paramValues = "ParameterName=[" + p.ParameterName + "], Value=[" + p.Value + "], SqlDbType=[" + p.SqlDbType + "]"; log.Debug("Parameter " + i++ + " val=[" + paramValues + "]"); }); } transaction = hdb.BeginTransaction("crearPelicula"); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, paramList.ToArray()); rslt = 1; } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Result sets to -1"); } rslt = -1; } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Result sets to -1"); } rslt = -1; } } if (log.IsDebugEnabled) { log.Debug("rslt=[" + rslt + "]"); log.Debug("createPelicula Ends"); } return rslt; }
/// <summary> /// Get all records given name entity. /// </summary> /// <param name="nombreEntidad">The entity to retrieve from</param> /// <returns>A list of EntidadDto objects, empty if no records found</returns> public List<EntidadDto> getValoresEntidad(string nombreEntidad) { if (log.IsDebugEnabled) { log.Debug("getValoresEntidad Starts"); } HandleDatabase hdb = null; SqlTransaction transaction = null; SqlDataReader rdr = null; List<EntidadDto> listaResultado = new List<EntidadDto>(); try { hdb = new HandleDatabase(Settings.Connection); hdb.Open(); SqlParameter param = new SqlParameter(); param.ParameterName = "@ne"; param.Value = nombreEntidad.Trim().ToString(); param.SqlDbType = SqlDbType.VarChar; String sql = "sp_obtenerValoresEntidad @ne"; if (log.IsDebugEnabled) { log.Debug("sql=[" + sql + "]"); } transaction = hdb.BeginTransaction(sql); rdr = hdb.ExecSelectSQLStmtAsReader(transaction, sql, param); EntidadDto entidad; while (rdr.Read()) { entidad = new EntidadDto(); entidad.idEntidad = Convert.ToInt32(rdr["IDENTIDAD"]); entidad.codEntidad = Convert.ToInt32(rdr["CODENTIDAD"]); entidad.nombreEntidad = rdr["NOMBREENTIDAD"].ToString(); entidad.valorEntidad = rdr["VALORENTIDAD"].ToString(); entidad.descripcionEntidad = rdr["DESCRIPCIONENTIDAD"].ToString(); listaResultado.Add(entidad); } } catch (Exception ex) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + ex.Message); log.Fatal("Exception trace=[" + ex.StackTrace + "]"); log.Fatal("Empty list returned"); } listaResultado = new List<EntidadDto>(); } finally { try { if (rdr != null) { rdr.Close(); } if (transaction != null) { transaction.Commit(); } if (hdb != null) { hdb.Close(); } } catch (Exception e) { if (log.IsFatalEnabled) { log.Fatal("Exception occurred " + e.Message); log.Fatal("Exception trace=[" + e.StackTrace + "]"); log.Fatal("Empty list returned"); } listaResultado = new List<EntidadDto>(); } } if (log.IsDebugEnabled) { log.Debug("getValoresEntidad Ends"); } return listaResultado; }