系统介绍:
通过ADO.NET技术连接数据库通过vs2015实现对数据库信息的增删改查以及各种操作
系统目标:
- 可以实现学生信息的增删改查
- 可以实现学生课程的增删改查
- 可以实现学生院系的增删改查
- 可以实现学生成绩的增删改查
- 可以实现学生的快速查找,成绩排名等
开发环境:
开发工具:VS2015
开发环境:Windows系统
技术要求:
ADO.NET技术
C#
SQL Server
设计过程错误归纳:
无法遍历信息
修改信息失败
课程无法查询
删除课程无法删除,连接关闭
总结:
通过本项目的设计,训练了自己的逻辑能力与设计能力,首先设计整体框架非常重要
有了整体框架就是代码的填充,途中遇到很多的问题,例如,没有判断,添加了重
复的信息,删除了不存在的信息,修改了不存在的信息,忘记打开数据库等,让自己获得了更多的项目经验,尝试了更多的错误,日后,这些错误将会随时警醒着我
上代码:
主页面
static void Main(string[] args)
{
while (true) {
Console.Clear();
Console.WriteLine("===================欢迎登陆学生信息管理系统=======================");
Console.WriteLine("---------------------请选择菜单项---------------------------------");
Console.WriteLine(" 1、学生信息管理");
Console.WriteLine(" 2、课程信息管理");
Console.WriteLine(" 3、考试成绩管理");
Console.WriteLine(" 4、综合信息查询");
Console.WriteLine(" 5、院系信息管理");
Console.WriteLine(" 0、退出系统");
Console.WriteLine("==================================================================");
int x = int.Parse(Console.ReadLine());
switch (x) {
case 1:
P1 p = new P1();
p.Student();
break;
case 2:
P2 p2 = new P2();
p2.Course();
break;
case 3:
P3 p3 = new P3();
p3.Score();
break;
case 4:
Console.WriteLine("本模块功能为付费 VIP 内容");
Console.WriteLine("-1 充值 -2 返回上一层");
int y=int.Parse(Console.ReadLine());
if (y == 1)
{
P5 p5 = new P5();
p5.ZongHe();
}
else {
break;
}
Console.ReadLine();
break;
case 5:
P4 p4 = new P4();
p4.Dep();
break;
case 0:
Console.WriteLine("谢谢使用!");
Console.ReadKey();
return;
}
}
}
public void Student() {
while (true) {
Console.Clear();
Console.WriteLine("==================学生信息管理==================");
Console.WriteLine("-A:查看信息 -B:添加信息 -C:修改信息 -D:删除信息 -O:返回主菜单");
Console.WriteLine("==================请选择操作并按回车结束========");
string s = Console.ReadLine().ToUpper();
//Console.ReadKey();
switch (s) {
case "A":
Stu s1 = new Stu();
s1.SSelect();
break;
case "B":
Stu s2 = new Stu();
s2.SInsert();
break;
case "C":
Stu s3 = new Stu();
s3.SUpdate();
break;
case "D":
Stu s4 = new Stu();
s4.SRemove();
break;
case "O":
return;
}
}
}
public void Course()
{
while (true)
{
Console.Clear();
Console.WriteLine("==================课程信息管理==================");
Console.WriteLine("-A:查看课程 -B:添加课程 -C:修改课程 -D:删除课程 -O:返回主菜单");
Console.WriteLine("==================请选择操作并按回车结束========");
string s = Console.ReadLine().ToUpper();
Console.ReadKey();
switch (s)
{
case "A":
Cou c1 = new Cou();
c1.CSelect();
break;
case "B":
Cou c2 = new Cou();
c2.CInsert();
break;
case "C":
Cou c3 = new Cou();
c3.CUpdate();
break;
case "D":
Cou c4 = new Cou();
c4.CRemove();
break;
case "O":
return;
}
}
}
public void Score()
{
while (true)
{
Console.Clear();
Console.WriteLine("==================考试成绩管理==================");
Console.WriteLine("-A:查看成绩 -B:修改成绩 -C:删除成绩 -O:返回主菜单");
Console.WriteLine("==================请选择操作并按回车结束========");
string s = Console.ReadLine().ToUpper();
Console.ReadKey();
switch (s)
{
case "A":
Sco s1 = new Sco();
s1.Sselect();
break;
case "B":
Sco s2 = new Sco();
s2.Sselect();
break;
case "C":
Sco s3 = new Sco();
s3.Sremove();
break;
case "O":
return;
}
}
}
public void Dep() {
while (true) {
Console.Clear();
Console.WriteLine("==================院系信息管理==================");
Console.WriteLine("-A:查看所有院系 -B:添加院系 -C:修改院系信息 -D:删除院系 -O:返回主菜单");
Console.WriteLine("==================请选择操作并按回车结束========");
string s = Console.ReadLine().ToUpper();
switch (s) {
case "A":
Dept d1 = new Dept();
d1.DSelect();
break;
case "B":
Dept d2 = new Dept();
d2.DInsert();
break;
case "C":
Dept d3 = new Dept();
d3.DUpdate();
break;
case "D":
Dept d4 = new Dept();
d4.DDelete();
break;
case "O":
return;
}
}
}
public void ZongHe() {
while (true)
{
Console.Clear();
Console.WriteLine("==================综合信息查询==================");
Console.WriteLine("-A:查找指定学生信息 -B:成绩排序 -O:返回主菜单");
Console.WriteLine("==================请选择操作并按回车结束========");
string s = Console.ReadLine().ToUpper();
switch (s)
{
case "A":
ZH z1 = new ZH();
z1.zhselect();
break;
case "B":
ZH z2 = new ZH();
z2.zhscore();
break;
case "O":
return;
}
}
}
class Stu
{
static string strc = ConfigurationManager.ConnectionStrings["strc"].ToString();
//查看学生信息
public void SSelect() {
using (SqlConnection conn = new SqlConnection(strc)) {
string sql = "select * from students";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + "\t");
}
Console.WriteLine();
}
Console.ReadKey();
}
}
//添加学生信息
public void SInsert() {
using (SqlConnection conn = new SqlConnection(strc)) {
Console.WriteLine("输入需要添加的学号、姓名、性别、年龄、生日、手机号码、住址、院系,按回车结束");
//string sql = "insert into students values (1005,'王二',1,16,GETDATE(),16246551233,'山东威海',2)";
string id = Console.ReadLine();
string name = Console.ReadLine();
int sex =int.Parse( Console.ReadLine());
int age = int.Parse(Console.ReadLine());
DateTime br = Convert.ToDateTime( Console.ReadLine());
string ph = Console.ReadLine();
string ad = Console.ReadLine();
int dp = int.Parse(Console.ReadLine());
string sql = string.Format("insert into students (stuid,stuname,stusex,stuage,stuborndate,stuphone,stuaddress,deptid) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",id,name,sex,age,br,ph,ad,dp);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
//修改学生信息
public void SUpdate() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("请输入需要修改信息的学生姓名:");
string name = Console.ReadLine();
string sql = @"select count(*) from students where stuname=@na";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@na",name)
};
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.Parameters.AddRange(para);
int c = (int)cmd.ExecuteScalar();
if (c > 0)
{
Console.WriteLine("请输入更改后的学生信息");
string Sname = Console.ReadLine();
string Ssex = Console.ReadLine();
string Sage = Console.ReadLine();
string SDate = Console.ReadLine();
string Sphone = Console.ReadLine();
string Saddress = Console.ReadLine();
string deptid = Console.ReadLine();
conn.Close();
string sql1 = @"update stud set StuName=@Tna,StuSex=@Tsex,StuAge=@Tage,StuBornDate=@Tborn,StuPhone=@Tphone,StuAddress=@Taddress,DeptId=@deptid where StuName=@Tname";
SqlParameter[] para1 = new SqlParameter[] {
new SqlParameter("@Tname",name),
new SqlParameter("@Tna",Sname),
new SqlParameter("@Tsex",Ssex),
new SqlParameter("@Tage",Sage),
new SqlParameter("@Tborn",SDate),
new SqlParameter("@Tphone",Sphone),
new SqlParameter("@Taddress",Saddress),
new SqlParameter("@deptid",deptid)
};
SqlCommand cmd2 = new SqlCommand(sql1,conn);
conn.Open();
cmd2.Parameters.AddRange(para1);
Console.ReadKey();
}
else {
Console.WriteLine("此学生不存在!");
Console.ReadKey();
}
}
}
//删除学生信息
public void SRemove() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("请输入需要删除的学生学号:");
int id = int.Parse(Console.ReadLine());
string sql = @"select count(*) from students where stuid=@id";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@id",id)
};
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.Parameters.AddRange(para);
int c = (int)cmd.ExecuteScalar();
if (c>0) {
string sql2 = @"delete from students where stuid=@id";
SqlParameter[] para2 = new SqlParameter[] {
new SqlParameter("@id",id)
};
SqlCommand cmd2 = new SqlCommand(sql2,conn);
cmd2.Parameters.AddRange(para2);
cmd2.ExecuteNonQuery();
} else {
Console.WriteLine("您要删除的学生不存在");
Console.ReadKey();
}
}
}
}
class Cou
{
static string strc = ConfigurationManager.ConnectionStrings["strc"].ToString();
//添加课程
public void CInsert() {
using (SqlConnection conn=new SqlConnection (strc)) {
Console.WriteLine("请输入课程编号、课程名称、课程学分并按回车结束");
string id = Console.ReadLine();
string name = Console.ReadLine();
string ct = Console.ReadLine();
string sql = string.Format("insert into course values ('{0}','{1}','{2}')",id,name,ct);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
//查看课程
public void CSelect() {
using (SqlConnection conn=new SqlConnection(strc)) {
string sql = "select * from Course ";
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine("课程编号\t课程名称\t课程学分");
while (dr.Read()) {
for (int i=0;i<dr.FieldCount;i++) {
Console.Write(dr[i]+"\t\t");
}
Console.WriteLine();
}
Console.ReadKey();
}
}
//删除课程
public void CRemove() {
using (SqlConnection conn=new SqlConnection (strc)) {
Console.WriteLine("请输入需要删除的课程编号");
string id = Console.ReadLine();
string sql = string .Format("delete course where courseid='{0}'",id);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
//修改课程名称
public void CUpdate() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("请输入需要修改的课程编号");
string id = Console.ReadLine();
Console.WriteLine("请输入修改后的课程编号、名称、学分");
string did= Console.ReadLine();
string dname = Console.ReadLine();
string dit = Console.ReadLine();
string sql =string.Format( "update course set courseid='{0}',coursename='{1}',credit='{2}' where courseid='{3}' ",did,dname,dit,id);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
}
class Sco
{
static string strc = ConfigurationManager.ConnectionStrings["strc"].ToString();
//查询成绩
public void Sselect() {
using (SqlConnection conn = new SqlConnection(strc))
{
string sql = "select * from Score ";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
Console.WriteLine("学号\t课程号\t考试时间\t\t考试成绩");
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + "\t");
}
Console.WriteLine();
}
}
Console.ReadKey();
}
//修改成绩
public void Supdate() {
using (SqlConnection conn = new SqlConnection(strc))
{
Console.WriteLine("请输入需要修改成绩的学号和课程编号");
string id = Console.ReadLine();
string cn = Console.ReadLine();
Console.WriteLine("请输入修改后的学号、课程编号、考试时间、考试成绩");
string did = Console.ReadLine();
string cid = Console.ReadLine();
string da = Console.ReadLine();
string sc = Console.ReadLine();
string sql = string.Format("update score set stuid='{0}',courseid='{1}',examdate='{2}',examscore='{3}' where stuid='{4}' and courseid='{5}' ", did, cid, da,sc, id,cn);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
//删除成绩
public void Sremove() {
using (SqlConnection conn = new SqlConnection(strc))
{
Console.WriteLine("请输入需要删除的课程成绩");
string id = Console.ReadLine();
string sql = string.Format("delete course where courseid='{0}'", id);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
Console.ReadKey();
}
}
}
class Dept
{
static string strc = ConfigurationManager.ConnectionStrings["strc"].ToString();
//添加院系
public void DInsert() {
using (SqlConnection conn =new SqlConnection (strc)) {
Console.WriteLine("请输入需要添加的院系代码和院系名,按回车结束");
string id=Console.ReadLine();
string name = Console.ReadLine();
string sql = string.Format("insert into department values ('{0}','{1}') ",id,name);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//修改院系信息
public void DUpdate() {
using (SqlConnection conn=new SqlConnection (strc)) {
Console.WriteLine("请输入需要修改的院系编号:");
string id=Console.ReadLine();
Console.WriteLine("请输入修改后的院系信息:");
string did=Console.ReadLine();
string name=Console.ReadLine();
string sql = string.Format("update depertment set deptid='{0}',deptname='{1}' where deptid='{2}'",did,name,id);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//删除院系
public void DDelete() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("请输入需要删除的院系编号:");
string id = Console.ReadLine();
string sql = string.Format("delete from department where deptid='{0}'",id);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
}
}
//查看所有院系
public void DSelect() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("系别编号\t系名");
string sql = "select * from department";
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read()) {
for (int i = 0; i < dr.FieldCount; i++) {
Console.Write(dr[i]+"\t\t");
}
Console.WriteLine();
}
Console.ReadKey();
}
}
}
static string strc = ConfigurationManager.ConnectionStrings["strc"].ToString();
//查找指定学生信息
public void zhselect() {
using (SqlConnection conn=new SqlConnection (strc)) {
Console.WriteLine("请输入学生学号:");
string id=Console.ReadLine();
string sql = @"select count(*) from students where stuid=@id";
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("@id",id)
};
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
cmd.Parameters.AddRange(para);
int c = (int)cmd.ExecuteScalar();
if (c > 0)
{
string sql2 = @"select * from students where stuid=@id";
SqlParameter[] para2 = new SqlParameter[] {
new SqlParameter("@id",id)
};
SqlCommand cmd2 = new SqlCommand(sql2,conn);
cmd2.Parameters.AddRange(para2);
cmd2.ExecuteNonQuery();
SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + "\t");
}
Console.WriteLine();
}
Console.ReadKey();
}
else {
Console.WriteLine("此学生不存在!");
Console.ReadKey();
}
}
}
//成绩排序
public void zhscore() {
using (SqlConnection conn=new SqlConnection(strc)) {
Console.WriteLine("请输入需要成绩排序的系别:");
string dpid=Console.ReadLine();
string sql = string .Format("select s.stuname,sc.ExamScore from Students s inner join score sc on s.stuid = sc.stuid inner join Department d on s.DeptId=d.DeptId where d.DeptId='{0}' order by ExamScore desc",dpid);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write(dr[i] + "\t");
}
Console.WriteLine();
}
Console.ReadKey();
}
}
//查询成绩优秀者
//查询成绩不合格者
}