分层式学生管理系统

来源：互联网发布：德国关税的算法编辑：程序博客网时间：2024/05/18 14:11

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySchool.DAL;
namespace MySchool.BLL
{
public class GradeBLL
{
GradeDAL grade=new GradeDAL();
//根据年纪名称获取年纪编号
public int GetIdByName(string gradename)
{
return grade.GetIdByName(gradename);
}
//获取所有年纪信息
public DataTable FindAllGrade()
{
return grade.FindAllGrade();
}
//显示所有学生信息
public DataTable chuxun()
{
return grade.chuxun();
}
//年级查询
public DataTable Chaxun1(string name)
{
return grade.Chaxun1(name);
}
//姓名查询
public DataTable chaxun2(string name)
{
return grade.chaxun2(name);
}
}
}
///////////MySchool.BLL.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySchool.DAL;
using MySchool.Model;
namespace MySchool.BLL
{
public class StudentBLL
{
private StudentDAL dal = new StudentDAL();
public bool IsLongin(string username, string pwd)
{
//登陆
return dal.IsLongin(username, pwd);
}
//添加学生
public bool AddStudent(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone,
string Address, DateTime Birthday, string Email)
{
return dal.AddStudent(LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email);
}
//删除
public int shanchu(string shu)
{
return dal.shanchu(shu);
}
//查询
public DataTable chuxnun3(string name)
{
return dal.chuxnun3(name);
}
//修改密码
public bool xiugaimima(Student stu)
{
return dal.xiugaimima(stu);
}
//带参
public DataTable get(string name, int gid, out int i)
{
return dal.get(name,gid, out i);
}
}
}
////////// MySchool.DAL.cs
using SIM.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MySchool.DAL
{
public class GradeDAL
{
//根据年纪名称获取年纪编号
public int GetIdByNameAgo(string gradename)
{
string str = SqlHelper.Constr;
SqlConnection con = new SqlConnection(str);
string sql = "select gradeid from grade where gradename='" + gradename + "'";
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
int gradeid = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
return gradeid;
}
public int GetIdByName(string gradename)
{
string sql = "select gradeid from grade where gradename='" + gradename + "'";
int gradeid = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
return gradeid;
}
//获取所有年纪信息
public DataTable FindAllGradeAgo()
{
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"];
}
public DataTable FindAllGrade()
{
string sql = "select gradeid,gradename from grade";
return SqlHelper.ExecuteDataTable(sql);
}
//显示所有学生信息
public DataTable chuxun()
{
string str = SqlHelper.Constr;
SqlConnection con = new SqlConnection(str);
string sql = @"select [StudentNo],[LoginPwd],[StudentName],[Gender],Grade.[GradeId],[GradeName],phone
from student,grade
where student.GradeId=Grade.GradeId";
SqlDataAdapter sa = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sa.Fill(ds, "info");
return ds.Tables["info"];
}
public DataTable chuxun1()
{
string sql = @"select [StudentNo],[LoginPwd],[StudentName],[Gender],Grade.[GradeId],[GradeName],phone
from student,grade
where student.GradeId=Grade.GradeId";
return SqlHelper.ExecuteDataTable(sql);
}
//年级查询
public DataTable Chaxun1Ago(string name)
{
string str = SqlHelper.Constr;
SqlConnection con = new SqlConnection(str);
con.Open();
string sql = "select studentno,studentname,gender,birthday from student,grade where student.gradeid=grade.gradeid and grade.gradeid in (select gradeid from grade where gradename='" + name + "')";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(ds, "Studentlist");
return ds.Tables["Studentlist"];
}
public DataTable Chaxun1(string name)
{
string sql = "select studentno,studentname,gender,birthday from student,grade where student.gradeid=grade.gradeid and grade.gradeid in (select gradeid from grade where gradename='" + name + "')";
return SqlHelper.ExecuteDataTable(sql);
}
//姓名查询
public DataTable chaxun2(string name)
{
string str = SqlHelper.Constr;
SqlConnection con=new SqlConnection(str);
con.Open();
string sql = @"select * from Student,grade where student.gradeid=grade.gradeid
and studentName like'%" + name + "%'";
DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter(sql,con);
da.Fill(ds, "info");
return ds.Tables["info"];
}
public DataTable chaxun2Ago(string name)
{
string sql = @"select * from Student,grade where student.gradeid=grade.gradeid
and studentName like'%" + name + "%'";
return SqlHelper.ExecuteDataTable(sql);
}
}
}
//////////////////// SIM.DAL .cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace SIM.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;
}
}
}
}
}
///////////////// StudentDAL .cs
using SIM.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySchool.Model;
namespace MySchool.DAL
{
//数据访问层
public class StudentDAL
{
//添加学生
public bool AddStudentAgo(string LoginPwd,string StudentName,string Gender,int GradeId, string Phone, string Address, DateTime Birthday, string Email )
{
bool flage = false;
string str = SqlHelper.Constr;
SqlConnection con = new SqlConnection(str);
string sql = "insert into [dbo].[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)
{
flage = true;
}
return flage;
}
public bool AddStudent(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone,
string Address, DateTime Birthday, string Email)
{
bool flage = false;
string sql =
"insert into [dbo].[Student](LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email)values('" +
LoginPwd + "','" + StudentName + "','" + Gender + "','" + GradeId + "','" + Phone + "','" + Address +
"','" + DateTime.Now.ToShortDateString() + "','" + Email + "') ";
int count = SqlHelper.ExecuteNonQuery(sql);
if (count > 0)
{
flage = true;
}
return flage;
}
public bool IsLonginAgo(string username, string pwd)
{
bool flage = false;
string str = SqlHelper.Constr;
SqlConnection con = new SqlConnection(str);
string sql = "select count(1) from student where studentname='"+username+"'and loginpwd='"+pwd+"'";
SqlCommand com = new SqlCommand(sql,con);
con.Open();
int i = Convert.ToInt32(com.ExecuteScalar());
if (i>0)
{
flage = true;
}
con.Close();
return flage;
}
//登陆方法
public bool IsLongin(string username, string pwd)
{
bool flage = false;
string sql = "select count(1) from student where studentname='" + username + "'and loginpwd='" + pwd + "'";
int i = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
if (i > 0)
{
flage = true;
}
return flage;
}
//修改密码
public bool xiugaimima(Student stu)
{
//string str = SqlHelper.Constr;
//SqlConnection con=new SqlConnection(str);
string sql = "update student set loginpwd='" + stu.LoginPwd+ "'where studentname='" + stu.StudentName + "'";
//SqlCommand cmd=new SqlCommand(sql,con);
//con.Open();
int i = SqlHelper.ExecuteNonQuery(sql);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
//删除
public int shanchu(string shu)
{
string sql = "delete from student where studentno='"+shu+"'";
int i = SqlHelper.ExecuteNonQuery(sql);
return i;
}
public DataTable chuxnun3(string name)
{
String str = SqlHelper.Constr;
SqlConnection con=new SqlConnection(str);
string sql = "select * from student where studentname=@name";
SqlParameter para=new SqlParameter("@name",name);
SqlCommand cmd=new SqlCommand(sql,con);
cmd.Parameters.Add(para);
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds=new DataSet();
da.Fill(ds,"stuinfo]") ;
return ds.Tables["stuinfo"];
}
//带参
public DataTable get(string name,int gid,out int i)
{
string str =SqlHelper.Constr;
SqlConnection con=new SqlConnection(str);
string sql = "usp_0139";
SqlCommand cmd=new SqlCommand(sql,con);
SqlParameter[] paras =
{
new SqlParameter("@gender", name),
new SqlParameter("@gradeid", gid),
new SqlParameter("@countnum", SqlDbType.Int),
new SqlParameter("@myreturn",SqlDbType.Int),
};
paras[2].Direction = ParameterDirection.Output;
paras[3].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.AddRange(paras);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds=new DataSet();
da.Fill(ds,"info");
i = Convert.ToInt32(paras[2].Value);
int myreturn = Convert.ToInt32(paras[3].Value);
Console.WriteLine(myreturn);
Console.WriteLine(i);
return ds.Tables["info"];
}
}
}
////////////////////MySchool.Model .cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MySchool.Model
{
public class Student
{
public Student()
{
}
public int StudentNo { set; get ; }
public string LoginPwd { set ; get ;}
public string StudentName { set ; get ;}
public string Gender { set ; get ;}
public int GradeId { set ; get ;}
public string Phone { set ; get ;}
public string Address { set ; get ;}
public DateTime? Birthday { set ; get ;}
public string Email { set ; get ;}
public int? MyTT { set ; get ;}
}
}
//////Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace MySchool.UI
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1() );
}
}
}

0 0

分层式 学生管理系统

分层式学生管理系统