数据访问层

namespace MySchool2.DAL
{
   public  class GradeDAL
    {
       public List<GradeMode> GetGradeEx()
       {
           string constr = SQLHelper.Constr;
           string sql = " select *  from  grade";
           List<GradeMode > gradeList = new List<GradeMode>();
           using (SqlConnection conn = new SqlConnection(constr))
           {
               try
               {
                   SqlCommand cmd = new SqlCommand(sql, conn);
                   conn.Open();
                   SqlDataReader dr = cmd.ExecuteReader();
                   while (dr.Read())
                   {
                       GradeMode item = new GradeMode();
                       item.GradeId = Convert.ToInt32(dr["GradeId"]);
                       item.GradeName = dr["GradeName"].ToString();
                       gradeList.Add(item);
                   }
                   dr.Close();
                   return gradeList;
               }
               catch (SqlException ex)
               {
                   Console.WriteLine(ex.Message);
               }
               catch (Exception ex)
               {

                   return null;
               }
           }

           return gradeList;
       }
    }
}

namespace MySchool2.DAL
{
   public  class ResultDAL
   {
      
       public List<ResultMode> StudetnResult(string subjectno, string stuname)
       {
           string constr = SQLHelper.Constr;
           StringBuilder sb = new StringBuilder();
           sb.Append(@"select a.* from result a join student b  on  a.studentno=b.studentno  where 1=1");
           if (subjectno != "-1")
           {
               sb.Append(" and a.subjectno=@subjectno");
           }
           if (!string.IsNullOrEmpty(stuname))
           {
               sb.Append(" and b.studentname like '%@studentname%'");
           }
           SqlParameter[] para =
           {
               new SqlParameter("@subjectno", subjectno),
               new SqlParameter("studentname", stuname)
           };
           return GetScoresBySql(sb.ToString(), para);
       }

       private static List<ResultMode> GetScoresBySql(string sql, params SqlParameter[] paras)
       {
           string constr = SQLHelper.Constr;
           using (SqlConnection conn = new SqlConnection(constr))
           {
               SqlCommand cmd = new SqlCommand(sql, conn);
               cmd.Parameters.AddRange(paras);
               conn.Open();
               SqlDataReader reader = cmd.ExecuteReader();
               List<ResultMode> resultList = new List<ResultMode>();
               while (reader.Read())
               {
                   ResultMode result = new ResultMode();
                   result.StudentNo = Convert.ToInt32(reader["studentno"]);
                   result.SubjectId = Convert.ToInt32(reader["subjectid"]);
                   result.StudentResult = Convert.ToInt16(reader["studentresult"]);
                   result.ExamDate = Convert.ToDateTime(reader["examdate"]);
                   resultList.Add(result);
               }
               reader.Close();
               return resultList;
           }
          
       }
    }
}

namespace MySchool2.DAL
{
   public  class StudentDAL
    {
       public DataTable GetStudentName()
       {
           string str = SQLHelper.Constr;
           SqlConnection conn = new SqlConnection(str);
       
               conn.Open();
               string sql = @"select  student.studentname,subject.subjectname,result.studentresult,result.ExamDate  from  Student,subject,result  where
                  student. studentno=result.studentno and result.subjectid=subject.subjectid
                         ";
               SqlDataAdapter da = new SqlDataAdapter(sql, conn);
               DataSet ds = new DataSet();

               da.Fill(ds, "student");
               return ds.Tables["student"];
           }

       public DataTable GetAllStudentByProcedureOutput(string name, int gid, out int totalnum, out int myreturn)
       {
          
           string str = SQLHelper.Constr;
           SqlConnection conn = new SqlConnection(str);
           string sql = "usp_03201";
           SqlCommand cmd = new SqlCommand(sql, conn);
           SqlParameter[] paras =
           {
               new SqlParameter("@gender", name),
               new SqlParameter("gradeid", gid),
               new SqlParameter("@countnum", SqlDbType.Int),
               new SqlParameter( "@myreturn",SqlDbType.Int)
           };
           paras[2].Direction = ParameterDirection.Output;
           paras[3].Direction = ParameterDirection.ReturnValue;

           cmd.Parameters.AddRange(paras);
           cmd.CommandType = CommandType.StoredProcedure;
           SqlDataAdapter da = new SqlDataAdapter();
           da.SelectCommand = cmd;
           DataSet ds = new DataSet();
           da.Fill(ds, "stuinfo");
           totalnum = Convert.ToInt32(paras[2].Value);
           myreturn = Convert.ToInt32(paras[3].Value);
           Console.WriteLine(totalnum);
           Console.WriteLine(myreturn);
           return ds.Tables["stuinfo"];

       }
       }
    }

namespace MySchool2.DAL
{
   public  class SubjectDAL
    {
       public List<SubjectMode> GetSubjectEx(int gradeid)
       {
           string sql = "select  *  from  subject where  gradeid=@a";  
           string constr = SQLHelper.Constr;
           SqlParameter[] para = new SqlParameter[]
           {
               new SqlParameter("@a", gradeid)
           };
           List<SubjectMode> subjectList = new List<SubjectMode>();
           using (SqlConnection conn = new SqlConnection(constr))
           {
               try
               {
                   SqlCommand cmd = new SqlCommand(sql, conn);
                   cmd.Parameters.AddRange(para);
                   conn.Open();                 
                   SqlDataReader dr = cmd.ExecuteReader();
                   while (dr.Read())
                   {
                       SubjectMode item = new SubjectMode();
                       item.SubjectId = Convert.ToInt32(dr["subjectid"]);
                       item.GradeId = Convert.ToInt32(dr["gradeid"]);
                       item.ClassHour = Convert.ToInt32(dr["ClassHour"]);
                       item.SubjectName = dr["subjectname"].ToString();
                       subjectList.Add(item);
                   }
                   dr.Close();
                  return subjectList;

               }
               catch (SqlException ex)
               {
                
               }
               catch (Exception ex)
               {

                
               }
           }
           return subjectList;
       }

      
    }
}