学生管理系统

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
<connectionStrings>
  <add name ="constr" connectionString="Data Source=.;Initial Catalog=MySchool;Integrated Security=True"/>

</connectionStrings>
</configuration>

namespace MySchool.DAL
{
   public  class SqlHelper
    {
      // public static string constr = "Data Source=.;Initial Catalog=MySchool;Integrated Security=True";
       public static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
     
       public static int id;
       /// <summary>
       /// 执行NonQuery命令
       /// </summary>
       /// <param name="cmdTxt"></param>
       /// <param name="parames"></param>
       /// <returns></returns>
       public static int ExecuteNonQuery(string cmdTxt, params SqlParameter[] parames)
       {
           return ExecuteNonQuery(cmdTxt, CommandType.Text, parames);
       }
       //可以使用存储过程的ExecuteNonquery
       public static int ExecuteNonQuery(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
       {
           //判断脚本是否为空 ，直接返回0
           if (string.IsNullOrEmpty(cmdTxt))
           {
               return 0;
           }
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
               {
                   if (parames != null)
                   {
                       cmd.CommandType = cmdtype;
                       cmd.Parameters.AddRange(parames);
                   }
                   con.Open();
                   return cmd.ExecuteNonQuery();
               }
           }
       }
       public static SqlDataReader ExecuteDataReader(string cmdTxt, params SqlParameter[] parames)
       {
           return ExecuteDataReader(cmdTxt, CommandType.Text, parames);
       }
       //SQLDataReader存储过程方法
       public static SqlDataReader ExecuteDataReader(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
       {
           if (string.IsNullOrEmpty(cmdTxt))
           {
               return null;
           }
           SqlConnection con = new SqlConnection(constr);

           using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
           {
               cmd.CommandType = cmdtype;
               if (parames != null)
               {

                   cmd.Parameters.AddRange(parames);
               }
               con.Open();
               //把reader的行为加进来。当reader释放资源的时候，con也被一块关闭
               return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
           }

       }
       public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
       {
           return ExecuteDataTable(sql, CommandType.Text, parames);
       }
       //调用存储过程的类，关于(ExecuteDataTable)
       public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] parames)
       {
           if (string.IsNullOrEmpty(sql))
           {
               return null;
           }
           DataTable dt = new DataTable();
           using (SqlDataAdapter da = new SqlDataAdapter(sql, constr))
           {
               da.SelectCommand.CommandType = cmdType;
               if (parames != null)
               {
                   da.SelectCommand.Parameters.AddRange(parames);
               }
               da.Fill(dt);
               return dt;
           }
       }

       /// <summary>
       /// ExecuteScalar
       /// </summary>
       /// <param name="cmdTxt">第一个参数，SQLServer语句</param>
       /// <param name="parames">第二个参数，传递0个或者多个参数</param>
       /// <returns></returns>
       public static object ExecuteScalar(string cmdTxt, params SqlParameter[] parames)
       {
           return ExecuteScalar(cmdTxt, CommandType.Text, parames);
       }
       //可使用存储过程的ExecuteScalar
       public static object ExecuteScalar(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
       {
           if (string.IsNullOrEmpty(cmdTxt))
           {
               return null;
           }
           using (SqlConnection con = new SqlConnection(constr))
           {
               using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
               {
                   cmd.CommandType = cmdtype;
                   if (parames != null)
                   {
                       cmd.Parameters.AddRange(parames);
                   }
                   con.Open();
                   return cmd.ExecuteScalar();
               }
           }

       }
       //调用存储过程的DBHelper类（关于ExeceutScalar,包含事务,只能处理Int类型，返回错误号）
       public static object ExecuteScalar(string cmdTxt, CommandType cmdtype, SqlTransaction sqltran, params SqlParameter[] parames)
       {
           if (string.IsNullOrEmpty(cmdTxt))
           {
               return 0;
           }
           using (SqlConnection con = new SqlConnection(constr))
           {
               int sum = 0;
               using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
               {
                   cmd.CommandType = cmdtype;
                   if (parames != null)
                   {
                       cmd.Parameters.AddRange(parames);
                   }
                   con.Open();
                   sqltran = con.BeginTransaction();
                   try
                   {
                       cmd.Transaction = sqltran;
                       sum = Convert.ToInt32(cmd.ExecuteScalar());
                       sqltran.Commit();
                   }
                   catch (SqlException ex)
                   {
                       sqltran.Rollback();
                   }
                   return sum;
               }
           }
       }
    }

namespace MySchool.DAL
{
  public   class StudentDAL
    {


        //查询所有学生信息
        public List<StudentEx> GetAllStudentList()
        {
            List<StudentEx> list = new List<StudentEx>();

            string str = SqlHelper.constr;
            string sql = "select studentname,subjectname,studentresult from student,subject,result where student.studentno=result.studentno and result.subjectid=subject.subjectid";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            foreach (DataRow row in dt.Rows)
            {
                //一个StudentEx对象
                StudentEx stu = new StudentEx();
                stu.StudentName = row["studentname"].ToString();
                stu.SubName = row["subjectname"].ToString();
                stu.StudentResult = Convert.ToInt32(row["studentresult"]);

                list.Add(stu);
            }

            return list;
        }
      //判断身份证号是否存在
      //public static bool CheckIdentityCard(string identityCard)
      //{
      //    string sql = "select count(0) from dtudent where identitycard=";
      //}

      //输出参数的存储过程
      public DataTable GetAllStudentByProceduceOutput(string name, int gid, out  int totalnum)
      {
          string str = SqlHelper.constr;
          using (SqlConnection conn = new SqlConnection(str))
          {
              string sql = "usp_0319";
              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 Adapter = new SqlDataAdapter();
              DataSet da = new DataSet();
              Adapter.SelectCommand = cmd;
              Adapter.Fill(da, "studentInfo");
              totalnum = Convert.ToInt32((paras[2].Value));
              int myretrun = Convert.ToInt32((paras[3].Value));
              Co