public bool UserLoginValidation(Admin ad)
{
string query = "SELECT * from Admin WHERE AID = '" + ad.AId + "' AND APassword='******'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
// dcc.CloseConnection();
if (sdr.Read())
{
ad.AName = sdr["AName"].ToString();
ad.ASal = Convert.ToDouble(sdr["ASal"]);
ad.AEmail = sdr["AEmail"].ToString();
ad.APhone = sdr["APhone"].ToString();
dcc.CloseConnection();
return(true);
}
else
{
dcc.CloseConnection();
return(false);
}
}
public bool InsertLibrarian(Librarian librarian, byte[] images)
{
string query = "INSERT into Librarian VALUES ( '" + librarian.Id + "','" + librarian.Email + "','" + librarian.Contact + "','" + librarian.Address + "','" + librarian.Salary + "', '" + librarian.FirstName + "', '" + librarian.LastName + "', '" + librarian.Password + "', '" + librarian.Confirm + "', '" + @librarian.Image + "','" + librarian.Surname + "', '" + librarian.UserName + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
/* string sqlQuery = "Insert into Librarian(Image)values(@image) WHERE Id='" + librarian.Id + "'";
* try
* {
* myCommand = new SqlCommand(sqlQuery);
* myCommand.Parameters.Add(new SqlParameter("@image", images));
* dcc.ConnectWithDB();
* int n = dcc.ExecuteSQL(query);
* dcc.CloseConnection();
* // MessageBox.Show(N.ToString() + "Data Saved Successfully..!");
* }
* catch
* {
* //MessageBox.Show("Please insert your image");
* }
*/
}
public bool InsertStudent(Student student, byte[] images)
{
string query = "INSERT into Student VALUES ('" + @student.Image + "', '" + student.Id + "', '" + student.Firstname + "', '" + student.Surname + "', '" + student.Lastname + "', '" + student.Contact + "', '" + student.Dept + "', '" + student.Year + "','" + student.Type + "', '" + student.Due + "', '" + student.Borrowed + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
string sqlQuery = "Insert into Student(Image)values(@image) WHERE Id='" + student.Id + "'";
try
{
myCommand = new SqlCommand(sqlQuery);
myCommand.Parameters.Add(new SqlParameter("@image", images));
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
// MessageBox.Show(N.ToString() + "Data Saved Successfully..!");
}
catch
{
//MessageBox.Show("Please insert your image");
}
}
public bool InsertCustomer(Customer cust)
{
string query = "INSERT into Customers VALUES('" + cust.CustId + "', '" + cust.Name + "', '" + cust.PhnNumber + "')";// "+cust.Salary+", '"+cust.Designation+"')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp) { return(false); }
}
public bool InsertEmployee(Employee emp)
{
string query = "INSERT into Employees VALUES('" + emp.EmpId + "', '" + emp.Name + "', '" + emp.PhnNumber + "', " + emp.Salary + ", '" + emp.Designation + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp) { return(false); }
}
public List <Admin> GetAllAdmin()
{
string query = "SELECT AId,AName,ASal,AEmail,APicture from Admin";
List <Admin> adminList = new List <Admin>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Admin add = new Admin();
add.AId = sdr.GetString(0);
add.AName = sdr.GetString(1);
add.ASal = sdr.GetDouble(2);
add.APhone = sdr.GetString(3);
add.AEmail = sdr.GetString(4);
add.APicture = (byte[])sdr[5];
adminList.Add(add);
}
dcc.CloseConnection();
return(adminList);
}
public List <Admin> GetAdmin(string AId)
{
string query = "SELECT AName,APassword,APhone,AEmail,ASal,AId from Admin WHERE AId = '" + AId + "'";
List <Admin> admininfo = new List <Admin>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
Admin add = new Admin();
string AName = sdr.GetString(0);
string APassword = sdr.GetString(1);
string APhone = sdr.GetString(2);
string AEmail = sdr.GetString(3);
double ASal = sdr.GetDouble(4);
string aidd = sdr.GetString(5);
admininfo.Add(new Admin()
{
AId = aidd, AName = AName, APassword = APassword, APhone = APhone, AEmail = AEmail, ASal = ASal
});
}
dcc.CloseConnection();
return(admininfo);
}
public List <CourseResults> GetAllDroppedStudent(string semester, string course, string section)
{
string query = "SELECT * from CourseResultsTable WHERE Semester='" + semester + "' AND CourseName='" + course + "' AND Section='" + section + "'";
List <CourseResults> cList = new List <CourseResults>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseResults c = new CourseResults();
c.CourseName = sdr["CourseName"].ToString();
c.Section = sdr["Section"].ToString();
c.Semester = sdr["Semester"].ToString();
c.AccountId = sdr["AccountId"].ToString();
c.Quiz = Convert.ToDouble(sdr["Quiz"]);
c.Term = Convert.ToDouble(sdr["Term"]);
c.FinalMark = Convert.ToDouble(sdr["FinalMark"]);
c.Grade = Convert.ToDouble(sdr["Grade"]);
c.Status = sdr["Status"].ToString();
cList.Add(c);
}
dcc.CloseConnection();
return(cList);
}
public List <string> DoubleAccountIDRegistrationVerify()
{
string query = "SELECT * from AccountTable ";
List <string> sList = new List <string>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Account a = new Account();
a.AccountId = sdr["AccountId"].ToString();
a.Password = sdr["Password"].ToString();
a.Name = sdr["Name"].ToString();
a.Gender = sdr["Gender"].ToString();
a.DOB = sdr["DOB"].ToString();
a.BloodGroup = sdr["BloodGroup"].ToString();
a.Address = sdr["Address"].ToString();
a.AccountType = sdr["AccountType"].ToString();
sList.Add(a.AccountId);
}
dcc.CloseConnection();
return(sList);
}
public List <CourseRegistration> GetAllStudents(string semester, string course, string section)
{
string query = "SELECT * from CourseRegistrationTable WHERE Semester='" + semester + "'AND CourseName='" + course + "' AND Section='" + section + "'";
List <CourseRegistration> cList = new List <CourseRegistration>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseRegistration cr = new CourseRegistration();
cr.CourseId = sdr["CourseId"].ToString();
cr.CourseName = sdr["CourseName"].ToString();
cr.Semester = sdr["Semester"].ToString();
cr.Section = sdr["Section"].ToString();
cr.AccountId = sdr["AccountId"].ToString();
cr.Status = sdr["Status"].ToString();
cList.Add(cr);
}
dcc.CloseConnection();
return(cList);
}
public bool InsertAdmin(Admin admin)
{
string query = "INSERT into Admin(id,Name,Contact,Pass) VALUES( '" + admin.Id + "','" + admin.Name + "','" + admin.Contact + "','" + admin.Password + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public bool InsertTable(Table table)
{
string query = "INSERT into Tables VALUES ('" + table.TableId + "', '" + table.NumberOfSeats + "','" + table.Status + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public List <Course> GetCourseIdStudent(string course, string semester, string section)
{
string query = "SELECT * from CourseTable WHERE Name= '" + course + "' AND Semester ='" + semester + "' AND Section='" + section + "'";
List <Course> cList = new List <Course>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Course c = new Course();
c.CourseId = sdr["CourseId"].ToString();
c.Name = sdr["Name"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Department = sdr["Department"].ToString();
c.Section = sdr["Section"].ToString();
c.MaxStudent = Convert.ToInt32(sdr["MaxStudent"]);
c.CurrentStudent = Convert.ToInt32(sdr["CurrentStudent"]);
c.Credit = Convert.ToDouble(sdr["Credit"]);
c.Schedule = sdr["Schedule"].ToString();
cList.Add(c);
}
dcc.CloseConnection();
return(cList);
}
public bool CheckCourseIdAndSemesterName2(Course s)
{
string query = "SELECT * from CourseTable WHERE CourseId = '" + s.CourseId + "' AND Semester = '" + s.Semester + "'";
Course c = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
c = new Course();
c.CourseId = sdr["CourseId"].ToString();
c.Name = sdr["Name"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Department = sdr["Department"].ToString();
c.Section = sdr["Section"].ToString();
c.MaxStudent = Convert.ToInt32(sdr["MaxStudent"]);
c.CurrentStudent = Convert.ToInt32(sdr["CurrentStudent"]);
c.Credit = Convert.ToInt32(sdr["Credit"]);
c.Schedule = sdr["Schedule"].ToString();
return(true);
}
dcc.CloseConnection();
return(false);
}
public List <string> GetCourseIdNameSectionsBySemester2(string Semester)
{
string query = "SELECT * from CourseTable WHERE Semester = '" + Semester + "'";
List <string> cList = new List <string>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Course c = new Course();
c.CourseId = sdr["CourseId"].ToString();
c.Name = sdr["Name"].ToString();
c.Semester = sdr["Semester"].ToString();
c.Department = sdr["Department"].ToString();
c.Section = sdr["Section"].ToString();
c.MaxStudent = Convert.ToInt32(sdr["MaxStudent"]);
c.CurrentStudent = Convert.ToInt32(sdr["CurrentStudent"]);
c.Credit = Convert.ToInt32(sdr["Credit"]);
c.Schedule = sdr["Schedule"].ToString();
cList.Add(c.CourseId + " " + c.Name + " [" + c.Section + "]");
}
dcc.CloseConnection();
return(cList);
}
public List <Account> SearchAccounts2(string text)
{
string query = "SELECT * from AccountTable WHERE AccountId LIKE '%" + text + "%' OR Name LIKE '%" + text + "%'";
List <Account> aList = new List <Account>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Account a = new Account();
a.AccountId = sdr["AccountId"].ToString();
a.Password = sdr["Password"].ToString();
a.Name = sdr["Name"].ToString();
a.Gender = sdr["Gender"].ToString();
a.DOB = sdr["DOB"].ToString();
a.BloodGroup = sdr["BloodGroup"].ToString();
a.Address = sdr["Address"].ToString();
a.AccountType = sdr["AccountType"].ToString();
if (a.AccountType == "Student")
{
aList.Add(a);
}
}
dcc.CloseConnection();
return(aList);
}
public string CourseVerification2(string courseId)
{
string query = "SELECT * from CourseTable WHERE CourseId= '" + courseId + "'";
Course a = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
a = new Course();
a.CourseId = sdr["CourseId"].ToString();
// a.Name = sdr["Name"].ToString();
// a.Name = sdr["Name"].ToString();
// a.Gender = sdr["Gender"].ToString();
// a.Address = sdr["Address"].ToString();
// a.AccountType = sdr["AccountType"].ToString();
// a.BloodGroup = sdr["BloodGroup"].ToString();
// a.Dob = sdr["DOB"].ToString(); ;
}
dcc.CloseConnection();
try
{
s = a.CourseId;
}
catch
{
}
return(s);
}
public List <string> GetOnlyID(string semester, string ID, string course, string section)
{
string query = "SELECT * from CourseRegistrationTable WHERE Semester= '" + semester + "' AND CourseName= '" + course + "' AND Section= '" + section + "'";
List <string> courseList = new List <string>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
CourseRegistration c = new CourseRegistration();
c.CourseId = sdr["CourseId"].ToString();
c.CourseName = sdr["CourseName"].ToString();
c.Section = sdr["Section"].ToString();
c.AccountId = sdr["AccountId"].ToString();
c.Status = sdr["Status"].ToString();
if (c.AccountId != ID)
{
courseList.Add(c.AccountId);
}
}
dcc.CloseConnection();
return(courseList);
}
public List <Account> GetAllAccounts3(string type)
{
string query = "SELECT * from AccountTable";
List <Account> aList = new List <Account>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
while (sdr.Read())
{
Account a = new Account();
a.AccountId = sdr["AccountId"].ToString();
a.Password = sdr["Password"].ToString();
a.Name = sdr["Name"].ToString();
a.Gender = sdr["Gender"].ToString();
a.DOB = sdr["DOB"].ToString();
a.BloodGroup = sdr["BloodGroup"].ToString();
a.Address = sdr["Address"].ToString();
a.AccountType = sdr["AccountType"].ToString();
if (a.AccountType == type)
{
}
else
{
aList.Add(a);
}
}
dcc.CloseConnection();
return(aList);
}
public List <Customer> Getcustomer(string CId)
{
string query = "SELECT CName,CPassword,CPhone,CEmail,CId from Customer WHERE CId = '" + CId + "'";
List <Customer> Customerinfo = new List <Customer>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
Customer add = new Customer();
string CName = sdr.GetString(0);
string CPassword = sdr.GetString(1);
string CPhone = sdr.GetString(2);
string CEmail = sdr.GetString(3);
string cidd = sdr.GetString(4);
Customerinfo.Add(new Customer()
{
CId = cidd, CName = CName, CPassword = CPassword, CPhone = CPhone, CEmail = CEmail
});
}
dcc.CloseConnection();
return(Customerinfo);
}
public Course GetCourseId(string courseName, string semester, string section)
{
string query = "SELECT * from CourseTable WHERE Name= '" + courseName + "' AND Semester ='" + semester + "' AND Section='" + section + "'";
Course a = null;
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
a = new Course();
a.CourseId = sdr["CourseId"].ToString();
a.Name = sdr["Name"].ToString();
a.Semester = sdr["Semester"].ToString();
a.Department = sdr["Department"].ToString();
a.Section = sdr["Section"].ToString();
a.MaxStudent = Convert.ToInt32(sdr["MaxStudent"]);
a.CurrentStudent = Convert.ToInt32(sdr["CurrentStudent"]);
a.Credit = Convert.ToInt32(sdr["Credit"]);
a.Schedule = sdr["Schedule"].ToString();
}
dcc.CloseConnection();
return(a);
}
public List <Owner> Getowner(string OId)
{
string query = "SELECT OName,OPassword,OPhone,OEmail,OId from Owner WHERE OId = '" + OId + "'";
List <Owner> Ownerinfo = new List <Owner>();
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
Owner add = new Owner();
string OName = sdr.GetString(0);
string OPassword = sdr.GetString(1);
string OPhone = sdr.GetString(2);
string OEmail = sdr.GetString(3);
string Oidd = sdr.GetString(4);
Ownerinfo.Add(new Owner()
{
OId = Oidd, OName = OName, OPassword = OPassword, OPhone = OPhone, OEmail = OEmail
});
}
dcc.CloseConnection();
return(Ownerinfo);
}
public bool InsertMedicine(Medicine md)
{
string query = "INSERT into Medicine values('" + md.MadId + "', '" + md.MadName + "', '" + md.MadCompanyname + "', '" + md.ManufacturingDate + "', '" + md.Expiringdate + "', '" + md.StockDate + "', " + md.Price + " , " + md.Quantity + ")";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
return(false);
}
finally { dcc.CloseConnection(); }
}
public bool InsertProduct(Product item)
{
string query = "INSERT into Products VALUES ('" + item.ProductId + "', '" + item.Name + "', " + item.Price + ",'" + item.Description + "' )";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public bool Save_Password(Login l, string pass)
{
string query = "UPDATE Login SET Password = '******' WHERE Id = '" + l.Id + "'";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception ex)
{
dcc.CloseConnection();
return(false);
}
}
public bool InsertOrder(Order order)
{
string query = "INSERT into Orders VALUES ('" + order.Id + "', '" + order.ProductId + "', " + order.TotalPrice + ", " + order.Discount + "," + order.Paid + "," + order.OrderStatus + " )";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp)
{
return(false);
}
}
public bool InsertEmployee(Employee emp)
{
string query = "INSERT into Employees values('" + emp.EmpId + "', '" + emp.EmpName + "', " + emp.EmpAge + " , '" + emp.EmpPhoneNum + "', " + emp.EmpSalary + " , '" + emp.EmpDesignation + "', '" + emp.EmpAddress + "')";
try
{
dcc.ConnectWithDB();
int n = dcc.ExecuteSQL(query);
dcc.CloseConnection();
return(true);
}
catch (Exception exp) { return(false); }
finally { dcc.CloseConnection(); }
}
public bool CheckAssignedCourse2(CourseAssign s)
{
string query = "SELECT * from CourseAssignTable WHERE AccountId = '" + s.AccountId + "' AND AssignedCourse = '" + s.AssignedCourse + "' AND Section = '" + s.Section + "' AND Semester = '" + s.Semester + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
dcc.CloseConnection();
return(false);
}
else
{
dcc.CloseConnection();
return(true);
}
}
public bool CheckIsRegistrationOpen(string name)
{
string query = "SELECT * from SemesterTable WHERE Name = '" + name + "' AND RegistrationStatus = 'Open'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
dcc.CloseConnection();
return(true);
}
else
{
dcc.CloseConnection();
return(false);
}
}
public bool CheckSemester2(string SemsesterName)
{
string query = "SELECT * from SemesterTable WHERE Name = '" + SemsesterName + "'";
DatabaseConnectionClass dcc = new DatabaseConnectionClass();
dcc.ConnectWithDB();
SqlDataReader sdr = dcc.GetData(query);
if (sdr.Read())
{
dcc.CloseConnection();
return(true);
}
else
{
dcc.CloseConnection();
return(false);
}
}