public DataTable ReadFullByClass(DataTable table, Object.ClassStudent classStudent)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select Id,StudentInClassId,ClassName,ClassId,StudentName,StudentId,TimesAbsent,TimesExcused,TimesLate From vwStudentInfoInClassFull_tblStudentInfoInClass Where ClassId = @ClassId", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ClassId", classStudent.ClassId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["Id"] = rdr["Id"];
row["StudentInClassId"] = rdr["StudentInClassId"];
row["ClassName"] = rdr["ClassName"];
row["ClassId"] = rdr["ClassId"];
row["StudentName"] = rdr["StudentName"];
row["StudentId"] = rdr["StudentId"];
row["TimesAbsent"] = rdr["TimesAbsent"];
row["TimesExcused"] = rdr["TimesExcused"];
row["TimesLate"] = rdr["TimesLate"];
table.Rows.Add(row);
}
return(table);
}
}
}
public int Update(Object.ClassStudent classStudent)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("spUpdateStudentInClass_tblStudentInfoInClass", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", classStudent.Id);
cmd.Parameters.AddWithValue("@ClassId", classStudent.ClassId);
cmd.Parameters.AddWithValue("@StudentInClassId", classStudent.StudentInClassId);
cmd.Parameters.AddWithValue("@StudentId", classStudent.StudentId);
cmd.Parameters.AddWithValue("@TimesAbsent", classStudent.TimesAbsent);
cmd.Parameters.AddWithValue("@TimesExcused ", classStudent.TimesExcused);
cmd.Parameters.AddWithValue("@TimesLate", classStudent.TimesLate);
SqlParameter Result = new SqlParameter("@ResultNumber", DbType.Int32);
Result.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Result);
con.Open();
cmd.ExecuteScalar();
con.Close();
return((int)Result.Value);
}
}
public DataTable ReadStudentWithClassId(DataTable table, Object.ClassStudent classStudent)
{
using (SqlConnection con = new SqlConnection(DataAccessLayer.SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Select StudentInClassId,FullName From vwStudentInfoWithId_tblStudentInfo Where ClassId = @ClassId", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ClassId", classStudent.ClassId);
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
DataRow row = table.NewRow();
row["StudentInClassId"] = rdr["StudentInClassId"];
row["FullName"] = rdr["StudentInClassId"].ToString() + " " + rdr["FullName"].ToString();
table.Rows.Add(row);
}
return(table);
}
}
}
public DataTable ReadFullByClass(int ClassId)
{
DataTable table = new DataTable();
table.Columns.Add("Id");
table.Columns.Add("StudentInClassId");
table.Columns.Add("ClassName");
table.Columns.Add("ClassId");
table.Columns.Add("StudentName");
table.Columns.Add("StudentId");
table.Columns.Add("TimesAbsent");
table.Columns.Add("TimesExcused");
table.Columns.Add("TimesLate");
if (ClassId == 0)
{
return(crud.ReadFull(table));
}
else
{
Object.ClassStudent classStudent = new Object.ClassStudent();
classStudent.ClassId = ClassId;
return(crud.ReadFullByClass(table, classStudent));
}
}
public void Delete(int Id, out string Message, out System.Drawing.Color Color)
{
Object.ClassStudent classStudent = new Object.ClassStudent();
classStudent.Id = Id;
int ResultNumber = crud.Delete(classStudent);
message.Delete(ResultNumber, out Message, out Color);
}
public DataTable ReadStudentWithClassId(int ClassId)
{
DataTable table = new DataTable();
table.Columns.Add("StudentInClassId");
table.Columns.Add("FullName");
Object.ClassStudent classStudent = new Object.ClassStudent();
classStudent.ClassId = ClassId;
return(crud.ReadStudentWithClassId(table, classStudent));
}
public int Delete(Object.ClassStudent classStudent)
{
using (SqlConnection con = new SqlConnection(SharedMethods.getConnectionString()))
{
SqlCommand cmd = new SqlCommand("Delete From tblStudentInfoInClass Where Id = @Id", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Id", classStudent.Id);
con.Open();
return((int)cmd.ExecuteNonQuery());
}
}
public void Create(int StudentInClassId, int ClassId, int StudentId, int TimesAbsent, int TimesExcused, float TimesLate, out string Message, out System.Drawing.Color Color)
{
Object.ClassStudent classStudent = new Object.ClassStudent();
classStudent.StudentInClassId = StudentInClassId;
classStudent.ClassId = ClassId;
classStudent.StudentId = StudentId;
classStudent.TimesAbsent = TimesAbsent;
classStudent.TimesExcused = TimesExcused;
classStudent.TimesLate = TimesLate;
int ResultNumber = crud.Create(classStudent);
message.Create(ResultNumber, out Message, out Color);
}
