首页 > 代码库 > ASP.NET MVC 排球计分程序 （四）实体类和SqlHelper类的设计
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类来进行
ASP.NET MVC 排球计分程序 （四）实体类和SqlHelper类的设计
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。