public IEnumerable <Vat> GetAllPendingVats()
{
try
{
List <Vat> vats = new List <Vat>();
CommandObj.CommandText = "UDSP_GetAllPendingVats";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
vats.Add(new Vat
{
VatId = Convert.ToInt32(reader["VatId"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
VatAmount = Convert.ToDecimal(reader["VatAmount"]),
UpdateDate = Convert.ToDateTime(reader["UpdateDate"]),
UpdateByUserId = Convert.ToInt32(reader["UpdateByUserId"])
});
}
reader.Close();
return(vats);
}
catch (Exception exception)
{
throw new Exception("Could not collect pending Vat info", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public string GetLastAddedStudentRegistration(string searchKey)
{
string query = "SELECT * FROM t_Student st WHERE RegNo LIKE '%" + searchKey + "%' and Id=(select Max(Id) FROM t_Student st WHERE RegNo LIKE '%" + searchKey + "%' )";
CommandObj.CommandText = query;
ConnectionObj.Open();
Student aStudent = null;
string regNo = null;
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
aStudent = new Student
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
RegNo = reader["RegNo"].ToString(),
Email = reader["Email"].ToString(),
Contact = reader["ContactNo"].ToString(),
};
regNo = aStudent.RegNo;
}
ConnectionObj.Close();
CommandObj.Dispose();
reader.Close();
return(regNo);
}
public int GetMaxInvoiceNo()
{
try
{
CommandObj.CommandText = "spGetMaxInvoiceNo";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
int maxInvoiceNo = 0;
if (reader.Read())
{
maxInvoiceNo = Convert.ToInt32(reader["MaxInvoiceNo"]);
}
reader.Close();
return(maxInvoiceNo);
}
catch (Exception exception)
{
throw new Exception("Could not collect max invoice no", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <CityViewerModel> GetCityInformation(string cityName)
{
try
{
string query = "SELECT * FROM GetCountryInformation WHERE City_Name LIKE '%" + cityName + "%'";
CommandObj.CommandText = query;
ConnectionObj.Open();
List <CityViewerModel> listOfCityViewerModels = new List <CityViewerModel>();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
CityViewerModel cityViewerModel = new CityViewerModel();
cityViewerModel.Id = Convert.ToInt32(reader["Id"].ToString());
cityViewerModel.CityName = reader["City_Name"].ToString();
cityViewerModel.AboutCity = reader["About_City"].ToString();
cityViewerModel.NoOfDewellers = Convert.ToInt64(reader["No_of_Dwellers"].ToString());
cityViewerModel.Location = reader["Location"].ToString();
cityViewerModel.Weather = reader["Weather"].ToString();
cityViewerModel.CountryName = reader["Country_Name"].ToString();
cityViewerModel.AboutCountry = reader["About_Country"].ToString();
listOfCityViewerModels.Add(cityViewerModel);
}
reader.Close();
return(listOfCityViewerModels);
}
catch (Exception exception)
{
throw new Exception("Unable to Get city information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public IEnumerable <ViewVat> GetProductLatestVat()
{
try
{
List <ViewVat> vats = new List <ViewVat>();
CommandObj.CommandText = "UDSP_GetProductLatestVat";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
vats.Add(new ViewVat
{
ProductName = reader["ProductName"].ToString(),
Segment = reader["Segment"].ToString(),
VatAmount = Convert.ToDecimal(reader["VatAmount"]),
UpdateDate = Convert.ToDateTime(reader["UpdateDate"])
});
}
reader.Close();
return(vats);
}
catch (Exception exception)
{
throw new Exception("Could not collect Vat info", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public StudentViewModel GetStudentInformationById(int id)
{
CommandObj.CommandText = "SELECT s.Id,s.RegNo,s.Name,s.Email,s.ContactNo,s.RegisterationDate,s.Address,d.Name as Department FROM Student_tbl s INNER JOIN Department_tbl d ON s.DepartmentId=d.Id AND s.Id=@Id";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@Id", id);
StudentViewModel student = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
student = new StudentViewModel
{
Id = Convert.ToInt32(reader["Id"].ToString()),
RegNo = reader["RegNo"].ToString(),
Name = reader["Name"].ToString(),
Email = reader["Email"].ToString(),
ContactNo = reader["ContactNo"].ToString(),
RegisterationDate = Convert.ToDateTime(reader["RegisterationDate"].ToString()),
Address = reader["Address"].ToString(),
Department = reader["Department"].ToString()
};
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(student);
}
public Country GetCountryInformationByName(string countryName)
{
try
{
string query = "SELECT * FROM t_Country WHERE Name=@countryName";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@countryName", countryName);
Country country = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
country = new Country();
country.Id = Convert.ToInt32(reader["Id"].ToString());
country.Name = reader["Name"].ToString();
country.About = reader["About"].ToString();
}
reader.Close();
return(country);
}
catch (Exception exception)
{
throw new Exception("Unable to get country information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public IEnumerable <Department> GetAll()
{
try
{
string query = "SELECT * FROM t_Departments";
CommandObj.CommandText = query;
List <Department> departments = new List <Department>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Department department = new Department
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Code = reader["Code"].ToString()
};
departments.Add(department);
}
reader.Close();
return(departments);
}
catch (Exception exception)
{
throw new Exception("Unable to connect department", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public List <CourseAssignToTeacher> GetAllCourseAssignToTeacher()
{
CommandObj.CommandText = "SELECT * FROM CourseAssignToTeacher_tbl";
List <CourseAssignToTeacher> courseAssignToTeachers = new List <CourseAssignToTeacher>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
CourseAssignToTeacher assignToTeacher = new CourseAssignToTeacher
{
Id = Convert.ToInt32(reader["Id"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
TeacherId = Convert.ToInt32(reader["TeacherId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
Status = Convert.ToBoolean(reader["IsAssign"].ToString())
};
courseAssignToTeachers.Add(assignToTeacher);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(courseAssignToTeachers);
}
public bool IsThisBarcodeExitsInScrapInventory(string barcode)
{
try
{
CommandObj.CommandText = "UDSP_IsThisBarcodeExitsInScrapInventory";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@Barcode", barcode);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
int rowAffected = 0;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
rowAffected = Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
}
return(rowAffected > 0);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not found Scrap barcode", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public Department GetDepartmentByCode(string code)
{
try
{
string query = "SELECT * FROM t_Departments WHERE Code=@code";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@code", code);
Department department = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
department = new Department
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Code = reader["Code"].ToString()
};
}
reader.Close();
return(department);
}
catch (Exception exception)
{
throw new Exception("Unable to connect department", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public ICollection <object> GetEmployeeListByDepartmentAndSearchTerm(int departmentId, string searchTerm, int branchId)
{
try
{
CommandObj.CommandText = "UDSP_GetEmployeeListByDepartmentAndSearchTerm";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DepartmentId", departmentId);
CommandObj.Parameters.AddWithValue("@SearchTerm", searchTerm);
CommandObj.Parameters.AddWithValue("@BranchId", branchId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <object> employees = new List <object>();
while (reader.Read())
{
employees.Add(new
{
val = Convert.ToInt32(reader["EmployeeId"]),
label = reader["EmployeeName"].ToString()
});
}
reader.Close();
return(employees);
}
catch (Exception exception)
{
throw new Exception("Could not collect Employee Information by department and search Term", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int GetEmployeeMaxSerialNo()
{
try
{
int maxSlno = 0;
CommandObj.CommandText = "spGetEmployeeMaxSerialNo";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
maxSlno = Convert.ToInt32(reader["MaxSlNo"]);
}
reader.Close();
return(maxSlno);
}
catch (Exception exception)
{
throw new Exception("Colud not get Employee max serial", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <Registation> GetAllInfo(string userId)
{
string selectSql = "SELECT * FROM tbl_user WHERE user_id ='" + userId + "'";
ConnectionObj.Open();
CommandObj.CommandText = selectSql;
SqlDataReader reader = CommandObj.ExecuteReader();
var userInfoList = new List <Registation>();
while (reader.Read())
{
var registation = new Registation
{
Id = Convert.ToInt32(reader["id"].ToString()),
UserId = reader["user_id"].ToString(),
Password = reader["user_pass"].ToString(),
Type = reader["user_type"].ToString(),
RetryAttemps = Convert.ToInt32(reader["retryAttemps"].ToString()),
Islocked = Convert.ToBoolean(reader["isLocked"].ToString())
};
userInfoList.Add(registation);
}
reader.Close();
ConnectionObj.Close();
return(userInfoList);
}
public List <StudentResult> GetAllStudentResults()
{
CommandObj.CommandText = "SELECT * FROM StudentResult_tbl";
List <StudentResult> studentResults = new List <StudentResult>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
StudentResult studentResult = new StudentResult
{
Id = Convert.ToInt32(reader["Id"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Grade = reader["Grade"].ToString(),
Status = (bool)reader["IsStudentActive"]
};
studentResults.Add(studentResult);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(studentResults);
}
public List <ClassRoom> GetAllClassroomInformation()
{
List <ClassRoom> scheduleList = new List <ClassRoom>();
CommandObj.CommandText = "SELECT * FROM AllocateClassRoom_tbl";
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
ClassRoom classRoom = new ClassRoom
{
Id = Convert.ToInt32(reader["Id"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
DayId = Convert.ToInt32(reader["DayId"].ToString()),
RoomId = Convert.ToInt32(reader["RoomId"].ToString()),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
Endtime = Convert.ToDateTime(reader["EndTime"].ToString()),
AlloctionStaus = Convert.ToBoolean(reader["AllocationStatus"].ToString())
};
scheduleList.Add(classRoom);
}
reader.Close();
ConnectionObj.Close();
CommandObj.Dispose();
return(scheduleList);
}
public List <StudentResultViewModel> GetStudentResultByStudentId(int id)
{
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
CommandObj.CommandText = "SELECT en.StudentId, c.Code,c.Name,COALESCE(r.Grade,'Not Graded yet') as Grade FROM StudentResult_tbl r RIGHT OUTER JOIN ( SELECT e.Id,e.StudentId,e.CourseId FROM StudentEnrollInCourse_tbl e WHERE e.StudentId=@studentId AND e.IsStudentActive=1) en ON r.CourseId=en.CourseId AND r.StudentId=en.StudentId AND r.IsStudentActive=1 INNER JOIN Course_tbl c ON en.CourseId=c.Id";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@studentId", id);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel
{
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
Grade = reader["Grade"].ToString()
};
studentResults.Add(studentResult);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(studentResults);
}
public List <AllocateClassSchedule> GetAllAllocateClassSchedules()
{
List <AllocateClassSchedule> scheduleList = new List <AllocateClassSchedule>();
CommandObj.CommandText = "SELECT * FROM ScheduleOfClassView";
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
AllocateClassSchedule schedule = new AllocateClassSchedule
{
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CourseCode = reader["Code"].ToString(),
CourseName = reader["Name"].ToString(),
RoomName = reader["Room_Name"].ToString(),
DayName = reader["Day_Name"].ToString(),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
EndTime = Convert.ToDateTime(reader["EndTime"].ToString())
};
scheduleList.Add(schedule);
}
reader.Close();
ConnectionObj.Close();
CommandObj.Dispose();
return(scheduleList);
}
public List <Country> GetAll()
{
try
{
List <Country> countries = new List <Country>();
string query = "SELECT * FROM t_Country ORDER BY Name";
CommandObj.CommandText = query;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Country aCountry = new Country();
aCountry.Id = Convert.ToInt32(reader["Id"].ToString());
aCountry.Name = reader["Name"].ToString();
aCountry.About = reader["About"].ToString();
countries.Add(aCountry);
}
reader.Close();
return(countries);
}
catch (Exception exception)
{
throw new Exception("Unable to get the coutries information.", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public List <ClassRoom> GetClassSchedulByStartAndEndingTime(int roomId, int dayId, DateTime startTime, DateTime endTime)
{
CommandObj.CommandText = "Select * from AllocateClassRoom_tbl Where DayId=@DayId AND RoomId=@RoomId AND AllocationStatus=1";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("RoomId", roomId);
CommandObj.Parameters.AddWithValue("DayId", dayId);
List <ClassRoom> tempClassSchedules = new List <ClassRoom>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
ClassRoom temp = new ClassRoom
{
Id = Convert.ToInt32(reader["Id"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
RoomId = Convert.ToInt32(reader["RoomId"].ToString()),
DayId = Convert.ToInt32(reader["DayId"].ToString()),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
Endtime = Convert.ToDateTime(reader["EndTime"].ToString())
};
tempClassSchedules.Add(temp);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(tempClassSchedules);
}
public IEnumerable <EmployeeType> GetAll()
{
try
{
CommandObj.CommandText = "spGetAllEmployeeType";
CommandObj.CommandType = CommandType.StoredProcedure;
List <EmployeeType> employeeTypes = new List <EmployeeType>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
employeeTypes.Add(new EmployeeType
{
EmployeeTypeId = Convert.ToInt32(reader["EmployeeTypeId"]),
EmployeeTypeName = reader["EmployeeTypeName"].ToString()
});
}
reader.Close();
return(employeeTypes);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect employee Types", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <AllocateClassSchedule> GetAllClassSchedulesByDeparmentId(int departmentId, int courseId)
{
List <AllocateClassSchedule> scheduleList = new List <AllocateClassSchedule>();
CommandObj.CommandText = "SELECT * FROM ScheduleOfClassView WHERE DepartmentId= @DepartmentId AND CourseId=@CourseId AND AllocationStatus= 1 ";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("DepartmentId", departmentId);
CommandObj.Parameters.AddWithValue("CourseId", courseId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
AllocateClassSchedule schedule = new AllocateClassSchedule
{
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CourseCode = reader["Code"].ToString(),
CourseName = reader["Name"].ToString(),
RoomName = reader["Room_Name"].ToString(),
DayName = reader["Day_Name"].ToString(),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
EndTime = Convert.ToDateTime(reader["EndTime"].ToString()),
Status = Convert.ToBoolean(reader["AllocationStatus"])
};
scheduleList.Add(schedule);
}
reader.Close();
ConnectionObj.Close();
CommandObj.Dispose();
return(scheduleList);
}
public List <CountryViewerModel> GetCountryInformation()
{
try
{
string query = "SELECT * FROM CountryInformationWithCity ORDER BY Name";
CommandObj.CommandText = query;
List <CountryViewerModel> countryViewerModels = new List <CountryViewerModel>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
CountryViewerModel countryViewerModel = new CountryViewerModel();
countryViewerModel.CountryName = reader["Name"].ToString();
countryViewerModel.AboutCountry = reader["About"].ToString();
countryViewerModel.NoOfCities = Convert.ToInt32(reader["Total_City"].ToString());
countryViewerModel.NoOfCityDwellers = Convert.ToInt64(reader["Total_Dwellers"].ToString());
countryViewerModels.Add(countryViewerModel);
}
reader.Close();
return(countryViewerModels);
}
catch (Exception exception)
{
throw new Exception("Unable to collect country Information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public List <Teacher> GetAllTeachers()
{
string query = "SELECT * FROM Teacher_tbl";
CommandObj.CommandText = query;
List <Teacher> teachers = new List <Teacher>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Teacher teacher = new Teacher
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Address = reader["Address"].ToString(),
Email = reader["Email"].ToString(),
Contact = reader["Contact"].ToString(),
DesignationId = Convert.ToInt32(reader["DesignationId"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CreditTobeTaken = Convert.ToDecimal((reader["CreditToBeTaken"].ToString())),
CreditTaken = Convert.ToDecimal((reader["CreditTaken"].ToString()))
};
teachers.Add(teacher);
}
reader.Close();
ConnectionObj.Close();
return(teachers);
}
public IEnumerable <StudentResultViewModel> GetStudentResultByStudentId(int id)
{
try
{
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
CommandObj.CommandText = "spGetStudentResult";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@studentId", id);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel
{
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
Grade = reader["Grade"].ToString()
};
studentResults.Add(studentResult);
}
reader.Close();
return(studentResults);
}
catch (Exception exception)
{
throw new Exception("Unable to collect sudent result", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <Student> GetAllStudents()
{
string query = "SELECT * FROM Student_tbl";
CommandObj.CommandText = query;
List <Student> students = new List <Student>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Student student = new Student
{
Id = Convert.ToInt32(reader["Id"].ToString()),
RegNo = reader["RegNo"].ToString(),
Name = reader["Name"].ToString(),
Email = reader["Email"].ToString(),
Address = reader["Address"].ToString(),
Contact = reader["ContactNo"].ToString(),
RegDate = Convert.ToDateTime(reader["RegisterationDate"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString())
};
students.Add(student);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(students);
}
public int GetMaxVoucherNoByTransactionInfix(string infix)
{
try
{
CommandObj.CommandText = "spGetMaxVoucherNoOfByTransactionInfix";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@TransactionInfix", infix);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
int voucherNo = 0;
if (reader.Read())
{
voucherNo = Convert.ToInt32(reader["MaxVoucherNo"]);
}
reader.Close();
return(voucherNo);
}
catch (Exception exception)
{
throw new Exception("Could not collect max voucher no", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <EnrollStudentInCourse> GetEnrollCourses()
{
CommandObj.CommandText = "SELECT * FROM StudentEnrollInCourse_tbl";
List <EnrollStudentInCourse> enrollStudentInCourses = new List <EnrollStudentInCourse>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
EnrollStudentInCourse enrollStudentInCourse = new EnrollStudentInCourse
{
Id = Convert.ToInt32(reader["Id"].ToString()),
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
EnrollDate = Convert.ToDateTime(reader["EnrollDate"].ToString()),
Status = Convert.ToBoolean(reader["IsStudentActive"].ToString())
};
enrollStudentInCourses.Add(enrollStudentInCourse);
}
reader.Close();
ConnectionObj.Close();
CommandObj.Dispose();
return(enrollStudentInCourses);
}
public IEnumerable <Invoice> GetInvoicedRefferencesByClientId(int clientId)
{
try
{
CommandObj.CommandText = "spGetInvoicedRefferencesByClientId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ClientId", clientId);
List <Invoice> invoiceList = new List <Invoice>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Invoice invoice = new Invoice
{
Amounts = Convert.ToDecimal(reader["Amount"]),
InvoiceRef = reader["InvoiceRef"].ToString(),
InvoiceId = Convert.ToInt32(reader["InvoiceId"]),
ClientId = clientId
};
invoiceList.Add(invoice);
}
reader.Close();
return(invoiceList);
}
catch (Exception exception)
{
throw new Exception("Could not get Invoice reference by client id", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <Student> GetallStudent()
{
ConnectionObj.Open();
string select = String.Format("SELECT * FROM tblStudentInfo");
CommandObj.Parameters.Clear();
CommandObj.CommandText = select;
SqlDataReader reader = CommandObj.ExecuteReader();
List <Student> students = new List <Student>();
while (reader.Read())
{
Student aStudent = new Student();
aStudent.StudentId = Convert.ToInt32(reader[0]);
aStudent.StudentName = reader[1].ToString();
aStudent.RollNo = Convert.ToInt32(reader[2]);
aStudent.RegNo = Convert.ToInt32(reader[3]);
aStudent.Age = Convert.ToInt32(reader[4]);
students.Add(aStudent);
}
ConnectionObj.Close();
return(students);
}