/// <summary>
/// 获取前20条学生数据及其班级信息
/// </summary>
/// <returns></returns>
public List<Student> GetTop20Data()
{
List<Student> list = new List<Student>();
string sql = "select top 20 MyStudent.*,MyClass.className from MyStudent inner join MyClass on MyStudent.FClassId=MyClass.classId";
//Fid FName FAge FGender FMath FEnglish FClassId FBirthday className
using (SqlDataReader reader=SqlHelper.ExecuteReader(sql, System.Data.CommandType.Text))
{
if (reader.HasRows)
{
while (reader.Read())
{
Student s = new Student();
s.StudentId = reader.GetInt32(0);
s.Name = reader.GetString(1);
s.Age = reader.GetInt32(2);
s.Gender = reader.GetString(3);
s.MathScore = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);
s.EnglishScore = reader.GetInt32(5);
s.Birthday = reader.GetDateTime(7);
StudentClass studentClass = new StudentClass();
studentClass.ClassId = reader.GetInt32(6);
studentClass.ClassName = reader.GetString(8);
s.Myclass = studentClass;
list.Add(s);
}
}
}
return list;
}
/// <summary>
/// 插入一条新数据
/// </summary>
/// <param name="s">学生数据</param>
/// <returns>数据库影响条数</returns>
public int Insert(Student s)
{
string sql = "insert into MyStudent values(@name,@age,@gender,@math,@english,@classId,@birthday)";
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@name",s.Name),
new SqlParameter("@age",s.Age),
new SqlParameter("@gender",s.Gender),
new SqlParameter("@math",s.MathScore==null?DBNull.Value:(object)s.MathScore),
new SqlParameter("@english",s.EnglishScore),
new SqlParameter("@classId",s.Myclass.ClassId),
new SqlParameter("@birthday",s.Birthday)
};
return SqlHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, pars);
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="s"></param>
/// <returns>数据库影响条数</returns>
public int Update(Student s)
{
return dal.Update(s);
}
/// <summary>
/// 更新数据
/// </summary>
/// <param name="s"></param>
/// <returns>数据库影响条数</returns>
public int Update(Student s)
{
string sql = "update MyStudent set FName=@name,FAge=@age,FGender=@gender,FMath=@math,FEnglish=@english,FClassId=@classId,FBirthday=@birthday where Fid=@id";
SqlParameter[] pas = new SqlParameter[]{
new SqlParameter("@name",s.Name),
new SqlParameter("@age",s.Age),
new SqlParameter("@gender",s.Gender),
new SqlParameter("@math",s.MathScore==null?DBNull.Value:(object)s.MathScore),
new SqlParameter("@english",s.EnglishScore),
new SqlParameter("@classId",s.Myclass.ClassId),
new SqlParameter("@birthday",s.Birthday),
new SqlParameter("@id",s.StudentId)
};
return SqlHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, pas);
}
/// <summary>
/// 添加一条学生数据到数据库
/// </summary>
/// <param name="s">学生数据</param>
/// <returns>数据库影响条数</returns>
public int Insert(Student s)
{
return dal.Insert(s);
}
