public static int Add(LoginDetails logindetails)
{
int retvalue = -1;
try
{
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.CommandText = "procLoginDetails_LoginDetails";
db.cmd.Parameters.AddWithValue("@Username", logindetails.Username);
db.cmd.Parameters.AddWithValue("@Password", logindetails.Password);
db.cmd.Parameters.AddWithValue("@AllowAccess", logindetails.AllowAccess);
db.cmd.Parameters.Add("@id", SqlDbType.Int);
db.cmd.Parameters["@id"].Direction = ParameterDirection.Output;
db.con.Open();
db.cmd.ExecuteNonQuery();
retvalue = Convert.ToInt32(db.cmd.Parameters["@id"].Value);
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
return(retvalue);
}
public static void Update(int parentId, Parents parents)
{
try
{
Persons.PersonUpdate(parents.PersonId, parents);
LoginDetails.Update(parents.LoginDetailsId, parents.LoginDetails);
db.cmd.CommandText = @"UPDATE [dbo].[tblParents]
SET [Person_ID] = @pid
,[LoginDetail_ID] = @lid
WHERE Id=id";
db.cmd.Parameters.AddWithValue("@pid", parents.PersonId);
db.cmd.Parameters.AddWithValue("@lid", parents.LoginDetailsId);
db.cmd.Parameters.AddWithValue("@id", parents.Id);
db.con.Open();
db.cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
}
private static List <LoginDetails> _GetListOfLoginDetails()
{
List <LoginDetails> loginDetails = new List <LoginDetails>();
try
{
Command = @"select * from tblLoginDetails";
db.cmd.CommandText = Command;
db.con.Open();
SqlDataReader rdr = db.cmd.ExecuteReader();
while (rdr.Read())
{
LoginDetails singleLoginDetails = new LoginDetails();
singleLoginDetails.Id = (int)rdr[0];
singleLoginDetails.Username = rdr[1].ToString();
singleLoginDetails.Password = rdr[2].ToString();
singleLoginDetails.AllowAccess = Convert.ToBoolean(rdr[3]);
loginDetails.Add(singleLoginDetails);
}
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.con.Close();
}
return(loginDetails);
}
public static void Delete(int id)
{
Parents parents = new Parents();
Persons person = new Persons();
var parent = Parents.ListOfParents.SingleOrDefault(p => p.Id == id);
Persons.PersonDelete(parent.PersonsId);
LoginDetails.Delete(parents.LoginDetailsId);
try
{
db.cmd.CommandText = "delete from tblParents where Id=@id";
db.cmd.Parameters.AddWithValue("@id", id);
db.con.Open();
db.cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
}
public static int Add(Students students)
{
int retvalue = -1;
try
{
Persons persons = new Persons();
persons.PersonId = students.PersonsId;
persons.FirstName = students.FirstName;
persons.LastName = students.LastName;
persons.DateOfBirth = students.DateOfBirth;
persons.CNIC = students.CNIC;
persons.ImageUrlPath = students.ImageUrlPath;
persons.Phone1 = students.Phone1;
persons.Phone2 = students.Phone2;
persons.GendersId = students.GendersId;
students.PersonsId = Persons.PersonAdd(persons);
students.LoginDetailsId = LoginDetails.Add(students.LoginDetails);
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.CommandText = "procStudents_AddStudents";
db.cmd.Parameters.AddWithValue("@RegistrationID", students.RegistrationId);
db.cmd.Parameters.AddWithValue("@DiscountPercentage", students.DiscountPercentage);
db.cmd.Parameters.AddWithValue("@IdentityCardUrl", students.IdentityCardUrl);
db.cmd.Parameters.AddWithValue("@AdmissionCard", students.AdmissionCard);
db.cmd.Parameters.AddWithValue("@PostalAddress", students.PostalAddress);
db.cmd.Parameters.AddWithValue("@ResidentialAddress", students.ResidentialAddress);
db.cmd.Parameters.AddWithValue("@Person_ID", students.PersonsId);
db.cmd.Parameters.AddWithValue("@LoginDetail_ID", students.LoginDetailsId);
db.cmd.Parameters.AddWithValue("@Parent_ID", students.ParentsId);
db.cmd.Parameters.AddWithValue("@Class_ID", students.ClassesId);
db.cmd.Parameters.Add("@id", SqlDbType.Int);
db.cmd.Parameters["@id"].Direction = ParameterDirection.Output;
db.con.Open();
db.cmd.ExecuteNonQuery();
retvalue = Convert.ToInt32(db.cmd.Parameters["@id"].Value);
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
return(retvalue);
}
public static void Update(int id, Students std)
{
try
{
Parents.Update(std.ParentsId, std.Parents);
PersonUpdate(std.PersonId, std);
LoginDetails.Update(std.LoginDetailsId, std.LoginDetails);
db.cmd.CommandText = @"UPDATE [dbo].[tblStudents]
SET [RegistrationID] = @regid
,[DiscountPercentage] = @disper
,[IdentityCardUrl] = @idcard
,[AdmissionCard] = @addmission
,[PostalAddress] = @post
,[ResidentialAddress] = @resi
,[Person_ID] = @perid1
,[LoginDetail_ID] = @lid1
,[Parent_ID] = @pid1
,[Class_ID] = @cid1
WHERE Id=@id";
db.cmd.Parameters.AddWithValue("@regid", std.RegistrationId);
db.cmd.Parameters.AddWithValue("@disper", std.DiscountPercentage);
db.cmd.Parameters.AddWithValue("@idcard", std.IdentityCardUrl);
db.cmd.Parameters.AddWithValue("@addmission", std.AdmissionCard);
db.cmd.Parameters.AddWithValue("@post", std.PostalAddress);
db.cmd.Parameters.AddWithValue("@resi", std.ResidentialAddress);
db.cmd.Parameters.AddWithValue("@perid1", std.PersonsId);
db.cmd.Parameters.AddWithValue("@lid1", std.LoginDetailsId);
db.cmd.Parameters.AddWithValue("@pid1 ", std.ParentsId);
db.cmd.Parameters.AddWithValue("@cid1 ", std.ClassesId);
db.cmd.Parameters.AddWithValue("@id", std.Id);
db.con.Open();
db.cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
}
public static int Add(Parents parents)
{
int retvalue = -1;
try
{
Persons persons = new Persons();
persons.FirstName = parents.FirstName;
persons.LastName = parents.LastName;
persons.DateOfBirth = parents.DateOfBirth;
persons.CNIC = parents.CNIC;
persons.ImageUrlPath = parents.ImageUrlPath;
persons.Phone1 = parents.Phone1;
persons.Phone2 = parents.Phone2;
persons.GendersId = parents.GendersId;
parents.PersonsId = Persons.PersonAdd(persons);
parents.LoginDetailsId = LoginDetails.Add(parents.LoginDetails);
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.CommandText = "procParents_AddParents";
db.cmd.Parameters.AddWithValue("@Person_ID", parents.PersonsId);
db.cmd.Parameters.AddWithValue("@LoginDetail_ID", parents.LoginDetailsId);
db.cmd.Parameters.Add("@id", SqlDbType.Int);
db.cmd.Parameters["@id"].Direction = ParameterDirection.Output;
db.con.Open();
db.cmd.ExecuteNonQuery();
db.con.Close();
retvalue = Convert.ToInt32(db.cmd.Parameters["@id"].Value);
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
return(retvalue);
}
public static void Update(int empId, Employees employees)
{
try
{
Persons.PersonUpdate(employees.PersonId, employees);
LoginDetails.Update(employees.LoginDetailsId, employees.LoginDetails);
db.cmd.CommandText = @" update tblEmployees set RegistrationID=@regid,
Salary=@salary,IdentityCardUrl=@idcardurl,
PostalAddress=@postaladd,ResidentialAddress=@residadd,
AggrementUrl=@aggurl,Person_ID=@pid,
Designation_ID=@did,
EmployeeType_ID=@eid,
LoginDetail_ID=@lid where Id=@id;";
db.cmd.Parameters.AddWithValue("@regid", employees.RegistrationID);
db.cmd.Parameters.AddWithValue("@salary", employees.Salary);
db.cmd.Parameters.AddWithValue("@idcardurl", employees.IdentityCardUrl);
db.cmd.Parameters.AddWithValue("@postaladd", employees.PostalAddress);
db.cmd.Parameters.AddWithValue("@residadd", employees.ResidentialAddress);
db.cmd.Parameters.AddWithValue("@aggurl", employees.AggrementUrl);
db.cmd.Parameters.AddWithValue("@pid", employees.PersonsId);
db.cmd.Parameters.AddWithValue("@did", employees.DesignationsId);
db.cmd.Parameters.AddWithValue("@eid ", employees.EmployeeTypesId);
db.cmd.Parameters.AddWithValue("@lid ", employees.LoginDetailsId);
db.cmd.Parameters.AddWithValue("@id", employees.Id);
db.con.Open();
db.cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
}
public static int Add(Employees employee)
{
Persons person = new Persons();
int retvalue = -1;
try
{
employee.PersonsId = Persons.PersonAdd(employee);
employee.LoginDetailsId = LoginDetails.Add(employee.LoginDetails);
db.cmd.CommandType = CommandType.StoredProcedure;
db.cmd.CommandText = "procEmployees_AddEmployees";
db.cmd.Parameters.AddWithValue("@RegistrationID", employee.RegistrationID);
db.cmd.Parameters.AddWithValue("@Salary", employee.Salary);
db.cmd.Parameters.AddWithValue("@IdentityCardUrl", employee.IdentityCardUrl);
db.cmd.Parameters.AddWithValue("@PostalAddress", employee.PostalAddress);
db.cmd.Parameters.AddWithValue("@ResidentialAddress", employee.RegistrationID);
db.cmd.Parameters.AddWithValue("@AggrementUrl", employee.AggrementUrl);
db.cmd.Parameters.AddWithValue("@Person_ID", employee.PersonsId);
db.cmd.Parameters.AddWithValue("@Designation_ID", employee.DesignationsId);
db.cmd.Parameters.AddWithValue("@EmployeeType_ID", employee.EmployeeTypesId);
db.cmd.Parameters.AddWithValue("@LoginDetail_ID", employee.LoginDetailsId);
db.cmd.Parameters.Add("@id", SqlDbType.Int);
db.cmd.Parameters["@id"].Direction = ParameterDirection.Output;
db.con.Open();
db.cmd.ExecuteNonQuery();
retvalue = Convert.ToInt32(db.cmd.Parameters["@id"].Value);
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
return(retvalue);
}
public static void Update(int id, LoginDetails logindetails)
{
try
{
db.cmd.CommandText = "update tblLoginDetails set Username = @Username,Password=@Password,AllowAccess=@AllowAccess where Id=@id";
db.cmd.Parameters.AddWithValue("@Username", logindetails.Username);
db.cmd.Parameters.AddWithValue("@Password", logindetails.Password);
db.cmd.Parameters.AddWithValue("@AllowAccess", logindetails.AllowAccess);
db.cmd.Parameters.AddWithValue("@id", id);
db.con.Open();
db.cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.CloseDb(db.con, db.cmd);
}
}
private static List <Students> _GetListOfStudents()
{
List <Students> students = new List <Students>();
try
{
Command = @"select * from tblStudents std join tblLoginDetails lgn
on std.LoginDetail_ID=lgn.Id join tblParents pts
on std.Parent_ID=pts.Id join tblClasses cls
on std.Class_ID=cls.Id";
db.cmd.CommandText = Command;
db.con.Open();
SqlDataReader rdr = db.cmd.ExecuteReader();
while (rdr.Read())
{
Students singleStudents = new Students();
singleStudents.Id = (int)rdr[0];
singleStudents.RegistrationId = rdr[1].ToString();
singleStudents.DiscountPercentage = Convert.ToSingle(rdr[2]);
singleStudents.IdentityCardUrl = rdr[3].ToString();
singleStudents.AdmissionCard = rdr[4].ToString();
singleStudents.PostalAddress = rdr[5].ToString();
singleStudents.ResidentialAddress = rdr[6].ToString();
singleStudents.PersonsId = (int)rdr[7];
singleStudents.LoginDetailsId = (int)rdr[8];
singleStudents.ParentsId = (int)rdr[9];
singleStudents.ClassesId = (int)rdr[10];
var logindetail = new LoginDetails();
singleStudents.LoginDetails = LoginDetails.ListOfLoginDetails.SingleOrDefault(lg => lg.Id == singleStudents.LoginDetailsId);
//singleStudents.LoginDetails.Id= (int)rdr[11];
//singleStudents.LoginDetails.Username = rdr[12].ToString();
//singleStudents.LoginDetails.Password = rdr[13].ToString();
//singleStudents.LoginDetails.AllowAccess = (int)rdr[14];
var parents = new Parents();
singleStudents.Parents = Parents.ListOfParents.SingleOrDefault(p => p.Id == singleStudents.ParentsId);
//singleStudents.Parents.Id = (int)rdr[15];
//singleStudents.Parents.PersonsId = (int)rdr[16];
//singleStudents.Parents.LoginDetailsId = (int)rdr[17];
var classes = new Classes();
singleStudents.Classes = Classes.ListOfClasses.SingleOrDefault(cls => cls.Id == singleStudents.ClassesId);
//singleStudents.Classes.Id = (int)rdr[18];
//singleStudents.Classes.ClassesName = rdr[19].ToString() ;
//singleStudents.Classes.GradesId = (int)rdr[20];
var person = Persons.ListOfPersons.SingleOrDefault(p => p.PersonId == singleStudents.PersonsId);
singleStudents.Id = person.PersonId;
singleStudents.FirstName = person.FirstName;
singleStudents.LastName = person.LastName;
singleStudents.DateOfBirth = person.DateOfBirth;
singleStudents.CNIC = person.CNIC;
singleStudents.ImageUrlPath = person.ImageUrlPath;
singleStudents.Phone1 = person.Phone1;
singleStudents.Phone2 = person.Phone2;
singleStudents.GendersId = person.GendersId;
singleStudents.Genders.Id = person.Genders.Id;
singleStudents.Genders.GenderName = person.Genders.GenderName;
students.Add(singleStudents);
}
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.con.Close();
}
return(students);
}
public Students()
{
LoginDetails = new LoginDetails();
Parents = new Parents();
Classes = new Classes();
}
private static List <Parents> _GetListOfParents()
{
List <Parents> parents = new List <Parents>();
try
{
Command = @" select * from tblPersons prs
join tblGenders gndr
on prs.Gender_ID = gndr.id
join tblParents prnt
on prs.Id=prnt.Person_ID
join tblLoginDetails lgn
on prnt.LoginDetail_ID=lgn.id;" ;
db.cmd.CommandText = Command;
db.con.Open();
SqlDataReader rdr = db.cmd.ExecuteReader();
while (rdr.Read())
{
Parents singleparent = new Parents();
singleparent.PersonId = (int)rdr[0];
singleparent.FirstName = rdr[1].ToString();
singleparent.LastName = rdr[2].ToString();
singleparent.DateOfBirth = rdr[3].ToString();
singleparent.CNIC = rdr[4].ToString();
singleparent.ImageUrlPath = rdr[5].ToString();
singleparent.Phone1 = rdr[6].ToString();
singleparent.Phone2 = rdr[7].ToString();
singleparent.GendersId = (int)rdr[8];
singleparent.Genders.Id = (int)rdr[9];
singleparent.Genders.GenderName = rdr[10].ToString();
singleparent.Id = (int)rdr[11];
singleparent.PersonId = (int)rdr[12];
singleparent.LoginDetailsId = (int)rdr[13];
//
var logindetail = new LoginDetails();
singleparent.LoginDetails = LoginDetails.ListOfLoginDetails.SingleOrDefault(lg => lg.Id == singleparent.LoginDetailsId);
//
//singleparent.LoginDetails.Id = (int)rdr[14];
//singleparent.LoginDetails.Username = rdr[15].ToString();
//singleparent.LoginDetails.Password = rdr[16].ToString();
//singleparent.LoginDetails.AllowAccess = (int)rdr[17];
parents.Add(singleparent);
}
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.con.Close();
}
return(parents);
}
public Parents()
{
LoginDetails = new LoginDetails();
}
private static List <Employees> _GetEmployees()
{
List <Employees> employees = new List <Employees>();
try
{
Command = @"select * from tblEmployees";
db.cmd.CommandText = Command;
db.con.Open();
SqlDataReader rdr = db.cmd.ExecuteReader();
while (rdr.Read())
{
Employees singleEmployees = new Employees();
singleEmployees.Id = (int)rdr[0];
singleEmployees.RegistrationID = rdr[1].ToString();
singleEmployees.Salary = Convert.ToSingle(rdr[2]);
singleEmployees.IdentityCardUrl = rdr[3].ToString();
singleEmployees.PostalAddress = rdr[4].ToString();
singleEmployees.ResidentialAddress = rdr[5].ToString();
singleEmployees.AggrementUrl = rdr[6].ToString();
singleEmployees.PersonsId = (int)rdr[7];
singleEmployees.DesignationsId = (int)rdr[8];
singleEmployees.EmployeeTypesId = (int)rdr[9];
singleEmployees.LoginDetailsId = (int)rdr[10];
var emptypes = new EmployeeTypes();
singleEmployees.EmployeeTypes = EmployeeTypes.ListOfEmployeeTypes.SingleOrDefault(et => et.Id == singleEmployees.EmployeeTypesId);
var logindetails = new LoginDetails();
singleEmployees.LoginDetails = LoginDetails.ListOfLoginDetails.SingleOrDefault(ld => ld.Id == singleEmployees.LoginDetailsId);
var desig = new Designations();
singleEmployees.Designations = Designations.ListOfDesignations.SingleOrDefault(d => d.Id == singleEmployees.DesignationsId);
var a = Persons.ListOfPersons.SingleOrDefault(p => p.PersonId == singleEmployees.PersonsId);
singleEmployees.FirstName = a.FirstName;
singleEmployees.LastName = a.LastName;
singleEmployees.PersonId = a.PersonId;
singleEmployees.DateOfBirth = a.DateOfBirth;
singleEmployees.CNIC = a.CNIC;
singleEmployees.ImageUrlPath = a.ImageUrlPath;
singleEmployees.Phone1 = a.Phone1;
singleEmployees.Phone2 = a.Phone2;
singleEmployees.GendersId = a.GendersId;
singleEmployees.Genders.Id = a.Genders.Id;
singleEmployees.Genders.GenderName = a.Genders.GenderName;
employees.Add(singleEmployees);
}
}
catch (SqlException sqlex)
{
SqlExceptionErrorHandling rh = new SqlExceptionErrorHandling();
rh.GetError(sqlex);
}
finally
{
db.con.Close();
}
return(employees);
}
public Employees()
{
EmployeeTypes = new EmployeeTypes();
LoginDetails = new LoginDetails();
Designations = new Designations();
}