public static void Delete(Student Student)
{
_command = new SqlCommand("delete from StudentList where SerialNo=@SerialNo");
_command.Parameters.AddWithValue("@SerialNo", Student.SerialNo);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
_command.Connection = _connection;
_command.Transaction = _transaction;
DalCourse.Delete(true, Student.StudentID, _connection, _transaction);
_command.ExecuteNonQuery();
_transaction.Commit();
_connection.Close();
}
catch (Exception ex)
{
_transaction.Rollback();
_connection.Close();
throw ex;
}
}
public static List <Student> GetAllStudent()
{
List <Student> StudentList = new List <Student>();
DataTable dataTable = new DataTable();
string query = string.Empty;
query = "select * from StudentList";
_adapter = new SqlDataAdapter(query, _connection);
try
{
_connection.Open();
_adapter.Fill(dataTable);
_connection.Close();
IEnumerable <DataRow> rows = dataTable.Rows.Cast <DataRow>();
foreach (var row in rows)
{
Student Student = MapStudent(row);
List <Course> courses = DalCourse.GetByStudentId(Student.StudentID);
Student.Courses = courses;
StudentList.Add(Student);
}
}
catch (Exception ex)
{
_connection.Close();
throw ex;
}
return(StudentList);
}
public static void Insert(Student Student)
{
_command = new SqlCommand("insert into StudentList(StudentID, Name, Gender, [Group]) values(@StudentID, @Name, @Gender, @Group)");
_command.Parameters.AddWithValue("@StudentID", Student.StudentID);
_command.Parameters.AddWithValue("@Name", Student.Name);
_command.Parameters.AddWithValue("@Gender", Student.Gender);
_command.Parameters.AddWithValue("@Group", Student.Group);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
_command.Connection = _connection;
_command.Transaction = _transaction;
_command.ExecuteNonQuery();
foreach (var course in Student.Courses)
{
course.StudentID = Student.StudentID;
DalCourse.Insert(true, course, _connection, _transaction);
}
_transaction.Commit();
_connection.Close();
}
catch (Exception ex)
{
_transaction.Rollback();
_connection.Close();
throw ex;
}
}
public static void Update(Student Student)
{
_command = new SqlCommand("update StudentList set StudentID = @StudentID, Name= @Name, Gender = @Gender, [Group] = @Group where SerialNo=@SerialNo");
_command.Parameters.AddWithValue("@StudentID", Student.StudentID);
_command.Parameters.AddWithValue("@Name", Student.Name);
_command.Parameters.AddWithValue("@Gender", Student.Gender);
_command.Parameters.AddWithValue("@Group", Student.Group);
_command.Parameters.AddWithValue("@SerialNo", Student.SerialNo);
try
{
_connection.Open();
_transaction = _connection.BeginTransaction();
_command.Connection = _connection;
_command.Transaction = _transaction;
_command.ExecuteNonQuery();
DalCourse.Delete(true, Student.StudentID, _connection, _transaction);
foreach (var course in Student.Courses)
{
DalCourse.Insert(true, course, _connection, _transaction);
}
_transaction.Commit();
_connection.Close();
}
catch (Exception ex)
{
_transaction.Rollback();
_connection.Close();
throw ex;
}
}