//Created by Yogalakshmi on 17.07.2013
//Modified by Vivek on 23.07.2013
/// <summary>
/// Gets the student info by roll number.
/// </summary>
/// <param name="rollNumber">The roll number.</param>
/// <returns>Student</returns>
public StudentDSR GetStudentInfoByRollNumber(int rollNumber)
{
StudentDSR studentDSR = null;
using (var connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[usp_get_student_info_by_roll_number]";
cmd.Parameters.Add(new SqlParameter("@roll_number", rollNumber));
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
if (dataReader.HasRows)
{
int studentNameOrdinal = dataReader.GetOrdinal("student_name");
int standardOrdinal = dataReader.GetOrdinal("standard");
int rollNumberOrdinal = dataReader.GetOrdinal("roll_number");
int languageOrdinal = dataReader.GetOrdinal("language");
int englishOrdinal = dataReader.GetOrdinal("english");
int mathsOrdinal = dataReader.GetOrdinal("maths");
int scienceOrdinal = dataReader.GetOrdinal("science");
int socialOrdinal = dataReader.GetOrdinal("social");
int totalOrdinal = dataReader.GetOrdinal("total");
int resultOrdinal = dataReader.GetOrdinal("result");
while (dataReader.Read())
{
studentDSR=new StudentDSR()
{
roll_number = dataReader.GetInt32(rollNumberOrdinal),
student_name = dataReader.GetString(studentNameOrdinal),
standard = dataReader.GetString(standardOrdinal),
language = dataReader.GetInt32(rollNumberOrdinal),
english = dataReader.GetInt32(englishOrdinal),
maths = dataReader.GetInt32(mathsOrdinal),
science = dataReader.GetInt32(scienceOrdinal),
social = dataReader.GetInt32(socialOrdinal),
total = dataReader.GetInt32(totalOrdinal),
result = Convert.ToChar(dataReader.GetValue(resultOrdinal)),
};
}
}
}
}
}
return studentDSR;
}
//Created by Vivek on 17.07.2013
/// <summary>
/// Gets the by search.
/// </summary>
/// <param name="studentDSR">The student DSR.</param>
/// <returns>List of StudentDSR</returns>
public List<StudentDSR> GetBySearch(StudentDSR studentDSR)
{
List<StudentDSR> studentLst = null;
StudentDSR studentDsr = null;
using (var connection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[usp_student_search]";
studentDsr = new StudentDSR();
if (studentDSR.student_name == null && studentDsr.student_name == string.Empty)
{
command.Parameters.Add(new SqlParameter("@student_name", SqlDbType.NVarChar)).Value = null;
}
else
{
command.Parameters.Add(new SqlParameter("@student_name", studentDSR.student_name));
}
if (studentDSR.roll_number != null)
{
command.Parameters.Add(new SqlParameter("@roll_number", SqlDbType.Int)).Value = null;
}
else
{
command.Parameters.Add(new SqlParameter("@roll_number", studentDSR.roll_number));
}
if (studentDsr.result != null)
{
command.Parameters.Add(new SqlParameter("@result", SqlDbType.VarChar)).Value = null;
}
else
{
command.Parameters.Add(new SqlParameter("@result", studentDSR.result));
}
if (studentDSR.standard == null && studentDsr.standard == string.Empty)
{
command.Parameters.Add(new SqlParameter("@standard", SqlDbType.NVarChar)).Value = null;
}
else
{
command.Parameters.Add(new SqlParameter("@standard", studentDSR.standard));
}
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
studentLst = new List<StudentDSR>();
int regnoOrdinal = dataReader.GetOrdinal("roll_number");
int studentsNameOrdinal = dataReader.GetOrdinal("student_name");
int standardOrdinal = dataReader.GetOrdinal("standard");
int languageOrdinal = dataReader.GetOrdinal("language");
int englishOrdinal = dataReader.GetOrdinal("english");
int mathsOrdinal = dataReader.GetOrdinal("maths");
int ScienceOrdinal = dataReader.GetOrdinal("science");
int socialOrdinal = dataReader.GetOrdinal("social");
int totalOrdinal = dataReader.GetOrdinal("total");
int resultOrdinal = dataReader.GetOrdinal("result");
while (dataReader.Read())
{
studentDsr = new StudentDSR
{
roll_number = dataReader.GetInt32(regnoOrdinal),
student_name = dataReader.GetString(studentsNameOrdinal),
standard=dataReader.GetString(standardOrdinal),
language = dataReader.GetInt32(languageOrdinal),
english = dataReader.GetInt32(englishOrdinal),
maths = dataReader.GetInt32(mathsOrdinal),
science = dataReader.GetInt32(ScienceOrdinal),
social = dataReader.GetInt32(socialOrdinal),
total = dataReader.GetInt32(totalOrdinal),
result = Convert.ToChar(dataReader.GetValue(resultOrdinal))
};
studentLst.Add(studentDsr);
}
}
}
}
}
return studentLst;
}
//Created by Vivek on 18.07.2013
/// <summary>
/// Students the search.
/// </summary>
/// <returns></returns>
public static StudentDSR StudentSearch()
{
var search = new StudentDSR
{
roll_number = 2,
student_name = "vivek",
result = 'P',
};
return search;
}
public void StudentDeleteTest()
{
//Creating a Student
Student student = CreateStudent();
Assert.AreNotEqual(0, student.RollNumber);
//Deleting a Student
student.Modified_By = 2;
mStudentDataService.DeleteStudent(student.RollNumber);
//Getting Student details by RollNumber
StudentDSR studentDSR = new StudentDSR();
var expected = mStudentDataService.GetStudentInfoByRollNumber(studentDSR.roll_number);
Assert.AreEqual(expected.roll_number, student.RollNumber);
}
//Created by Vivek on 18.07.2013
/// <summary>
/// Students the search.
/// </summary>
/// <returns></returns>
public static StudentDSR StudentSearch()
{
var searchList = new List<StudentDSR>();
var search = new StudentDSR
{
roll_number = 2,
student_name = "Vivek",
standard = "X",
result = 'P',
};
searchList = studentDataService.GetBySearch(search);
return search;
}
//Created by Vivek on 18.07.2013
/// <summary>
/// Gets the by search.
/// </summary>
/// <param name="studentDSR">The student DSR.</param>
/// <returns>StudentDSRList</returns>
public List<StudentDSR> GetBySearch(StudentDSR studentDSR)
{
return studentDataService.GetBySearch(studentDSR);
}
