public bool student_insert(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "INSERT INTO hm_student (student_name, student_usn, student_phone, student_email, room_id, mess) VALUES (@student_name, @student_usn, @student_phone, @student_email, @room_id, @mess)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@student_name", h.student_name);
cmd.Parameters.AddWithValue("@student_usn", h.student_usn);
cmd.Parameters.AddWithValue("@student_phone", h.student_phone);
cmd.Parameters.AddWithValue("@student_email", h.student_email);
cmd.Parameters.AddWithValue("@room_id", h.room_id);
cmd.Parameters.AddWithValue("@mess", h.mess);
conn.Open();
int rows = cmd.ExecuteNonQuery();
string sql1 = "UPDATE hm_rooms SET no_of_beds = no_of_beds - 1 WHERE room_id = @room_id AND no_of_beds >= 1";
SqlCommand cmd1 = new SqlCommand(sql1, conn);
cmd1.Parameters.AddWithValue("@room_id", h.room_id);
cmd1.ExecuteNonQuery();
string sql2 = "UPDATE hm_rooms SET availability = 'NO' WHERE no_of_beds = 0";
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd2.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to add the student, Try Again.");
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool student_delete(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
conn.Open();
string sql0 = "UPDATE hm_rooms SET no_of_beds = no_of_beds + 1 WHERE room_id IN (SELECT room_id FROM hm_student WHERE student_id = @student_id)";
SqlCommand cmd0 = new SqlCommand(sql0, conn);
cmd0.Parameters.AddWithValue("@student_id", h.student_id);
int rows0 = cmd0.ExecuteNonQuery();
string sql1 = "UPDATE hm_rooms SET availability = 'YES' WHERE availability = 'NO' AND room_id IN (SELECT room_id FROM hm_student WHERE student_id = @student_id)";
SqlCommand cmd1 = new SqlCommand(sql1, conn);
cmd1.Parameters.AddWithValue("@student_id", h.student_id);
int rows1 = cmd1.ExecuteNonQuery();
string sql = "DELETE FROM hm_student WHERE student_id = @student_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@student_id", h.student_id);
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to delete, Try Again!");
}
finally
{
}
return(isSuccess);
}
public bool room_insert(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "INSERT INTO hm_rooms (room_no, room_type, no_of_beds, price, description, availability, total_beds) VALUES (@room_no, @room_type, @no_of_beds, @price, @description, @availability, @no_of_beds)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@room_no", h.room_no);
cmd.Parameters.AddWithValue("@room_type", h.room_type);
cmd.Parameters.AddWithValue("@no_of_beds", h.no_of_beds);
cmd.Parameters.AddWithValue("@price", h.price);
cmd.Parameters.AddWithValue("@description", h.description);
cmd.Parameters.AddWithValue("@availability", h.availability);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to add room, Try Again.");
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Select(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
DataTable dt = new DataTable();
try
{
string sql = "SELECT * FROM hm_login WHERE username=@username AND password=@password";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@username", h.username);
cmd.Parameters.AddWithValue("@password", h.password);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
conn.Open();
int i = cmd.ExecuteNonQuery();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
UserID = h.username;
welcome settingsForm = new welcome();
settingsForm.Show();
isSuccess = true;
}
else
{
MessageBox.Show("Please enter Correct Username or Password");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed");
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool mess_insert(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "INSERT INTO hm_mess (item_name, item_quantity, total_price, month, year) VALUES (@item_name, @item_quantity, @total_price, @month, @year)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@item_name", h.item_name);
cmd.Parameters.AddWithValue("@item_quantity", h.item_quantity);
cmd.Parameters.AddWithValue("@total_price", h.total_price);
cmd.Parameters.AddWithValue("@month", h.month);
cmd.Parameters.AddWithValue("@year", h.year);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to add room, Try Again.");
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool student_update(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "UPDATE hm_student SET student_name = @student_name, student_usn = @student_usn, student_phone = @student_phone, student_email = @student_email, mess = @mess WHERE student_id = @student_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@student_id", h.student_id);
cmd.Parameters.AddWithValue("@student_name", h.student_name);
cmd.Parameters.AddWithValue("@student_usn", h.student_usn);
cmd.Parameters.AddWithValue("@student_phone", h.student_phone);
cmd.Parameters.AddWithValue("@student_email", h.student_email);
cmd.Parameters.AddWithValue("@mess", h.mess);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to update, Try Again!");
}
finally
{
}
return(isSuccess);
}
public bool room_delete(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
try
{
string sql = "DELETE FROM hm_rooms WHERE room_id = @room_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@room_id", h.room_id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch
{
MessageBox.Show("Failed to delete the room, Try Again.");
}
finally
{
}
return(isSuccess);
}
public bool room_update(hostelClass h)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstring);
conn.Open();
try
{
SqlCommand cmd1 = new SqlCommand("SELECT total_beds FROM hm_rooms WHERE room_id = @room_id", conn);
cmd1.Parameters.AddWithValue("@room_id", h.room_id);
SqlDataReader old_total_beds = null;
old_total_beds = cmd1.ExecuteReader();
while (old_total_beds.Read())
{
old_beds = old_total_beds["total_beds"].ToString();
//MessageBox.Show(old_beds);
}
string sql = "UPDATE hm_rooms SET room_no = @room_no, room_type = @room_type, total_beds = @no_of_beds, price = @price, description = @description WHERE room_id = @room_id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@room_id", h.room_id);
cmd.Parameters.AddWithValue("@room_no", h.room_no);
cmd.Parameters.AddWithValue("@room_type", h.room_type);
cmd.Parameters.AddWithValue("@no_of_beds", h.no_of_beds);
cmd.Parameters.AddWithValue("@price", h.price);
cmd.Parameters.AddWithValue("@description", h.description);
old_total_beds.Close();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
string sql0 = "UPDATE hm_rooms SET no_of_beds = no_of_beds + (total_beds - @old_beds) WHERE room_id = @room_id";
SqlCommand cmd0 = new SqlCommand(sql0, conn);
cmd0.Parameters.AddWithValue("@room_id", h.room_id);
cmd0.Parameters.AddWithValue("@old_beds", Convert.ToInt32(h.old_beds));
cmd0.ExecuteNonQuery();
string sql2 = "UPDATE hm_rooms SET availability = 'NO' WHERE no_of_beds < 1";
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd2.ExecuteNonQuery();
string sql3 = "UPDATE hm_rooms SET availability = 'YES' WHERE no_of_beds > 0";
SqlCommand cmd3 = new SqlCommand(sql3, conn);
cmd3.ExecuteNonQuery();
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to update the details, Try again." + ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}