using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using School.DAL;
namespace School.DAL
{
public class GradeDAL
{
//写一个根据年级名称获取年级编号的方法
public int GetName(string gradename)
{
string str = SqlHelper.constr;
SqlConnection con=new SqlConnection(str);
string sql = "select gradeid from grade where gradename='" + gradename + "'";
SqlCommand com=new SqlCommand(sql,con);
con.Open();
int gradeid = Convert.ToInt32(com.ExecuteScalar());
con.Close();
return gradeid;
}
//获取所有年级信息
public DataTable FindALLGrade()
{
string str = SqlHelper.constr;
SqlConnection con = new SqlConnection(str);
string sql = "select gradeid,gradename from grade";
DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(sql,con);
da.Fill(ds, "GradeInfo");
return ds.Tables["GradeInfo"];
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace School.DAL
{
public static class SqlHelper
{
//用静态的方法调用的时候不用创建SQLHelper的实例
//Execetenonquery
// public static string Constr = "server=HAPPYPIG\\SQLMODEL;database=shooltest;uid=sa;pwd=6375196;";
public static string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
public static int id;
/// <summary>
/// 执行NonQuery命令
/// </summary>
/// <param name="cmdTxt"></param>
/// <param name="parames"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteNonQuery(cmdTxt, CommandType.Text, parames);
}
//可以使用存储过程的ExecuteNonquery
public static int ExecuteNonQuery(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
{
//判断脚本是否为空 ,直接返回0
if (string.IsNullOrEmpty(cmdTxt))
{
return 0;
}
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
if (parames != null)
{
cmd.CommandType = cmdtype;
cmd.Parameters.AddRange(parames);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static SqlDataReader ExecuteDataReader(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteDataReader(cmdTxt, CommandType.Text, parames);
}
//SQLDataReader存储过程方法
public static SqlDataReader ExecuteDataReader(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return null;
}
SqlConnection con = new SqlConnection(constr);
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType = cmdtype;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
//把reader的行为加进来。当reader释放资源的时候,con也被一块关闭
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parames)
{
return ExecuteDataTable(sql, CommandType.Text, parames);
}
//调用存储过程的类,关于(ExecuteDataTable)
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] parames)
{
if (string.IsNullOrEmpty(sql))
{
return null;
}
DataTable dt = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter(sql, constr))
{
da.SelectCommand.CommandType = cmdType;
if (parames != null)
{
da.SelectCommand.Parameters.AddRange(parames);
}
da.Fill(dt);
return dt;
}
}
/// <summary>
/// ExecuteScalar
/// </summary>
/// <param name="cmdTxt">第一个参数,SQLServer语句</param>
/// <param name="parames">第二个参数,传递0个或者多个参数</param>
/// <returns></returns>
public static object ExecuteScalar(string cmdTxt, params SqlParameter[] parames)
{
return ExecuteScalar(cmdTxt, CommandType.Text, parames);
}
//可使用存储过程的ExecuteScalar
public static object ExecuteScalar(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return null;
}
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType = cmdtype;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
//调用存储过程的DBHelper类(关于ExeceutScalar,包含事务,只能处理Int类型,返回错误号)
public static object ExecuteScalar(string cmdTxt, CommandType cmdtype,SqlTransaction sqltran, params SqlParameter[] parames)
{
if (string.IsNullOrEmpty(cmdTxt))
{
return 0;
}
using (SqlConnection con = new SqlConnection(constr))
{
int sum = 0;
using (SqlCommand cmd = new SqlCommand(cmdTxt, con))
{
cmd.CommandType=cmdtype;
if (parames != null)
{
cmd.Parameters.AddRange(parames);
}
con.Open();
sqltran = con.BeginTransaction();
try
{
cmd.Transaction = sqltran;
sum=Convert.ToInt32( cmd.ExecuteScalar());
sqltran.Commit();
}
catch (SqlException ex)
{
sqltran.Rollback();
}
return sum;
}
}
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using School.DAL;
namespace SchoolDAL
{
public class StudentDAL
{ DataSet ds = new DataSet();
//显示学生列表
public DataTable GetALLStudent()
{
string str = SqlHelper.constr;
SqlConnection con = new SqlConnection(str);
string sql = "select * from Student";
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(ds, "info");
return ds.Tables["info"];
}
//public DataTable GetALLStudent(string name)
//{
// string str = SqlHelper.constr;
// SqlConnection con = new SqlConnection(str);
// string sql = "select * from Student where studentname=@name";
// SqlParameter pare=new SqlParameter("@name",name);
// SqlCommand cmd=new SqlCommand(sql,con);
// cmd.Parameters.Add(pare);
// SqlDataAdapter da = new SqlDataAdapter();
// da.SelectCommand = cmd;
// DataSet ds=new DataSet();
// da.Fill(ds, "info");
// return ds.Tables["info"];
//}
public DataTable Show()
{
string sql = SqlHelper.constr;
SqlConnection con = new SqlConnection(sql);
string str = "select StudentNo, LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email from student";
SqlDataAdapter Adapter = new SqlDataAdapter(str, con);
DataSet da = new DataSet();
Adapter.Fill(da, "pp");
return da.Tables["pp"];
}
//将信学生信息显到文本上
//public void sulinlin()
//{
// string str = SqlHelper.constr;
// SqlConnection con = new SqlConnection(str);
// string sql = "select * from student where StudentNo='" + t + "'";
// con.Open();
// string sqlName = string.Format("select * from [Student],[Grade] where [Student].[GradeId]=[Grade].[GradeId] and [studentNo]='{0}'", t);
// SqlCommand comm = new SqlCommand(sqlName.ToString(), s.Connection);
// SqlDataReader reader = comm.ExecuteReader();
// while (reader.Read())
// {
// this.txt.Text = reader["StudentNo"].ToString();
// this.textBox2.Text = reader["LoginPwd"].ToString();
// this.textBox3.Text = reader["StudentName"].ToString();
// this.textBox4.Text = reader["Phone"].ToString();
// this.textBox5.Text = reader["GradeId"].ToString();
// this.textBox6.Text = reader["Address"].ToString();
// this.textBox7.Text = reader["Email"].ToString();
// this.dateTimePicker1.Text = reader["BornDate"].ToString();
// //this.comboBox1.Text = reader["GreadeName"].ToString();
// if (reader["Sex"].ToString() == "男")
// {
// this.radioButton1.Checked = true;
// }
// else
// {
// this.radioButton2.Checked = true;
// }
// MessageBox.Show(sql);
// }
// reader.Close();
//}
//修改学生信息
public bool xiugai(string studentno,string LoginPwd, string StudentName, string Gender, int GradeId, string Phone, string Address, DateTime Birthday, string Email)
{
bool flag = false;
string str = SqlHelper.constr;
SqlConnection con = new SqlConnection(str);
//StringBuilder sql = new StringBuilder();
//sql.AppendFormat("UPDATE [Student] SET ");
//sql.AppendFormat(" [LoginPwd]='{0}'", LoginPwd);
//sql.AppendFormat(" , [StudentName]='{0}'", StudentName);
//sql.AppendFormat(" ,[Gender]='{0}'", Gender);
//sql.AppendFormat(" ,[GradeId]='{0}'", GradeId);
//sql.AppendFormat(" , [Phone]='{0}'", Phone);
//sql.AppendFormat(" ,[Address]='{0}'", Address);
//sql.AppendFormat(" ,[Birthday]='{0}'", Birthday);
//sql.AppendFormat(" ,[Email]='{0}'", Email);
// sql.AppendFormat(" where [StudentNo]='{0}'", username);
string sql = "update student set loginpwd='" + LoginPwd + "',studentname='" + StudentName + "',Gender='" +
Gender +
"',GradeId='" + GradeId + "',Phone='" + Phone + "',Address='" + Address + "',Birthday='" +
Birthday + "',Email='" + Email +
"' where studentno='" +studentno + "'";
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
int count = cmd.ExecuteNonQuery();
// con.Close();
if (count > 0)
{
flag = true;
}
return flag;
}
//修改学生密码
public static bool updatepwd(string Name,string Pwd)
{
bool flag = false;
string str = SqlHelper.constr;
SqlConnection conn=new SqlConnection(str);
string sql = "update student set LoginPwd=" + Pwd + " where Studentno=" + Name;
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count>0)
{
flag = true;
}
conn.Close();
return flag;
}
//添加学生
public static bool Add(string LoginPwd,string StudentName,string Gender,int GradeId,string Phone,string Address,DateTime Birthday,string Email)
{
bool flag = false;
string str = SqlHelper.constr;
SqlConnection con=new SqlConnection(str);
string sql = @"INSERT INTO [Student]
([LoginPwd]
,[StudentName]
,[Gender]
,[GradeId]
,[Phone]
,[Address]
,[Birthday]
,[Email])
VALUES
('" + LoginPwd + "','" + StudentName + "','" + Gender + "','" +GradeId+ "','" + Phone + "','" + Address + "','" + DateTime.Now.ToShortDateString() + "','" + Email + "')";
SqlCommand cmd=new SqlCommand(sql,con);
con.Open();
int count = cmd.ExecuteNonQuery();
con.Close();
if (count > 0)
{
flag = true;
}
return flag;
}
//年级查询
// public DataTable SelectStudentGradeidAgo(string Gradename)
//{
// string con = SqlHelper.constr;
// string sql = "select * from Student where Gradeid in (select Gradeid from Grade where GradeName='" + Gradename + "')";
// SqlConnection conn = new SqlConnection(con);
// SqlDataAdapter da = new SqlDataAdapter(sql, conn);
// DataSet ds = new DataSet();
// da.Fill(ds, "Gradeinfo");
// return ds.Tables["Gradeinfo"];
//}
public DataTable SelectStudentGradeid(string Gradename)
{
string sql = "select * from Student where Gradeid in (select Gradeid from Grade where GradeName='" + Gradename + "')";
return SqlHelper.ExecuteDataTable(sql);
}
//查询姓名的
public static DataTable SelectStudentName(string name)
{
string con = SqlHelper.constr;
SqlConnection conn = new SqlConnection(con);
string sql = "select * from Student where StudentName='" + name + "'";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Gradeinfo");
return ds.Tables["Gradeinfo"];
}
//删除学生信息
public static bool Delect(string studentNo)
{
bool f = false;
string con = SqlHelper.constr;
SqlConnection conn=new SqlConnection(con);
string sql = "DELETE FROM Student WHERE [StudentNo]='" + studentNo+"'";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
int count = cmd.ExecuteNonQuery();
// conn.Close();
if (count > 0)
{
f= true;
}
return f;
}
//登录
public bool LS(string username, string pwd)
{
bool f = false;
string str = SqlHelper.constr;
SqlConnection con = new SqlConnection(str);
string sql = "select count(1) from Student where StudentNo=" + username + " and LoginPwd='" + pwd + "'";
SqlCommand com = new SqlCommand(sql, con);
try
{
con.Open();
int count = Convert.ToInt32(com.ExecuteScalar());
if (count > 0)
{
f = true;
}
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
return f;
}
}
}
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 School.DAL;
using SchoolDAL;
namespace 登录上机3
{
public partial class Add : Form
{
public Add()
{
InitializeComponent();
}
private void btnEdit_Click(object sender, EventArgs e)
{//密码
string pwd = txtPwd.Text;
//姓名
string uname = txtName.Text;
//确认密码
string pwd1 = txtRePwd.Text;
//性别
string gender = rbtnMale.Checked ?"1" :"0";
//年级
string gradename = cboGrade.Text;
int gradeid = gradeDAL.GetName(gradename);
//电话
string phone = txtPhone.Text;
//邮箱
string email = txtEmail.Text;
//地址
string address = txtAddress.Text;
//出生日期
DateTime birthday = dpBirthday.Value;
bool flag = StudentDAL.Add(pwd, uname,
gender, gradeid, phone, address ,birthday,email);
if (flag)
{
MessageBox.Show("添加成功!!你很棒");
}
else
{
MessageBox.Show("c错了 改改吧");
}
}
GradeDAL gradeDAL=new GradeDAL();
StudentDAL studentDAL=new StudentDAL();
public string can;
public string canName;
public string canpwd;
// public string canpwd1;
public string cangrade;
public string canphone;
public string canemail;
public string canBirthday;
public string canaddress;
private void Add_Load(object sender, EventArgs e)
{
this.txtStudentNo.Text = can;
this.txtName.Text = canName;
this.txtPwd.Text = canpwd;
// this.txtRePwd.Text = canpwd1;
this.txtAddress.Text = canaddress;
this.txtEmail.Text = canemail;
this.txtPhone.Text = canphone;
this.dpBirthday.Text = canBirthday;
DataTable dt= gradeDAL.FindALLGrade();
cboGrade.ValueMember = "gradeid";
cboGrade.DisplayMember = "gradename";
cboGrade.DataSource = dt;
}
private void button1_Click(object sender, EventArgs e)
{
//密码
string pwd = txtPwd.Text;
//姓名
string uname = txtName.Text;
//确认密码
string pwd1 = txtRePwd.Text;
//性别
string gender = rbtnMale.Checked ? "1" : "0";
//年级
string gradename = cboGrade.Text;
int gradeid = gradeDAL.GetName(gradename);
//电话
string phone = txtPhone.Text;
//邮箱
string email = txtEmail.Text;
//地址
string address = txtAddress.Text;
//出生日期
DateTime birthday = dpBirthday.Value;
string studentno = txtStudentNo.Text;
int gradeids = gradeDAL.GetName(gradename);
bool flag = studentDAL.xiugai(studentno, pwd, uname,
gender, gradeid, phone, address, birthday, email);
if (flag)
{
MessageBox.Show("修改成功!!你很棒");
}
else
{
MessageBox.Show("c错了 改改吧");
}
}
private void btnClear_Click(object sender, EventArgs e)
{
this.txtAddress.Text = string.Empty;
this.txtEmail.Text = string.Empty;
this.txtName.Text = string.Empty;
this.txtPhone.Text = string.Empty;
this.txtPwd.Text = string.Empty;
this.txtRePwd.Text = string.Empty;
//if (this.studentNo != -1)
//{
// this.txtStudentNo.Text = string.Empty;
//}
}
}
}
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 School.DAL;
using SchoolDAL;
namespace 登录上机3
{
public partial class CXgrade : Form
{
public CXgrade()
{
InitializeComponent();
}
GradeDAL gd = new GradeDAL();
private void CXgrade_Load(object sender, EventArgs e)
{
//StudentDAL stu = new StudentDAL();
//DataTable dt = stu.GetALLStudent();
//dataGridView1.DataSource = dt;
string grandename = comboBox1.Text;
DataTable dt = gd.FindALLGrade();
comboBox1.DataSource = dt;
comboBox1.ValueMember = "Gradeid";
comboBox1.DisplayMember = "GradeName";
}
private void button2_Click(object sender, EventArgs e)
{
StudentDAL ds = new StudentDAL();
string name = comboBox1.Text;
if (comboBox1.Text != "")
{
DataTable da = ds.SelectStudentGradeid(name);
dataGridView1.DataSource = da;
}
else
{
MessageBox.Show("请输入正确的年级");
}
}
}
}
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 SchoolDAL;
namespace 登录上机3
{
public partial class CXname : Form
{
public CXname()
{
InitializeComponent();
}
StudentDAL stu=new StudentDAL();
private void CXname_Load(object sender, EventArgs e)
{
//StudentDAL stu = new StudentDAL();
//DataTable dt = stu.GetALLStudent();
//dataGridView1.DataSource = dt;
string grandename = comboBox1.Text;
DataTable dt = stu.SelectStudentName(Name);
comboBox1.DataSource = dt;
comboBox1.ValueMember = "Gradeid";
comboBox1.DisplayMember = "GradeName";
}
private void button2_Click(object sender, EventArgs e)
{
//StudentDAL ds = new StudentDAL();
string name = comboBox1.Text;
//if (comboBox1.Text != "")
//{
DataTable da = StudentDAL.SelectStudentName(name);
dataGridView1.DataSource = da;
// }
//else
//{
// MessageBox.Show("请输入正确的姓名");
//}
}
}
}
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 SchoolDAL;
namespace 登录上机3
{
public partial class DengLu : Form
{
public DengLu()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
StudentDAL da=new StudentDAL();
string username = textBox1.Text;
string pwd = textBox2.Text;
bool f = da.LS(username, pwd);
if (f)
{
//MessageBox.Show("登录成功!!!");
FrmMain a=new FrmMain();
a.s = textBox1.Text;
a.Show();
// a.Hide();
}
else
{
MessageBox.Show("登录失败!!!");
}
}
}
}
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;
namespace 登录上机3
{
public partial class FrmMain : Form
{
public FrmMain()
{
InitializeComponent();
}
public string s;
private void FrmMain_Load(object sender, EventArgs e)
{
toolStripStatusLabel2.Text = "工作日期:" + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString();
toolStripStatusLabel1.Text = "嗨,我的小花们!" + s;
}
private void button1_Click(object sender, EventArgs e)
{
//add.Hide();
}
private void FrmMain_FormClosing(object sender, FormClosingEventArgs e)
{
Application.Exit();
}
private void tsbtnNewStudent_Click(object sender, EventArgs e)
{
Add add=new Add();
add.Show();
}
private void tsbtnStudentList_Click(object sender, EventArgs e)
{
liebiao lb=new liebiao();
lb.Show();
}
private void tsbtnStudentByGrade_Click(object sender, EventArgs e)
{
CXgrade lb = new CXgrade();
lb.Show();
}
private void tsbtnStudentByName_Click(object sender, EventArgs e)
{
CXname lb = new CXname();
lb.Show();
}
private void tsmiExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void tsmiModifyPwd_Click(object sender, EventArgs e)
{
updatepwd upd=new updatepwd();
upd.Show();
}
private void toolStripSeparator1_Click(object sender, EventArgs e)
{
}
private void 查询成绩信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
StudentResult sr=new StudentResult();
sr.Show();
}
}
}
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 SchoolDAL;
namespace 登录上机3
{
public partial class liebiao : Form
{
public liebiao()
{
InitializeComponent();
}
StudentDAL stu = new StudentDAL();
private void liebiao_Load(object sender, EventArgs e)
{
DataTable dataTable = stu.Show();
dataGridView1.DataSource = dataTable;
DataTable dt = stu.GetALLStudent();
dataGridView1.DataSource = dt;
}
Add f=new Add();
private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
{
//
//l.Show();
f.can = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
f.canpwd = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
f.canName = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
f.canphone = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
f.cangrade = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
f.canaddress = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();
f.canBirthday= dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
f.canemail = dataGridView1.SelectedRows[0].Cells[8].Value.ToString();
MessageBox.Show(f.can);
MessageBox.Show(f.canName);
f.Show();
}
private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
{
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void 删除ToolStripMenuItem_Click_1(object sender, EventArgs e)
{ string studentNo= dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
bool delete = StudentDAL.Delect(studentNo);
if (delete)
{
MessageBox.Show("删除成功");
}
}
}
}
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 SchoolDAL;
namespace 登录上机3
{
public partial class updatepwd : Form
{
public updatepwd()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string name=textBox2.Text;
string pwd = textBox1.Text;
bool flag = StudentDAL.updatepwd(name, pwd);
if (flag)
{
MessageBox.Show("c错了 改改吧");
}
else
{
MessageBox.Show("修改密码成功!!你很棒");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}