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 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 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();
}
}
}
