using Student.DAL;
using Student.Modal;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Student.BLL
{
public class StudentBLL
{
StudentDAL studentsDal = new StudentDAL();
public int SaveStudents(StudentModal student)
{
SqlParameter[] paras = null;
if (student.Sid == Guid.Empty)
{
paras = new SqlParameter[6];
paras[0] = new SqlParameter("@sname", student.Sname);
paras[1] = new SqlParameter("@sage", student.Sage);
paras[2] = new SqlParameter("@saddress", student.Saddress);
paras[3] = new SqlParameter("@ssex", student.Ssex);
paras[4] = new SqlParameter("@gid", student.Gid);
paras[5] = new SqlParameter("@sid", student.Sid);
return studentsDal.AddStudents(paras);
}
paras = new SqlParameter[5];
paras[0] = new SqlParameter("@sname", student.Sname);
paras[1] = new SqlParameter("@sage", student.Sage);
paras[2] = new SqlParameter("@saddress", student.Saddress);
paras[3] = new SqlParameter("@ssex", student.Ssex);
paras[4] = new SqlParameter("@gid", student.Gid);
return studentsDal.EditStudents(paras);
}
public int RemoveStudents(StudentModal student)
{
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@sid", student.Sid);
return studentsDal.RemoveStudents(paras);
}
public DataTable FindStudentsAllBySnamePage(StudentModal student, int page, int rows)
{
SqlParameter[] paras = null;
if (student != null)
{
paras = new SqlParameter[1];
paras[0] = new SqlParameter("@sname", "%"+student.Sname+"%");
}
return studentsDal.FindStudentsAllBySnamePage(paras, page, rows);
}
public int FindStudentsMaxPage(StudentModal student, int rows)
{
SqlParameter[] paras = null;
if (student != null)
{
paras = new SqlParameter[1];
paras[0] = new SqlParameter("@sname", "%"+student.Sname+"%");
}
return studentsDal.FindStudentsMaxPage(paras, rows);
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Student.Modal;
using System.Data.SqlClient;
namespace Student.DAL
{
public class StudentDAL
{
DBHeple db = new DBHeple();
public int AddStudents(SqlParameter[] paras)
{
return db.ExecuteNonQuery("insert into t_students values(NEWID(),@sname,@sage,@saddress,@ssex,@gid)", paras);
}
public int EditStudents(SqlParameter[] paras)
{
return db.ExecuteNonQuery("update t_students set sname=@sname,sage=@sage,saddress=@saddress,ssex=@ssex,gid=@gid where sid=@sid", paras);
}
public int RemoveStudents(SqlParameter[] paras)
{
return db.ExecuteNonQuery("delete from t_students where sid=@sid", paras);
}
public DataTable FindStudentsAllBySnamePage(SqlParameter[] paras,int page,int rows)
{
StringBuilder sb = new StringBuilder();
int start = ((page - 1) * rows) + 1;
int end = page * rows;
sb.Append(" select * from ( ");
sb.Append(" select * ,ROW_NUMBER() over(order by sname desc) as 'rid' from ");
sb.Append(" t_students ");
if (paras != null) {
sb.Append(" where sname like @sname");
}
sb.Append(" ) temp where rid between "+start);
sb.Append(" and " + end);
return db.ExecuteDataTable(sb.ToString(), paras);
}
public int FindStudentsMaxPage(SqlParameter[] paras,int rows) {
string sql = "select count(sid) from t_students";
if (paras != null)
{
sql += " where sname like @sname";
}
int maxRows =int.Parse(db.ExecuteScalar(sql, paras).ToString());
return maxRows % rows == 0 ? maxRows / rows : (maxRows / rows) + 1;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Student.Modal
{
public class StudentModal
{
private Guid sid;
private string sname;
private int sage;
private string saddress;
private string ssex;
private Guid gid;
public StudentModal()
{
}
public StudentModal(string sname, int sage, string saddress, string ssex, Guid gid)
{
this.sname = sname;
this.sage = sage;
this.saddress = saddress;
this.ssex = ssex;
this.gid = gid;
}
public Guid Sid
{
get
{
return sid;
}
set
{
sid = value;
}
}
public string Sname
{
get
{
return sname;
}
set
{
sname = value;
}
}
public int Sage
{
get
{
return sage;
}
set
{
sage = value;
}
}
public string Saddress
{
get
{
return saddress;
}
set
{
saddress = value;
}
}
public string Ssex
{
get
{
return ssex;
}
set
{
ssex = value;
}
}
public Guid Gid
{
get
{
return gid;
}
set
{
gid = value;
}
}
}
}
using Student.BLL;
using Student.Modal;
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 Winfrom06
{
public partial class Frm_StudentsTable : Form
{
StudentBLL studentBll = new StudentBLL();
int page = 1;
int rows = 10;
int maxpage = 0;
public Frm_StudentsTable()
{
InitializeComponent();
}
private void Frm_StudentsTable_Load(object sender, EventArgs e)
{
DataGridViewDataBind(1, 10);
cmb_Rows.SelectedIndex = 0;
}
private void btn_Add_Click(object sender, EventArgs e)
{
for (int i = 0; i < 200; i++)
{
StudentModal sm = new StudentModal("增加的学生" + i, i, "增加学生的地址" + i, i % 2 == 0 ? "男" : "女", Guid.Parse("d2a8df58-9e58-43a9-8ad6-9549a8ef5c7b"));
studentBll.SaveStudents(sm);
}
}
private void btn_Query_Click(object sender, EventArgs e)
{
DataGridViewDataBind(1, 10);
}
private void btn_Remove_Click(object sender, EventArgs e)
{
}
#region 自定义方法
public void DataGridViewDataBind(int page, int rows)
{
StudentModal studetModal = null;
if (txt_Query.Text.Trim().Length > 0)
{
studetModal = new StudentModal();
studetModal.Sname = txt_Query.Text.Trim();
}
dgv_Students.DataSource = studentBll.FindStudentsAllBySnamePage(studetModal, page, rows);
maxpage = studentBll.FindStudentsMaxPage(studetModal, rows);
ShowPageMessAge();
}
public void ShowPageMessAge() {
lab_ShowPage.Text = page + "/" + maxpage;
}
#endregion
private void btn_FirstPage_Click(object sender, EventArgs e)
{
page = 1;
rows = 10;
DataGridViewDataBind(page, rows);
}
private void btn_UpPage_Click(object sender, EventArgs e)
{
page = page > 1 ? page - 1 : 1;
DataGridViewDataBind(page, rows);
}
private void btn_NextPage_Click(object sender, EventArgs e)
{
page = page == maxpage ? maxpage : page + 1;
DataGridViewDataBind(page, rows);
}
private void btn_LastPage_Click(object sender, EventArgs e)
{
page = maxpage;
DataGridViewDataBind(page, rows);
}
private void cmb_Rows_SelectedIndexChanged(object sender, EventArgs e)
{
rows = int.Parse(cmb_Rows.Text.Trim());
page = 1;
DataGridViewDataBind(page, rows);
}
}
}