一、项目框架
二、
CommandInfo.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Common;
//using MySql.Data.MySqlClient;
namespace WinStudent
{
public class CommandInfo
{
public string CommandText;//mysql或存储过程名
public DbParameter[] Parameters;//参数列表
public bool IsProc;//是否为存储过程
public CommandInfo()
{
}
public CommandInfo(string comText,bool isProc)
{
this.CommandText = comText;
this.IsProc = isProc;
}
}
}
FormLogin.cs:
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 MySql.Data.MySqlClient;
namespace WinStudent
{
public partial class FormLogin : Form
{
public FormLogin()
{
InitializeComponent();
txtUserName.Text = "admin";
txtUserPwd.Text = "1";
}
private void btnLogin_Click(object sender, EventArgs e)
{
//获取用户的输入信息
string uName = txtUserName.Text.Trim();
string uPwd = txtUserPwd.Text.Trim();
//判断是否为空
if(string .IsNullOrEmpty(uName))
{
MessageBox.Show("请输入账号!", "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUserName.Focus();//光标停留在用户名框
return;
}
if (string.IsNullOrEmpty(uPwd))
{
MessageBox.Show("请输入密码!", "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtUserPwd.Focus();//光标停留在密码框
return;
}
//与数据库通信,检查输入与数据库中是否一致
{
//写查询语句
//string mysql = "select count(1) from UserInfo where UserName='" + uName + "' and UserPwd='"+uPwd+"'";//拼接式sql注入
//推荐使用参数化MySql
//string mysql = "select count(1) from UserInfo where UserName=@Username and UserPwd=@UserPwd ";
string mysql = "select count(1) from userinfo where username=@UserName and userpwd=@UserPwd;";
//添加参数
MySqlParameter[] paras =
{
new MySqlParameter("@UserName",uName),
new MySqlParameter("@UserPwd",uPwd)
};
#region 调用数据库逻辑代码
建立与数据库的连接
连接字符串--钥匙
//string connString = "server=.;database=StudentDB;uid=root;pwd=1122;";//sqlserver身份验证 Data Source Initial Catalog User Id Password
//MySqlConnection conn = new SqlConnection(connString);
写查询语句
string mysql = "select count(1) from UserInfos where UserName='" + uName + "' and UserPwd='"+uPwd+"'";//拼接式sql注入
推荐使用参数化Sql
//string mysql = "select count(1) from UserInfos where UserName=@Username and UserPwd=@UserPwd ";
添加参数
//MySqlParameter[] para =
//{
// new SqlParameter("@UserName",uName),
// new SqlParameter("@UserPwd",uPwd)
//};
创建Command对象
//MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;//存储过程
打开连接
//conn.Open();//最晚打开,最早关闭
执行命令 要求必须在连接状态 Opened
//object o= cmd.ExecuteScalar();//执行查询返回结果集第一行第一列的值,忽略其他行或列
关闭连接
//conn.Close();
#endregion
object o = MySqlHelper.ExecuteScalar(mysql, paras);
//处理结果
if (o == null || o == DBNull.Value || Convert.ToInt32(o) == 0)
{
MessageBox.Show("登录账号或密码有错,请检查!", "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
//MessageBox.Show("登录成功!", "登录提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//转到主页面
FrmMain fMain = new FrmMain();
fMain.Show();
this.Hide();
}
}
//返回的结果进行不同的提示
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
//Application.Exit();
}
}
}
^X
FormLogin.Designer.cs:
namespace WinStudent
{
partial class FormLogin
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing">如果应释放托管资源,为 true;否则为 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要修改
/// 使用代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.txtUserName = new System.Windows.Forms.TextBox();
this.txtUserPwd = new System.Windows.Forms.TextBox();
this.btnLogin = new System.Windows.Forms.Button();
this.btnExit = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(43, 55);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(41, 12);
this.label1.TabIndex = 0;
this.label1.Text = "账号:";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(43, 120);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(41, 12);
this.label2.TabIndex = 1;
this.label2.Text = "密码:";
//
// txtUserName
//
this.txtUserName.Location = new System.Drawing.Point(104, 52);
this.txtUserName.Name = "txtUserName";
this.txtUserName.Size = new System.Drawing.Size(167, 21);
this.txtUserName.TabIndex = 2;
//
// txtUserPwd
//
this.txtUserPwd.Location = new System.Drawing.Point(104, 111);
this.txtUserPwd.Name = "txtUserPwd";
this.txtUserPwd.Size = new System.Drawing.Size(167, 21);
this.txtUserPwd.TabIndex = 3;
//
// btnLogin
//
this.btnLogin.BackColor = System.Drawing.SystemColors.ControlLightLight;
this.btnLogin.Location = new System.Drawing.Point(76, 181);
this.btnLogin.Name = "btnLogin";
this.btnLogin.Size = new System.Drawing.Size(71, 34);
this.btnLogin.TabIndex = 0;
this.btnLogin.Text = "登录";
this.btnLogin.UseVisualStyleBackColor = false;
this.btnLogin.Click += new System.EventHandler(this.btnLogin_Click);
//
// btnExit
//
this.btnExit.BackColor = System.Drawing.SystemColors.ControlLightLight;
this.btnExit.Location = new System.Drawing.Point(200, 181);
this.btnExit.Name = "btnExit";
this.btnExit.Size = new System.Drawing.Size(71, 34);
this.btnExit.TabIndex = 5;
this.btnExit.Text = "退出";
this.btnExit.UseVisualStyleBackColor = false;
this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
//
// FormLogin
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(334, 254);
this.Controls.Add(this.btnExit);
this.Controls.Add(this.btnLogin);
this.Controls.Add(this.txtUserPwd);
this.Controls.Add(this.txtUserName);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;
this.MaximizeBox = false;
this.Name = "FormLogin";
this.Text = "登录页面";
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox txtUserName;
private System.Windows.Forms.TextBox txtUserPwd;
private System.Windows.Forms.Button btnLogin;
private System.Windows.Forms.Button btnExit;
}
}
FrmAddClass.cs
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 MySql.Data.MySqlClient;
namespace WinStudent
{
public partial class FrmAddClass : Form
{
public FrmAddClass()
{
InitializeComponent();
}
/// <summary>
/// 初始化年级列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FrmAddClass_Load(object sender, EventArgs e)
{
InitGradeList();
}
//一个班级必然属于某个年级
private void InitGradeList()
{
string mysql = "select GradeId,GradeName from GradeInfo;";
DataTable dtGradeList = MySqlHelper.GetDataTable(mysql);
cboGrades.DataSource = dtGradeList;
//年级名称-----项
cboGrades.DisplayMember = "GradeName";//显示的内容
cboGrades.ValueMember = "GradeId";//值
cboGrades.SelectedIndex = 0;
}
/// <summary>
/// 添加班级
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e)
{
//信息获取
string className = txtClassName.Text.Trim();
int gradeId = (int)cboGrades.SelectedValue;
string remark = txtRemark.Text.Trim();
//判定是否为空
if(string.IsNullOrEmpty(className))
{
MessageBox.Show("班级名称不能为空!", "添加班级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//判定是否已存在 数据库里去检查----------与数据库进行交互
{
//----------------------------------查重,查询数据库的表中已存入了几份相同数据-----------------------------------------------------------------------------------------
string mysqlExists = "select count(1) from ClassInfo where ClassName=@ClassName and GradeId=@GradeId";
MySqlParameter[] paras =
{
new MySqlParameter("@ClassName",className),
new MySqlParameter("@GradeId",gradeId)
};
object oCount = MySqlHelper.ExecuteScalar(mysqlExists, paras);
//----------------------------------查重,查询数据库的表中已存入了几份相同数据-----------------------------------------------------------------------------------------
if (oCount==null || oCount==DBNull.Value ||((Convert.ToInt32(oCount)))==0)
{
//添加操作
//Bug2:由于ClassId在数据库中被定义为主键非空,导致执行时ClassID 没有定义为默认值,而在界面上给ClassId添加值容易出现乱序且无意义
string mysqlAdd = "insert into ClassInfo(ClassName,GradeId,Remark) values (@ClassName,@GradeId,@Remark)";
//fixBug2:这里将mysql中的ClassId修改为自增auto_increment,ClassID自动按序增加添加序号
MySqlParameter[] parasAdd =
{
new MySqlParameter("@ClassName",className),
new MySqlParameter ("@GradeId",gradeId),
new MySqlParameter ("@Remark",remark)
};
//--------------------------------添加操作执行后查询数据库的表中有多少行数据被受到影响发生了改变---------------------------------------------------------------------------------
//执行并返回值
int count = MySqlHelper.ExecuteNonQuery(mysqlAdd, parasAdd);
if(count>0)
{
MessageBox.Show($"班级:{className}添加成功!", "添加班级提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("班级名称已存在!", "添加班级提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//--------------------------------添加操作执行后查询数据库的表中有多少行数据被受到影响发生了改变---------------------------------------------------------------------------------
}
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
FrmAddClass.Designer.cs
namespace WinStudent
{
partial class FrmAddClass
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.panel1 = new System.Windows.Forms.Panel();
this.btnClose = new System.Windows.Forms.Button();
this.btnAdd = new System.Windows.Forms.Button();
this.txtRemark = new System.Windows.Forms.TextBox();
this.cboGrades = new System.Windows.Forms.ComboBox();
this.txtClassName = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.panel1.SuspendLayout();
this.SuspendLayout();
//
// panel1
//
this.panel1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.panel1.Controls.Add(this.btnClose);
this.panel1.Controls.Add(this.btnAdd);
this.panel1.Controls.Add(this.txtRemark);
this.panel1.Controls.Add(this.cboGrades);
this.panel1.Controls.Add(this.txtClassName);
this.panel1.Controls.Add(this.label3);
this.panel1.Controls.Add(this.label2);
this.panel1.Controls.Add(this.label1);
this.panel1.Location = new System.Drawing.Point(30, 32);
this.panel1.Name = "panel1";
this.panel1.Size = new System.Drawing.Size(334, 325);
this.panel1.TabIndex = 0;
//
// btnClose
//
this.btnClose.BackColor = System.Drawing.SystemColors.ControlLightLight;
this.btnClose.Location = new System.Drawing.Point(199, 282);
this.btnClose.Name = "btnClose";
this.btnClose.Size = new System.Drawing.Size(93, 23);
this.btnClose.TabIndex = 7;
this.btnClose.Text = "关闭";
this.btnClose.UseVisualStyleBackColor = false;
this.btnClose.Click += new System.EventHandler(this.btnClose_Click);
//
// btnAdd
//
this.btnAdd.BackColor = System.Drawing.SystemColors.ControlLightLight;
this.btnAdd.Location = new System.Drawing.Point(61, 282);
this.btnAdd.Name = "btnAdd";
this.btnAdd.Size = new System.Drawing.Size(93, 23);
this.btnAdd.TabIndex = 6;
this.btnAdd.Text = "添加";
this.btnAdd.UseVisualStyleBackColor = false;
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
//
// txtRemark
//
this.txtRemark.Location = new System.Drawing.Point(60, 118);
this.txtRemark.Multiline = true;
this.txtRemark.Name = "txtRemark";
this.txtRemark.Size = new System.Drawing.Size(271, 136);
this.txtRemark.TabIndex = 5;
//
// cboGrades
//
this.cboGrades.FormattingEnabled = true;
this.cboGrades.Location = new System.Drawing.Point(61, 70);
this.cboGrades.Name = "cboGrades";
this.cboGrades.Size = new System.Drawing.Size(186, 20);
this.cboGrades.TabIndex = 4;
//
// txtClassName
//
this.txtClassName.Location = new System.Drawing.Point(61, 29);
this.txtClassName.Name = "txtClassName";
this.txtClassName.Size = new System.Drawing.Size(271, 21);
this.txtClassName.TabIndex = 3;
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(4, 121);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(41, 12);
this.label3.TabIndex = 2;
this.label3.Text = "描述:";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(3, 73);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(41, 12);
this.label2.TabIndex = 1;
this.label2.Text = "年级:";
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(4, 32);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(41, 12);
this.label1.TabIndex = 0;
this.label1.Text = "班名:";
//
// FrmAddClass
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(393, 382);
this.Controls.Add(this.panel1);
this.Name = "FrmAddClass";
this.Text = "班级信息页面";
this.Load += new System.EventHandler(this.FrmAddClass_Load);
this.panel1.ResumeLayout(false);
this.panel1.PerformLayout();
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.Panel panel1;
private System.Windows.Forms.Button btnClose;
private System.Windows.Forms.Button btnAdd;
private System.Windows.Forms.TextBox txtRemark;
private System.Windows.Forms.ComboBox cboGrades;
private System.Windows.Forms.TextBox txtClassName;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label1;
}
}
FrmAddStudent.cs:
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 MySql.Data.MySqlClient;
namespace WinStudent
{
public partial class FrmAddStudent : Form
{
public FrmAddStudent()
{
InitializeComponent();
}
/// <summary>
/// 加载班级列表\性别默认选择男
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FrmAddStudent_Load(object sender, EventArgs e)
{
InitClasses();//加载班级列表
rbtMale.Checked = true;
}
private void InitClasses()
{
//获取数据 ----查询 ---写sql
//string mysql = "select ClassId,ClassName,GradeName from ClassInfo c,GradeInfo g where c.GradeId=g.GradeId";
string mysql = "select ClassId,ClassName,GradeName from ClassInfo c inner join GradeInfo g on c.GradeId=g.GradeId";
DataTable dtClasses = MySqlHelper.GetDataTable(mysql);
//组合班级列表显示项
if (dtClasses.Rows.Count > 0)
{
foreach (DataRow dr in dtClasses.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className + "--" + gradeName;
}
}
//指定数据源
cboClasses.DataSource = dtClasses;
cboClasses.DisplayMember = "ClassName";
cboClasses.ValueMember = "ClassId";
cboClasses.SelectedIndex = 0;
}
private void btnAdd_Click(object sender, EventArgs e)
{
//Featture:
//(1)获取页面信息输入
string stuName = txtStuName.Text.Trim();
int classId = Convert.ToInt32(cboClasses.SelectedValue);
string sex = rbtMale.Checked ? rbtMale.Text : rbtFemale.Text.Trim();
string phone = txtPhone.Text.Trim();
//(2)判空处理 姓名不可以为空 电话不可以为空
if (string.IsNullOrEmpty(stuName))
{
MessageBox.Show("学生姓名不能为空!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrEmpty(phone))
{
MessageBox.Show("学生姓名不能为空!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//(3)判断 姓名+电话 是否在数据库里已存在 姓名+电话
string mysql = "select count(1) from StudentInfo where StuName=@StuName and Phone=@Phone";
MySqlParameter[] paras =
{
new MySqlParameter("@StuName",stuName),
new MySqlParameter("@Phone",phone)
};
object o = MySqlHelper.ExecuteScalar(mysql, paras);
if(o!=null && o!=DBNull.Value && (Convert.ToInt32(o)) > 0)
{
MessageBox.Show("该学生已存在!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//(4)添加入库 sql 参数 执行 完成返回受影响的行数
string mysqlAdd = "insert into StudentInfo" +
" (StuName,ClassId,Sex,Phone) values" +
" (@StuName,@ClassId,@Sex,@Phone)";
MySqlParameter[] parasAdd =
{
new MySqlParameter ("@StuName",stuName),
new MySqlParameter ("@ClassId",classId),
new MySqlParameter ("@Sex",sex),
new MySqlParameter ("@Phone",phone)
};
int count = MySqlHelper.ExecuteNonQuery(mysqlAdd, parasAdd);
if (count>0)
{
MessageBox.Show($"学生:{stuName}添加成功!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
MessageBox.Show("该学生添加失败,请检查!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//Featture/
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
FrmAddStudent.Designer.cs:
namespace WinStudent
{
partial class FrmAddStudent
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.txtStuName = new System.Windows.Forms.TextBox();
this.txtPhone = new System.Windows.Forms.TextBox();
this.rbtMale = new System.Windows.Forms.RadioButton();
this.rbtFemale = new System.Windows.Forms.RadioButton();
this.cboClasses = new System.Windows.Forms.ComboBox();
this.btnAdd = new System.Windows.Forms.Button();
this.btnClose = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(54, 45);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(41, 12);
this.label1.TabIndex = 0;
this.label1.Text = "姓名:";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(55, 119);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(41, 12);
this.label2.TabIndex = 1;
this.label2.Text = "班级:";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(53, 185);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(41, 12);
this.label3.TabIndex = 2;
this.label3.Text = "性别:";
//
// label4
//
this.label4.AutoSize = true;
this.label4.Location = new System.Drawing.Point(54, 247);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(41, 12);
this.label4.TabIndex = 3;
this.label4.Text = "电话:";
//
// txtStuName
//
this.txtStuName.Location = new System.Drawing.Point(100, 45);
this.txtStuName.Name = "txtStuName";
this.txtStuName.Size = new System.Drawing.Size(193, 21);
this.txtStuName.TabIndex = 7;
//
// txtPhone
//
this.txtPhone.Location = new System.Drawing.Point(102, 244);
this.txtPhone.Name = "txtPhone";
this.txtPhone.Size = new System.Drawing.Size(191, 21);