using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using adoceshi.model;
using System.Data.SqlClient;
namespace adoceshi.dataopration
{
public class studentdata
{
SqlConnection conn = null;
SqlCommand cmd = null;
public studentdata()
{
conn = new SqlConnection("server=.;database=adoceshi;user=sa;pwd=123;");
cmd = conn.CreateCommand();
}
public List<Student> selectall()
{
List<Student> list = new List<Student>();
cmd.CommandText = "select * from Student";
try
{
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.SsubjectCode = dr["SsubjectCode"].ToString();
list.Add(s);
}
}
conn.Close();
}
catch
{
conn.Close();
Console.WriteLine("数据库连接失败!请重新连接!");
}
return list;
}
//用户操作
//1新增
public void xinzeng()
{
string code, name, zhuanye,zhuanyecode,s;
bool sex;
DateTime shengri;
while (true)
{
Console.Write("请输入学生编号:");
string c = Console.ReadLine();
cmd.CommandText = "select * from student where Code=@c";
cmd.Parameters.Clear();
cmd.Parameters.Add("@c",c);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
conn.Close();
Console.WriteLine("您输入的学生编号已拥有,请重新输入!");
}
else
{
conn.Close();
code = c;
break;
}
}
while (true)
{
Console.Write("请输入学生姓名(不能为空):");
string n = Console.ReadLine();
if (n == "")
{
Console.WriteLine("用户名不能为空!请重新输入!");
}
else
{
name = n;
break;
}
}
while (true)
{
Console.Write("请输入学生性别(男/女):");
s = Console.ReadLine();
if (s == "男")
{
sex = true;
break;
}
else if (s == "女")
{
sex = false;
break;
}
else
{
Console.WriteLine("您的输入有误!只能输入男或女,请重新输入!");
}
}
while (true)
{
Console.Write("请输入学生生日(如2000/12/12):");
try
{
DateTime ri = Convert.ToDateTime(Console.ReadLine());
shengri = ri;
break;
}
catch
{
Console.WriteLine("您输入的生日格式不正确!请重新输入!");
}
}
while (true)
{
Console.Write("请输入学生专业:");
string z = Console.ReadLine();
cmd.CommandText = "select * from Ssubject where SsubjectName like @n";
cmd.Parameters.Clear();
cmd.Parameters.Add("@n", "%"+z+"%");
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
zhuanye = dr["SsubjectName"].ToString();
zhuanyecode = dr["SsubjectCode"].ToString();
conn.Close();
break;
}
else
{
conn.Close();
Console.WriteLine("没有该专业!请重新输入!");
}
}
Console.WriteLine(code+"\t"+name+"\t"+s+"\t"+shengri+"\t"+zhuanye);
while(true)
{
Console.Write("以上为新增学生的信息,是否确定添加?(Y/N):");
string queding = Console.ReadLine();
if(queding=="y"||queding=="Y")
{
cmd.CommandText="insert into student values(@c,@x,@s,@b,@sub)";
cmd.Parameters.Clear();
cmd.Parameters.Add("@c",code);
cmd.Parameters.Add("@x",name);
cmd.Parameters.Add("@s",sex);
cmd.Parameters.Add("@b",shengri);
cmd.Parameters.Add("@sub",zhuanyecode);
try
{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.Clear();
Console.WriteLine("添加成功!");
break;
}
catch
{
Console.WriteLine("连接数据库失败!请重新连接!");
}
}
else if(queding=="n"||queding=="N")
{
Console.Clear();
break;
}
else
{
Console.WriteLine("输入不正确!请重新输入!");
}
}
}
//修改
public void xiugai(string bianhao)
{
string code, name, zhuanye,zhuanyecode,ss;
bool sex;
DateTime shengri;
cmd.CommandText = "select * from Student where Code=@c";
cmd.Parameters.Clear();
cmd.Parameters.Add("@c",bianhao);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
Student s = new Student();
s.Name = dr["Name"].ToString();
s.Sex = Convert.ToBoolean(dr["Sex"]);
s.Birthday = Convert.ToDateTime(dr["Birthday"]);
s.SsubjectCode = dr["SsubjectCode"].ToString();
conn.Close();
Console.WriteLine("已查询到"+bianhao+"的学生信息,请进行以下修改:");
Console.WriteLine(bianhao+"\t"+s.Name+"\t"+s.Sex+"\t"+s.Birthday+"\t"+s.SsubjectCode);
Console.WriteLine(""+bianhao+"学生当前姓名为:"+s.Name+"");
while (true)
{
Console.Write("请输入修改后学生姓名(不能为空):");
string n = Console.ReadLine();
if (n == "")
{
Console.WriteLine("用户名不能为空!请重新输入!");
}
else
{
name = n;
break;
}
}
Console.WriteLine("" + bianhao + "学生当前性别为:" + s.Sex + "");
while (true)
{
Console.Write("请输入您的修改学生性别(男/女):");
ss = Console.ReadLine();
if (ss == "男")
{
sex = true;
break;
}
else if (ss == "女")
{
sex = false;
break;
}
else
{
Console.WriteLine("您的输入有误!只能输入男或女,请重新输入!");
}
}
Console.WriteLine("" + bianhao + "学生当前生日为:" + s.strbirthday + "");
while (true)
{
Console.Write("请输入修改学生生日(如2000/12/12):");
try
{
DateTime ri = Convert.ToDateTime(Console.ReadLine());
shengri = ri;
break;
}
catch
{
Console.WriteLine("您输入的生日格式不正确!请重新输入!");
}
}
Console.WriteLine("" + bianhao + "学生当前专业编号为:" + s.SsubjectCode + "");
while (true)
{
Console.Write("请输入修改后的学生专业:");
string z = Console.ReadLine();
cmd.CommandText = "select * from Ssubject where SsubjectName like @n";
cmd.Parameters.Clear();
cmd.Parameters.Add("@n", "%" + z + "%");
try
{
conn.Open();
SqlDataReader dr1 = cmd.ExecuteReader();
if (dr1.HasRows)
{
dr1.Read();
zhuanye = dr1["SsubjectName"].ToString();
zhuanyecode = dr1["SsubjectCode"].ToString();
conn.Close();
break;
}
else
{
conn.Close();
Console.WriteLine("没有该专业!请重新输入!");
}
}
catch
{
conn.Close();
Console.WriteLine("连接数据库失败!请重新连接!");
}
}
Console.WriteLine(bianhao+ name + "\t" + sex + "\t" + shengri + "\t" + zhuanye);
while (true)
{
Console.Write("以上为修改学生的信息,是否确定添加?(Y/N):");
string queding = Console.ReadLine();
if (queding == "y" || queding == "Y")
{
cmd.CommandText = " update Student set Name=@x,Sex=@s,Birthday=@b,SsubjectCode=@sub where Code='"+bianhao +"'";
cmd.Parameters.Clear();
cmd.Parameters.Add("@x", name);
cmd.Parameters.Add("@s", sex);
cmd.Parameters.Add("@b", shengri);
cmd.Parameters.Add("@sub", zhuanyecode);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.Clear();
Console.WriteLine("修改成功!");
break;
}
else if (queding == "n" || queding == "N")
{
Console.Clear();
break;
}
else
{
Console.WriteLine("输入不正确!请重新输入!");
}
}
}
else
{
Console.WriteLine("没有该学生的信息!请重新输入");
}
}
//删除
public void shanchu(string bianhao)
{
cmd.CommandText = "select * from Student where Code=@c";
cmd.Parameters.Clear();
cmd.Parameters.Add("@c",bianhao);
conn.Open();
SqlDataReader dr=cmd.ExecuteReader();
if (dr.HasRows)
{
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.SsubjectCode = dr["SsubjectCode"].ToString();
Console.WriteLine(bianhao + "\t" + s.Name + "\t" + s.Sex + "\t" + s.Birthday + "\t" + s.SsubjectCode);
conn.Close();
cmd.CommandText = "delete from student where Code=@cc";
cmd.Parameters.Clear();
cmd.Parameters.Add("@cc", bianhao);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
Console.Clear();
Console.WriteLine("删除成功!");
}
else
{
conn.Close();
Console.WriteLine("没有该学生信息请重新输入");
}
conn.Close();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace adoceshi.model
{
public class Student
{
SqlConnection conn = null;
SqlCommand cmd = null;
public Student()
{
conn = new SqlConnection("server=.;database=adoceshi;user=sa;pwd=123;");
cmd = conn.CreateCommand();
}
private string _Code;
/// <summary>
/// 学生编号
/// </summary>
public string Code
{
get { return _Code; }
set { _Code = value; }
}
private string _Name;
/// <summary>
/// 学生姓名
/// </summary>
public string Name
{
get { return _Name; }
set { _Name = value; }
}
private bool _Sex;
/// <summary>
/// 学生性别
/// </summary>
public bool Sex
{
get { return _Sex; }
set { _Sex = value; }
}
public string strsex
{
get
{
if (_Sex == true)
{
return "男";
}
else
{
return "女";
}
}
}
private DateTime _Birthday;
/// <summary>
/// 学生生日
/// </summary>
public DateTime Birthday
{
get { return _Birthday; }
set { _Birthday = value; }
}
public string strbirthday
{
get
{
return _Birthday.ToString("yyyy年MM月dd日");
}
}
public int nianling
{
get
{
return
(DateTime.Now.Year - _Birthday.Year);
}
}
private string _SsubjectCode;
/// <summary>
/// 学生专业
/// </summary>
public string SsubjectCode
{
get { return _SsubjectCode; }
set { _SsubjectCode = value; }
}
/// <summary>
/// 专业名称
/// </summary>
public string SsubjectName
{
get
{
string name = "";
cmd.CommandText = "select * from Ssubject where SsubjectCode=@c";
cmd.Parameters.Clear();
cmd.Parameters.Add("@c", _SsubjectCode);
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
name = dr["SsubjectName"].ToString();
}
conn.Close();
}
catch
{
conn.Close();
Console.WriteLine("连接数据库失败!请重新输入!");
}
return name;
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using adoceshi.model;
using adoceshi.dataopration;
namespace adoceshi
{
class Program
{
static void Main(string[] args)
{
while (true)
{
//打印学生表
List<Student> data = new studentdata().selectall();
Console.WriteLine("编号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "年龄" + "\t" + "生日" + "\t" + "\t" + "专业");
foreach (Student s in data)
{
Console.WriteLine(s.Code + "\t" + s.Name + "\t" + s.strsex + "\t" + s.nianling + "\t" + s.strbirthday + "\t" + s.SsubjectName);
}
Console.WriteLine("--------------------------------------------------------------------------");
//用户操作
Console.Write("请输入您的操作数字(1=新增,2=修改,3=删除,4=退出):");
string caozuo = Console.ReadLine();
if(caozuo=="1")
{
studentdata z = new studentdata();
z.xinzeng();
}
else if (caozuo == "2")
{
Console.Write("请输入您要修改的学生的编号:");
string bianhao = Console.ReadLine();
studentdata g = new studentdata();
g.xiugai(bianhao);
}
else if (caozuo == "3")
{
Console.Write("请输入您要删除的学生的编号:");
string bianhao = Console.ReadLine();
studentdata g = new studentdata();
g.shanchu(bianhao);
}
else if (caozuo == "4")
{
break;
}
else
{
Console.WriteLine("您的输入有误!请重新输入!");
}
}
Console.ReadKey();
}
}
}
![](https://images2015.cnblogs.com/blog/943867/201606/943867-20160627214709062-1545946512.png)
![](https://images2015.cnblogs.com/blog/943867/201606/943867-20160627214714952-929502406.png)
![](https://images2015.cnblogs.com/blog/943867/201606/943867-20160627214724234-1929424202.png)
![](https://images2015.cnblogs.com/blog/943867/201606/943867-20160627214733202-683239843.png)
![](https://images2015.cnblogs.com/blog/943867/201606/943867-20160627214742031-1175707369.png)