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 int Insert(Student aStudent)
{
try
{
string query = "INSERT INTO t_Student VALUES(@RegNo,@Name,@Email,@ContactNo,@RegisterationDate,@Address,@DepartmentId)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@RegNo", aStudent.RegNo);
CommandObj.Parameters.AddWithValue("@Name", aStudent.Name);
CommandObj.Parameters.AddWithValue("@Email", aStudent.Email.ToLower());
CommandObj.Parameters.AddWithValue("@ContactNo", aStudent.Contact);
CommandObj.Parameters.AddWithValue("@RegisterationDate", aStudent.RegDate.ToShortDateString());
CommandObj.Parameters.AddWithValue("@Address", aStudent.Address);
CommandObj.Parameters.AddWithValue("@DepartmentId", aStudent.DepartmentId);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could Not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
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 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 int Update(EnrollStudentInCourse enrollStudentInCourse)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UPDATE t_StudentEnrollInCourse SET IsStudentActive=1 WHERE StudentId='" + enrollStudentInCourse.StudentId + "' AND CourseId='" + enrollStudentInCourse.CourseId + "'";
int updateResult = CommandObj.ExecuteNonQuery();
// int updateResult = UpdateStudentEnrolledCourses(enrollStudentInCourse);
sqlTransaction.Commit();
return(updateResult);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public int Insert(City aCity)
{
try
{
string query = "INSERT INTO t_City VALUES(@name,@about,@noOfDwellers,@location,@weather,@countryId)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@name", aCity.Name);
CommandObj.Parameters.AddWithValue("@about", aCity.About);
CommandObj.Parameters.AddWithValue("@noOfDwellers", aCity.NoOfDewellers);
CommandObj.Parameters.AddWithValue("@location", aCity.Location);
CommandObj.Parameters.AddWithValue("@weather", aCity.Weather);
CommandObj.Parameters.AddWithValue("@countryId", aCity.CountryId);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
ConnectionObj.Close();
CommandObj.Dispose();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Unable not save", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public int Insert(Teacher teacher)
{
try
{
CommandObj.CommandText = "spAddTeacher";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@Name", teacher.Name);
CommandObj.Parameters.AddWithValue("@Address", teacher.Address);
CommandObj.Parameters.AddWithValue("@Email", teacher.Email.ToLower());
CommandObj.Parameters.AddWithValue("@Contact", teacher.Contact);
CommandObj.Parameters.AddWithValue("@DesignationId", teacher.DesignationId);
CommandObj.Parameters.AddWithValue("@DepartmentId", teacher.DepartmentId);
CommandObj.Parameters.AddWithValue("@CreditTobeTaken", teacher.CreditTobeTaken);
CommandObj.Parameters.AddWithValue("@RemainingCredit", 0);
ConnectionObj.Open();
return(CommandObj.ExecuteNonQuery());
}
catch (Exception exception)
{
throw new Exception("Could Not save teacher", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
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 <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 <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 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 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 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 <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 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 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 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 <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 int Add(Discount discount)
{
try
{
CommandObj.CommandText = "UDSP_AddDiscount";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ProductId", discount.ProductId);
CommandObj.Parameters.AddWithValue("@ClientTypeId", discount.ClientTypeId);
CommandObj.Parameters.AddWithValue("@DiscountPercent", discount.DiscountPercent);
CommandObj.Parameters.AddWithValue("@UpdatedByUserId", discount.UpdateByUserId);
CommandObj.Parameters.AddWithValue("@UpdateDate", discount.UpdateDate);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
ConnectionObj.Open();
CommandObj.ExecuteNonQuery();
var rowAffected = Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not add discount", exception);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
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 int Add(Vat vat)
{
try
{
CommandObj.CommandText = "UDSP_AddVat";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ProductId", vat.ProductId);
CommandObj.Parameters.AddWithValue("@VatAmount", vat.VatAmount);
CommandObj.Parameters.AddWithValue("@UpdateDate", vat.UpdateDate);
CommandObj.Parameters.AddWithValue("@UpdateByUserId", vat.UpdateByUserId);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
ConnectionObj.Open();
CommandObj.ExecuteNonQuery();
int rowAffected = Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not save Vat info", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int UnAssignCourse()
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.CommandText = "UPDATE t_CourseAssignToTeacher SET IsActive=0";
CommandObj.Transaction = sqlTransaction;
CommandObj.ExecuteNonQuery();
teacherGateway.UpdateTeacherInformation();
int a = ResetStudentResult();
int i = UnAssignStudentCourse();
sqlTransaction.Commit();
return(i);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Failed to Unassign course", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
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 int Insert(Course aCourse)
{
try
{
CommandObj.CommandText = "spAddCourse";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@Code", aCourse.Code.ToUpper());
CommandObj.Parameters.AddWithValue("@Name", aCourse.Name);
CommandObj.Parameters.AddWithValue("@Credit", aCourse.Credit);
CommandObj.Parameters.AddWithValue("@Description", aCourse.Description);
CommandObj.Parameters.AddWithValue("@DepartmentId", aCourse.DepartmentId);
CommandObj.Parameters.AddWithValue("@SemesterId", aCourse.SemesterId);
ConnectionObj.Open();
return(CommandObj.ExecuteNonQuery());
}
catch (Exception exception)
{
throw new Exception("Unable to Seve course", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
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 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 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 int Add(EmployeeType model)
{
try
{
CommandObj.CommandText = "spAddNewEmployeeType";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@TypeName", model.EmployeeTypeName);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
ConnectionObj.Open();
CommandObj.ExecuteNonQuery();
var rowAffected = Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
return(rowAffected);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not add employee Type", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
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 <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);
}