文章目录
C# 学生管理系统
使用数据库为SQL Server
代码已存放在Github:https://github.com/BrumaireParis/Csharp_Curricula_Variable_System
B站视频演示:https://www.bilibili.com/video/BV1U54y1B7DL/
概览
本次的实验我的程序架构大概是这个样子：
先给出我的Test解决方案里面所有的程序块：
数据库一览：
接下来我会对每项的代码进行详细说明。
C#代码
1、LoginForm(登录界面)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Windows.Forms;
using System.Media;
namespace Test
{
public partial class LoginForm : Form
{
protected override CreateParams CreateParams //发现系统的双缓冲加载并不给力，网上搜到的重写的函数，效果拔群
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
public LoginForm()
{
InitializeComponent();
this.MaximizeBox = false; //禁止最大化
StartPosition = FormStartPosition.CenterScreen;
}
bool formMove = false;//窗体是否移动
Point formPoint;//记录窗体的位置 这是把默认的Border去掉后的窗体移动的函数
//一直到下面的Mouse_Down都是
private void LoginForm_MouseDown(object sender, MouseEventArgs e)//鼠标按下
{
formPoint = new Point();
int xOffset;
int yOffset;
if (e.Button == MouseButtons.Left)
{
xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
formPoint = new Point(xOffset, yOffset);
formMove = true;//开始移动
}
}
private void LoginForm_MouseMove(object sender, MouseEventArgs e)//鼠标移动
{
if (formMove == true)
{
Point mousePos = Control.MousePosition;
mousePos.Offset(formPoint.X, formPoint.Y);
Location = mousePos;
}
}
private void LoginForm_MouseUp(object sender, MouseEventArgs e)//鼠标松开
{
if (e.Button == MouseButtons.Left)//按下的是鼠标左键
{
formMove = false;//停止移动
}
}
private string code; //声明变量Code，作验证码
private void playsound()
{
SoundPlayer play = new SoundPlayer(Test.Properties.Resources.GF_Title);
play.PlayLooping();
} //播放背景音乐
private void LoginForm_Load(object sender, EventArgs e)
{
playsound(); //！！注意！！ 启用背景音乐时，进入重置密码界面会导致Crash，原因不明
Random ran = new Random();
int number;
char code1;
for (int i = 0; i < 4; i++)
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26));
this.code += code1.ToString();
}
label6.Text = code;
}
private void Button1_Click(object sender, EventArgs e)
{
string username = textBoxUserName.Text.Trim(); //取出账号
string password = EncryptWithMD5(textBoxPassWord.Text.Trim()); //取出密码
string checkcode = textBoxCode.Text.Trim();
if (string.Compare(checkcode, code, true) != 0)
{
MessageBox.Show("验证码错误！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
foreach (Control control in this.Controls)
{ if (control.GetType().Name == "TextBox") { ((TextBox)control).Text = string.Empty; } }
LoginForm_Load();
return;
}
//string connstr = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); //读取连接字符串
try
{
string myConnString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection sqlConnection = new SqlConnection(myConnString); //实例化连接对象
sqlConnection.Open();
string sql = "select Accounts,Passwords from Account where Accounts = '" + username + "' and Passwords = '" + password + "'";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows)
{
SoundPlayer play = new SoundPlayer(Test.Properties.Resources.HK416);
play.Load();
play.Play();
play.Dispose();
MessageBox.Show("欢迎登录：" + username, "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
sqlDataReader.Close();
string sql1 = "SELECT * FROM Account WHERE Accounts = '" + username + "'";
SqlCommand command = new SqlCommand(sql1, sqlConnection);
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
Program.loginName = read["Accounts"].ToString();
Program.photoPath = read["Photo"].ToString();
Program.loginID = read["ID"].ToString();
Program.LoginType = read["Type"].ToString();
}
MainForm mainform = new MainForm();
mainform.Show();
this.Hide();
}
else
{
LoginForm_Load();
MessageBox.Show("账号密码错误!\nError:001", "notice", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
sqlConnection.Close();
}
catch (SqlException) {
LoginForm_Load();
MessageBox.Show("数据库连接失败！请联系管理人员！\nError:002", "发生错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void LoginForm_KeyPress(object sender, KeyPressEventArgs e)
{
if ((Keys)e.KeyChar == Keys.Enter)
{
Button1_Click(sender, e);
}
}
private void LoginForm_Load()
{
//throw new NotImplementedException();
Random ran = new Random();
int number;
char code1;
code = null;
for (int i = 0; i < 4; i++)
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26));
this.code += code1.ToString();
}
label6.Text = code;
}
private void Button3_Click(object sender, EventArgs e)
{
Reg reg = new Reg();
reg.StartPosition = FormStartPosition.CenterScreen;
reg.ShowDialog();
}
public static string EncryptWithMD5(string source)
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
private void PictureBox1_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void PictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
private void Label6_Click(object sender, EventArgs e)
{
LoginForm_Load();
}
private void LinkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
ResetP resetp = new ResetP();
resetp.StartPosition = FormStartPosition.CenterScreen;
resetp.Show();
}
}
}
2、Reg(注册界面)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Test
{
public partial class Reg : Form
{
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
public Reg()
{
InitializeComponent();
}
bool formMove = false;//窗体是否移动
Point formPoint;//记录窗体的位置
private void Reg_MouseDown(object sender, MouseEventArgs e)//鼠标按下
{
formPoint = new Point();
int xOffset;
int yOffset;
if (e.Button == MouseButtons.Left)
{
xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
formPoint = new Point(xOffset, yOffset);
formMove = true;//开始移动
}
}
private void Reg_MouseMove(object sender, MouseEventArgs e)//鼠标移动
{
if (formMove == true)
{
Point mousePos = Control.MousePosition;
mousePos.Offset(formPoint.X, formPoint.Y);
Location = mousePos;
}
}
private void Reg_MouseUp(object sender, MouseEventArgs e)//鼠标松开
{
if (e.Button == MouseButtons.Left)//按下的是鼠标左键
{
formMove = false;//停止移动
}
}
private string code;
private void Reg_Load(object sender, EventArgs e)
{
Random ran = new Random();
int number;
char code1;
for (int i = 0; i < 4; i++)
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26));
this.code += code1.ToString();
}
label7.Text = code;
}
public static string EncryptWithMD5(string source)
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
private void Button1_Click(object sender, EventArgs e)
{
string r_username = textBox1.Text.Trim();
string password1 = textBox2.Text.Trim();
string password2 = textBox3.Text.Trim();
string type = comboBox1.Text.Trim();
string sid = textBox4.Text.Trim();
string checkcode = textBox5.Text.Trim();
string mail = textBox6.Text.Trim();
foreach (Control control in this.Controls)
{ if (control.GetType().Name == "TextBox") { ((TextBox)control).Text = string.Empty; } }
if (r_username==""||password2==""||sid==""||mail=="" )
{
MessageBox.Show("请完整填写信息！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Reg_Load();
return;
}
if (string.Equals(password1, password2) == false)
{
MessageBox.Show("两次输入密码不相符！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Reg_Load();
return;
}
//MessageBox.Show(type);
if (type!="学生"&&type!="教师"&&type!="管理员")
{
MessageBox.Show("未选择账号类型！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
Reg_Load();
return;
}
if (string.Compare(checkcode,code,true)!=0)
{
MessageBox.Show("验证码错误！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
Reg_Load();
return;
}
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "INSERT INTO Account (Accounts,Passwords,ID,Type,Email) VALUES (@username, @userpassword,@userid,@usertype,@usermail)";
SqlCommand command = new SqlCommand(sql, connection);
string sql2 = "SELECT Accounts FROM Account WHERE ID='" + sid + "'OR Accounts='"+r_username+"'";
SqlCommand command2 = new SqlCommand(sql2, connection);
SqlParameter sqlParameter = new SqlParameter("@username", r_username);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userpassword", EncryptWithMD5(password1));
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usertype", type);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userid", sid);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usermail", mail);
command.Parameters.Add(sqlParameter);
//打开数据库连接
connection.Open();
SqlDataReader sqlDataReader = command2.ExecuteReader();
if (sqlDataReader.HasRows)
{
MessageBox.Show("该ID或账号已被注册!\nError:003", "notice", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
Reg_Load();
return;
}
sqlDataReader.Close();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("注册成功！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
//MessageBox.Show("看到这条表示程序还在运行","警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
private void Reg_Load()
{
//throw new NotImplementedException();
Random ran = new Random();
int number;
char code1;
code = null;
for (int i = 0; i < 4; i++)
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26));
this.code += code1.ToString();
}
label7.Text = code;
}
private void Button2_Click(object sender, EventArgs e)
{
this.Close();
}
private void textBox2_Leave(object sender, EventArgs e)
{
if (textBox2.Text.Trim() != "")
{
//使用regex（正则表达式）进行格式设置 至少有数字、大写字母、小写字母各一个。最少6个字符、最长16个字符。
Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{6,16}");
if (regex.IsMatch(textBox2.Text))//判断格式是否符合要求
{
//MessageBox.Show("输入密码格式正确!");
}
else
{
MessageBox.Show("至少有数字、大写字母、小写字母各一个。最少6个字符、最长16个字符！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
textBox2.Text = string.Empty;
Reg_Load();
textBox2.Focus();
}
}
}
private void Button3_Click(object sender, EventArgs e)
{
MessageBox.Show("1、密码必须大于等于6位且小于等于16位;\n2、密码至少含有一个大写字母、小写字母和数字;\n3、每人仅可注册一个账号，如有管理员权限请注册为管理员;\n4、验证码不区分大小写。", "帮助信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void PictureBox3_Click(object sender, EventArgs e)
{
this.Close();
}
private void PictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
private void Label7_Click(object sender, EventArgs e)
{
Reg_Load();
}
}
}
3、ResetP(重设密码界面)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
using System.Data.SqlClient;
using System.Media;
namespace Test
{
public partial class ResetP : Form
{
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
public ResetP()
{
InitializeComponent();
}
private void PictureBox3_Click(object sender, EventArgs e)
{
this.Close();
}
private void PictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
private string code;
private int cnt1, cnt2;
public static string CreateRandomCode(int length) //生成由数字和大小写字母组成的验证码
{
string list = "qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM1234567890";
Random random = new Random();
string code = ""; //验证码
for (int i = 0; i < length; i++) //循环6次得到一个伪随机的六位数验证码
{
code += list[random.Next(0, list.Length - 1)];
}
return code;
}
private void Button1_Click(object sender, EventArgs e)
{
string userid = textBox1.Text.Trim(); //账号
string email = textBox2.Text.Trim(); //邮箱
if (!String.IsNullOrEmpty(userid) && !String.IsNullOrEmpty(email)) //账号、邮箱非空
{
SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=SCHOOL;User ID=sa;Password=a123456");
connection.Open();
SqlCommand command = new SqlCommand("SELECT Accounts,Email FROM Account WHERE Accounts='" + userid + "' AND Email='" + email + "'", connection);
SqlDataReader data = command.ExecuteReader();
if (data.HasRows) //若输入的电子邮箱是账号注册时填写的邮箱
{
try
{
MailMessage mail = new MailMessage(); //实例化一个发送邮件类
mail.From = new MailAddress(Program.Email163); //发件人邮箱地址
mail.To.Add(new MailAddress(email)); //收件人邮箱地址
mail.Subject = "【选课管理系统V1.0】找回密码"; //邮件标题
code = CreateRandomCode(6); //生成伪随机的6位数验证码
mail.Body = "验证码是: " + code + "，请在5分钟内进行验证。验证码提供给他人可能导致账号被盗，请勿泄露，谨防被骗。系统邮件请勿回复。"; //邮件内容
SmtpClient client = new SmtpClient("smtp.163.com"); //实例化一个SmtpClient类。
client.EnableSsl = true; //使用安全加密连接
client.Credentials = new NetworkCredential(Program.Email163, Program.AuthorizationCode);//验证发件人身份(发件人的邮箱，邮箱里的生成授权码);
client.Send(mail);
//计时器初始化
cnt1 = 600;
cnt2 = 3000;
timer1.Enabled = true; //time1用来记录1分钟
timer2.Enabled = true; //time2用来记录5分钟
button1.Enabled = false; //发送按钮不可点击
MessageBox.Show("发送成功！");
}
catch
{
MessageBox.Show("发送失败！\n请检查邮箱是否输入有误。", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("该邮箱不是账号绑定的邮箱。", "失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("请将账号和邮箱填写完整！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void Timer1_Tick(object sender, EventArgs e) //发送完邮件,需要60秒后才能再次发送邮件
{
if (cnt1 > 0)
{
cnt1--;
button1.Text = "发送(" + cnt1/10 + ")";
}
else
{
timer1.Enabled = false;
button1.Enabled = true;
button1.Text = "发送";
}
}
private void Timer2_Tick(object sender, EventArgs e) //验证码5分钟内有效,但是如果有新的验证码出现,旧验证码就会GG
{
if (cnt2 == 0)
{
timer2.Enabled = false;
code = CreateRandomCode(6); //旧的验证码过期,生成一个新的验证码
}
}
private void Button2_Click(object sender, EventArgs e)
{
string rpassword = Program.EncryptWithMD5(textBox4.Text.Trim());
string ac = textBox1.Text.Trim();
if(string.Compare(textBox3.Text.Trim(),code,true)==0)
{
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "Update Account set Passwords = '"+rpassword+"'Where Accounts='"+ac+"'";
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter sqlParameter = new SqlParameter("@userid", ac);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userpassword", rpassword);
command.Parameters.Add(sqlParameter);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("更改密码成功！");
this.Close();
}
catch(SqlException)
{
MessageBox.Show("数据库连接错误!");
}
}
else
{
MessageBox.Show("验证码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
4、MainForm(主界面，包括了选课和查询界面)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Threading.Tasks;
using System.Drawing;
using System.Windows.Forms;
using System.Media;
using System.IO;
using System.Data.SqlClient;
using System.Linq.Expressions;
namespace Test
{
public partial class MainForm : Form
{
int PanelWidth;
bool isCollapsed;
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
public MainForm()
{
InitializeComponent();
PanelWidth = 0;//panel1.Left;
isCollapsed = true;
}
bool formMove = false;//窗体是否移动
Point formPoint;//记录窗体的位置
private void MainForm_MouseDown(object sender, MouseEventArgs e)//鼠标按下
{
formPoint = new Point();
int xOffset;
int yOffset;
if (e.Button == MouseButtons.Left)
{
xOffset = -e.X - SystemInformation.FrameBorderSize.Width;
yOffset = -e.Y - SystemInformation.CaptionHeight - SystemInformation.FrameBorderSize.Height;
formPoint = new Point(xOffset, yOffset);
formMove = true;//开始移动
}
}
private void MainForm_MouseMove(object sender, MouseEventArgs e)//鼠标移动
{
if (formMove == true)
{
Point mousePos = Control.MousePosition;
mousePos.Offset(formPoint.X, formPoint.Y);
Location = mousePos;
}
}
private void MainForm_MouseUp(object sender, MouseEventArgs e)//鼠标松开
{
if (e.Button == MouseButtons.Left)//按下的是鼠标左键
{
formMove = false;//停止移动
}
}
protected override void OnResize(EventArgs e)
{
this.Region = null;
SetWindowR();
}
private void SetWindowR()
{
System.Drawing.Drawing2D.GraphicsPath
gPath = new System.Drawing.Drawing2D.GraphicsPath();
Rectangle rect = new Rectangle(0, 5, this.Width, this.Height - 5);
gPath = GetRoundedRP(rect, 30); //后面的30是圆的角度，数值越大圆角度越大
this.Region = new Region(gPath);
}
private System.Drawing.Drawing2D.GraphicsPath GetRoundedRP(Rectangle rect, int a)
{
int diameter = a;
Rectangle arcRect = new Rectangle(rect.Location, new Size(diameter, diameter));
System.Drawing.Drawing2D.GraphicsPath gp = new System.Drawing.Drawing2D.GraphicsPath();
gp.AddArc(arcRect, 180, 90);
arcRect.X = rect.Right - diameter;
gp.AddArc(arcRect, 270, 90);
arcRect.Y = rect.Bottom - diameter;
gp.AddArc(arcRect, 0, 90);
arcRect.X = rect.Left;
gp.AddArc(arcRect, 90, 90);
gp.CloseFigure();
return gp;
}
private void MainForm_Load(object sender, EventArgs e)
{
timer1.Start();
pictureBox6.Visible = false;
pictureBox7.Visible = false;
pictureBox8.Visible = false;
pictureBox9.Visible = false;
SoundPlayer play = new SoundPlayer(Test.Properties.Resources.GF_Lobby);
play.PlayLooping();
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection connection = new SqlConnection(connString);
string sql = "SELECT Photo FROM Account WHERE Accounts='"+Program.loginName+"'";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
SqlDataReader read = command.ExecuteReader();
while (read.Read())
Program.photoPath = read["Photo"].ToString();
read.Close();
connection.Close();
//判断是否为空，为空时的不执行
if (Program.photoPath != null)
{
// 将图片放置在 PictureBox 中
this.pictureBox10.SizeMode = PictureBoxSizeMode.Zoom;
this.pictureBox10.BackgroundImage = Image.FromFile(@Program.photoPath);
}
}
catch (Exception)
{
//MessageBox.Show("未能加载到照片，请尽快提交！");
pictureBox10.BackgroundImage = Test.Properties.Resources._404logo;
}
if(Program.LoginType.Trim()=="学生")
{
try
{
string conString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection myconnection = new SqlConnection(conString);
string sql2 = "SELECT * FROM Student WHERE ID='" + Program.loginID + "'";
SqlCommand sqlCommand = new SqlCommand(sql2, myconnection);
myconnection.Open();
SqlDataReader read1 = sqlCommand.ExecuteReader();
while (read1.Read())
{
Program.realName = read1["Sname"].ToString();
label11.Text = read1["Sage"].ToString();
label16.Text = read1["Ssex"].ToString();
label14.Text = read1["Sdept"].ToString();
label20.Text = read1["Sclass"].ToString();
label18.Text = read1["Stel"].ToString();
}
read1.Close();
myconnection.Close();
button3.Enabled = false;
button4.Enabled = false;
button5.Enabled = false;
}
catch (Exception)
{
MessageBox.Show("无法初始化学生类账户！");
}
}
else if (Program.LoginType.Trim() == "教师")
{
try
{
string cconString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection cconnection = new SqlConnection(cconString);
string sql2 = "SELECT * FROM Teacher WHERE ID='" + Program.loginID + "'";
SqlCommand sqlCCommand = new SqlCommand(sql2, cconnection);
cconnection.Open();
SqlDataReader read2 = sqlCCommand.ExecuteReader();
while (read2.Read())
{
Program.realName = read2["Tname"].ToString();
label11.Text = read2["Tage"].ToString();
label16.Text = read2["Tsex"].ToString();
label14.Text = read2["Tdept"].ToString();
label20.Text = read2["Tlev"].ToString();
label18.Text = read2["Ttel"].ToString();
}
read2.Close();
cconnection.Close();
label19.Text = "职称：";
button3.Enabled = false;
button4.Enabled = false;
}
catch (Exception) { MessageBox.Show("无法初始化教师类账户！"); }
}
label3.Text = Program.loginName;
labelLG.Text = Program.loginName;
label7.Text = Program.realName;
label9.Text = Program.loginID;
label21.Text = Program.LoginType;
}
private void PictureBox3_Click(object sender, EventArgs e)
{
timer2.Stop();
Random ran = new Random();
int r = ran.Next(0, 5);
pictureBox6.Visible=false;
pictureBox7.Visible = false;
pictureBox8.Visible = false;
pictureBox9.Visible = false;
if (r == 1)
pictureBox6.Visible = true;
if (r == 2)
pictureBox7.Visible = true;
if (r == 3)
pictureBox8.Visible = true;
if (r == 4)
pictureBox9.Visible = true;
timer2.Start();
}
private void Timer2_Tick(object sender, EventArgs e)
{
//timer2.Stop();
pictureBox6.Visible = false;
pictureBox7.Visible = false;
pictureBox8.Visible = false;
pictureBox9.Visible = false;
}
private void PictureBox4_Click(object sender, EventArgs e)
{
this.Dispose();
LoginForm lg = new LoginForm();
lg.Show();
}
private void Timer3_Tick(object sender, EventArgs e)
{
if (isCollapsed)
{
panel1.Left = panel1.Left + 50;
if (panel1.Left >= PanelWidth)
{
timer3.Stop();
isCollapsed = false;
this.Refresh();
}
}
else
{
panel1.Left = panel1.Left - 50;
if (panel1.Left <= -500)
{
timer3.Stop();
isCollapsed = true;
this.Refresh();
}
}
}
private void PictureBox5_Click(object sender, EventArgs e)
{
timer3.Start();
}
private void Button7_Click(object sender, EventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.Title = "请选择要上传的图片";
openfile.Filter = "图片(*.jpg;*.bmp;*png)|*.jpeg;*.jpg;*.bmp;*.png|AllFiles(*.*)|*.*";
if (DialogResult.OK == openfile.ShowDialog())
{
try
{
string picturePath = openfile.FileName;
string picName = openfile.SafeFileName;
string pSaveFilePath = "D:\\LocalPic\\";//指定存储的路径
if (!System.IO.Directory.Exists(@"D:\LocalPic"))
{
System.IO.Directory.CreateDirectory(@"D:\LocalPic");//不存在就创建目录
}
string picSave = pSaveFilePath + picName;
Program.photoPath = picSave;
//MessageBox.Show(Program.photoPath);
if (File.Exists(picturePath))//必须判断要复制的文件是否存在
File.Copy(picturePath, picSave, true);
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection connection = new SqlConnection(connString);
string sql = "Update Account set Photo = '"+picSave+"'WHERE Accounts = '"+Program.loginName+"'";
SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
this.pictureBox10.SizeMode = PictureBoxSizeMode.Zoom;
this.pictureBox10.BackgroundImage = Image.FromFile(@Program.photoPath);
MessageBox.Show("上传成功！");
}
catch(Exception) { MessageBox.Show("上传图片出现错误！"); }
}
}
private void button6_Click(object sender, EventArgs e)
{
Pinfo pinfo = new Pinfo();
pinfo.ShowDialog();
MainForm_Load(sender,e);
pinfo.Close();
}
private void button1_Click(object sender, EventArgs e)
{
panel4.Visible = true;
}
private void button2_Click(object sender, EventArgs e)
{
panel3.Visible = true;
}
private void button3_Click(object sender, EventArgs e)
{
//MessageBox.Show("Update与Delete已整合在一起。");
Admin admin = new Admin();
admin.Show();
this.Close();
}
private void button4_Click(object sender, EventArgs e)
{
//MessageBox.Show("Update与Delete已整合在一起。");
Admin admin = new Admin();
admin.Show();
this.Close();
}
private void button5_Click(object sender, EventArgs e)
{
panel5.Visible = true;
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "EXEC Proc_TRANSFER1 " +
"SELECT* FROM SC_Avg";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView3.DataSource = ds.Tables[0];
dataGridView3.ReadOnly = true;
dataGridView3.AllowUserToAddRows = false;
dataGridView3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView3.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void PictureBox1_Click(object sender, EventArgs e)
{
timer.Start();
}
private void PictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
private void Timer1_Tick(object sender, EventArgs e)
{
label2.Text = "今の時間:" + System.DateTime.Now.ToString();
}
private void Timer_Tick(object sender, EventArgs e)
{
if (this.Opacity >= 0.025)
this.Opacity -= 0.025;
else
{
timer.Stop();
Application.Exit();
}
}
/*---------------------------*/
//查询界面
private bool xscx;
private bool cxcj;
private void Panel3_Load(object sender,EventArgs e)
{
label22.Visible = false;
textBox1.Visible = false;
button11.Visible = false;
label23.Visible = false;
if (Program.LoginType.Trim()=="教师")
{
button8.BackgroundImage = Test.Properties.Resources.skcx;
button9.BackgroundImage = Test.Properties.Resources.xscx;
pictureBox11.BackgroundImage = Test.Properties.Resources.txgz;
pictureBox12.BackgroundImage = Test.Properties.Resources.lyco;
xscx = false;
}
else
{
cxcj = false;
}
}
private void button10_Click(object sender, EventArgs e)
{
panel3.Visible = false;
dataGridView1.Visible = false;
label22.Visible = false;
label23.Visible = false;
textBox1.Visible = false;
button11.Visible = false;
}
//查询全部课程
private void StuCourse()
{
//数据库连接串
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
//创建SqlConnection的实例
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
//打开数据库
conn.Open();
string sql = "SELECT TRIM(Sname) as 姓名, TRIM(Cno) as 课程号, TRIM(Cname) as 课程名,Ccredit as 学分, Trim(Tname) as 任课老师 FROM Stuc WHERE Sname='" + Program.realName+"'";
//创建SqlDataAdapter类的对象
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//创建DataSet类的对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
da.Fill(ds,"Stuc");
//设置表格控件的DataSource属性
dataGridView1.DataSource = ds.Tables["Stuc"];//.DefaultView;//.Tables[0];
//设置数据表格为只读
dataGridView1.ReadOnly = true;
//不允许添加行
dataGridView1.AllowUserToAddRows = false;
//整行选中
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void StuGrade()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
//创建SqlConnection的实例
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
//打开数据库
conn.Open();
string sql = "SELECT Trim(Sname) as 姓名 ,Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit AS 学分,Grade as 成绩 FROM Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno AND Student.ID='"+Program.loginID+"'";
string sql1 = "SELECT Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit AS 学分,Grade as 成绩 FROM Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno AND Cname like '%"+textBox1.Text.Trim()+"%' and Student.ID = '"+Program.loginID+"' ";
//创建SqlDataAdapter类的对象
if (textBox1.Text.Trim() != "ALL")
{
SqlDataAdapter db = new SqlDataAdapter(sql1, conn);
DataSet dss = new DataSet();
db.Fill(dss);
dataGridView1.DataSource = dss.Tables[0];
}
else
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void TeaCourse()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
//创建SqlConnection的实例
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT Trim(Tname) as 姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='"+Program.loginID+"' ";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void TeacherStu()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
//创建SqlConnection的实例
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
//打开数据库
conn.Open();
string sql = "SELECT Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名 ,Trim(Student.ID) as 学号,trim(Sname) as 学生姓名,Grade as 成绩 FROM Teacher, Course, SC, Student WHERE Student.ID = SC.ID AND SC.Cno = Course.Cno and Course.ID = Teacher.ID And Teacher.ID='" + Program.loginID + "'order by SC.Cno ASC; ";
string sql1 = "SELECT Trim(Student.ID) AS 学号, Trim(Sname) as 学生名,Trim(SC.Cno) as 课序号 , Trim(Cname) as 课程名,trim(Tname) as 教师名 FROM Course, SC, Student, Teacher WHERE Teacher.ID = Course.ID AND SC.Cno = Course.Cno AND SC.ID = Student.ID and Sname like '%" + textBox1.Text.Trim() + "%' and Teacher.ID = '" + Program.loginID + "'";
if (textBox1.Text.Trim() != "ALL")
{
SqlDataAdapter daa = new SqlDataAdapter(sql1, conn);
DataSet dss = new DataSet();
daa.Fill(dss);
dataGridView1.DataSource = dss.Tables[0];
}
else
{
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
//设置数据表格为只读
dataGridView1.ReadOnly = true;
//不允许添加行
dataGridView1.AllowUserToAddRows = false;
//整行选中
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void button8_Click(object sender, EventArgs e)
{
dataGridView1.Visible = true;
label22.Visible = false;
label23.Visible = false;
textBox1.Visible = false;
button11.Visible = false;
if (Program.LoginType.Trim() == "学生")
{
StuCourse();
}
else if (Program.LoginType.Trim() == "教师")
{
TeaCourse();
}
}
private void button9_Click(object sender, EventArgs e)
{
dataGridView1.Visible = true;
label22.Visible = true;
textBox1.Visible = true;
label23.Visible = true;
button11.Visible = true;
dataGridView1.DataSource = null;
if (Program.LoginType.Trim() == "学生")
{
cxcj = true;
xscx = false;
label22.Text = "请输入你要查询的课程名：";
}
if (Program.LoginType.Trim() == "教师")
{
xscx = true;
cxcj = false;
label22.Text = "请输入你要查询的学生名：";
}
}
private void button11_Click(object sender, EventArgs e)
{
if(cxcj==true)
{
dataGridView1.DataSource = null;
StuGrade();
}
else if(xscx==true)
{
dataGridView1.DataSource = null;
TeacherStu();
}
}
/*------------------------------------------------------------------------*/
//选课退课
private bool studentsel;
private bool studentdel;
private bool teachersel;
private bool teacherdel;
private bool teachersel2 = false;
private bool teacherdel2 = false;
private string sid;
private string ccid;
private void Panel4_Load(object sender,EventArgs e)
{
label24.Visible = false;
button15.Visible = false;
if (Program.LoginType.Trim()=="教师")
{
pictureBox13.BackgroundImage = Test.Properties.Resources.NPC_Seele;
pictureBox14.BackgroundImage = Test.Properties.Resources._404logo;
button12.BackgroundImage = Test.Properties.Resources.jszr;
button13.BackgroundImage = Test.Properties.Resources.jstk;
}
}
private void button14_Click(object sender, EventArgs e)
{
panel4.Visible = false;
label24.Visible = false;
button15.Visible = false;
dataGridView2.DataSource = null;
dataGridView2.Visible = false;
}
private void StuSle()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT distinct TRIM(Course.Cno) as 课序号, TRIM(Cname) as 课程名, TRIM(Cpno) as 先修课序号,Ccredit as 学分, Trim(Tname) as 任课老师 FROM Course, Teacher, SC Where Cname not in (SELECT Cname From SC, Course where SC.ID = '"+Program.loginID+"' and Course.Cno = SC.Cno) and Teacher.ID = Course.ID; ";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.MultiSelect = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void StuDel()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT TRIM(Sname) as 姓名, TRIM(Cno) as 课程号, TRIM(Cname) as 课程名,Ccredit as 学分, Trim(Tname) as 任课老师 FROM Stuc WHERE Sname='" + Program.realName + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Stuc");
dataGridView2.DataSource = ds.Tables["Stuc"];//.DefaultView;//.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void TeaSel()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT TRIM(ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级 FROM Student";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void TeaDel()
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT Trim(Tname) as 姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='" + Program.loginID + "' ";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
//关闭数据库连接
conn.Close();
}
}
}
private void button12_Click(object sender, EventArgs e)
{
dataGridView2.DataSource = null;
dataGridView2.Visible = true;
label24.Visible = true;
button15.Visible = true;
if (Program.LoginType.Trim() == "学生")
{
studentsel = true;
studentdel = false;
teachersel = false;
teacherdel = false;
StuSle();
}
else
{
label24.Text = "请选择您要置入课程的学生：";
TeaSel();
studentsel = false;
studentdel = false;
teachersel = true;
teacherdel = false;
}
}
private void button13_Click(object sender, EventArgs e)
{
dataGridView2.DataSource = null;
dataGridView2.Visible = true;
label24.Visible = true;
button15.Visible = true;
if (Program.LoginType.Trim() == "学生")
{
studentsel = false;
studentdel = true;
teachersel = false;
teacherdel = false;
label24.Text = "请选择你要退选的课：";
StuDel();
}
else
{
label24.Text = "请选择您要移除学生的课程：";
TeaDel();
studentsel = false;
studentdel = false;
teachersel = false;
teacherdel = true;
}
}
private void button15_Click(object sender, EventArgs e)
{
if (studentsel == true)
{
int a = dataGridView2.CurrentRow.Index;
string cid = dataGridView2.Rows[a].Cells[0].Value.ToString();
DialogResult dr = MessageBox.Show("你确定要选修这门课吗？", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "insert into SC (ID,Cno,Grade) " +
"values (@uid, @uno,NULL)";
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter sqlParameter = new SqlParameter("@uid", Program.loginID.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@uno", cid);
command.Parameters.Add(sqlParameter);
//打开数据库连接
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("选课成功！");
}
catch (Exception)
{
MessageBox.Show("选课失败!");
}
}
else
return;
}
else if (studentdel == true)
{
int a = dataGridView2.CurrentRow.Index;
string cid = dataGridView2.Rows[a].Cells[1].Value.ToString();
DialogResult dr = MessageBox.Show("你确定要退选这门课吗？", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "Delete From SC Where Cno = '" + cid + "'and ID='" + Program.loginID + "'";
SqlCommand command = new SqlCommand(sql, connection);
//打开数据库连接
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("退课成功！");
}
catch (Exception)
{
MessageBox.Show("退课失败!");
}
}
else
return;
}
else if (teachersel == true && teachersel2==false)
{
int a = dataGridView2.CurrentRow.Index;
sid = dataGridView2.Rows[a].Cells[0].Value.ToString();
MessageBox.Show("选定学生成功!");
dataGridView2.DataSource = null;
label24.Text = "请选择您要置入的课程(仅能置入您教授的课)：";
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT Trim(Tname) as 教师姓名 ,Trim(Course.Cno) as 课序号 , Trim(Cname) as 课程名 ,Ccredit as 学分 ,Trim(Cpno) as 先修课序号 FROM Course, Teacher WHERE Course.ID = Teacher.ID AND Teacher.ID='" + Program.loginID + "' ";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
teachersel2 = true;
return;
}
else if (teacherdel == true && teacherdel2 == false)
{
int a = dataGridView2.CurrentRow.Index;
ccid = dataGridView2.Rows[a].Cells[1].Value.ToString();
MessageBox.Show("选定课程成功!");
dataGridView2.DataSource = null;
label24.Text = "请选择您要退课的学生(仅能去除您教授的学生)：";
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection conn = null;
try
{
conn = new SqlConnection(connStr);
conn.Open();
string sql = "SELECT TRIM(Student.ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级 FROM Student,SC Where Student.ID=SC.ID and SC.Cno='"+ccid+"'";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dataGridView2.DataSource = ds.Tables[0];
dataGridView2.ReadOnly = true;
dataGridView2.AllowUserToAddRows = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView2.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
teacherdel2 = true;
return;
}
else if(teachersel2==true&&teachersel==true)
{
int a = dataGridView2.CurrentRow.Index;
ccid = dataGridView2.Rows[a].Cells[1].Value.ToString();
DialogResult dr = MessageBox.Show("你确定要置入这门课吗？", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "insert into SC (ID,Cno,Grade) " +
"values (@uid, @uno,NULL)";
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter sqlParameter = new SqlParameter("@uid", sid);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@uno", ccid);
command.Parameters.Add(sqlParameter);
//打开数据库连接
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("置入成功！");
}
catch (Exception)
{
MessageBox.Show("置入失败!");
}
teachersel2 = false;
this.button12_Click(sender, e);
}
else
{
label24.Text = "请选择您要置入课程的学生：";
this.button12_Click(sender,e);
teachersel2 = false;
return;
}
}
else if(teacherdel2==true&&teacherdel==true)
{
int a = dataGridView2.CurrentRow.Index;
sid = dataGridView2.Rows[a].Cells[0].Value.ToString();
DialogResult dr = MessageBox.Show("你确定要删除这名学生的选课吗？", "通知", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
try
{
string connString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "Delete from SC where ID='"+sid+"' and Cno='"+ccid+"'";
SqlCommand command = new SqlCommand(sql, connection);
//打开数据库连接
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("删除成功！");
}
catch (Exception)
{
MessageBox.Show("删除失败!");
}
teacherdel2 = false;
this.button13_Click(sender, e);
}
else
{
this.button13_Click(sender, e);
label24.Text = "请选择您要删除学生的课程：";
teacherdel2 = false;
return;
}
}
else
MessageBox.Show("Debug~~");
}
private void pictureBox15_Click(object sender, EventArgs e)
{
panel5.Visible = false;
dataGridView3.DataSource = null;
}
}
}
5、Pinfo(个人信息界面)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Test
{
public partial class Pinfo : Form
{
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
protected override void WndProc(ref Message m)
{
if (m.Msg == 163 && this.ClientRectangle.Contains(this.PointToClient(new Point(m.LParam.ToInt32()))) && m.WParam.ToInt32() == 2)
m.WParam = (IntPtr)1;
base.WndProc(ref m);
if (m.Msg == 132 && m.Result.ToInt32() == 1)
m.Result = (IntPtr)2;
}
public Pinfo()
{
InitializeComponent();
}
private void Pinfo_Load(object sender, EventArgs e)
{
if (Program.LoginType.Trim() == "学生")
{
try
{
string conString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection myconnection = new SqlConnection(conString);
string sql2 = "SELECT * FROM Student WHERE ID='" + Program.loginID + "'";
SqlCommand sqlCommand = new SqlCommand(sql2, myconnection);
myconnection.Open();
SqlDataReader read1 = sqlCommand.ExecuteReader();
while (read1.Read())
{
textBox1.Text = read1["Sname"].ToString().Trim();
textBox2.Text = read1["Sage"].ToString().Trim();
comboBox1.Text = read1["Ssex"].ToString().Trim();
textBox3.Text = read1["Sdept"].ToString().Trim();
textBox4.Text = read1["Sclass"].ToString().Trim();
textBox5.Text = read1["Stel"].ToString().Trim();
}
read1.Close();
myconnection.Close();
}
catch (Exception)
{
MessageBox.Show("无法初始化学生类账户！");
}
}
else if (Program.LoginType.Trim() == "教师")
{
try
{
string cconString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection cconnection = new SqlConnection(cconString);
string sql2 = "SELECT * FROM Teacher WHERE ID='" + Program.loginID + "'";
SqlCommand sqlCCommand = new SqlCommand(sql2, cconnection);
cconnection.Open();
SqlDataReader read2 = sqlCCommand.ExecuteReader();
while (read2.Read())
{
textBox1.Text = read2["Tname"].ToString().Trim();
textBox2.Text = read2["Tage"].ToString().Trim();
comboBox1.Text = read2["Tsex"].ToString().Trim();
textBox3.Text = read2["Tdept"].ToString().Trim();
textBox4.Text = read2["Tlev"].ToString().Trim();
textBox5.Text = read2["Ttel"].ToString().Trim();
}
read2.Close();
cconnection.Close();
label6.Text = "职称：";
}
catch (Exception) { MessageBox.Show("无法查询教师类账户！"); }
}
}
private void button2_Click(object sender, EventArgs e)
{
foreach (Control control in this.Controls)
{
if (control.GetType().Name == "TextBox")
{
((TextBox)control).Text = string.Empty;
}
}
comboBox1.Text = "-请选择-";
}
private void textBox2_Leave(object sender, EventArgs e)
{
bool flag = true;
string text = textBox2.Text.Trim();
for (int i = 0; i <= text.Length-1; i++)
{
if((int)text[i]<48||(int)text[i]>58)
{
flag = false;
break;
}
}
if (textBox2.Text.Trim() != "")
{
if (flag == true)
{ }
else
{
MessageBox.Show("请输入数字！");
textBox2.Text = "";
textBox2.Focus();
}
}
else
MessageBox.Show("信息不能为空");
}
private void comboBox1_Leave(object sender,EventArgs e)
{
if (comboBox1.Text.Trim() != "男" || comboBox1.Text.Trim() != "女" || comboBox1.Text.Trim() != "其它")
MessageBox.Show("请不要输入奇怪的信息～");
}
private void textBox5_Leave(object sender, EventArgs e)
{
bool flag = true;
string text = textBox5.Text.Trim();
for (int i = 0; i <= text.Length - 1; i++)
{
if ((int)text[i] == 43 || (int)text[i] == 45)
break;
if ((int)text[i] < 48 || (int)text[i] > 58)
{
flag = false;
break;
}
}
if (textBox5.Text.Trim() != "")
{
if (flag == true)
{ }
else
{
MessageBox.Show("请输入正确的手机号码格式！");
//textBox5.Text = "";
textBox5.Focus();
}
}
else
MessageBox.Show("信息不能为空");
}
private void button1_Click(object sender, EventArgs e)
{
string username = textBox1.Text.Trim();
string age = textBox2.Text.Trim();
string sex = comboBox1.Text.Trim();
string dept = textBox3.Text.Trim();
string Lclass = textBox4.Text.Trim();
string tel = textBox5.Text.Trim();
if (username == "" || age == "" || sex == "" || dept == "" || Lclass == "" || tel == "")
{
MessageBox.Show("有信息为空，操作失败!");
return;
}
string myConnString = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
SqlConnection sqlConnection = new SqlConnection(myConnString); //实例化连接对象
sqlConnection.Open();
if (Program.LoginType.Trim() == "学生")
{
/*try
{*/
string sql = "Update Student set Sname= @usern, Sage=@usera,Ssex=@users,Sdept=@userd,Sclass=@userc,Stel=@usert Where ID='" + Program.loginID + "'";
SqlCommand command = new SqlCommand(sql, sqlConnection);
SqlParameter sqlParameter = new SqlParameter("@usern", username);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usera", age);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@users", sex);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userd", dept);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userc", Lclass);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usert", tel);
command.Parameters.Add(sqlParameter);
command.ExecuteNonQuery();
/*}
catch (Exception) { MessageBox.Show("数据更新出错"); }*/
}
else if (Program.LoginType.Trim() == "教师")
{
/*try
{*/
string sql = "Update Teacher set Tname= @usern, Tage=@usera,Tsex=@users,Tdept=@userd,Tlev=@userc,Ttel=@usert Where ID='" + Program.loginID + "'";
SqlCommand command = new SqlCommand(sql, sqlConnection);
SqlParameter sqlParameter = new SqlParameter("@usern", username);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usera", age);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@users", sex);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userd", dept);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userc", Lclass);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@usert", tel);
command.Parameters.Add(sqlParameter);
command.ExecuteNonQuery();
/*}
catch (Exception) { MessageBox.Show("数据更新出错"); }*/
}
else
{
MessageBox.Show("管理员不配拥有资料哦!");
return;
}
MessageBox.Show("更改成功!");
sqlConnection.Close();
this.Close();
}
private void pictureBox1_Click(object sender, EventArgs e)
{
this.Close();
}
private void pictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
}
}
6、Admin(管理员操作界面)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Test
{
public partial class Admin : Form
{
protected override CreateParams CreateParams
{
get
{
CreateParams cp = base.CreateParams;
cp.ExStyle |= 0x02000000;//用双缓冲绘制窗口的所有子控件
return cp;
}
}
protected override void WndProc(ref Message m)
{
if (m.Msg == 163 && this.ClientRectangle.Contains(this.PointToClient(new Point(m.LParam.ToInt32()))) && m.WParam.ToInt32() == 2)
m.WParam = (IntPtr)1;
base.WndProc(ref m);
if (m.Msg == 132 && m.Result.ToInt32() == 1)
m.Result = (IntPtr)2;
}
public Admin()
{
InitializeComponent();
}
private void Admin_Load(object sender, EventArgs e)
{
}
private void pictureBox1_Click(object sender, EventArgs e)
{
MainForm mainForm = new MainForm();
mainForm.Show();
this.Close();
}
private void pictureBox2_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
DataTable dt;
SqlConnection conn;
private void button2_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
conn = null;
conn = new SqlConnection(connStr);
conn.Open();
try
{
string sql = "SELECT TRIM(ID) as 学号, TRIM(Sname) as 姓名, TRIM(Ssex) as 性别,Sage as 年龄, Trim(Sdept) as 院系, Trim(Sclass) as 班级,Trim(Stel) as 手机号 FROM Student";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dataGridViewS.DataSource = dt.DefaultView;
dataGridViewS.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
conn.Close();
}
private void button4_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
conn = null;
conn = new SqlConnection(connStr);
conn.Open();
try
{
string sql = "SELECT TRIM(ID) as 工号, TRIM(Tname) as 姓名, TRIM(Tsex) as 性别,Tage as 年龄, Trim(Tdept) as 院系, Trim(Tlev) as 职称,Trim(Ttel) as 手机号 FROM Teacher";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dataGridViewT.DataSource = dt.DefaultView;
dataGridViewT.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
}
private void button6_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
conn = null;
conn = new SqlConnection(connStr);
conn.Open();
try
{
string sql = "SELECT TRIM(ID) as 学号, TRIM(Cno) as 课序号, Grade as 成绩 FROM SC";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dataGridViewSC.DataSource = dt.DefaultView;
dataGridViewSC.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
}
private void button8_Click(object sender, EventArgs e)
{
string connStr = "Data Source=.;Initial Catalog=SCHOOL;Persist Security Info=True;User ID=sa;Password=a123456";
conn = null;
conn = new SqlConnection(connStr);
conn.Open();
try
{
string sql = "SELECT TRIM(Cno) as 课序号, TRIM(Cname) as 课程名, TRIM(Cpno) as 先修课序号,Ccredit as 学分, Trim(ID) as 工号 FROM Course";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
dataGridViewC.DataSource = dt.DefaultView;
dataGridViewC.MultiSelect = false;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
DataTable changeDt = dt.GetChanges();
foreach (DataRow dr in changeDt.Rows)
{
string sql = string.Empty;
if (dr.RowState == System.Data.DataRowState.Added)
{
sql = "INSERT INTO Student(ID,Sname,Ssex,Sage,Sdept,Sclass,Stel)" +
"VALUES('" + dr["学号"].ToString() + "','" + dr["姓名"].ToString() + "','" + dr["性别"].ToString() + "','" + dr["年龄"].ToString() + "'," +
"'" + dr["院系"].ToString() + "','" + dr["班级"].ToString() + "','" + dr["手机号"].ToString() + "')";
}
else if (dr.RowState == System.Data.DataRowState.Deleted)
{
sql = "DELETE FROM Student WHERE Student.ID='"+dr["学号",DataRowVersion.Original].ToString()+"'";
}
else if (dr.RowState == System.Data.DataRowState.Modified)
{
sql = "UPDATE Student SET Ssex='" + dr["性别"].ToString() + "'," +
"Sage='"+Convert.ToInt32(dr["年龄"])+"',Sdept='" + dr["院系"].ToString() + "',Sclass='" + dr["班级"].ToString() + "',Stel='" + dr["手机号"].ToString() + "'" +
"WHERE ID='"+ dr["学号"].ToString() +"'";
}
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
}
catch(Exception)
{
MessageBox.Show("保存失败!");
}
}
private void button3_Click(object sender, EventArgs e)
{
try
{
DataTable changeDt = dt.GetChanges();
foreach (DataRow dr in changeDt.Rows)
{
string sql = string.Empty;
if (dr.RowState == System.Data.DataRowState.Added)
{
sql = "INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel)" +
"VALUES('" + dr["工号"].ToString() + "','" + dr["姓名"].ToString() + "','" + dr["性别"].ToString() + "','" + dr["年龄"].ToString() + "'," +
"'" + dr["院系"].ToString() + "','" + dr["职称"].ToString() + "','" + dr["手机号"].ToString() + "')";
}
else if (dr.RowState == System.Data.DataRowState.Deleted)
{
sql = "DELETE FROM Teacher WHERE Teacher.ID='" + dr["工号", DataRowVersion.Original].ToString() + "'";
}
else if (dr.RowState == System.Data.DataRowState.Modified)
{
sql = "UPDATE Teacher SET Tsex='" + dr["性别"].ToString() + "'," +
"Tage='" + Convert.ToInt32(dr["年龄"]) + "',Tdept='" + dr["院系"].ToString() + "',Tlev='" + dr["职称"].ToString() + "',Ttel='" + dr["手机号"].ToString() + "'" +
"WHERE ID='" + dr["工号"].ToString() + "'";
}
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception)
{
MessageBox.Show("保存失败!");
}
}
private void button5_Click(object sender, EventArgs e)
{
try
{
DataTable changeDt = dt.GetChanges();
foreach (DataRow dr in changeDt.Rows)
{
string sql = string.Empty;
if (dr.RowState == System.Data.DataRowState.Added)
{
sql = "INSERT INTO SC(ID,Cno,Grade)" +
"VALUES('" + dr["学号"].ToString() + "','" + dr["课序号"].ToString() + "','" + dr["成绩"].ToString() + "')";
}
else if (dr.RowState == System.Data.DataRowState.Deleted)
{
sql = "DELETE FROM SC WHERE ID='" + dr["学号", DataRowVersion.Original].ToString() + "' AND Cno='"+ dr["课序号"].ToString() + "'";
}
else if (dr.RowState == System.Data.DataRowState.Modified)
{
sql = "UPDATE SC SET Grade='" + Convert.ToInt32(dr["成绩"]) + "'" +
"WHERE ID='" + dr["学号"].ToString() + "' AND Cno='" + dr["课序号"].ToString() + "'";
}
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception)
{
MessageBox.Show("保存失败!");
}
}
private void button7_Click(object sender, EventArgs e)
{
try
{
DataTable changeDt = dt.GetChanges();
foreach (DataRow dr in changeDt.Rows)
{
string sql = string.Empty;
if (dr.RowState == System.Data.DataRowState.Added)
{
sql = "INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID)" +
"VALUES('" + dr["课序号"].ToString() + "','" + dr["课程名"].ToString() + "','" + dr["先修课序号"].ToString() + "','" + dr["工号"].ToString() + "')";
}
else if (dr.RowState == System.Data.DataRowState.Deleted)
{
sql = "DELETE FROM Course WHERE Cno='" + dr["课序号", DataRowVersion.Original].ToString() + "'";
}
else if (dr.RowState == System.Data.DataRowState.Modified)
{
sql = "UPDATE Course SET Cname='" + dr["课程名"].ToString() + "'," +
"Ccredit='" + Convert.ToInt32(dr["学分"]) + "',ID='" + dr["工号"].ToString() + "',Cpno='" + dr["先修课序号"].ToString() + "'" +
"WHERE Cno='" + dr["课序号"].ToString() + "'";
}
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception)
{
MessageBox.Show("保存失败!");
}
}
}
}
7、Program.cs(主要是一些全局变量、函数)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Test
{
static class Program
{
//静态全局变量
public static string Email163 = "你的邮箱";
public static string AuthorizationCode = "邮箱授权码";
public static string loginName;
public static string realName;
public static string loginID;
public static string LoginType;
public static string photoPath;
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new LoginForm());
}
public static string EncryptWithMD5(string source)
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
}
}
SQL语句
1、建表语句
--EDIT BY FDR_Enterprise_CSDN
DROP TABLE IF EXISTS SC
DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Course
DROP TABLE IF EXISTS Teacher
DROP TABLE IF EXISTS Account
CREATE TABLE Student
(
ID VARCHAR(30) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20),
Sclass CHAR(20),
Stel VARCHAR(15)
);
CREATE TABLE Teacher
(
ID VARCHAR(30) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
Tname CHAR(10) UNIQUE, /* Sname取唯一值*/
Tsex NCHAR(2),
Tage SMALLINT,
Tdept NCHAR(20),
Tlev NCHAR(10),
Ttel VARCHAR(15)
);
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
ID VARCHAR(30),
FOREIGN KEY (Cpno) REFERENCES Course(Cno), /* 表级完整性约束条件， Cpno是外码，被参照表是自身*/
FOREIGN KEY (ID) REFERENCES Teacher(ID)
);
CREATE TABLE SC
(
ID VARCHAR(30),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (ID,Cno), /* 主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY (ID) REFERENCES Student(ID), /* 表级完整性约束条件，Sno是外码，被参照表是Student*/
FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件，Cno是外码，被参照表是Course*/
);
CREATE TABLE Account
(
Accounts VARCHAR(30) PRIMARY KEY,
Passwords VARCHAR(50) NOT NULL,
ID VARCHAR(30) UNIQUE,
Type NCHAR(10),
Email VARCHAR(30),
Photo VARCHAR(MAX)
);
INSERT INTO Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215121','李勇','男','CS',20,'1','15800000000');
INSERT INTO Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215122','刘晨','女','CS',19,'2','15800000011');
INSERT INTO Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215123','王敏','女','MA',18,'1','15800000022');
INSERT INTO Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215125','张立','男','IS',19,'3','15800000033');
INSERT INTO Student (ID,Sname,Ssex,Sdept,Sage,Sclass,Stel) VALUES ('201215128','陈冬','男','IS',20,'2','15800000044');
SELECT * FROM Student
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('1','数据库',NULL,4,'T200001');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('2','数学',NULL,4,'T200002');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('3','信息系统',NULL,4,'T200003');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('4','操作系统',NULL,4,'T200003');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('5','数据结构',NULL,4,'T200001');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('6','数据处理',NULL,4,'T200001');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit,ID) VALUES ('7','Pascal语言',NULL,4,'T200001');
UPDATE Course SET Cpno = '5' WHERE Cno = '1'
UPDATE Course SET Cpno = '1' WHERE Cno = '3'
UPDATE Course SET Cpno = '6' WHERE Cno = '4'
UPDATE Course SET Cpno = '7' WHERE Cno = '5'
UPDATE Course SET Cpno = '6' WHERE Cno = '7'
SELECT * FROM Course
//可以根据自己的需求多插入几条选课记录
INSERT INTO SC(ID,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT INTO SC(ID,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT INTO SC(ID,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT INTO SC(ID,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT INTO SC(ID,Cno,Grade) VALUES ('201215122 ','3',80);
SELECT * FROM SC
INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200001','张明','男',32,'CS','讲师','15000000001');
INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200002','李强','男',32,'62','教授','15000000002');
INSERT INTO Teacher(ID,Tname,Tsex,Tage,Tdept,Tlev,Ttel) VALUES ('T200003','王敏','女',32,'44','副教授','15000000003');
select *
from Course
2、视图
建立学生选的课的视图
3、触发器
CREATE TABLE SysLog
(
UserID NCHAR(20) ,
DateAndTime datetime,
UserOperation NCHAR(200)
);
IF(OBJECT_ID('regist_recorder') is not null) -- 判断名为 regist_recorder 的触发器是否存在
DROP TRIGGER regist_recorder -- 删除触发器
GO
CREATE TRIGGER regist_recorder
ON Account,Student,Teacher,SC,Course
AFTER
INSERT
AS
declare @UserName nchar(20)
declare @DateTime datetime
declare @UserOperation nchar(200)
select @UserName = system_user
select @DateTime = CONVERT(datetime,GETDATE(),120)
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
select @UserOperation = @op
INSERT INTO SysLog(UserID,DateAndTime,UserOperation)
VALUES (@UserName,@DateTime,@UserOperation)
4、存储过程
DROP TABLE IF EXISTS SC_Avg;
CREATE TABLE SC_Avg
(
Cno CHAR(4),
Cname CHAR(10),
Avg_Score FLOAT
)
-----------------------------------------
INSERT INTO SC_Avg VALUES('1','数据库',0)
INSERT INTO SC_Avg VALUES('2','数学',0)
INSERT INTO SC_Avg VALUES('3','信息系统',0)
INSERT INTO SC_Avg VALUES('4','操作系统',0)
INSERT INTO SC_Avg VALUES('5','数据结构',0)
INSERT INTO SC_Avg VALUES('6','数据处理',0)
INSERT INTO SC_Avg VALUES('7','Pascal语言',0)
SELECT * FROM SC_Avg
-------------------
IF(exists(select * from sys.objects where name='Proc_TRANSFER1'))
DROP PROCEDURE Proc_TRANSFER1
GO
CREATE PROCEDURE Proc_TRANSFER1
AS
BEGIN TRANSACTION TRANS
DECLARE
@Avg1 FLOAT,
@Avg2 FLOAT,
@Avg3 FLOAT,
@Avg4 FLOAT,
@Avg5 FLOAT,
@Avg6 FLOAT,
@Avg7 FLOAT;
SELECT @Avg1=AVG(Grade)
FROM SC
WHERE Cno ='1'
UPDATE SC_Avg SET Avg_Score=@Avg1 WHERE Cno ='1'
SELECT @Avg2=AVG(Grade)
FROM SC
WHERE Cno ='2'
UPDATE SC_Avg SET Avg_Score=@Avg2 WHERE Cno ='2'
SELECT @Avg3=AVG(Grade)
FROM SC
WHERE Cno ='3'
UPDATE SC_Avg SET Avg_Score=@Avg3 WHERE Cno ='3'
SELECT @Avg4=AVG(Grade)
FROM SC
WHERE Cno ='4'
UPDATE SC_Avg SET Avg_Score=@Avg4 WHERE Cno ='4'
SELECT @Avg5=AVG(Grade)
FROM SC
WHERE Cno ='5'
UPDATE SC_Avg SET Avg_Score=@Avg5 WHERE Cno ='5'
SELECT @Avg6=AVG(Grade)
FROM SC
WHERE Cno ='6'
UPDATE SC_Avg SET Avg_Score=@Avg6 WHERE Cno ='6'
SELECT @Avg7=AVG(Grade)
FROM SC
WHERE Cno ='7'
UPDATE SC_Avg SET Avg_Score=@Avg7 WHERE Cno ='7'
COMMIT TRANSACTION TRANS
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
