private void btnSave_Click(object sender, EventArgs e)
{
if (txtTermName.Text == "")
{
txtTermName.BackColor = Color.MistyRose;
MessageBox.Show("Please Enter Term Name");
txtTermName.BackColor = Color.White;
return;
}
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "insert into schoolterm (termname, startdate, enddate) values ('" + txtTermName.Text + "','" + dtpStartDate.Text + "','" + dtpEndDate.Text + "') ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Successfully Saved", "Events Records", MessageBoxButtons.OK, MessageBoxIcon.Information);
SQLConn.ConnClose();
Clears();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.ToString());
}
}
public void LoadSchoolTerms()
{
try
{
SQLConn.ConnOpen();
DataTable dt = new DataTable();
DataGridView1.DataSource = null;
SQLConn.sqL = "SELECT id, termname, startdate, enddate FROM schoolterm order by startdate";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(SQLConn.sqL, SQLConn.conn);
da.Fill(dt);
DataGridView1.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (txtTermName.Tag == null)
{
MessageBox.Show("Select Term in Grid to Edit");
return;
}
try
{
SQLConn.ConnOpen();
string startdate = tm.TimeToString(dtpStartDate.Value);
string enddate = tm.TimeToString(dtpEndDate.Value);
SQLConn.cmd.CommandText = "update schoolterm set termname='" + txtTermName.Text + "', startdate ='" + startdate + "', enddate='" + enddate + "' WHERE id=" + txtTermName.Tag + " ";
SQLConn.cmd.ExecuteNonQuery();
SQLConn.conn.Close();
MessageBox.Show("Updated Saved", "Term Records", MessageBoxButtons.OK, MessageBoxIcon.Information);
Clears();
LoadSchoolTerms();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.ToString());
}
}
private void AddSchool()
{
try {
SQLConn.ConnOpen();
SQLConn.sqL = "insert into school(schoolname,address,phone,email,website) values (@schoolname,@address,@phone,@email,@website)";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@schoolname", cmbSchool.Text);
SQLConn.cmd.Parameters.AddWithValue("@address", txtAddress.Text);
SQLConn.cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
SQLConn.cmd.Parameters.AddWithValue("@email", cmbEmail.Text);
SQLConn.cmd.Parameters.AddWithValue("@website", txtWeb.Text);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("School Information Saved Successfully", "Report Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
ResetAll();
}
catch (Exception ex) {
MessageBox.Show(this, ex.Message);
}
}
private void UpdateSchool()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update school SET schoolname=@schoolname,address=@address, email=@email,website=@website where id= " + cmbSchool.SelectedValue + " ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@schoolname", cmbSchool.Text);
SQLConn.cmd.Parameters.AddWithValue("@address", txtAddress.Text);
SQLConn.cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
SQLConn.cmd.Parameters.AddWithValue("@email", cmbEmail.Text);
SQLConn.cmd.Parameters.AddWithValue("@website", txtWeb.Text);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("School Information Updated Successfully", "Report Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message);
}
}
private void Update_record_Click(object sender, EventArgs e)
{
try{
SQLConn.ConnOpen();
string cb = "update subject set subjectcode=@d1, subjectname=@d2,classname=@d4 where subjectcode='" + txtSubjectCode.Text + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d1", txtSubjectCode.Text.Trim()));
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d2", txtSubjectName.Text.Trim()));
SQLConn.cmd.Parameters.Add(new NpgsqlParameter("@d4", cmbClass.Text));
SQLConn.cmd.ExecuteNonQuery();
MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
st2 = "Updated subject whose subjectCode is'" + txtSubjectCode.Text + "'";
cf.LogFunc(st1, System.DateTime.Now, st2);
Update_record.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
public void LoadFeeCategories()
{
try
{
conn = new NpgsqlConnection(SQLConn.DBcon);
conn.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
DataGridView1.DataSource = null;
SQLConn.sqL = "SELECT * FROM feetype order by id";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(SQLConn.sqL, conn);
da.Fill(dt);
DataGridView1.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void LoadSchool()
{
try {
SQLConn.ConnOpen();
SQLConn.sqL = "Select id, address,phone,fax,email,website from school where id='" + cmbSchool.SelectedValue + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read() == true)
{
cmbSchool.SelectedValue = SQLConn.dr["id"].ToString();
txtAddress.Text = SQLConn.dr["id"].ToString();
txtPhone.Text = SQLConn.dr["phone"].ToString();
cmbEmail.Text = SQLConn.dr["email"].ToString();
txtWeb.Text = SQLConn.dr["website"].ToString();
DGV.Focus();
}
else
{
ResetAll();
}
}
catch (NpgsqlException ex) {
MessageBox.Show(ex.ToString());
}
}
private void AutocompleteScholarNo()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT distinct admissionno FROM BusFeePayment";
da.SelectCommand = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
dt.Clear();
da.Fill(dt);
AdmissionNo.Items.Clear();
foreach (DataRow drow in dt.Rows)
{
AdmissionNo.Items.Add(drow[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Course_SelectedIndexChanged(object sender, EventArgs e)
{
txtsection.Items.Clear();
txtsection.Text = "";
txtsection.Enabled = true;
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select distinct RTRIM(section) from BusFeePayment,BusHolders,Student where BusFeePayment.AdmissionNo=Student.AdmissionNo and BusHolders.AdmissionNo=Student.AdmissionNo and Student.class= '" + txtclass.Text + "'";
SQLConn.dr = SQLConn.cmd.ExecuteReader();
while (SQLConn.dr.Read())
{
txtsection.Items.Add(SQLConn.dr[0]);
}
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void InitialiseDB()
{
lblinit.Text = "Initialising Database. Please Wait..";
lblinit.ForeColor = Color.Red;
lblinit.Visible = true;
txtSQL.Visible = true;
try
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string file = appPath + "\\SQL\\schoolguru.sql";
SQLConn.sqL = File.ReadAllText(file).ToString();
txtSQL.Text = SQLConn.sqL;
SQLConn.ConnOpen();
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
int r = SQLConn.cmd.ExecuteNonQuery();
if (r > 0)
{
MessageBox.Show("Database has been Initialised Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
SQLConn.ConnClose();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void AutocompleteClass()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT distinct RTRIM(Class) FROM BusFeePayment,Student,BusHolders where BusFeePayment.AdmissionNo=Student.AdmissionNo and BusHolders.AdmissionNo=Student.AdmissionNo";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
da.Fill(dt);
txtclass.Items.Clear();
foreach (DataRow drow in dt.Rows)
{
txtclass.Items.Add(drow[0].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void delete_records()
{
try
{
int RowsAffected = 0;
SQLConn.ConnOpen();
SQLConn.sqL = "delete from examtype where id = '" + txtExamName.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
if (RowsAffected > 0)
{
MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
string st2 = "Deleted Exam '" + txtExamName.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
Reset();
}
else
{
MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reset();
}
SQLConn.ConnClose();
GetData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ScholarNo_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select RTrim(FeePaymentID)[Fee Payment ID], RTRIM(Student.AdmissionNo)[Admission No.],RTRIM(Studentname)[Student Name],RTRIM(Student.Class)[Class],RTRIM(Section)[Section],RTRIM(Transportation.SourceLocation)[Source Location],RTRIM(BusFeePayment.BusCharges)[Bus Charges],RTRIM(DateOfPayment)[Payment Date],RTRIM(ModeOfPayment)[Mode Of Payment],RTRIM(PaymentModeDetails)[Payment Mode Details],RTRIM(TotalPaid)[Total Paid],RTRIM(Fine)[Fine],RTRIM(DueFees)[Due Fees] from BusFeePayment,Student,Transportation,BusHolders where Student.AdmissionNo=BusHolders.AdmissionNo and BusFeePayment.AdmissionNo=Student.AdmissionNo and Transportation.SourceLocation=BusHolders.SourceLocation and Busfeepayment.AdmissionNo= '" + AdmissionNo.Text + "'order by DateOfPayment";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter();
DataSet myDataSet = new DataSet();
da.Fill(myDataSet, "BusFeePayment");
da.Fill(myDataSet, "Transportation");
da.Fill(myDataSet, "Student");
da.Fill(myDataSet, "BusHolders");
dataGridView2.DataSource = myDataSet.Tables["BusFeePayment"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["Transportation"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["Student"].DefaultView;
dataGridView2.DataSource = myDataSet.Tables["BusHolders"].DefaultView;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void FillRecords()
{
try
{
string s = "doj between @date1 and @date2 ";
SQLConn.sqL = "select id, staffname, department, gender,dob,status, paddress,taddress,phone, mobile, doj,qualification,yop, designation, email, salary from staff WHERE 1=1 ";
if (txtStaffName.Text.Trim() != "")
{
SQLConn.sqL += " AND staffname ILIKE '%" + txtStaffName.Text + "%' ";
}
SQLConn.sqL += " order by staffname ";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void LoadEvents()
{
try
{
SQLConn.ConnOpen();
DataTable dt = new DataTable();
DGV.DataSource = null;
SQLConn.sqL = "SELECT id, eventname, startdate, enddate, manager, activity FROM event order by startdate asc";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
// data adapter making request from our connection
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
MessageBox.Show(dt.Rows.Count.ToString());
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Update_record_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update staffset staffname=@d2,department=@d3,gender=@d4,fathername=@d5,permanentaddress=@d6,temporaryaddress=@d7,phoneno=@d8,mobileno=@d9,dateofjoining=@d10,qualification=@d11,yearofexperience=@d12,designation=@d13,email=@d14,Basicsalary=@d15,picture=@d16,DOB=@d17,mothername=@d19,status=@d18 where staffid=@d1";
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("Successfully Updated", "staffRecord", MessageBoxButtons.OK, MessageBoxIcon.Information);
st2 = "Updated the Staff'" + txtStaffName.Text + "' having StaffID '" + txtStaffID.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
btnupdate.Enabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void DeleteUser()
{
try
{
SQLConn.ConnOpen();
if (txtUserId.Tag.ToString() == "1")
{
MessageBox.Show("Cannot Delete Super Admin");
return;
}
SQLConn.sqL = "delete from user_registration where id=@f1";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.Parameters.AddWithValue("@f1", txtUserId.Tag);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("Deleted Successfully");
Clears();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void Update_record_Click(object sender, EventArgs e)
{
if (!ValidEntry())
{
return;
}
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "update exam set examname='" + txtExamName.Text + "',examtype='" + cmbExamType.SelectedValue + "' , startdate='" + TM.TimeToString(dtpStart.Value) + "', schoolclass='" + cmbClass.Text + "' , schoolterm='" + cmbTerm.SelectedValue + "' , enddate='" + TM.TimeToString(dtpEnd.Value) + "' where id='" + txtExamName.Tag + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
Debug.WriteLine(SQLConn.sqL);
MessageBox.Show("Successfully updated", "Exam Details", MessageBoxButtons.OK, MessageBoxIcon.Information);
// st1 = lblUser.Text;
// st2 = "Updated Exam'" +txtExamName.Text+ "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
btnUpdate.Enabled = false;
ListExams();
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DeleteTeacherSubject()
{
try
{
int t = int.Parse(cmbStaff.SelectedValue.ToString());
string s = cmbSubject.SelectedValue.ToString();
SQLConn.sqL = "delete from staffsubject where id=" + int.Parse(btnDelete.Tag.ToString()) + "";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
Console.WriteLine(SQLConn.sqL);
st2 = "Deleted '" + groupBox1.Tag + "'";
MessageBox.Show(st2, "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
// cf.LogFunc(st1, System.DateTime.Now, st2);
Reset();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
public DataTable GetClassSubjects(string schoolclass)
{
try
{
SQLConn.sqL = "SELECT subjectcode, subjectname from subject Where classname='" + schoolclass + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
dt.Columns.Add("subjectcode", typeof(string));
dt.Columns.Add("subjectname", typeof(string));
dt.Rows.Add(new Object[] { "-1", "Select Subject" });
SQLConn.da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
finally
{
SQLConn.ConnClose();
SQLConn.cmd.Dispose();
}
}
private void button14_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select RTrim(FeePaymentID)[Fee Payment ID], RTRIM(Student.AdmissionNo)[Admission No.],RTRIM(Studentname)[Student Name],RTRIM(Student.Class)[Class],RTRIM(Section)[Section],RTRIM(Transportation.SourceLocation)[Source Location],RTRIM(BusFeePayment.BusCharges)[Bus Charges],RTRIM(DateOfPayment)[Payment Date],RTRIM(ModeOfPayment)[Mode Of Payment],RTRIM(PaymentModeDetails)[Payment Mode Details],RTRIM(TotalPaid)[Total Paid],RTRIM(Fine)[Fine],RTRIM(DueFees)[Due Fees] from BusFeePayment,Student,Transportation,BusHolders where Student.AdmissionNo=BusHolders.AdmissionNo and BusFeePayment.AdmissionNo=Student.AdmissionNo and Transportation.SourceLocation=BusHolders.SourceLocation and DateofPayment between @date1 and @date2 and Fine>0 order by DateOfPayment";
SQLConn.cmd.Parameters.AddWithValue("@date1", "DateOfPayment").Value = dateTimePicker1.Value.Date;
SQLConn.cmd.Parameters.AddWithValue("@date2", "DateOfPayment").Value = dateTimePicker2.Value.Date;
NpgsqlDataAdapter myDA = new NpgsqlDataAdapter(SQLConn.cmd);
DataSet myDataSet = new DataSet();
myDA.Fill(myDataSet, "BusFeePayment");
myDA.Fill(myDataSet, "Transportation");
myDA.Fill(myDataSet, "Student");
myDA.Fill(myDataSet, "BusHolders");
dataGridView5.DataSource = myDataSet.Tables["BusFeePayment"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["Transportation"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["Student"].DefaultView;
dataGridView5.DataSource = myDataSet.Tables["BusHolders"].DefaultView;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void SearchStudents()
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "SELECT admissionno,CONCAT(firstname, ' ' ,othername) as fullname,status , classection,termname,term, admissiondate,gender,category from studentdetail LEFT JOIN schoolterm on studentdetail.term=schoolterm.id WHERE 1=1";
if (int.Parse(cmbTerm.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND term ='" + cmbTerm.SelectedValue.ToString() + "'";
}
if (int.Parse(cmbClass.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND classection ='" + cmbClass.Text + "'";
}
if (int.Parse(cmbSection.SelectedValue.ToString()) > 0)
{
SQLConn.sqL += " AND section ='" + cmbSection.Text + "'";
}
SQLConn.sqL += " order by admissionno ";
Console.WriteLine(SQLConn.sqL);
DGV.DataSource = null;
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.da = new NpgsqlDataAdapter(SQLConn.cmd);
DataTable dt = new DataTable();
SQLConn.da.Fill(dt);
DGV.DataSource = dt;
SQLConn.ConnClose();
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void Form1_Load(object sender, EventArgs e)
{
if (!SQLConn.IsServerConnected())
{
MessageBox.Show("You database is not yet configured or is not configured correctly \n You will be redirected to Database configuration form ", "Database Configuration");
frmDatabaseConfig frm = new frmDatabaseConfig();
frm.ShowDialog();
this.Close();
}
}
private void delRecords()
{
SQLConn.sqL = "delete from ClassFeePayment where AdmissionNo='" + txtAdmNo.Text + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteNonQuery();
SQLConn.ConnClose();
MessageBox.Show("Records deleted Successsfully...");
ResetAll();
}
private void AddSubjectTeacher()
{
try
{
SQLConn.ConnOpen();
int st = int.Parse(cmbStaff.SelectedValue.ToString());
string s = cmbSubject.SelectedValue.ToString();
SQLConn.sqL = "select subjectcode from staffsubject where staffid=" + st + " AND subjectcode= '" + s + "'";
Console.WriteLine(SQLConn.sqL);
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
MessageBox.Show("Teacher Already Added for this subject Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
if ((SQLConn.dr != null))
{
SQLConn.dr.Close();
}
return;
}
SQLConn.sqL = "insert into staffsubject(staffid,subjectcode) VALUES ('" + cmbStaff.SelectedValue.ToString() + "','" + cmbSubject.SelectedValue.ToString() + "')";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
Console.WriteLine(SQLConn.sqL);
SQLConn.cmd.ExecuteNonQuery();
st2 = "Added New Teacher for is '" + cmbSubject.Text + "'";
MessageBox.Show(st2, "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void InitialiseDB_Click(object sender, EventArgs e)
{
if (SQLConn.IsServerConnected())
{
Interaction.MsgBox("Database Initialised Already!", MsgBoxStyle.Information, "Database Initialised");
}
else
{
InitialiseDB();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
SQLConn.ConnOpen();
SQLConn.sqL = "select classname , section from sclass where classname='" + txtClass.Text + "' and section='" + cmbSection.Text + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
MessageBox.Show("Record Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtClass.Text = "";
Reset();
txtClass.Focus();
if ((SQLConn.dr != null))
{
SQLConn.dr.Close();
}
return;
}
SQLConn.sqL = "insert into sclass(classname,section) VALUES ('" + txtClass.Text + "','" + cmbSection.Text + "')";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.cmd.ExecuteReader();
GetData();
st2 = "Added the New Class='" + txtClass.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnSave.Enabled = false;
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
SQLConn.cmd.Dispose();
SQLConn.ConnClose();
}
}
private void DeleteSchool()
{
try
{
int RowsAffected = 0;
SQLConn.ConnOpen();
SQLConn.sqL = "select from studentdetail where school=" + cmbSchool.SelectedValue + "";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
SQLConn.dr = SQLConn.cmd.ExecuteReader();
if (SQLConn.dr.Read())
{
MessageBox.Show("Unable to delete. Already in use", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
if ((SQLConn.dr != null))
{
SQLConn.dr.Close();
}
return;
}
SQLConn.sqL = "delete from school where id = '" + cmbSchool.SelectedValue + "'";
SQLConn.cmd = new NpgsqlCommand(SQLConn.sqL, SQLConn.conn);
RowsAffected = SQLConn.cmd.ExecuteNonQuery();
if (RowsAffected > 0)
{
MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
// st1 = lblUser.Text;
st2 = "Deleted the school'" + cmbSchool.Text + "'";
// cf.LogFunc(st1, System.DateTime.Now, st2);
}
else
{
MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
SQLConn.ConnClose();
//GetData();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void MoveClass()
{
StringCollection sqline = new StringCollection();
foreach (DataGridViewRow row in DGV.Rows)
{
DataGridViewCheckBoxCell c = (DataGridViewCheckBoxCell)row.Cells[0];
bool state = (bool)c.Value;
string admno = row.Cells["admissionno"].Value.ToString();
int term = int.Parse(cmbTermNext.SelectedValue.ToString());
string sclass = cmbClassNext.SelectedValue.ToString();
string up = "";
if (state == true)
{
up = "update studentdetail set term= " + term + ", sclass='" + sclass + "' WHERE admissionno= '" + admno + "'";
sqline.Add(up);
}
SQLConn.ConnOpen();
NpgsqlTransaction tr = (NpgsqlTransaction)SQLConn.conn.BeginTransaction();
try
{
for (int i = 0; i <= sqline.Count - 1; i++)
{
Console.WriteLine(sqline[i]);
NpgsqlCommand cmd = new NpgsqlCommand(sqline[i], SQLConn.conn);
cmd.ExecuteNonQuery();
}
tr.Commit();
MessageBox.Show("Class Batch Action Complete");
SQLConn.ConnClose();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
// Attempt to roll back the transaction.
try
{
tr.Rollback();
}
catch (Exception ex2)
{
MessageBox.Show(ex2.ToString());
}
}
}
}
