ASP.NET MVC 排球计分程序 （四）实体类和SqlHelper类的设计

整体解决方案的图如下：

、

在Model里添加一个叫PersonA的类

public class PersonA
    {
        
        public int id { get; set; }
        public string name { get; set; }
    }

添加一个叫PersonB的类

    public class PersonB
    {
        public int id { get; set; }
        public string name { get; set; }
    }

添加一个叫ScoreA的类

public class ScoreA
{
public int id { get; set; }
public int personId { get; set; }
public string jiQiu { get; set; }
public string isScore { get; set; }
}

添加一个叫ScoreB的类

 public class ScoreB
    {
        public int id { get; set; }
        public int personId { get; set; }
        public string jiQiu { get; set; }
        public string isScore { get; set; }
    }

在解决方案根目录下的Webconfig里添加数据库的连接字符串

<connectionStrings>
    <add name="connstr" connectionString="Data Source=.;Initial Catalog=volleyball;Integrated Security=True"/>
  </connectionStrings>

创建一个Infrastructure文件夹

添加一个SqlHelper类

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using 排球计分程序.Models;

namespace 排球计分程序.Infrastructure
{
    public enum jiQiu
    {
        发球,扣球,抹球,拦网,一传
    }
    public class SqlHelper
    {

        private static readonly String constr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

        //执行增删改
        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {

                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);

                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        //执行返回单个值得
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }
        //执行返回sqldatareader
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                try
                {
                    con.Open();
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    con.Close();
                    con.Dispose();
                    throw;
                }

            }

        }
        //执行返回datatable
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
        {
            DataTable dt = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
            {
                if (pms != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pms);

                }
                adapter.Fill(dt);
                return dt;
            }

        }
        //执行删除Person表中数据  
        public static int ClearBiaoRen()
        {
            //using (SqlConnection conn = new SqlConnection(constr))
            //{
            int a = 0;
            //    using (SqlCommand comm = new SqlCommand("delete from PersonA", conn))
            //    {
            //         SqlCommand com = new SqlCommand("delete from PersonB", conn);
            //            conn.Open();
            //        a = comm.ExecuteNonQuery() + com.ExecuteNonQuery();
            //        ClearBiaoFen();
            return a;
            //    }
                
            //}
        }
        //返回PersonA表中的内容
        public static List<PersonA> GetAllPersonA()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {

                using (SqlCommand comm = new SqlCommand("select * from PersonA", conn))
                {
                    //查询出PersonA中所有内容
                    List<PersonA> personA = new List<PersonA>();
                    conn.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {
                        
                        while (dr.Read())
                        {
                            PersonA per = new PersonA();
                            per.id= int.Parse(dr["id"].ToString());
                            per.name=dr["name"].ToString();
                            personA.Add(per);
                            //personA.a(dr["id"], dr["name"]);
                        }
                    }
                    return personA;
                }
            }
        }
        //返回PersonB表中的内容
        public static List<PersonB> GetAllPersonB()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {

                using (SqlCommand comm = new SqlCommand("select * from PersonB", conn))
                {
                    //查询出PersonB中所有内容
                    List<PersonB> personB = new List<PersonB>();
                    conn.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {

                        while (dr.Read())
                        {
                            PersonB per = new PersonB();
                            per.id = int.Parse(dr["id"].ToString());
                            per.name = dr["name"].ToString();
                            personB.Add(per);
                            //personA.a(dr["id"], dr["name"]);
                        }
                    }
                    return personB;
                }
            }
        }
        //返回ScoreA表中的内容
        public static List<ScoreA> GetAllScoreA()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {

                using (SqlCommand comm = new SqlCommand("select * from ScoreA", conn))
                {
                    //查询出PersonB中所有内容
                    List<ScoreA> scoreA = new List<ScoreA>();
                    conn.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {

                        while (dr.Read())
                        {
                            ScoreA sco = new ScoreA();
                            sco.id = int.Parse(dr["id"].ToString());
                            sco.personId = int.Parse(dr["personId"].ToString());
                            sco.jiQiu = dr["jiQiu"].ToString();
                            sco.isScore = dr["isScore"].ToString();
                            scoreA.Add(sco);
                            //personA.a(dr["id"], dr["name"]);
                        }
                    }
                    return scoreA;
                }
            }
        }

        //返回ScoreB表中的内容
        public static List<ScoreB> GetAllScoreB()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {

                using (SqlCommand comm = new SqlCommand("select * from ScoreB", conn))
                {
                    //查询出PersonB中所有内容
                    List<ScoreB> scoreB = new List<ScoreB>();
                    conn.Open();
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows)
                    {

                        while (dr.Read())
                        {
                            ScoreB sco = new ScoreB();
                            sco.id = int.Parse(dr["id"].ToString());
                            sco.personId = int.Parse(dr["personId"].ToString());
                            sco.jiQiu = dr["jiQiu"].ToString();
                            sco.isScore = dr["isScore"].ToString();
                            scoreB.Add(sco);
                            //personA.a(dr["id"], dr["name"]);
                        }
                    }
                    return scoreB;
                }
            }
        }
        //返回A中得分最高的人
        public static string getAMaxScore()
        {
            string str = "";
            string sql = "select count(*) as a,personId,isScore from ScoreA where isScore='是' group by personId, isScore  order by count(*) desc;";
            SqlDataReader dr= ExecuteReader(sql, null);
            if (dr.HasRows)
            {
                dr.Read();
                str += "分数最高的人是：" + dr["personId"].ToString();
                str += "分数为：" + dr["a"].ToString();
            }
            return str;
        }
        //返回B中得分最高的人
        public static string getBMaxScore()
        {
            string str = "";
            string sql = "select count(*) as a,personId,isScore from ScoreB where isScore='是' group by personId, isScore  order by count(*) desc;";
            SqlDataReader dr = ExecuteReader(sql, null);
            if (dr.HasRows)
            {
                dr.Read();
                str += "分数最高的人是：" + dr["personId"].ToString();
                str += "分数为：" + dr["a"].ToString();
            }
            return str;
        }
        public static string getAFaQiuMax(jiQiu enums)
        {
            string str = "";
            string sql = "select count(*) as a,personId,isScore from ScoreA where isScore='是' and jiQiu='"+enums+"' group by personId, isScore  order by count(*) desc";
            SqlDataReader dr = ExecuteReader(sql, null);
            if (dr.HasRows)
            {
                dr.Read();
                str += enums+"分最高的人是：" + dr["personId"].ToString();
                str += "分数为：" + dr["a"].ToString();
            }
            return str;
        }
        public static string getBFaQiuMax(jiQiu enums)
        {
            string str = "";
            string sql = "select count(*) as a,personId,isScore from ScoreB where isScore='是' and jiQiu='"+enums+"' group by personId, isScore  order by count(*) desc";
            SqlDataReader dr = ExecuteReader(sql, null);
            if (dr.HasRows)
            {
                dr.Read();
                str += enums+"分最高的人是：" + dr["personId"].ToString();
                str += "分数为：" + dr["a"].ToString();
            }
            return str;
        }
        //删除的Score表中数据
        public static int ClearBiaoFen()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                int a = 0;
                using (SqlCommand comm = new SqlCommand("delete from ScoreA", conn))
                {
                    SqlCommand com = new SqlCommand("delete from ScoreB", conn);
                    conn.Open();
                    a = comm.ExecuteNonQuery() + com.ExecuteNonQuery();
                    return a;
                }

            }
        }

        
    }
}

基本的关于数据库的操作都通过这个SQLHelper类来进行