连接数据库
<connectionStrings>
<add name="sqlConnStr" connectionString="server=.;database=studentdb;user=sa;password=xiaobao"/>
</connectionStrings>
<connectionStrings>
<add name="sqlConnStr" connectionString="server=.;database=studentdb;user=sa;password=xiaobao"/>
</connectionStrings>
course
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class Course
{
private int cid;
private string cno;
private string cname;
private double credit;
private int period;
public int Cid
{
set { this.cid = value; }
get { return this.cid; }
}
public string Cno
{
set { this.cno = value; }
get { return this.cno; }
}
public string Cname {
set { this.cname = value; }
get { return this.cname; }
}
public double Credit {
set { this.credit = value; }
get { return this.credit; }
}
public int Period
{
set { this.period = value; }
get { return this.period; }
}
public Course() {
}
public Course(int cid,string cno,string cname,double credit,int period)
{
this.cid = cid;
this.cno = cno;
this.cname = cname;
this.credit = credit;
this.period = period;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class Course
{
private int cid;
private string cno;
private string cname;
private double credit;
private int period;
public int Cid
{
set { this.cid = value; }
get { return this.cid; }
}
public string Cno
{
set { this.cno = value; }
get { return this.cno; }
}
public string Cname {
set { this.cname = value; }
get { return this.cname; }
}
public double Credit {
set { this.credit = value; }
get { return this.credit; }
}
public int Period
{
set { this.period = value; }
get { return this.period; }
}
public Course() {
}
public Course(int cid,string cno,string cname,double credit,int period)
{
this.cid = cid;
this.cno = cno;
this.cname = cname;
this.credit = credit;
this.period = period;
}
}
}
courseBll
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class CourseBll
{
private Pagination page;
public Pagination Page
{
set {
this.page=value;
}
get
{
return this.page;
}
}
//下面这个方法没有返回值的是不是是在上面返回值
//应该是可以这么写 public 返回类型是page对象 setPageData(需要接收的值)
public void setPageData()
{
//得到记录数
string sql = "select COUNT(*) from tb_course";
page.TotalRecords=(int)(DbUtil.ExecuteScalar(sql,null));
//通过记录数计算总页数
page.TotalPages=page.TotalRecords % page.PageSize == 0 ?
page.TotalRecords % page.PageSize :
page.TotalRecords % page.PageSize + 1;
//获取当前页的数据
sql = string.Format(@"select top 10 * from tb_course
where cid not in (select top {0} cid from tb_course)",
(page.CurrentPageNo-1)*page.PageSize);
page.DataOfPage=DbUtil.ExecuteReader(sql, "course", null);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class CourseBll
{
private Pagination page;
public Pagination Page
{
set {
this.page=value;
}
get
{
return this.page;
}
}
//下面这个方法没有返回值的是不是是在上面返回值
//应该是可以这么写 public 返回类型是page对象 setPageData(需要接收的值)
public void setPageData()
{
//得到记录数
string sql = "select COUNT(*) from tb_course";
page.TotalRecords=(int)(DbUtil.ExecuteScalar(sql,null));
//通过记录数计算总页数
page.TotalPages=page.TotalRecords % page.PageSize == 0 ?
page.TotalRecords % page.PageSize :
page.TotalRecords % page.PageSize + 1;
//获取当前页的数据
sql = string.Format(@"select top 10 * from tb_course
where cid not in (select top {0} cid from tb_course)",
(page.CurrentPageNo-1)*page.PageSize);
page.DataOfPage=DbUtil.ExecuteReader(sql, "course", null);
}
}
}
工具类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace studentproject
{
class DbUtil
{
public static SqlConnection getConnection()
{
SqlConnection conn = new SqlConnection();
String connStr = ConfigurationManager.ConnectionStrings["sqlConnStr"].
ConnectionString;
conn.ConnectionString = connStr;
return conn;
}
public static bool ExecuteNoQuery(string sql,SqlParameter[] paras)
{
SqlConnection conn=getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if(paras!=null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
int count=comm.ExecuteNonQuery();
conn.Close();
return count > 0;
}
public static object ExecuteScalar(string sql, SqlParameter[] paras)
{
SqlConnection conn = getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
object o = comm.ExecuteScalar();
conn.Close();
return o;
}
public static DataSet ExecuteReader(string sql,string tableName,
SqlParameter[] paras)
{
SqlConnection conn = getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if(paras!=null)
{
comm.Parameters.AddRange(paras);
}
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
DataSet ds = new DataSet();
adapter.Fill(ds,tableName);
conn.Close();
return ds;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace studentproject
{
class DbUtil
{
public static SqlConnection getConnection()
{
SqlConnection conn = new SqlConnection();
String connStr = ConfigurationManager.ConnectionStrings["sqlConnStr"].
ConnectionString;
conn.ConnectionString = connStr;
return conn;
}
public static bool ExecuteNoQuery(string sql,SqlParameter[] paras)
{
SqlConnection conn=getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if(paras!=null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
int count=comm.ExecuteNonQuery();
conn.Close();
return count > 0;
}
public static object ExecuteScalar(string sql, SqlParameter[] paras)
{
SqlConnection conn = getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
object o = comm.ExecuteScalar();
conn.Close();
return o;
}
public static DataSet ExecuteReader(string sql,string tableName,
SqlParameter[] paras)
{
SqlConnection conn = getConnection();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sql;
if(paras!=null)
{
comm.Parameters.AddRange(paras);
}
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
DataSet ds = new DataSet();
adapter.Fill(ds,tableName);
conn.Close();
return ds;
}
}
}
pagination
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace studentproject
{
class Pagination//这里不是公共的
{
public int CurrentPageNo{ get;set;}//当前页
public int PageSize{get; set;}//页面要显示的代码
public int TotalPages{ get;set;}//所有的页数
public int TotalRecords { set; get; }//所有的记录数
public DataSet DataOfPage { set; get; }//数据dataset
public Pagination()//所以这里写一个公共的才可以访问?
{
CurrentPageNo = 1;/*页码等于一*/
PageSize = 10;/*要显示的数据是10*/
TotalPages = 0;
TotalRecords = 0;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace studentproject
{
class Pagination//这里不是公共的
{
public int CurrentPageNo{ get;set;}//当前页
public int PageSize{get; set;}//页面要显示的代码
public int TotalPages{ get;set;}//所有的页数
public int TotalRecords { set; get; }//所有的记录数
public DataSet DataOfPage { set; get; }//数据dataset
public Pagination()//所以这里写一个公共的才可以访问?
{
CurrentPageNo = 1;/*页码等于一*/
PageSize = 10;/*要显示的数据是10*/
TotalPages = 0;
TotalRecords = 0;
}
}
}
student
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class Student
{
private int id;
private string no;
private string name;
private int clazz;
public int Id { set; get; }
public string No { set; get; }
public string Name { set; get; }
public int Clazz { set; get; }
public Student()
{
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace studentproject
{
class Student
{
private int id;
private string no;
private string name;
private int clazz;
public int Id { set; get; }
public string No { set; get; }
public string Name { set; get; }
public int Clazz { set; get; }
public Student()
{
}
}
}
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.Data.SqlClient; //引入数据库操作的命名空间
namespace studentproject
{
public partial class WCourseForm : Form
{
public WCourseForm()
{
InitializeComponent();
}
/// <summary>
/// 添加课程信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e)
{
string sqlConnStr = "server=.;database=studentdb;user=sa;password=xiaobao";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sqlConnStr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = @"insert into tb_course
(cno, cname, credit, period)
values(@cno, @cname, @credit, @period)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@cno",txtCno.Text ),
new SqlParameter("@cname",txtCname.Text ),
new SqlParameter("@credit",Convert.ToDouble(txtCredit.Text) ),
new SqlParameter("@period", Convert.ToInt32(txtPeriod.Text)),
};
comm.Parameters.AddRange(paras);
comm.ExecuteNonQuery();
conn.Close();
}
}
}
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.Data.SqlClient; //引入数据库操作的命名空间
namespace studentproject
{
public partial class WCourseForm : Form
{
public WCourseForm()
{
InitializeComponent();
}
/// <summary>
/// 添加课程信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAdd_Click(object sender, EventArgs e)
{
string sqlConnStr = "server=.;database=studentdb;user=sa;password=xiaobao";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sqlConnStr;
conn.Open();
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = @"insert into tb_course
(cno, cname, credit, period)
values(@cno, @cname, @credit, @period)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@cno",txtCno.Text ),
new SqlParameter("@cname",txtCname.Text ),
new SqlParameter("@credit",Convert.ToDouble(txtCredit.Text) ),
new SqlParameter("@period", Convert.ToInt32(txtPeriod.Text)),
};
comm.Parameters.AddRange(paras);
comm.ExecuteNonQuery();
conn.Close();
}
}
}
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 NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data.SqlClient;
namespace studentproject
{
public partial class WImportStudent : Form
{
public WImportStudent()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel文档|*.xlsx";
if(dialog.ShowDialog()==DialogResult.OK)
{
FileStream fs = new FileStream(dialog.FileName,FileMode.Open,FileAccess.ReadWrite);
XSSFWorkbook book = new XSSFWorkbook(fs);
ISheet sheet=book.GetSheetAt(0);
//MessageBox.Show(sheet.PhysicalNumberOfRows.ToString());
List<Student> students = new List<Student>();
for(int i=1;i<sheet.PhysicalNumberOfRows;i++)
{
IRow row = sheet.GetRow(i);
Student s = new Student();
for(int j=0;j<row.Cells.Count;j++)
{
/*
ICell cell=row.GetCell(j);
if(cell.CellType==CellType.Numeric)
{
MessageBox.Show(cell.NumericCellValue.ToString());
}
else {
MessageBox.Show(cell.StringCellValue);
}
*/
s.Id = (int)row.GetCell(0).NumericCellValue;
s.No = row.GetCell(1).StringCellValue;
s.Name = row.GetCell(2).StringCellValue;
s.Clazz = (int)row.GetCell(3).NumericCellValue;
}
string sql = "insert into student(no,name,clazz) values(@no,@name,@clazz)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@no",s.No),
new SqlParameter("@name",s.Name),
new SqlParameter("@clazz",s.Clazz)
};
DbUtil.ExecuteNoQuery(sql, paras);
students.Add(s);
}
this.dataGridView1.DataSource = students;
fs.Close();
book.Close();
}
}
}
}
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 NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data.SqlClient;
namespace studentproject
{
public partial class WImportStudent : Form
{
public WImportStudent()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel文档|*.xlsx";
if(dialog.ShowDialog()==DialogResult.OK)
{
FileStream fs = new FileStream(dialog.FileName,FileMode.Open,FileAccess.ReadWrite);
XSSFWorkbook book = new XSSFWorkbook(fs);
ISheet sheet=book.GetSheetAt(0);
//MessageBox.Show(sheet.PhysicalNumberOfRows.ToString());
List<Student> students = new List<Student>();
for(int i=1;i<sheet.PhysicalNumberOfRows;i++)
{
IRow row = sheet.GetRow(i);
Student s = new Student();
for(int j=0;j<row.Cells.Count;j++)
{
/*
ICell cell=row.GetCell(j);
if(cell.CellType==CellType.Numeric)
{
MessageBox.Show(cell.NumericCellValue.ToString());
}
else {
MessageBox.Show(cell.StringCellValue);
}
*/
s.Id = (int)row.GetCell(0).NumericCellValue;
s.No = row.GetCell(1).StringCellValue;
s.Name = row.GetCell(2).StringCellValue;
s.Clazz = (int)row.GetCell(3).NumericCellValue;
}
string sql = "insert into student(no,name,clazz) values(@no,@name,@clazz)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@no",s.No),
new SqlParameter("@name",s.Name),
new SqlParameter("@clazz",s.Clazz)
};
DbUtil.ExecuteNoQuery(sql, paras);
students.Add(s);
}
this.dataGridView1.DataSource = students;
fs.Close();
book.Close();
}
}
}
}
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 NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace studentproject
{
public partial class WSearchCourse : Form
{
public WSearchCourse()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Pagination page = new Pagination();/*创建一个page对象*/
page.CurrentPageNo =Convert.ToInt32( this.tbCurrentPage.Text);//将文本框里面的数值给到page对象的当前页
page.PageSize = 10;//显示十条记录
CourseBll bll = new CourseBll();
bll.Page = page;//把page对象传过去
bll.setPageData();//然后这个方法就开始处理几个之间的逻辑关系(处理后page对象的值应该是改变的)
if(page.TotalPages < page.CurrentPageNo)/*假如总页数小于当前页数的话*/
{
MessageBox.Show("没有该页的数据");
}
else {
this.dgvCourse.DataSource = page.DataOfPage.Tables[0];//绑定它的数据源
}
}
public void createCell(IRow row,int index,string value)
{
ICell cell = row.CreateCell(index);
cell.SetCellValue(value);
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "excel file|*.xlsx";
if(dialog.ShowDialog()==DialogResult.OK)
{
//新建excel文件的内存模型
XSSFWorkbook book = new XSSFWorkbook(); //创建工作簿
ISheet sheet=book.CreateSheet("课程信息表"); //创建工作表
IRow row=sheet.CreateRow(0);//创建标题行(第一行为标题内容)
createCell(row, 0, "编号"); //为每列的单元赋值
createCell(row, 1, "课程号");
createCell(row, 2, "课程名");
createCell(row, 3, "学分");
createCell(row, 4, "学时");
//MessageBox.Show("have saved,the path is "+dialog.FileName);
DataTable dt=(DataTable)this.dgvCourse.DataSource; //获取dgvCourse控件中的数据,并转为DataTable
for(int i=0;i<dt.Rows.Count;i++) //循环DataTable的内容
{
row = sheet.CreateRow(i+1); //每循环DataTable的一行,相应的Excel表格就需要创建一行
for (int j=0;j<dt.Columns.Count;j++) //循环列,并为相应的行赋值
{
//MessageBox.Show(dt.Rows[i][j].ToString());
createCell(row, j, dt.Rows[i][j].ToString());
}
}
FileStream fs = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite);
book.Write(fs);
fs.Close();
book.Close();
}
}
}
}
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 NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
namespace studentproject
{
public partial class WSearchCourse : Form
{
public WSearchCourse()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Pagination page = new Pagination();/*创建一个page对象*/
page.CurrentPageNo =Convert.ToInt32( this.tbCurrentPage.Text);//将文本框里面的数值给到page对象的当前页
page.PageSize = 10;//显示十条记录
CourseBll bll = new CourseBll();
bll.Page = page;//把page对象传过去
bll.setPageData();//然后这个方法就开始处理几个之间的逻辑关系(处理后page对象的值应该是改变的)
if(page.TotalPages < page.CurrentPageNo)/*假如总页数小于当前页数的话*/
{
MessageBox.Show("没有该页的数据");
}
else {
this.dgvCourse.DataSource = page.DataOfPage.Tables[0];//绑定它的数据源
}
}
public void createCell(IRow row,int index,string value)
{
ICell cell = row.CreateCell(index);
cell.SetCellValue(value);
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "excel file|*.xlsx";
if(dialog.ShowDialog()==DialogResult.OK)
{
//新建excel文件的内存模型
XSSFWorkbook book = new XSSFWorkbook(); //创建工作簿
ISheet sheet=book.CreateSheet("课程信息表"); //创建工作表
IRow row=sheet.CreateRow(0);//创建标题行(第一行为标题内容)
createCell(row, 0, "编号"); //为每列的单元赋值
createCell(row, 1, "课程号");
createCell(row, 2, "课程名");
createCell(row, 3, "学分");
createCell(row, 4, "学时");
//MessageBox.Show("have saved,the path is "+dialog.FileName);
DataTable dt=(DataTable)this.dgvCourse.DataSource; //获取dgvCourse控件中的数据,并转为DataTable
for(int i=0;i<dt.Rows.Count;i++) //循环DataTable的内容
{
row = sheet.CreateRow(i+1); //每循环DataTable的一行,相应的Excel表格就需要创建一行
for (int j=0;j<dt.Columns.Count;j++) //循环列,并为相应的行赋值
{
//MessageBox.Show(dt.Rows[i][j].ToString());
createCell(row, j, dt.Rows[i][j].ToString());
}
}
FileStream fs = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite);
book.Write(fs);
fs.Close();
book.Close();
}
}
}
}
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 studentproject
{
public partial class WStudentForm : Form
{
public WStudentForm()
{
InitializeComponent();
}
private void WStudentForm_Load(object sender, EventArgs e)
{
string sql = "select * from clazz";
DataSet ds=DbUtil.ExecuteReader(sql,"clazz",null);
cbClazz.DataSource = ds.Tables["clazz"];
cbClazz.DisplayMember = "name";
cbClazz.ValueMember = "id";
}
private void btnAdd_Click(object sender, EventArgs e)
{
}
}
}
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 studentproject
{
public partial class WStudentForm : Form
{
public WStudentForm()
{
InitializeComponent();
}
private void WStudentForm_Load(object sender, EventArgs e)
{
string sql = "select * from clazz";
DataSet ds=DbUtil.ExecuteReader(sql,"clazz",null);
cbClazz.DataSource = ds.Tables["clazz"];
cbClazz.DisplayMember = "name";
cbClazz.ValueMember = "id";
}
private void btnAdd_Click(object sender, EventArgs e)
{
}
}
}