ADO.net测试题
有幂函数的.cs文件中的代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient;//引用数据库命名空间 using ADO.NET测试题.数据访问类; using ADO.NET测试题.实体类; using System.Collections; namespace ADO.NET测试题 { class Program { static void Main(string[] args) { while (true) { shujufangwenlei shu = new shujufangwenlei(); List<student> ADO = shu.SelectAll(); Console.WriteLine("编号 姓名 性别 年龄 生日 专业"); foreach (student s in ADO) { Console.WriteLine(s.code.ToString() + " " + s.name + " " + (s.sex ? "男" : "女") + " " + s.age + " " + Convert.ToDateTime(s.birthday).ToString("yyyy年MM月dd日") + " " + s.sn); } //数据显示 SqlConnection conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123"); SqlCommand cmd = conn.CreateCommand(); //询问用户接下来的操作 Console.Write("请输入要进行的操作：（输入数字1:新增，输入数字2:修改，输入数字3:删除）"); int caozuo = Convert.ToInt32(Console.ReadLine()); if (caozuo == 1)//添加 { student ss = new student(); while (true) { Console.Write("请输入学生编号："); ss.code = Console.ReadLine(); //if (s.Code == "" || s.Code == null) //判断输入学号是否为空！ if (String.IsNullOrEmpty(ss.code)) { Console.WriteLine("学生编号不能为空！"); } else { bool isok = shu.SelectCode(ss.code); if (isok) { break; } else { Console.WriteLine("编号已被占用！"); } } } while (true) { Console.Write("请输入学生姓名："); ss.name = Console.ReadLine(); if (ss.name == "") { Console.WriteLine("姓名不能为空！"); } else { break; } } while (true) { Console.Write("请输入学生性别(true/男/1,false/女/0)："); string xb = Console.ReadLine(); if (xb == "男" || xb == "true" || xb == "女" || xb == "false" || xb == "1" || xb == "0") { if (xb == "男" || xb == "true" || xb == "1") { ss.sex = true; break; } else if (xb == "女" || xb == "false" || xb == "0") { ss.sex = false; break; } } else { Console.WriteLine("性别输入错误！"); } } while (true) { Console.Write("请输入学生生日："); try { ss.birthday = Convert.ToDateTime(Console.ReadLine()); break; } catch { Console.WriteLine("生日输入有误！"); } } while (true) { Console.Write("请输入专业："); string subname = Console.ReadLine(); string subcode = shu.selectsc(subname); if (subcode == "") { Console.WriteLine("查无此专业"); } else { ss.subjectcode = subcode; break; } } #region 专业 //while (true) //{ // Console.Write("请输入专业："); // string subjectname = Console.ReadLine(); // if (subjectname != "") // { // string sc = shu.sbname(subjectname); // if (shu.panduan1(sc)) // { // ss.subjectcode = shu.sbname(subjectname); // break; // } // else // { // Console.WriteLine("查无此专业！"); // } // } // else // { // Console.WriteLine("请输入专业："); // } //} #endregion Console.Write("以上为新增学生的信息，是否确定添加？（Y/N）："); string queren = Console.ReadLine(); if (queren.ToUpper() == "Y") { bool OK = shu.stuinsert(ss); if (OK)//true { Console.Clear(); Console.WriteLine("添加成功！"); } else { Console.WriteLine("添加失败！"); } } else { } continue; #region 添加是否成功 //string shifou = ""; //while (true) //{ // Console.Write("以上为新增学生的信息，是否确定添加？（Y/N）："); // shifou = Console.ReadLine(); // if (shifou.ToUpper() == "Y") // { // shu.insert(ss); // break; // } // else // { // Console.WriteLine("操作指令输入错误！"); // } //} //Console.Clear(); //Console.WriteLine("添加成功！"); //continue; #endregion } else if (caozuo == 2)//修改 { student sss = new student(); //首先要确定这个学号对应的学生信息存不存在 Console.Write("请输入要修改的学生编号："); string xiugai = Console.ReadLine(); bool has = shu.panduan(xiugai); if (has) { Console.WriteLine("已查询到" + xiugai + "的学生信息，请继续修改操作"); Console.WriteLine(xiugai + "学生当前姓名为：" + shu.upname(xiugai)); while (true) { Console.Write("请输入您的修改："); sss.name = Console.ReadLine(); if (sss.name == "") { Console.WriteLine("姓名不能为空！"); } else { break; } } Console.WriteLine(xiugai + "学生当前性别为：" + shu.upsex(xiugai)); while (true) { Console.Write("请输入您的修改："); string xb = Console.ReadLine(); if (xb == "男" || xb == "true" || xb == "女" || xb == "false" || xb == "1" || xb == "0") { if (xb == "男" || xb == "true" || xb == "1") { sss.sex = true; break; } else if (xb == "女" || xb == "false" || xb == "0") { sss.sex = false; break; } } else { Console.WriteLine("性别输入错误！"); } } Console.WriteLine(xiugai + "学生当前生日为：" + shu.upbirthday(xiugai)); while (true) { Console.Write("请输入您的修改："); try { sss.birthday = Convert.ToDateTime(Console.ReadLine()); break; } catch { Console.WriteLine("生日输入有误！"); } } Console.WriteLine(xiugai + "学生当前专业为：" + shu.upsubjectcode(xiugai)); while (true) { Console.WriteLine("请输入您的修改："); string subname = Console.ReadLine(); string subcode = shu.selectsc(subname); if (subcode == "") { Console.WriteLine("查无此专业"); } else { sss.subjectcode = subcode; break; } } } else { Console.WriteLine("查无" + xiugai + "的学生信息！"); } Console.Write("以上为新增学生的信息，是否确定添加？（Y/N）："); string queren = Console.ReadLine(); if (queren.ToUpper() == "Y") { bool OK = shu.stuupdate(sss,xiugai); if (OK) { Console.Clear(); Console.WriteLine("修改成功！"); } else { Console.WriteLine("修改失败！"); } } else { } continue; } else if (caozuo == 3)//删除 { while (true) { Console.Write("请输入要删除的学生编号："); string shanchu = Console.ReadLine(); bool has = shu.panduan(shanchu); if (has) { Console.WriteLine(shu.zhanshi(shanchu)); Console.Write("是否要删除此学生的信息？（Y/N):"); string queren = Console.ReadLine(); if (queren.ToUpper() == "Y") { shu.delete(shanchu); break; } else { Console.WriteLine("操作指令输入错误！"); } } else { Console.WriteLine("查无" + shanchu + "的学生信息！"); } } Console.Clear(); Console.WriteLine("删除成功！"); continue; } else { Console.WriteLine("操作指令输入有误！"); } } Console.ReadLine(); } } }
实体类中的代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; namespace ADO.NET测试题.实体类 { public class student { SqlConnection conn = null; SqlCommand cmd = null; public student() { conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123"); cmd = conn.CreateCommand(); } private string _code; public string code { get { return _code; } set { _code = value; } } private string _name; public string name { get { return _name; } set { _name = value; } } private bool _sex; public bool sex { get { return _sex; } set { _sex = value; } } private DateTime _birthday; public DateTime birthday { get { return _birthday; } set { _birthday = value; } } private string _subjectcode; public string subjectcode { get { return _subjectcode; } set { _subjectcode = value; } } public int age { get { DateTime dt = DateTime.Now; return dt.Year - _birthday.Year; } } public string sn { get { string subjectname = ""; cmd.CommandText = "select *from [subject] where subjectcode=@subjectcode"; cmd.Parameters.Clear(); cmd.Parameters.Add("@subjectcode", _subjectcode); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); subjectname = dr["subjectname"].ToString(); } conn.Close(); return subjectname; } } } }
数据访问类里的代码
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using ADO.NET测试题.实体类; using System.Collections; namespace ADO.NET测试题.数据访问类 { public class shujufangwenlei { SqlConnection conn = null; SqlCommand cmd = null; public shujufangwenlei() { conn = new SqlConnection("server=.;database=ADO;user=sa;pwd=123"); cmd = conn.CreateCommand(); } public List<student> SelectAll() { List<student> list = new List<student>(); cmd.CommandText = "select *from student"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { student s = new student(); s.code = dr["code"].ToString(); s.name = dr["name"].ToString(); s.sex = Convert.ToBoolean(dr["sex"]); s.birthday = Convert.ToDateTime(dr["birthday"]); s.subjectcode = dr["subjectcode"].ToString(); list.Add(s); } } conn.Close(); return list; } /// <summary> /// 判断编号是否可用，true是可用，false不可用 /// </summary> /// <param name="code"></param> /// <returns></returns> public bool SelectCode(string code) { bool has = true; cmd.CommandText = "select *from student where Code = @c"; cmd.Parameters.Clear(); cmd.Parameters.Add("@c", code); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { has = false; } conn.Close(); return has; } /// <summary> /// 查询有无此专业，如果有就返回专业编号 /// </summary> /// <param name="subname">模糊查询的专业名称</param> /// <returns></returns> public string selectsc(string subname) { string subcode = ""; cmd.CommandText = "select *from [subject] where subjectname like @s"; cmd.Parameters.Clear(); cmd.Parameters.Add("@s", "%" + subname + "%"); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); subcode = dr["subjectcode"].ToString(); } conn.Close(); return subcode; } /// <summary> /// 添加学生信息，返回true为添加成功 /// </summary> /// <param name="ss">要添加的学生对象</param> /// <returns></returns> public bool stuinsert(student s) { bool isok = false; cmd.CommandText = "insert into student values(@a,@b,@c,@d,@e);"; cmd.Parameters.Clear(); cmd.Parameters.Add("@a", s.code); cmd.Parameters.Add("@b", s.name); cmd.Parameters.Add("@c", s.sex); cmd.Parameters.Add("@d", s.birthday); cmd.Parameters.Add("@e", s.subjectcode); try { conn.Open(); cmd.ExecuteNonQuery(); isok = true; } catch { } finally { conn.Close(); } return isok; } public bool panduan(string code) { bool has = false; cmd.CommandText = "select *from student where code ='" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { has = true; } conn.Close(); return has; } public string upname(string code) { string name = ""; cmd.CommandText = "select *from student where code ='" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); name = dr["name"].ToString(); } conn.Close(); return name; } public string upsex(string code) { string sex = ""; bool sex1 = false; cmd.CommandText = "select *from student where code ='" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); sex1 = Convert.ToBoolean(dr["sex"]); } conn.Close(); if (sex1 == true) { sex = "男"; } else { sex = "女"; } return sex; } public string upbirthday(string code) { string birthday = ""; cmd.CommandText = "select *from student where code ='" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); birthday = dr["birthday"].ToString(); } conn.Close(); return birthday; } public string upsubjectcode(string code) { string subjectcode = ""; cmd.CommandText = "select *from student where code ='" + code + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); subjectcode = dr["subjectcode"].ToString(); } conn.Close(); return subjectcode; } public bool stuupdate(student s, string xg) { bool isok = false; cmd.CommandText = "update student set name=@name,sex=@sex,birthday=@birthday,subjectcode=@subjectcode where code='" + xg + "'"; cmd.Parameters.Clear(); cmd.Parameters.Add("@name", s.name); cmd.Parameters.Add("@sex", s.sex); cmd.Parameters.Add("@birthday", s.birthday); cmd.Parameters.Add("@subjectcode", s.subjectcode); try { conn.Open(); cmd.ExecuteNonQuery(); isok = true; } catch { } finally { conn.Close(); } return isok; } public string zhanshi(string sc) { string xx = ""; student ss = new student(); cmd.CommandText = "select *from student where code='" + sc + "'"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); ss.code = dr["code"].ToString(); ss.name = dr["name"].ToString(); ss.sex = Convert.ToBoolean(dr["sex"]); ss.birthday = Convert.ToDateTime(dr["birthday"]); ss.subjectcode = dr["subjectcode"].ToString(); } conn.Close(); return xx = ss.code + " " + ss.name + " " + Convert.ToString(ss.sex ? "男" : "女") + " " + ss.birthday.ToString("yyyy年MM月dd日") + " " + ss.sn; } public void delete(string no) { cmd.CommandText = "delete from student where code = @code"; cmd.Parameters.Clear(); cmd.Parameters.Add("@code", no); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } } }
