学生信息管理系统2(程序设计篇+源码+数据库备份)

前一篇，已经把数据库个设计好了。剩下的就是照着文档进行编码了。 文末的源码

开发环境：

1. Visual Studio 2013

2. SQL server 2008

 

快速指南：下载源码，sql2008还原数据库，vs2013打开项目，编译执行

 

一.配置数据库连接

首先，第一步配置好数据库连接：

1.在app.config文件里添加连接字符串，如下代码：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>


  <connectionStrings>   
    <add name="sql_homework_end.Properties.Settings.sql_homework_endConnectionString"
      connectionString="Data Source=.;Initial Catalog=sql_homework_end;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>


    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

连接字符串
连接字符串

2.建立数据库公共类：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Configuration;
 4 using System.Data;
 5 using System.Data.SqlClient;
 6 using System.Linq;
 7 using System.Security.Cryptography;
 8 using System.Text;
 9 using System.Threading.Tasks;
10 
11 namespace sql_homework_end
12 {
13     class sqlhelper
14     {
15         private static string connStr = ConfigurationManager.ConnectionStrings["sql_homework_end.Properties.Settings.sql_homework_endConnectionString"].ConnectionString;
16         /// <summary>
17         /// 返回受影响的数据行数
18         /// </summary>
19         /// <param name="sql"></param>
20         /// <returns></returns>
21         public static int ExecuteNoQuery(string sql)
22         {
23             using (SqlConnection conn=new SqlConnection(connStr))
24             {
25                 conn.Open();
26                 using (SqlCommand cmd=conn.CreateCommand())
27                 {
28                   cmd.CommandText = sql;
29                   return cmd.ExecuteNonQuery();
30                   
31                 }
32             }
33         }
34         /// <summary>
35         /// 返回一个数据集
36         /// </summary>
37         /// <param name="sql"></param>
38         /// <returns></returns>
39         public static DataSet ExecuteDataSet(string sql)
40         {
41             using (SqlConnection xonn=new SqlConnection(connStr))
42             {
43                 xonn.Open();
44                 using (SqlCommand cmd = xonn.CreateCommand())
45                 {
46                     cmd.CommandText = sql;
47                     SqlDataAdapter adapter = new SqlDataAdapter(cmd);
48                     DataSet dataset = new DataSet();
49                     adapter.Fill(dataset);
50                     return dataset;
51                 }
52             }
53         }
54         public static object ExecuteScalar(string sql)
55         {
56             using (SqlConnection conn=new SqlConnection(connStr))
57             {
58                 conn.Open();
59                 using (SqlCommand cmd=conn.CreateCommand())
60                 {
61                     cmd.CommandText = sql;
62                     return cmd.ExecuteScalar();
63                 }
64             }
65         }
66         /// <summary>
67         /// md5加密
68         /// </summary>
69         /// <param name="strPwd"></param>
70         /// <returns></returns>
71         public static string GetMD5(string strPwd)
72         {
73             string pwd = "";
74             //实例化一个md5对象
75             MD5 md5 = MD5.Create();
76             // 加密后是一个字节类型的数组
77             byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(strPwd));
78             //翻转生成的MD5码        
79             s.Reverse();
80             //通过使用循环，将字节类型的数组转换为字符串，此字符串是常规字符格式化所得
81             //只取MD5码的一部分，这样恶意访问者无法知道取的是哪几位
82             for (int i = 3; i < s.Length - 1; i++)
83             {
84                 //将得到的字符串使用十六进制类型格式。格式后的字符是小写的字母，如果使用大写（X）则格式后的字符是大写字符
85                 //进一步对生成的MD5码做一些改造
86                 pwd = pwd + (s[i] < 198 ? s[i] + 28 : s[i]).ToString("X");
87             }
88             return pwd;
89         }
90     
91     
92     }
93 }
View Code

二.功能设计

1.登录功能

界面预览：

功能有：身份验证，退出。公共变量传值。

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 using System.Windows.Forms;
10 
11 namespace sql_homework_end
12 {
13     public partial class login : Form
14     {
15         public login()
16         {
17             InitializeComponent();
18         }
19 
20         //界面传值
21         public static string GlobelValue; // 注意，必须申明为static变量
22         //登录按钮事件
23         private void btn_login_Click(object sender, EventArgs e)
24         {
25             if (cmb_identity.Text.ToString()=="管理员")
26             {
27                 verify_identidy("tb_admin","admin_name");
28                 main a = new main();
29                 a.Show();
30             }
31             else if (cmb_identity.Text.ToString()=="学生")
32             {
33                 verify_identidy("tb_student","student_name");
34                 studnet s = new studnet();
35                 s.Show();
36             }
37             
38         }
39         //身份验证的函数
40         private void verify_identidy(string table,string name)
41         {            
42             DataSet ds = new DataSet();
43             ds = sqlhelper.ExecuteDataSet("select * from "+table+" where "+name+" = '" + tbx_loginname.Text + "'");
44             DataTable dt = new DataTable();
45             dt = ds.Tables[0];
46             if (dt.Rows.Count != 0)
47             {
48                 GlobelValue = tbx_loginname.Text;                
49                 this.Visible = false;   //隐藏当前窗体                                                      
50             }
51             else
52             {
53                 MessageBox.Show("用户名不存在，请重新输入");
54             }
55         }
56         //退出按钮
57         private void btn_close_Click(object sender, EventArgs e)
58         {
59             this.Close();
60         }
61        
62         
63         
64     }
65 }
View Code

2.主界面-管理员

预览：

管理员信息：

学生信息：

主要使用控件：datagridview

相关代码：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Threading.Tasks;
  9 using System.Windows.Forms;
 10 
 11 namespace sql_homework_end
 12 {
 13     public partial class main : Form
 14     {
 15         public main()
 16         {
 17             InitializeComponent();
 18             
 19         }
 20         //绑定并显示相关信息
 21         DataSet ds = new DataSet();
 22         DataTable dt = new DataTable();
 23         private void 学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
 24         {
 25             ds = sqlhelper.ExecuteDataSet("select * from tb_student");
 26             dt = ds.Tables[0];
 27             dataGridView1.DataSource = dt;
 28         }
 29 
 30         private void 课程信息ToolStripMenuItem_Click(object sender, EventArgs e)
 31         {
 32             ds = sqlhelper.ExecuteDataSet("select * from tb_course");
 33             dt = ds.Tables[0];
 34             dataGridView1.DataSource = dt;
 35         }
 36 
 37         private void 选课信息ToolStripMenuItem_Click(object sender, EventArgs e)
 38         {
 39             ds = sqlhelper.ExecuteDataSet("select * from tb_student_course");
 40             dt = ds.Tables[0];
 41             dataGridView1.DataSource = dt;
 42             dataGridView1.Columns["sc_id"].DisplayIndex = 0;
 43         }        
 44         private void 管理员信息ToolStripMenuItem_Click(object sender, EventArgs e)
 45         {
 46             ds = sqlhelper.ExecuteDataSet("select * from tb_admin");
 47             dt = ds.Tables[0];
 48             dataGridView1.DataSource = dt;
 49         }
 50         //添加按钮事件
 51         private void btn_insert_Click(object sender, EventArgs e)
 52         {
 53             if (dataGridView1.Columns[0].HeaderText=="student_num")
 54             {
 55                 string sql = @"insert tb_student (student_num,student_name,student_password,student_sex
 56                         ,student_age,student_dept)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" +
 57                         dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value
 58                         + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value
 59                         + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[5].Value + "')";
 60                 sqlhelper.ExecuteNoQuery(sql);
 61                 
 62             }
 63             else if (dataGridView1.Columns[0].HeaderText=="course_num")
 64             {
 65                 string sql = @"insert tb_course (course_num,course_name,course_credit,course_semester)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
 66                 sqlhelper.ExecuteNoQuery(sql);
 67             }
 68             else if (dataGridView1.Columns[0].HeaderText=="sc_id")
 69             {
 70                 try
 71                 {
 72                     string sql = @"insert tb_student_course (sc_id,student_num,course_num,grade)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
 73                     sqlhelper.ExecuteNoQuery(sql);
 74                 }
 75                 catch (Exception)
 76                 {
 77                     MessageBox.Show("学号或姓名不存在，请重新添加。");                    
 78                 }
 79                 
 80             }
 81             else if (dataGridView1.Columns[0].HeaderText == "admin_id")
 82             {
 83                 string sql = @"insert tb_admin (admin_id,admin_name,admin_password,remark)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
 84                 sqlhelper.ExecuteNoQuery(sql);
 85             }
 86             MessageBox.Show("添加成功");
 87         }
 88         //更新按钮事件
 89         private void btn_update_Click(object sender, EventArgs e)
 90         {
 91             if (dataGridView1.Columns[0].HeaderText == "student_num")
 92             {
 93                 update("tb_student","student_num");
 94             }
 95             else if (dataGridView1.Columns[0].HeaderText == "course_num")
 96             {
 97                 update("tb_course","course_num");
 98             }
 99             else if (dataGridView1.Columns[0].HeaderText == "admin_id")
100             {
101                 update("tb_admin","admin_id");
102             }
103             else if (dataGridView1.Columns[0].HeaderText == "sc_id")
104             {
105                 try
106                 {
107                     update("tb_studnet_course","sc_id");
108                 }
109                 catch (Exception)
110                 {
111                     MessageBox.Show("学号或课程号不存在，请重新输入");
112                     throw;
113                 }                
114             }                       
115         }
116         //更新方法
117         private void update(string table,string head_id) 
118         {
119             for (int i = 0; i < dataGridView1.RowCount; i++)
120             {
121                 int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
122                 for (int j = 1; j < dataGridView1.ColumnCount; j++)
123                 {
124                     if (dataGridView1.Columns[j].Visible == true)
125                     {
126                         string columnName = dataGridView1.Columns[j].Name.ToString();
127                         string sql = "update " + table + " set " + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where " + head_id + "='" + id + "'";
128                         sqlhelper.ExecuteNoQuery(sql);
129                     }
130                 }
131             }
132             MessageBox.Show("更新成功");
133         }
134 
135         //删除事件
136         private void btn_delete_Click(object sender, EventArgs e)
137         {
138             if (dataGridView1.Columns[0].HeaderText == "student_num")
139             {
140                 string sql = "delete from tb_student where student_num='" + dataGridView1.SelectedCells[0].Value + "'";
141                 sqlhelper.ExecuteNoQuery(sql);                
142             }
143             else if (dataGridView1.Columns[0].HeaderText == "course_num")
144             {
145                 string sql = "delete from tb_course where course_num='" + dataGridView1.SelectedCells[0].Value + "'";
146                 sqlhelper.ExecuteNoQuery(sql);
147             }
148             else if (dataGridView1.Columns[0].HeaderText == "sc_id")
149             {
150                 string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";
151                 sqlhelper.ExecuteNoQuery(sql);
152             }
153             else if (dataGridView1.Columns[0].HeaderText == "admin_id")
154             {
155                 string sql = "delete from tb_admin where admin_id='" + dataGridView1.SelectedCells[0].Value + "'";
156                 sqlhelper.ExecuteNoQuery(sql);
157             }
158             MessageBox.Show("删除成功");
159         }
160         
161         //界面载入显示身份和登录时间
162         private void main_Load(object sender, EventArgs e)
163         {
164             lbl_username.Text = "Welcome," + login.GlobelValue + "";
165             lbl_logintime.Text ="登录时间:" +DateTime.Now.ToString()+"";
166         }
167     }
168 }
View Code

3.学生界面

界面预览：

个人信息显示：在这个界面，学生能看到自己的信息，并进行修改操作。

代码：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Threading.Tasks;
  9 using System.Windows.Forms;
 10 
 11 namespace sql_homework_end
 12 {
 13     public partial class studnet : Form
 14     {
 15         public studnet()
 16         {
 17             InitializeComponent();
 18         }
 19 
 20         DataSet ds = new DataSet();
 21         DataTable dt = new DataTable();
 22         private void studnet_Load(object sender, EventArgs e)
 23         {
 24             lbl_username.Text = "Welcome," + login.GlobelValue + "";
 25             lbl_logintime.Text = "登录时间:" + DateTime.Now.ToString() + "";
 26            
 27             
 28         }
 29         //相关信息显示
 30         private void 个人信息ToolStripMenuItem_Click(object sender, EventArgs e)
 31         {
 32             ds = sqlhelper.ExecuteDataSet("select * from tb_student where student_name='" + login.GlobelValue + "'");
 33             dt = ds.Tables[0];
 34             dataGridView1.DataSource = dt;
 35             btn_update.Visible = true;
 36             btn_insert.Visible = false;
 37             btn_delete.Visible = false;
 38         }
 39 
 40         private void 课程信息ToolStripMenuItem_Click(object sender, EventArgs e)
 41         {
 42             
 43             ds = sqlhelper.ExecuteDataSet("select * from tb_course");
 44             dt = ds.Tables[0];
 45             dataGridView1.DataSource = dt;
 46             btn_update.Visible = false;
 47             btn_insert.Visible = false;
 48             btn_delete.Visible = false;
 49         }
 50 
 51         private void 选课管理ToolStripMenuItem_Click(object sender, EventArgs e)
 52         {
 53             
 54             ds = sqlhelper.ExecuteDataSet(@"select sc.sc_id,s.student_num,c.course_num,c.course_name,grade
 55 from tb_student_course sc join tb_student s on sc.student_num=s.student_num
 56 join tb_course c on sc.course_num=c.course_num 
 57 where s.student_name = '" + login.GlobelValue + "'");
 58             dt = ds.Tables[0];
 59             dataGridView1.DataSource = dt;
 60             dataGridView1.Columns["sc_id"].DisplayIndex = 0;
 61             btn_update.Visible = false;
 62             btn_insert.Visible = true;
 63             btn_delete.Visible = true;
 64         }       
 65         //学生选课功能
 66         private void btn_insert_Click_1(object sender, EventArgs e)
 67         {
 68             if (Convert.ToInt32(dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value) > 0)
 69             {
 70                 MessageBox.Show("不准填写成绩");
 71             }
 72             else
 73             {
 74                 string sql = @"insert tb_student_course (sc_id,student_num,course_num)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "')";
 75                 sqlhelper.ExecuteNoQuery(sql);
 76                 MessageBox.Show("选课成功");
 77             }            
 78         }
 79         //删除选的课程
 80         private void btn_delete_Click(object sender, EventArgs e)
 81         {
 82             string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";
 83             sqlhelper.ExecuteNoQuery(sql);
 84             MessageBox.Show("删除成功，请重新选课。");
 85         }
 86         //更新个人信息
 87         private void btn_update_Click(object sender, EventArgs e)
 88         {
 89             update("tb_student", "student_num");
 90         }
 91         private void update(string table, string head_id)
 92         {
 93             for (int i = 0; i < dataGridView1.RowCount; i++)
 94             {
 95                 int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
 96                 for (int j = 1; j < dataGridView1.ColumnCount; j++)
 97                 {
 98                     if (dataGridView1.Columns[j].Visible == true)
 99                     {
100                         string columnName = dataGridView1.Columns[j].Name.ToString();
101                         string sql = "update " + table + " set " + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where " + head_id + "='" + id + "'";
102                         sqlhelper.ExecuteNoQuery(sql);
103                     }
104                 }
105             }
106             MessageBox.Show("更新成功");
107         }
108        
109     }
110 }
View Code

 

最后，写得有点乱啊。看不懂的话，可以看源码。

 

posted @ 2017-01-09 20:33  智博的日常  阅读(17652)  评论(1)  编辑  收藏  举报