学生管理系统，练习增删改等操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace MySchoolbase1
{
class Program
{
static void Main(string[] args)
{

//数据库与C#相关项目幼儿园版
Console.WriteLine("请输入用户名");
string name = Console.ReadLine();
Console.WriteLine("请输入密码");
string cardpwd = Console.ReadLine();
//在这里进行输入用户名和密码
string sql = "Data source =.; Initial catalog=QQDB; uid=" + name + ";pwd=" + cardpwd;
//sql语句用来判定相关操作与数据库的用户名和密码是否一致
SqlConnection con = new SqlConnection(sql);
//进行数据库的执行操作
con.Open();
//打开连接数据库的通道
Console.WriteLine( "登陆成功");
//输出语句
con.Close();
//打开通道后必须关闭
//调用以下二个方法
welcome();
welcome2();
Console.ReadLine();
}
public static void welcome()//欢迎菜单
{

Console.WriteLine("========请选择操作键");
Console.WriteLine("01.查询学生数量");
Console.WriteLine("02.查看学生名单");
Console.WriteLine("03.按学号查询学生姓名");
Console.WriteLine("04.按姓名查询学生信息");
Console.WriteLine("05.修改学生出生日期");
Console.WriteLine("06.删除学生记录");
Console.WriteLine("07.新增年纪记录");
Console.WriteLine("08.退出");
Console.WriteLine("请进行选择");
}
public static void welcome2()
{
int xuanze = Convert.ToInt32(Console.ReadLine());
switch (xuanze)//客户按键进行操作,选择功能
{
case 1://学生总数
Sumstudent();
break;
case 2://查询所有学生信息
Studentnumber();
break;
case 3://按学号查询学生姓名
ConsoltID();
break;
case 4://按姓名查询学生信息
ConsoltName();
break;
case 5://修改学生信息
StuRevise();
break;
case 6://删除学生记录
StuAmputate();
break;
case 7://新增年纪记录
Increase();
break;

case 8:
Console.WriteLine("成功退出");
break;
default:
welcome();
welcome2();
break;
}
}

private static void ConsoltName()//姓名查询
{//下面语句作用便是在进行姓名查询时,输入学生的姓名,与sql数据库进行连接后,查询所有学生的信息
Console.WriteLine("请输入学生姓名");
string name = Console.ReadLine();
string str= Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
string sql = "select ID,Sname,SID from Student_biao where Sname='"+name+"'";
SqlCommand cmd= new SqlCommand(sql, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();//调用只读只进的方法利用循环操作逐一搜索对应的相关信息
Console.WriteLine("Id\t\t姓名\t\t学号");
if (dr != null)//如果读取姓名信息不为空的话便进入下面的荀怀
{
while (dr.Read())
{//下面要进行类型的转换
int id = Convert.ToInt32(dr["ID"]);
string Sname = dr["Sname"].ToString();
int sid = Convert.ToInt32(dr["SID"]);
Console.WriteLine("{0}\t\t{1}\t\t\t\t{2}", id, Sname, sid);
}
dr.Close();
con.Close();
}

welcome();
welcome2();

}
private static bool StuNoIsOrNot(int sid)
{
bool flag = false;
string str = Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
string sql = "select ID,Sname,SID from Student_biao where SID=" + sid + " ";

SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int count = Convert.ToInt32(cmd.ExecuteScalar());

try
{
if (count > 0)
{
flag = true;
}
}
catch (Exception)
{

Console.WriteLine("没有此号");
}
finally
{
con.Close();
}
return flag;

}

private static void StuRevise()//修改学生生日
{//更改学生信息便要用到增删改方法的调用 ExecuteNonQuery()
Console.WriteLine("请输入学生编号");
int sid= Convert.ToInt32(Console.ReadLine());
string str = Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
bool flag = StuNoIsOrNot(sid);//用来判断学号是否存在
if(flag){
Console.WriteLine("请输入新的日期");
DateTime time =Convert.ToDateTime(Console.ReadLine());
string sql = "update Student_biao set Birthday='" + time + "' where SID=" + sid + "";//这句语句作用便是要进行执行的语句了
//说明:将学生在数据库里的信息更改为上面输入的日期,where条件便是上面输入的ID必须与数据库里的Id要对应
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int count = cmd.ExecuteNonQuery();//更改操作调用方法
if (count > 0)//ExecuteNonQuery()方法返回的是int类型所以要进行If判断条件便是>0的便是更新成功

{
Console.WriteLine("更新成功");
}
else
{
Console.WriteLine("学号不存在！");
}

con.Close();
}

}

private static void StuAmputate()//删除学生信息
{
Console.WriteLine("请输入学号");
int sid = Convert.ToInt32(Console.ReadLine());
string str = Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
string sql = "delete from Student_biao where SID=" + sid + "";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
Console.WriteLine("删除成功");
}

welcome();
welcome2();
}

private static void Increase()//新增年纪
{//进行年龄的更改方法与上面
Console.WriteLine("请输入要新增的年纪");
string gradename = Convert.ToString(Console.ReadLine());
string str = Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
string sql = "insert into TGrade(gradename) values ('"+gradename+"')";
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
int number= Convert.ToInt32(cmd.ExecuteNonQuery());
if (number > 0)
{
Console.WriteLine("添加成功");
}
else {
Console.WriteLine("添加失败");
}

welcome();
welcome2();
con.Close();

}

private static void ConsoltID()//同过ID查询学生名字
{
Console.WriteLine("请输入学号");
int stuNo = Convert.ToInt32(Console.ReadLine());
string str = Sqlhelper.str;
SqlConnection con = new SqlConnection(str);
string sql = "select Sname from Student_biao where SID='" + stuNo + "'";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
string name = Convert.ToString(cmd.ExecuteScalar());
con.Close();

Console.WriteLine("学号为{0}的学生姓名是{1}", stuNo,name);
welcome();
welcome2();
}

public static void Sumstudent()
{//求总人数的方法,
string str = "Data source=.;Initial Catalog=Student;user id=sa;pwd=111";
SqlConnection con = new SqlConnection(str);
string sql = "select count(SID) from Student_biao ";
SqlCommand cmd = new SqlCommand(sql, con);

con.Open();
int count = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine("总人数是{0}", count);
con.Close();
welcome();
welcome2();
}
private static void Studentnumber()
{
string str = "Data source=.;Initial Catalog=Student;user id=sa;pwd=111";
SqlConnection con = new SqlConnection(str);
string sql = "select ID,Sname,SID from Student_biao";
SqlCommand cmd=new SqlCommand(sql,con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine("Id\t\t姓名\t\t学号");
if (dr != null)
{
while (dr.Read())
{
int id = Convert.ToInt32(dr["ID"]);
string Sname = dr["Sname"].ToString();
int sid = Convert.ToInt32(dr["SID"]);
Console.WriteLine("{0}\t\t{1}\t\t\t\t{2}", id, Sname,sid );
}
dr.Close();
con.Close();
}
welcome();
welcome2();
}

 

}
}