using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace MySchool.Model{ class Subject { private int _subjectid; private string _subjectname; private int? _classhour; private int _gradeid; /// <summary> /// /// </summary> public int SubjectId { set { _subjectid = value; } get { return _subjectid; } } /// <summary> /// /// </summary> public string SubjectName { set { _subjectname = value; } get { return _subjectname; } } /// <summary> /// /// </summary> public int? ClassHour { set { _classhour = value; } get { return _classhour; } } /// <summary> /// /// </summary> public int GradeId { set { _gradeid = value; } get { return _gradeid; } } }} studnetExt 类：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace MySchool.Model{ public class StudentExt:Student { public string SubjectName{ get; set; } public int StudentResult { get; set; } }}
studentModel类：using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace MySchool.Model{ public class Student { private int _studentno;private string _loginpwd;private string _studentname;private string _gender;private int _gradeid=1;private string _phone;private string _address;private DateTime? _birthday;private string _email;private int? _mytt;/// <summary>/// /// </summary>public int StudentNo{set{ _studentno=value;}get{return _studentno;}}public string LoginPwd{set{ _loginpwd=value;}get{return _loginpwd;}}public string StudentName{set{ _studentname=value;}get{return _studentname;}}public string Gender{set{ _gender=value;}get{return _gender;}}public int GradeId{set{ _gradeid=value;}get{return _gradeid;}}public string Phone{set{ _phone=value;}get{return _phone;}}public string Address{set{ _address=value;}get{return _address;}}public DateTime? Birthday{set{ _birthday=value;}get{return _birthday;}}public string Email{set{ _email=value;}get{return _email;}}public int? MyTT{set{ _mytt=value;}get{return _mytt;}} }} MySchool.DAL:
studentDAL类:
using System;using System.CodeDom.Compiler;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Linq;using System.Runtime.CompilerServices;using System.Runtime.Remoting.Messaging;using System.Text;using System.Threading;using System.Threading.Tasks;using MySchool.Model;namespace MySchool.DAL{ public class StudentDAL { //添加学生方法 public bool tianjia(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone, string Address, DateTime Birthday, string Email) { bool flag = false; using (SqlConnection con = new SqlConnection(SqlHelper.str)) { string sql = @"INSERT INTO [Student] ([LoginPwd] ,[StudentName] ,[Gender] ,[GradeId] ,[Phone] ,[Address] ,[Birthday] ,[Email]) VALUES ('" + LoginPwd + "','" + StudentName + "','" + Gender + "','" + GradeId + "','" + Phone + "','" + Address + "','" + DateTime.Now.ToShortDateString() + "','" + Email + "')"; using (SqlCommand comm = new SqlCommand(sql, con)) { try { con.Open(); //打开事物(必须在数据库打开后) SqlTransaction tranScation = con.BeginTransaction(); comm.Transaction = tranScation; int count = comm.ExecuteNonQuery(); if (count > 0) { flag = true; } //回滚 //tranScation.Rollback();不能添加到数据库 tranScation.Commit(); } catch (Exception) { } finally { con.Close(); } return flag; } } } //学生登录方法 public bool denglu(string username, string pwd) { bool flag = false; //string sql = "select count(1) from Student where StudentNo=" + username + " and LoginPwd='" + pwd + "'"; //int count= Convert.ToInt32(SqlHelper.ExecuteScalar(sql)); //if (count>0) //{ // flag = true; //} //return flag; //修改为Sql简单方法 string sql = "select count(1) from Student where StudentNo=" + username + " and LoginPwd='" + pwd + "'"; int count = Convert.ToInt32(SqlHelper.ExecuteScalar(sql)); if (count>0) { flag = true; } return flag; } //按年级查询 public DataTable chaxun(string nianji) { //参数化查询 SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "select StudentNo,LoginPwd,StudentName,Gender,GradeName,Phone from Student,Grade where Student.GradeId=Grade.GradeId and GradeName like '%'+@nianji+'%'"; SqlParameter para=new SqlParameter("@nianji",nianji); DataSet ds = new DataSet(); SqlCommand com=new SqlCommand(sql,con); com.Parameters.Add(para); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = com; da.Fill(ds, "StudentInfo"); return ds.Tables["StudentInfo"]; //修改为简单方法 //string sql = "select StudentNo,LoginPwd,StudentName,Gender,GradeName,Phone from Student,Grade where Student.GradeId=Grade.GradeId and GradeName like'%" + nianji + "%'"; //return SqlHelper.ExecuteDataTable(sql); } //按姓名查询 public DataTable xingmingchaxun(string name) { //添加参数化查询 SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "select * from Student where StudentName like'%'+@name+'%'"; DataSet ds = new DataSet(); SqlParameter para=new SqlParameter("@name",name); SqlCommand com=new SqlCommand(sql,con); com.Parameters.Add(para); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = com; da.Fill(ds, "info"); return ds.Tables["info"]; //修改为简单方法 // string sql = "select * from Student where StudentName like '%" + name + "%'"; //return SqlHelper.ExecuteDataTable(sql); } //显示学生方法 public DataTable xianshi(){ //{ SqlConnection con = new SqlConnection(SqlHelper.str); // string sql = "select * from Student"; // DataSet ds=new DataSet(); // SqlDataAdapter da=new SqlDataAdapter(sql,con); // da.Fill(ds, "info"); // return ds.Tables["info"]; //修改为简单方法 string sql = "select * from Student"; return SqlHelper.ExecuteDataTable(sql); } //调度存储过程 //输出参数存储过程 //删除方法 public bool delect(string a) { //使用using bool flag = false; using (SqlConnection con = new SqlConnection(SqlHelper.str)) { string sql = @"delete from student where StudentNo='" + a + "'"; using (SqlCommand com = new SqlCommand(sql, con)) { con.Open(); int count = Convert.ToInt32(com.ExecuteNonQuery()); if (count > 0) { flag = true; } return flag; } } //修改为简单方法 //string sql = @"delete from student where StudentNo='" + a + "'"; //int count= Convert.ToInt32(SqlHelper.ExecuteNonQuery(sql)); //if (count>0) //{ // flag = true; //} //return flag; } //修改密码 public bool xiugai(string mima,string ming) { bool flag = false; //使用using using (SqlConnection con = new SqlConnection(SqlHelper.str)) { string sql = "update Student set LoginPwd='" + mima + "'where StudentNo='" + ming + "'"; try { con.Open(); SqlCommand comm = new SqlCommand(sql, con); int count = Convert.ToInt32(comm.ExecuteScalar()); if (count > 0) { flag = true; } } catch (Exception) { } return flag; } //修改为简单方法 //string sql = "update Student set LoginPwd='" + mima + "'where StudentNo='" + ming + "'"; //int count= SqlHelper.ExecuteNonQuery(sql); //if (count>0) //{ // flag = true; //} //return flag; }//修改学生信息 public bool UpdataStudent(string pwd, string gender, int gradeid, string phone, string address, DateTime birthday, string email, string x) { bool flag= false; string sql = SqlHelper.str; SqlConnection con = new SqlConnection(sql); SqlCommand com=new SqlCommand(sql,con); string str = "update student set LoginPwd='" + pwd + "',Gender='" + gender + "',phone='" + phone + "',address='" + address + "',birthday='" + birthday + "',email='" + email + "'where studentno='" + x + "'"; con.Open(); try { con.Open(); int count =Convert.ToInt32(com.ExecuteScalar()); if (count>0) { flag= true; } } catch (Exception) { } return flag; } //用泛型显示所有信息 //调用存储过程//输出参数output public DataTable cunchu(string name, int num, out int number) { SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "usp_0325"; SqlCommand com=new SqlCommand(sql, con); //SqlParameter para = new SqlParameter("@gender",name); //SqlParameter para1 = new SqlParameter("@GradeId",num); //com.Parameters.Add(para); //com.Parameters.Add(para1); SqlParameter[] para = { new SqlParameter("@gender", name), new SqlParameter("@GradeId", num), new SqlParameter("@count",SqlDbType.Int), new SqlParameter("@Myreturn",SqlDbType.Int) }; //告述它是输出参数 para[2].Direction = ParameterDirection.Output; para[3].Direction = ParameterDirection.ReturnValue; //bapara放入com com.Parameters.AddRange(para); //调度后台的存储过程 com.CommandType = CommandType.StoredProcedure; DataSet ds=new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); //接收com da.SelectCommand = com; //输出参数值拿到输出参数的值 number = Convert.ToInt32(para[2].Value); int number1 = Convert.ToInt32(para[3].Value); Console.WriteLine(number); Console.WriteLine(number1); da.Fill(ds,"info"); return ds.Tables["info"]; } //扩展实体类 public List<StudentExt> Show() { List<StudentExt> list = new List<StudentExt>(); SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "select StudentName,StudentResult,SubjectName from Student,Result,Subject where Student.StudentNo=Result.StudentNo and Result.SubjectId=Subject.SubjectId"; DataTable da = SqlHelper.ExecuteDataTable(sql); foreach (DataRow row in da.Rows) { StudentExt stu = new StudentExt(); stu.StudentName = row["StudentName"].ToString(); stu.SubjectName = row["SubjectName"].ToString(); stu.StudentResult = Convert.ToInt32(row["StudentResult"].ToString()); list.Add(stu); } return list; } }}gradeDAL类：
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace MySchool.DAL{ public class GradeDAL { //通过年级名称获取年级编号 public int nianjibianhao(string gradename) { ////使用using // using (SqlConnection con = new SqlConnection(SqlHelper.str)) // { // string sql = "select GradeId from Grade where GradeName='" + gradename + "'"; // SqlCommand comm = new SqlCommand(sql, con); // con.Open(); // int gradeid = Convert.ToInt32(comm.ExecuteScalar()); // con.Close(); // return gradeid; // } //修改为Sql简单方法 string sql = "select GradeId from Grade where GradeName='" + gradename + "'"; int gradeid= Convert.ToInt32(SqlHelper.ExecuteScalar(sql)); return gradeid; } //获取年级信息 public DataTable huoqunianjixingxi() { //SqlConnection con = new SqlConnection(SqlHelper.str); //string sql = "select * from Grade"; //DataSet ds=new DataSet(); //SqlDataAdapter da=new SqlDataAdapter(sql,con); //da.Fill(ds, "Grdeinfo"); //return ds.Tables["Grdeinfo"]; //修改为Sql简单方法 string sql = "select * from Grade"; return SqlHelper.ExecuteDataTable(sql); } }}subjectDAL类：
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;using MySchool.Model;namespace MySchool.DAL{ public class SubjectDAL { //查询科目信息 public DataTable Subject() { SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "select * from Subject"; SqlDataAdapter da=new SqlDataAdapter(sql,con); DataSet ds=new DataSet(); da.Fill(ds, "info"); return ds.Tables["info"]; } //查询科目编号 public int SubjectId(string Subjectname) { bool flag = false; using (SqlConnection con=new SqlConnection(SqlHelper.str)) { string sql = "select SubjectId from Subject where SubjectName=@Subjectname"; SqlParameter para = new SqlParameter("@Subjectname", Subjectname); SqlCommand com=new SqlCommand(sql,con); com.Parameters.Add(para); con.Open(); int graidId = Convert.ToInt32(com.ExecuteScalar()); return graidId; } } }}——————————————————————————————————————————SqlHelper类
using System;using System.Collections.Generic;using System.Configuration;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;using System.Data;namespace MySchool.DAL{ public class SqlHelper { public static string str=ConfigurationManager.ConnectionStrings["str"].ToString(); 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(str)) { 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(str); 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, str)) { 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(str)) { 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(str)) { 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 ) { sqltran.Rollback(); } return sum; } }}————————————————————————————————————————————逻辑层MySchool.BLL
StudentBLL类：
using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;using MySchool.DAL;using MySchool.Model;namespace MySchool.Bll{ public class StudentBLL { StudentDAL student = new StudentDAL(); //登陆 public bool denglu(string username, string pwd) { return student.denglu(username, pwd); } //修改密码 public bool xiugai(string mima, string ming) { return student.xiugai(mima, ming); } //修改信息 public bool UpdataStudent(string pwd, string gender, int gradeid, string phone, string address, DateTime birthday, string email, string x) { return student.UpdataStudent(pwd, gender, gradeid, phone, address, birthday, email, x); } //按姓名查询 public DataTable xingmingchaxun(string name) { return student.xingmingchaxun(name); } //按年级查询 public DataTable chaxun(string nianji) { return student.chaxun(nianji); } //添加学生信息 public bool tianjia(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone, string Address, DateTime Birthday, string Email) { return student.tianjia(LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email); } //显示学生信息 public DataTable xianshi() { SqlConnection con = new SqlConnection(SqlHelper.str); string sql = "select * from Student"; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds, "info"); return ds.Tables["info"]; } //删除 public bool delect(string a) { return student.delect(a); } //调度存储过程 //public DataTable gull(string gender, int genderid, out int couu) //{ // return student.gull(gender, genderid, couu); //} //泛型显示 //存储过程 public DataTable cunchu(string name, int num, out int number) { return student.cunchu(name, num, out number); } //扩展实体类 public List<StudentExt> Show() { return student.Show(); } }} }}
------subjectBll类
using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using MySchool.Model;using MySchool.DAL;namespace MySchool.Bll{ public class SubjectBLL { SubjectDAL sd=new SubjectDAL(); public DataTable Subject() { return sd.Subject(); } //查询科目编号 public int SubjectId(string Subjectname) { return sd.SubjectId(Subjectname); } }}--------GradeBll类：
using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using MySchool.DAL;namespace MySchool.Bll{ public class GradeBLL { //通过年级名称获取年级编号 GradeDAL grade=new GradeDAL(); public int nianjibianhao(string gradename) { return grade.nianjibianhao(gradename); } //获取年级信息 public DataTable huoqunianjixingxi() { return grade.huoqunianjixingxi(); } }} ————————————————————————————————————————————单元测试
1：显示所有学生信息
using System;using Microsoft.VisualStudio.TestTools.UnitTesting;using MySchool.DAL;using MySchool.Model;using System.Xml;using System.Data;namespace 单元测试{ [TestClass] public class UnitTest1 { [TestMethod] //显示所有学生信息单测 public void TestMethod1() { StudentDAL dal=new StudentDAL(); DataTable da=dal.xianshi(); foreach (DataRow item in da.Rows) { Console.WriteLine(item["StudentNo"]); } } }}2：登录测试
using System;using System.Data;using System.Text;using System.Collections.Generic;using Microsoft.VisualStudio.TestTools.UnitTesting;using MySchool.Model;using MySchool.DAL;namespace 单元测试{ /// <summary> /// UnitTest3 的摘要说明 /// </summary> [TestClass] public class UnitTest3 { public UnitTest3() { } private TestContext testContextInstance; /// <summary> ///获取或设置测试上下文，该上下文提供 ///有关当前测试运行及其功能的信息。 ///</summary> public TestContext TestContext { get { return testContextInstance; } set { testContextInstance = value; } } #region 附加测试特性 // // 编写测试时，可以使用以下附加特性: // // 在运行类中的第一个测试之前使用 ClassInitialize 运行代码 // [ClassInitialize()] // public static void MyClassInitialize(TestContext testContext) { } // // 在类中的所有测试都已运行之后使用 ClassCleanup 运行代码 // [ClassCleanup()] // public static void MyClassCleanup() { } // // 在运行每个测试之前，使用 TestInitialize 来运行代码 // [TestInitialize()] // public void MyTestInitialize() { } // // 在每个测试运行完之后，使用 TestCleanup 来运行代码 // [TestCleanup()] // public void MyTestCleanup() { } // #endregion [TestMethod] //获取登录单测 public void TestMethod1() { StudentDAL dal=new StudentDAL(); bool flag = dal.denglu("23219", "222222"); Assert.IsTrue(flag); } }}3：年级单元测试
using System;using System.Text;using System.Collections.Generic;using Microsoft.VisualStudio.TestTools.UnitTesting;using System.Data;using MySchool.Model;using MySchool.DAL;namespace 单元测试{ /// <summary> /// UnitTest2 的摘要说明 /// </summary> [TestClass] public class UnitTest2 { //获取年级信息单测 public UnitTest2() { GradeDAL dal=new GradeDAL(); DataTable da= dal.huoqunianjixingxi(); foreach (DataRow item in da.Rows) { Console.WriteLine(item["GradeId"]); } } private TestContext testContextInstance; /// <summary> ///获取或设置测试上下文，该上下文提供 ///有关当前测试运行及其功能的信息。 ///</summary> public TestContext TestContext { get { return testContextInstance; } set { testContextInstance = value; } } #region 附加测试特性 // // 编写测试时，可以使用以下附加特性: // // 在运行类中的第一个测试之前使用 ClassInitialize 运行代码 // [ClassInitialize()] // public static void MyClassInitialize(TestContext testContext) { } // // 在类中的所有测试都已运行之后使用 ClassCleanup 运行代码 // [ClassCleanup()] // public static void MyClassCleanup() { } // // 在运行每个测试之前，使用 TestInitialize 来运行代码 // [TestInitialize()] // public void MyTestInitialize() { } // // 在每个测试运行完之后，使用 TestCleanup 来运行代码 // [TestCleanup()] // public void MyTestCleanup() { } // #endregion [TestMethod] public void TestMethod1() { // // TODO: 在此处添加测试逻辑 // } }}
0 0
