public List <StudentEntityCl> GetAllStudentsDAL()
{
List <StudentEntityCl> studentList = null;
try
{
DbCommand command = DataConnection.CreateCommand();
command.CommandText = "uspGetAllStudents";
DataTable dataTable = DataConnection.ExecuteSelectCommand(command);
if (dataTable.Rows.Count > 0)
{
studentList = new List <StudentEntityCl>();
for (int rowCounter = 0; rowCounter < dataTable.Rows.Count; rowCounter++)
{
StudentEntityCl entobj = new StudentEntityCl();
entobj.STUDENTID = (int)dataTable.Rows[rowCounter][0];
entobj.STUDENTNAME = (string)dataTable.Rows[rowCounter][1];
entobj.CITY = (string)dataTable.Rows[rowCounter][2];
entobj.COURSE = (string)dataTable.Rows[rowCounter][3];
entobj.DATEOFADMISSION = (DateTime)dataTable.Rows[rowCounter][4];
studentList.Add(entobj);
}
}
}
catch (DbException ex)
{
throw new StudentExceptionCl(ex.Message);
}
return(studentList);
}
public static bool UpdateStudentBL(StudentEntityCl entobj)
{
bool studentUpdated = false;
try
{
StudentDALCl dal = new StudentDALCl();
studentUpdated = dal.UpdateStudentDAL(entobj);
//if (ValidateGuest(updateGuest))
//{
// GuestDAL guestDAL = new GuestDAL();
// guestUpdated = guestDAL.UpdateGuestDAL(updateGuest);
//}
}
catch (StudentExceptionCl)
{
throw;
}
catch (Exception ex)
{
throw ex;
}
return(studentUpdated);
}
public static bool Validation(StudentEntityCl entobj)
{
bool valid = true;
StringBuilder build = new StringBuilder();
//if((entobj.STUDENTID==null) ||(entobj.STUDENTNAME==null) || (entobj.CITY==null)
// || (entobj.COURSE==null)|| (entobj.DATEOFADMISSION==null))
//{
// valid = false;
// build.AppendLine("Please fill all the fields");
//}
if (entobj.STUDENTID <= 0)
{
valid = false;
build.AppendLine("1.ID cannot be negative");
}
if (!Regex.IsMatch(entobj.STUDENTNAME, @"^[a-zA-Z]+$"))
{
valid = false;
build.AppendLine("2.Name must contain alphabets only");
}
if (!Regex.IsMatch(entobj.CITY, @"^[a-zA-Z]+$"))
{
valid = false;
build.AppendLine("3.City must contain Alphabets only");
}
if (!Regex.IsMatch(entobj.COURSE, @"JAVA|DOTNET|J2EE|Python"))
{
valid = false;
build.AppendLine("4.Courses must be among specified ones");
}
if (entobj.DATEOFADMISSION > DateTime.Now)
{
valid = false;
build.AppendLine("5.Date cannot be greater than current date");
}
if (valid == false)
{
MessageBox.Show(build.ToString());
}
return(valid);
}
private void dataGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
StudentEntityCl entobj = (StudentEntityCl)(object)(dataGrid.SelectedItem);
textBox.Text = entobj.STUDENTID.ToString();
textBox1.Text = entobj.STUDENTNAME;
textBox2.Text = entobj.CITY;
textBox3.Text = entobj.COURSE;
DOA.Text = entobj.DATEOFADMISSION.ToString();
}
catch (Exception ex)
{
}
}
private void AddStudent_Click(object sender, RoutedEventArgs e)
{
if (!insertready)
{
fieldReset();
int id = StudentBALCl.getNextIdBAL();
textBox.Text = id.ToString();
textBox.IsReadOnly = true;
insertready = true;
}
else
{
try
{
StudentEntityCl entobj = new StudentEntityCl();
entobj.STUDENTID = Convert.ToInt32(textBox.Text);
entobj.STUDENTNAME = textBox1.Text;
entobj.CITY = textBox2.Text;
entobj.COURSE = textBox3.Text;
entobj.DATEOFADMISSION = Convert.ToDateTime(DOA.Text);
if (StudentBALCl.AddStudentBL(entobj))
{
MessageBox.Show("Student Successfully Added");
insertready = false;
textBox.IsReadOnly = false;
}
else
{
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
private void Find_Click(object sender, RoutedEventArgs e)
{
try
{
int flag = 0;
int id = Convert.ToInt32(textBox.Text);
StudentEntityCl entobj = StudentBALCl.SearchStudentBL(id);
List <StudentEntityCl> showlist = StudentBALCl.GetAllStudentsBL();
for (int index = 0; index < showlist.Count; index++)
{
if (showlist[index].STUDENTID == id)
{
flag = 1;
break;
}
else
{
flag = 0;
}
}
if (flag == 0)
{
MessageBox.Show("No such student found");
Environment.Exit(0);
}
// MessageBox.Show(entobj.DATEOFADMISSION.ToString());
textBox1.Text = entobj.STUDENTNAME;
textBox2.Text = entobj.CITY;
textBox3.Text = entobj.COURSE;
DOA.Text = entobj.DATEOFADMISSION.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void UpdateStudent_Click(object sender, RoutedEventArgs e)
{
try
{
StudentEntityCl entobj = new StudentEntityCl();
entobj.STUDENTID = Convert.ToInt32(textBox.Text);
entobj.STUDENTNAME = textBox1.Text;
entobj.CITY = textBox2.Text;
entobj.COURSE = textBox3.Text;
entobj.DATEOFADMISSION = Convert.ToDateTime(DOA.Text);
StudentBALCl.UpdateStudentBL(entobj);
MessageBox.Show("Student Updated Successfully");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static StudentEntityCl SearchStudentBL(int studentID)
{
StudentEntityCl entobj = null;
try
{
StudentDALCl dal = new StudentDALCl();
entobj = dal.SearchStudentDAL(studentID);
}
catch (StudentExceptionCl ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return(entobj);
}
public static bool AddStudentBL(StudentEntityCl entobj)
{
bool studentAdded = false;
try
{
if (Validation(entobj))
{
StudentDALCl dal = new StudentDALCl();
studentAdded = dal.AddStudentDAL(entobj);
}
}
catch (StudentExceptionCl ex)
{
MessageBox.Show(ex.ToString());
}
catch (Exception ex)
{
throw ex;
}
return(studentAdded);
}
public StudentEntityCl SearchStudentDAL(int searchStudentID)
{
StudentEntityCl searchStudent = null;
try
{
DbCommand command = DataConnection.CreateCommand();
command.CommandText = "uspSearchStudent";
DbParameter param = command.CreateParameter();
param.ParameterName = "@ipv_iStudentID";
param.DbType = DbType.Int32;
param.Value = searchStudentID;
command.Parameters.Add(param);
DataTable dataTable = DataConnection.ExecuteSelectCommand(command);
if (dataTable.Rows.Count > 0)
{
searchStudent = new StudentEntityCl();
searchStudent.STUDENTID = (int)dataTable.Rows[0][0];
searchStudent.STUDENTNAME = (string)dataTable.Rows[0][1];
searchStudent.CITY = (string)dataTable.Rows[0][2];
searchStudent.COURSE = (string)dataTable.Rows[0][3];
searchStudent.DATEOFADMISSION = Convert.ToDateTime(dataTable.Rows[0][4]);
}
}
catch (DbException ex)
{
throw new StudentExceptionCl(ex.Message);
}
catch (Exception ex) {
throw new StudentExceptionCl(ex.Message);
}
return(searchStudent);
}
public bool AddStudentDAL(StudentEntityCl entobj)
{
bool studentAdded = false;
try
{
DbCommand command = DataConnection.CreateCommand();
//PROCEDURE NAME
command.CommandText = "uspAddStudent";
//ID
DbParameter param = command.CreateParameter();
param.ParameterName = "@ipv_iStudentID";
param.DbType = DbType.Int32;
param.Value = entobj.STUDENTID;
command.Parameters.Add(param);
//NAME
param = command.CreateParameter();
param.ParameterName = "@ipv_vcStudentName";
param.DbType = DbType.String;
param.Value = entobj.STUDENTNAME;
command.Parameters.Add(param);
//CITY
param = command.CreateParameter();
param.ParameterName = "@ipv_vcCity";
param.DbType = DbType.String;
param.Value = entobj.CITY;
command.Parameters.Add(param);
//COURSE
param = command.CreateParameter();
param.ParameterName = "@ipv_vcCourse";
param.DbType = DbType.String;
param.Value = entobj.COURSE;
command.Parameters.Add(param);
//DATE OF ADMISSION
param = command.CreateParameter();
param.ParameterName = "@ipv_vcDOA";
param.DbType = DbType.String;
param.Value = entobj.DATEOFADMISSION;
command.Parameters.Add(param);
int affectedRows = DataConnection.ExecuteNonQueryCommand(command);
if (affectedRows > 0)
{
studentAdded = true;
}
}
catch (DbException ex)
{
string errormessage;
switch (ex.ErrorCode)
{
case -2146232060:
errormessage = "Database Does NotExists Or AccessDenied";
break;
default:
errormessage = ex.Message;
break;
}
throw new StudentExceptionCl(errormessage);
}
return(studentAdded);
}
public bool UpdateStudentDAL(StudentEntityCl entobj)
{
bool updateStudent = false;
try
{
DbCommand command = DataConnection.CreateCommand();
command.CommandText = "uspUpdateStudent";
//ID
DbParameter param = command.CreateParameter();
param.ParameterName = "@ipv_iStudentID";
param.DbType = DbType.Int32;
param.Value = entobj.STUDENTID;
command.Parameters.Add(param);
//NAME
param = command.CreateParameter();
param.ParameterName = "@ipv_vcStudentName";
param.DbType = DbType.String;
param.Value = entobj.STUDENTNAME;
command.Parameters.Add(param);
//CITY
param = command.CreateParameter();
param.ParameterName = "@ipv_vcCity";
param.DbType = DbType.String;
param.Value = entobj.CITY;
command.Parameters.Add(param);
//COURSE
param = command.CreateParameter();
param.ParameterName = "@ipv_vcCourse";
param.DbType = DbType.String;
param.Value = entobj.COURSE;
command.Parameters.Add(param);
//DATE OF ADMISSION
param = command.CreateParameter();
param.ParameterName = "@ipv_vcDOA";
param.DbType = DbType.String;
param.Value = entobj.DATEOFADMISSION;
command.Parameters.Add(param);
int affectedRows = DataConnection.ExecuteNonQueryCommand(command);
if (affectedRows > 0)
{
updateStudent = true;
}
}
catch (DbException ex)
{
throw new StudentExceptionCl(ex.Message);
}
return(updateStudent);
}
