private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlCommand thisCommand = sv.thisConnection.CreateCommand();
thisCommand.CommandText =
"update charges set gas = '" + textBoxGasBill.Text + "' , water = '" + textBoxWaterBill.Text + "', utility = '" + textBoxUtility.Text + "' where no = 1";
thisCommand.Connection = sv.thisConnection;
thisCommand.CommandType = CommandType.Text;
//For Insert Data Into Sql//
thisCommand.ExecuteNonQuery();
MessageBox.Show("Updated");
sv.thisConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
this.Close();
}
private void btnLogin_Click(object sender, EventArgs e)
{
/*Home h = new Home();
* h.ShowDialog();
* this.Close();*/
dbconnection CN = new dbconnection();
CN.thisConnection.Open();
SqlCommand thisCommand = new SqlCommand();
thisCommand.Connection = CN.thisConnection;
thisCommand.CommandText = "SELECT username FROM admin WHERE username='******' AND password='******'";
SqlDataReader thisReader = thisCommand.ExecuteReader();
if (thisReader.Read())
{
Home h = new Home();
h.ShowDialog();
this.Close();
}
else
{
MessageBox.Show("Username or Password incorrect! Please give the Correct Username or Password.", "Caution", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void chargesForm_Load(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select * from charges where no=1";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
textBoxWaterBill.Text = (DR["water"].ToString());
textBoxGasBill.Text = (DR["gas"].ToString());
textBoxUtility.Text = (DR["utility"].ToString());
}
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select * from flat where flatName='" + comboBox1.Text.ToString() + "'";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
textBoxInfo.Text = (DR["info"].ToString());
textBoxRent.Text = (DR["rent"].ToString());
}
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlCommand thisCommand = sv.thisConnection.CreateCommand();
thisCommand.CommandText =
"update flat set info = '" + textBoxInfo.Text + "' , rent = '" + textBoxRent.Text + "' where flatName= '" + comboBox1.Text + "'";
thisCommand.Connection = sv.thisConnection;
thisCommand.CommandType = CommandType.Text;
//For Insert Data Into Sql//
thisCommand.ExecuteNonQuery();
MessageBox.Show("Updated");
sv.thisConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
this.Close();
}
private void comboBoxFlatName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select * from flat f,tenant t,charges c where f.flatName='" + comboBoxFlatName.Text.ToString() + "' and f.tenantID = t.tenantId";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
textBoxTenantName.Text = (DR["name"].ToString());
textBoxRent.Text = (DR["rent"].ToString());
textBoxBalance.Text = (DR["balance"].ToString());
textBoxGasBill.Text = (DR["gas"].ToString());
textBoxWaterBill.Text = (DR["water"].ToString());
textBoxUtilityBill.Text = (DR["utility"].ToString());
}
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnRemove_Click(object sender, EventArgs e)
{
try
{
dbconnection con = new dbconnection();
con.thisConnection.Open();
SqlCommand thisCommand1 = con.thisConnection.CreateCommand();
thisCommand1.CommandText =
"delete flat where flatName= '" + comboBox1.Text + "'";
thisCommand1.Connection = con.thisConnection;
thisCommand1.CommandType = CommandType.Text;
//For Insert Data Into Sql//
thisCommand1.ExecuteNonQuery();
MessageBox.Show("delete successfully");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void billForm_Load(object sender, EventArgs e)
{
textBoxYear.Text = DateTime.Now.ToString("yyyy");
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select flatName from flat";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
comboBoxFlatName.Items.Add(DR[0]);
}
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlCommand thisCommand = sv.thisConnection.CreateCommand();
string gender = "";
bool isChecked = radioBtnMale.Checked;
if (isChecked)
{
gender = radioBtnMale.Text;
}
else
{
gender = radioBtnFemale.Text;
}
Image img = pictureBoxTenant.Image;
byte[] arr;
ImageConverter converter = new ImageConverter();
arr = (byte[])converter.ConvertTo(img, typeof(byte[]));
Image img2 = pictureBoxNID.Image;
byte[] arr2;
ImageConverter converter2 = new ImageConverter();
arr2 = (byte[])converter.ConvertTo(img2, typeof(byte[]));
/*thisCommand.CommandText =
* "update tenant set gender = '" + gender + "',fatherName = '" + textBoxFatherName.Text + "' ,motherName = '" + textBoxMotherName.Text + "',occupation = '" + textBoxOccupation.Text + "' ,country = '" + textBoxCountry.Text + "' ,phoneNo = '" + textBoxPhoneNo.Text + "' ,email = '" + textBoxEmail.Text + "' ,address = '" + textBoxAddress.Text + "' where name= '" + comboBoxTenantName.Text + "'";
*/
thisCommand.CommandText =
"update tenant set rentDate = '" + dateOfRent.Text.ToString() + "' where name= '" + comboBoxTenantName.Text + "'";
// , rentDate = '" + dateOfRent.Text + "'
//,photo = '" + arr + "' ,nid = '" + arr2 + "'
thisCommand.Connection = sv.thisConnection;
thisCommand.CommandType = CommandType.Text;
//For Insert Data Into Sql//
thisCommand.ExecuteNonQuery();
MessageBox.Show("Updated");
sv.thisConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
this.Close();
}
private void btnSubmit_Click(object sender, EventArgs e)
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM record", sv.thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "record");
DataRow thisRow = thisDataSet.Tables["record"].NewRow();
try
{
thisRow["recordId"] = textBoxBillNo.Text;
thisRow["month"] = comboBoxMonth.Text;
thisRow["year"] = textBoxYear.Text;
thisRow["flatName"] = comboBoxFlatName.Text;
thisRow["tenantName"] = textBoxTenantName.Text;
thisRow["remarks"] = textBoxRemarks.Text;
thisRow["totalBill"] = textBoxTotalBill.Text;
thisRow["electricityBill"] = textBoxElectricityBill.Text;
thisRow["date"] = date.Text;
thisDataSet.Tables["record"].Rows.Add(thisRow);
thisAdapter.Update(thisDataSet, "record");
MessageBox.Show("Submitted");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
sv.thisConnection.Close();
this.Close();
}
private void btnSubmit_Click(object sender, EventArgs e)
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM flat", sv.thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "flat");
DataRow thisRow = thisDataSet.Tables["flat"].NewRow();
try
{
thisRow["flatName"] = textBoxFlatName.Text;
thisRow["info"] = textBoxInfo.Text;
thisRow["rent"] = textBoxRent.Text;
thisDataSet.Tables["flat"].Rows.Add(thisRow);
thisAdapter.Update(thisDataSet, "flat");
MessageBox.Show("Submitted");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
sv.thisConnection.Close();
this.Close();
}
private void BillingRecord_Load(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM record", sv.thisConnection);
//SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet ds = new DataSet();
thisAdapter.Fill(ds, "record");
dataGridView1.DataSource = ds.Tables[0];
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void tenantRegistration_Load(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select flatName from flat";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
comboBoxFlatName.Items.Add(DR[0]);
}
sv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT * FROM tenant", sv.thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "tenant");
DataRow thisRow = thisDataSet.Tables["tenant"].NewRow();
thisRow["name"] = textBoxName.Text;
string value = "";
bool isChecked = radioBtnMale.Checked;
if (isChecked)
{
value = radioBtnMale.Text;
}
else
{
value = radioBtnFemale.Text;
}
thisRow["gender"] = value.ToString();
thisRow["fatherName"] = textBoxFatherName.Text;
thisRow["motherName"] = textBoxMotherName.Text;
thisRow["phoneno"] = textBoxPhoneNo.Text;
thisRow["email"] = textBoxEmail.Text;
thisRow["balance"] = 0;
thisRow["occupation"] = textBoxOccupation.Text;
thisRow["country"] = textBoxCountry.Text;
thisRow["address"] = textBoxAddress.Text;
thisRow["rentDate"] = dateOfRent.Text;
Image img = pictureBoxTenant.Image;
byte[] arr;
ImageConverter converter = new ImageConverter();
arr = (byte[])converter.ConvertTo(img, typeof(byte[]));
thisRow["photo"] = arr;
Image img2 = pictureBoxNID.Image;
byte[] arr2;
ImageConverter converter2 = new ImageConverter();
arr2 = (byte[])converter.ConvertTo(img2, typeof(byte[]));
thisRow["nid"] = arr2;
thisRow["nidno"] = textBoxNID.Text;
thisDataSet.Tables["tenant"].Rows.Add(thisRow);
thisAdapter.Update(thisDataSet, "tenant");
sv.thisConnection.Close();
dbconnection sv1 = new dbconnection();
sv1.thisConnection.Open();
string id = "";
string Sql = "select tenantId from tenant where name='" + textBoxName.Text.ToString() + "'";
SqlCommand cmd = new SqlCommand(Sql, sv1.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
id = (DR["tenantId"].ToString());
}
sv1.thisConnection.Close();
dbconnection sv2 = new dbconnection();
sv2.thisConnection.Open();
SqlCommand thisCommand = sv2.thisConnection.CreateCommand();
thisCommand.CommandText =
"update flat set tenantId = '" + id + "' where flatName= '" + comboBoxFlatName.Text + "'";
thisCommand.Connection = sv2.thisConnection;
thisCommand.CommandType = CommandType.Text;
thisCommand.ExecuteNonQuery();
sv2.thisConnection.Close();
MessageBox.Show("Submitted");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
private void comboBoxTenantName_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
dbconnection sv = new dbconnection();
sv.thisConnection.Open();
string Sql = "select * from tenant where name='" + comboBoxTenantName.Text.ToString() + "'";
SqlCommand cmd = new SqlCommand(Sql, sv.thisConnection);
SqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
string gender = (DR["gender"].ToString());
if (radioBtnMale.Text.Equals(gender))
{
radioBtnMale.Checked = true;
radioBtnFemale.Checked = false;
}
else
{
radioBtnFemale.Checked = true;
radioBtnMale.Checked = false;
}
textBoxFatherName.Text = (DR["fatherName"].ToString());
textBoxMotherName.Text = (DR["motherName"].ToString());
textBoxOccupation.Text = (DR["occupation"].ToString());
textBoxCountry.Text = (DR["country"].ToString());
textBoxPhoneNo.Text = (DR["phoneNo"].ToString());
textBoxEmail.Text = (DR["email"].ToString());
textBoxAddress.Text = (DR["address"].ToString());
//date
dateOfRent.Text = (DR["rentDate"].ToString());
}
sv.thisConnection.Close();
//for image retrieve
dbconnection svv = new dbconnection();
svv.thisConnection.Open();
SqlCommand cmdSelect = new SqlCommand("select photo from tenant where name='" + comboBoxTenantName.Text.ToString() + "'", svv.thisConnection);
byte[] barrImg = (byte[])cmdSelect.ExecuteScalar();
string strfn = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write);
fs.Write(barrImg, 0, barrImg.Length);
fs.Flush();
fs.Close();
pictureBoxTenant.Image = Image.FromFile(strfn);
SqlCommand cmdSelect2 = new SqlCommand("select nid from tenant where name='" + comboBoxTenantName.Text.ToString() + "'", svv.thisConnection);
byte[] barrImg2 = (byte[])cmdSelect2.ExecuteScalar();
string strfn2 = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs2 = new FileStream(strfn2, FileMode.CreateNew, FileAccess.Write);
fs2.Write(barrImg2, 0, barrImg2.Length);
fs2.Flush();
fs2.Close();
pictureBoxNID.Image = Image.FromFile(strfn2);
svv.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}