public void SqlCommandAtualizaReserva(SqlConnection conexao, Reserva reserva) { SqlCommand comando = new SqlCommand(null, conexao); // Create and prepare an SQL statement. comando.CommandText = "UPDATE Reserva SET id_filial=@id_filial, id_sala=@id_sala, dt_inicio=@dt_inicio, dt_fim=@dt_fim, id_responsavel=@id_responsavel, dv_cafe=@dv_cafe, qt_cafe=@qt_cafe, nm_descricao=@nm_descricao " + "WHERE id_reserva = @id_reserva"; SqlParameter id_filial = new SqlParameter("@id_filial", SqlDbType.UniqueIdentifier, 0); SqlParameter id_sala = new SqlParameter("@id_sala", SqlDbType.UniqueIdentifier, 0); SqlParameter dt_inicio = new SqlParameter("@dt_inicio", SqlDbType.DateTime, 0); SqlParameter dt_fim = new SqlParameter("@dt_fim", SqlDbType.DateTime, 0); SqlParameter id_responsavel = new SqlParameter("@id_responsavel", SqlDbType.UniqueIdentifier, 0); SqlParameter dv_cafe = new SqlParameter("@dv_cafe", SqlDbType.Bit, 0); SqlParameter qt_cafe = new SqlParameter("@qt_cafe", SqlDbType.SmallInt, 0); SqlParameter nm_descricao = new SqlParameter("@nm_descricao", SqlDbType.Text, 200); SqlParameter id_reserva = new SqlParameter("@id_reserva", SqlDbType.UniqueIdentifier, 0); id_filial.Value = reserva.id_filial; id_sala.Value = reserva.id_sala; dt_inicio.Value = reserva.dt_inicio; dt_fim.Value = reserva.dt_fim; id_responsavel.Value = reserva.id_responsavel; dv_cafe.Value = reserva.dv_cafe; qt_cafe.Value = reserva.qt_cafe; nm_descricao.Value = reserva.nm_descricao; id_reserva.Value = reserva.id_reserva; comando.Parameters.Add(id_filial); comando.Parameters.Add(id_sala); comando.Parameters.Add(dt_inicio); comando.Parameters.Add(dt_fim); comando.Parameters.Add(id_responsavel); comando.Parameters.Add(dv_cafe); comando.Parameters.Add(qt_cafe); comando.Parameters.Add(nm_descricao); comando.Parameters.Add(id_reserva); comando.Prepare(); comando.ExecuteNonQuery(); }
public Guid SqlCommandInsereReserva(SqlConnection conexao, Reserva reserva) { SqlCommand comando = new SqlCommand(null, conexao); // Create and prepare an SQL statement. comando.CommandText = "INSERT INTO Reserva (id_filial, id_sala, dt_inicio, dt_fim, id_responsavel, dv_cafe, qt_cafe, nm_descricao) " + "OUTPUT INSERTED.id_reserva " + "VALUES (@id_filial, @id_sala, @dt_inicio, @dt_fim, @id_responsavel, @dv_cafe, @qt_cafe, @nm_descricao)"; SqlParameter id_filial = new SqlParameter("@id_filial", SqlDbType.UniqueIdentifier, 0); SqlParameter id_sala = new SqlParameter("@id_sala", SqlDbType.UniqueIdentifier, 0); SqlParameter dt_inicio = new SqlParameter("@dt_inicio", SqlDbType.DateTime, 0); SqlParameter dt_fim = new SqlParameter("@dt_fim", SqlDbType.DateTime, 0); SqlParameter id_responsavel = new SqlParameter("@id_responsavel", SqlDbType.UniqueIdentifier, 0); SqlParameter dv_cafe = new SqlParameter("@dv_cafe", SqlDbType.Bit, 0); SqlParameter qt_cafe = new SqlParameter("@qt_cafe", SqlDbType.SmallInt, 0); SqlParameter nm_descricao = new SqlParameter("@nm_descricao", SqlDbType.Text, 200); id_filial.Value = reserva.id_filial; id_sala.Value = reserva.id_sala; dt_inicio.Value = reserva.dt_inicio; dt_fim.Value = reserva.dt_fim; id_responsavel.Value = reserva.id_responsavel; dv_cafe.Value = reserva.dv_cafe; qt_cafe.Value = reserva.qt_cafe; nm_descricao.Value = reserva.nm_descricao; comando.Parameters.Add(id_filial); comando.Parameters.Add(id_sala); comando.Parameters.Add(dt_inicio); comando.Parameters.Add(dt_fim); comando.Parameters.Add(id_responsavel); comando.Parameters.Add(dv_cafe); comando.Parameters.Add(qt_cafe); comando.Parameters.Add(nm_descricao); Guid id_reserva = (Guid)comando.ExecuteScalar(); return(id_reserva); }
public Retorno Reserva(Stream res) { try { StreamReader reader = new StreamReader(res); string JSONdata = reader.ReadToEnd(); JavaScriptSerializer jss = new JavaScriptSerializer(); Reserva reserva = jss.Deserialize <Reserva>(JSONdata); Retorno retorno = new Retorno(); if (reserva == null) { retorno.CodigoRetorno = 0; retorno.DescricaoRetorno = "Falha ao deserializar"; } else { Consulta conGetReservas = new Consulta(); List <Reserva> reservasintervalo = conGetReservas.GetReservasIntervalo(reserva.id_reserva, reserva.dt_inicio, reserva.dt_fim, reserva.id_sala, reserva.id_filial); if (reservasintervalo.Count == 0) { CadastroBancoDados banco = new CadastroBancoDados(); SqlConnection conexao = banco.abrirConexao(); if (reserva.id_reserva.ToString().Equals("00000000-0000-0000-0000-000000000000")) { retorno.guid = banco.SqlCommandInsereReserva(conexao, reserva); retorno.CodigoRetorno = 1; retorno.DescricaoRetorno = "Reserva inserida"; } else { banco.SqlCommandAtualizaReserva(conexao, reserva); retorno.guid = reserva.id_reserva; retorno.CodigoRetorno = 1; retorno.DescricaoRetorno = "Reserva atualizada"; } banco.fecharConexao(conexao); } else { Retorno retornoint = new Retorno(); retornoint.CodigoRetorno = 0; retornoint.DescricaoRetorno = "Já existe reunião para o horário solicitado"; return(retornoint); } } return(retorno); } catch (Exception error) { Retorno retorno = new Retorno(); retorno.CodigoRetorno = 0; retorno.DescricaoRetorno = "Ocorreu o erro:" + error.Message; return(retorno); } }
public List <Reserva> SqlCommandConsultaReservaIntervalo(SqlConnection conexao, Guid id_reserva, DateTime dt_inicio, DateTime dt_fim, Guid id_sala, Guid id_filial) { SqlDataReader reader = null; List <Reserva> reservas = new List <Reserva>(); try { SqlCommand comando = new SqlCommand(null, conexao); comando.CommandText = "SELECT * FROM Reserva " + "WHERE (@dt_inicio between dt_inicio and dt_fim OR " + "@dt_fim between dt_inicio and dt_fim OR " + "(@dt_inicio < dt_inicio AND " + "@dt_fim > dt_fim))"; SqlParameter parametro_inicio = new SqlParameter(); parametro_inicio.ParameterName = "@dt_inicio"; parametro_inicio.Value = dt_inicio; comando.Parameters.Add(parametro_inicio); SqlParameter parametro_fim = new SqlParameter(); parametro_fim.ParameterName = "@dt_fim"; parametro_fim.Value = dt_fim; comando.Parameters.Add(parametro_fim); comando = SqlAddParametro(conexao, comando, id_reserva, "id_reserva"); comando = SqlAddParametro(conexao, comando, id_sala, "id_sala"); comando = SqlAddParametro(conexao, comando, id_filial, "id_filial"); reader = comando.ExecuteReader(); while (reader.Read()) { Reserva reserva = new Reserva(); reserva.id_reserva = reader.GetGuid(reader.GetOrdinal("id_reserva")); reserva.id_filial = reader.GetGuid(reader.GetOrdinal("id_filial")); reserva.id_sala = reader.GetGuid(reader.GetOrdinal("id_sala")); reserva.dt_inicio = Convert.ToDateTime(reader["dt_inicio"].ToString()); reserva.dt_fim = Convert.ToDateTime(reader["dt_fim"].ToString()); reserva.id_responsavel = reader.GetGuid(reader.GetOrdinal("id_responsavel")); reserva.dv_cafe = Convert.ToBoolean(reader["dv_cafe"].ToString()); reserva.qt_cafe = Convert.ToInt16(reader["qt_cafe"].ToString()); reserva.nm_descricao = reader["nm_descricao"].ToString(); reservas.Add(reserva); } return(reservas); } catch (Exception e) { Reserva reserva = new Reserva(); reserva.id_reserva = new Guid(); reserva.nm_descricao = "Ocorreu o erro:" + e.Message; reservas.Add(reserva); return(reservas); } finally { // close reader if (reader != null) { reader.Close(); } } }