public static bool insertNewPrenotazione(PrenotazioneCliente p) { string connectionString = WebConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; string query = "insert into [dbo].[Prenotazioni] ([ID], [DataP], [Cliente], [NPostiP]) values ( newid(),@datap, @cliente, @npostip)"; using (SqlConnection connection = new SqlConnection(connectionString)) { try { SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@datap", p.Date); command.Parameters.AddWithValue("@cliente", p.Cliente); command.Parameters.AddWithValue("@npostip", p.Coperti); connection.Open(); command.ExecuteNonQuery(); return(true); } catch (Exception ex) { return(false); } finally { connection.Close(); } return(false); } }
protected void BTNSubmit_Click(object sender, EventArgs e) { PrenotazioneCliente prenot = new PrenotazioneCliente(); prenot.Date = Calendar.SelectedDate; prenot.Coperti = Convert.ToInt32(TXTCoperti.Text); prenot.Cliente = Session["username"].ToString(); if (DAL.verificaDisponibilita(prenot)) { if (DAL.insertNewPrenotazione(prenot)) { LBLOutput.Text = "Prenotazione registrata con successo"; generatePrenotazioniTable(); } else { LBLOutput.Text = "Prenotazione non avvenuta"; } } else { LBLOutput.Text = "Prova altra data, coperti richiesti superiori ai disponibili (100)"; } }
public static List <PrenotazioneCliente> getPrenotazioni(string usr) { List <PrenotazioneCliente> prenotazioni = new List <PrenotazioneCliente>(); string connectionString = WebConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; string query = "SELECT [ID],[DataP],[NPostiP] FROM [dbo].[Prenotazioni] WHERE [Cliente] = @username order by DataP DESC"; using (SqlConnection connection = new SqlConnection(connectionString)) { try { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@username", usr); connection.Open(); using (SqlDataAdapter da = new SqlDataAdapter(command)) { da.Fill(dt); } foreach (DataRow row in dt.Rows) { PrenotazioneCliente p = new PrenotazioneCliente(); p.ID = Guid.Parse(row["ID"].ToString()); p.Date = (DateTime)row["DataP"]; p.Coperti = (int)row["NPostiP"]; prenotazioni.Add(p); } } catch (Exception ex) { } finally { connection.Close(); } } return(prenotazioni); }
public static bool verificaDisponibilita(PrenotazioneCliente p) { string connectionString = WebConfigurationManager.ConnectionStrings["MainDB"].ConnectionString; string query = "SELECT * FROM [dbo].[Disponibilita] WHERE [DataP]= @datap"; string query_2 = "INSERT INTO [dbo].[Disponibilita] ([DataP]) VALUES (@datap)"; string query_3 = "UPDATE [dbo].[Disponibilita] SET [CopertiDisp] = ([CopertiDisp]-@coperti) WHERE [DataP]= @datap"; using (SqlConnection connection = new SqlConnection(connectionString)) { try { DataTable dt = new DataTable(); SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@datap", p.Date); connection.Open(); command.ExecuteNonQuery(); using (SqlDataAdapter da = new SqlDataAdapter(command)) { da.Fill(dt); } if (dt != null) { if (dt.Rows.Count > 0) { int postiDisponibili = int.Parse(dt.Rows[0]["CopertiDisp"].ToString()); DateTime data = DateTime.Parse(dt.Rows[0]["DataP"].ToString()); if (postiDisponibili >= p.Coperti) { //scala i posti alla data in tabella SqlCommand command_3 = new SqlCommand(query_3, connection); command_3.Parameters.AddWithValue("@datap", p.Date); command_3.Parameters.AddWithValue("@coperti", p.Coperti); command_3.ExecuteNonQuery(); return(true); } } else { //inserisci data nella tabella e scala i posti SqlCommand command_2 = new SqlCommand(query_2, connection); command_2.Parameters.AddWithValue("@datap", p.Date); command_2.ExecuteNonQuery(); //scala i posti SqlCommand command_3 = new SqlCommand(query_3, connection); command_3.Parameters.AddWithValue("@datap", p.Date); command_3.Parameters.AddWithValue("@coperti", p.Coperti); command_3.ExecuteNonQuery(); return(true); } } } catch (Exception ex) { } finally { connection.Close(); } return(false); } }