course
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApp1
{
public class Course
{
private string cno;
private string name;
private double credit;
public string Cno { get => cno; set => cno = value; }
public string Name { get => name; set => name = value; }
public double Credit { get => credit; set => credit = value; }
public Course()
{
}
public Course(string cno,string name,double credit)
{
this.cno = cno;
this.name = name;
this.credit = credit;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApp1
{
public class Course
{
private string cno;
private string name;
private double credit;
public string Cno { get => cno; set => cno = value; }
public string Name { get => name; set => name = value; }
public double Credit { get => credit; set => credit = value; }
public Course()
{
}
public Course(string cno,string name,double credit)
{
this.cno = cno;
this.name = name;
this.credit = credit;
}
}
}
工具类
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 WindowsFormsApp1
{
public class DbUtil
{
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns></returns>
public SqlConnection getConnection()
{
String conStr = ConfigurationManager.ConnectionStrings["course"].ConnectionString;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conStr;
return conn;
}
/// <summary>
/// 执行的是类似select *的语句返回多行多列数据
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataSet selectDataSet(String sql, string tableName, SqlParameter[] paras)
{
DataSet ds = new DataSet();
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds, tableName);
conn.Close();
}
return ds;
}
/// <summary>
/// 执行的是Insert,update,delete语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public bool InsertUpdatedelete(String sql, SqlParameter[] paras)
{
int count = 0;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
count = comm.ExecuteNonQuery();
conn.Close();
}
return count > 0;
}
/// <summary>
/// 用于执行select count(),avg()带聚合函数的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public object Scalar(String sql, SqlParameter[] paras)
{
object o;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
o = comm.ExecuteScalar();
//执行查询,并返回由查询返回的结果集中的第一行的第一列。其他列或行将被忽略。
conn.Close();
}
return o;
}
}
}
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 WindowsFormsApp1
{
public class DbUtil
{
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns></returns>
public SqlConnection getConnection()
{
String conStr = ConfigurationManager.ConnectionStrings["course"].ConnectionString;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conStr;
return conn;
}
/// <summary>
/// 执行的是类似select *的语句返回多行多列数据
/// </summary>
/// <param name="sql"></param>
/// <param name="tableName"></param>
/// <param name="paras"></param>
/// <returns></returns>
public DataSet selectDataSet(String sql, string tableName, SqlParameter[] paras)
{
DataSet ds = new DataSet();
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = comm;
adapter.Fill(ds, tableName);
conn.Close();
}
return ds;
}
/// <summary>
/// 执行的是Insert,update,delete语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public bool InsertUpdatedelete(String sql, SqlParameter[] paras)
{
int count = 0;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
count = comm.ExecuteNonQuery();
conn.Close();
}
return count > 0;
}
/// <summary>
/// 用于执行select count(),avg()带聚合函数的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public object Scalar(String sql, SqlParameter[] paras)
{
object o;
using (SqlConnection conn = getConnection())
{
SqlCommand comm = new SqlCommand();
comm.CommandText = sql;
comm.Connection = conn;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
conn.Open();
o = comm.ExecuteScalar();
//执行查询,并返回由查询返回的结果集中的第一行的第一列。其他列或行将被忽略。
conn.Close();
}
return o;
}
}
}
form1
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;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
DbUtil dbUtil = new DbUtil();
public Form1()
{
InitializeComponent();
}
//修改数据
private void button3_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dataGridView1.CurrentRow;//当前行
if (dr == null)
{
MessageBox.Show("请选择要修改的数据");
return;
}
//建立一个课程对象将DataGridViewRow里面的值赋值给课程对象
Course course = new Course();
course.Cno = dr.Cells["cno"].Value.ToString();
course.Name = dr.Cells["cname"].Value.ToString();
course.Credit = double.Parse(dr.Cells["credit"].Value.ToString());
//跳转到form2并传值过去
Form2 frm = new Form2(0, course);
frm.ShowDialog();
}
private void button4_Click(object sender, EventArgs e)
{
string sql = "select * from course";
DataSet ds = dbUtil.selectDataSet(sql, "course", null);
if (ds != null && ds.Tables.Count > 0)
{
dataGridView1.DataSource = ds.Tables[0];
}
}
private void button1_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2(1, null);
form2.ShowDialog();
}
private void button2_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dataGridView1.CurrentRow;//当前行
if (dr == null)
{
MessageBox.Show("请选择要删除的数据");
return;
}
string cno = dr.Cells["cno"].Value.ToString();
string sql ="delete from course where cno=@cno";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@cno", cno) };
if (dbUtil.InsertUpdatedelete(sql, para)==true)
{
MessageBox.Show("删除成功!");
}
}
private void searchGetPageData()
{
// string condition = "";
// 获取页数的
// if (this.tbStaffName.Text.Trim() != "")
// {
// //Trim()去掉空格
// condition += " and staffName like '%" + this.tbStaffName.Text.Trim() + "%'";
// //模糊查询
// }
// condition += " and employeeName='" + this.cbDepartment.Text + "'";
// DbUtil db = new DbUtil();
// string sql = @"select count(1)
// from tb_Staffbbasic s,tb_EmployeeGenre e
// where e.id=s.temployeeId";
// //select count(1)计算一共有多少符合条件的行当两个表中的id相等的时候,所以说是查数据搞页数的;
// sql += condition;
//string sql2 = string.Format(@"select top {0} s.id as id,staffName,folk,birthday,sex,workdate,e.employeeName
// from tb_Staffbbasic s,tb_EmployeeGenre e
// where e.id=s.temployeeId {1}
// and s.id not in
// (
// select top {2} s.id from tb_Staffbbasic s,tb_EmployeeGenre e where e.id=s.temployeeId {3}
// )", pageData.PageSize, condition, (pageData.CurrentPageNo - 1) * pageData.PageSize, condition);
//PageUtil.getDataofCurrentPage(sql, sql2, pageData);
getDataofCurrentPage获取当前页数的方法
//this.dgvStaff.DataSource = pageData.RecordsOfCurrentPage.Tables[0];
//this.linfoNav.Text = string.Format("当前页面第{0}页,总共{1}页,总记录数:{2}条",
//pageData.CurrentPageNo, pageData.PageCount, pageData.TotalRecords);
//this.tbCurrentpageNo.Text = pageData.CurrentPageNo.ToString();
}
private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
}
public void createCell(IRow row, int index, string value)
{
ICell cell = row.CreateCell(index);
cell.SetCellValue(value);
}
private void button5_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.dataGridView1.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();
DataTable dt = (DataTable)this.dataGridView1.DataSource;//先把表中的数据转换到datatable里面(DataTable)是不是强制转换成datatable类型?
//MessageBox.Show(dt.Rows.Count.ToString());
XSSFWorkbook book = new XSSFWorkbook();//新建立一个工作簿 XSSFWorkbook
ISheet sheet = book.CreateSheet("成绩单");//建立一个名叫成绩单的工作表
//接下来就是循环赋值(将)
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i); //IRow 行 新建行
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);//新建列
cell.SetCellValue(dt.Rows[i][j].ToString());/*给该行所在列赋值*/
}
}
//IRow row=sheet.CreateRow(0);
SaveFileDialog dialog = new SaveFileDialog(); //保存文件对话框
dialog.Filter = "xlsx文件|.xlsx"; //要保存的文件类型
string path = "";
if (dialog.ShowDialog() == DialogResult.OK)//假如弹出的对话框返回的点击结果是ok
{
path = dialog.FileName;//获得文件路径
FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);// FileStream文件流 (找到路径,打开文件(没有便创建),写入内容)
book.Write(fs);
fs.Close();
book.Close();
}
}
public static page setPageData( page page)
{
DbUtil dbUtil = new DbUtil();
//得到记录数
string sql = "select COUNT(*) from course";
page.TotalRecords = (int)(dbUtil.Scalar(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 course
where cno not in (select top {0} cno from course)",
(page.CurrentPageNo - 1) * page.PageSize);
page.DataOfPage = dbUtil.selectDataSet(sql, "course", null);//返回的是dataset
return page;
}
private void buttonGo_Click(object sender, EventArgs e)
{
page page = new page();/*创建一个page对象*/
page.CurrentPageNo = Convert.ToInt32(this.textBox1.Text);//将文本框里面的数值给到page对象的当前页
page.PageSize = 10;//显示十条记录
//CourseBll bll = new CourseBll();
//bll.Page = page;//把page对象传过去
setPageData(page);//然后这个方法就开始处理几个之间的逻辑关系(处理后page对象的值应该是改变的)
if (page.TotalPages < page.CurrentPageNo)/*假如总页数小于当前页数的话*/
{
MessageBox.Show("没有该页的数据");
}
else
{
this.dataGridView1.DataSource = page.DataOfPage.Tables[0];//绑定它的数据源
}
}
}
}
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;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
namespace WindowsFormsApp1
{
public partial class Form1 : Form
{
DbUtil dbUtil = new DbUtil();
public Form1()
{
InitializeComponent();
}
//修改数据
private void button3_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dataGridView1.CurrentRow;//当前行
if (dr == null)
{
MessageBox.Show("请选择要修改的数据");
return;
}
//建立一个课程对象将DataGridViewRow里面的值赋值给课程对象
Course course = new Course();
course.Cno = dr.Cells["cno"].Value.ToString();
course.Name = dr.Cells["cname"].Value.ToString();
course.Credit = double.Parse(dr.Cells["credit"].Value.ToString());
//跳转到form2并传值过去
Form2 frm = new Form2(0, course);
frm.ShowDialog();
}
private void button4_Click(object sender, EventArgs e)
{
string sql = "select * from course";
DataSet ds = dbUtil.selectDataSet(sql, "course", null);
if (ds != null && ds.Tables.Count > 0)
{
dataGridView1.DataSource = ds.Tables[0];
}
}
private void button1_Click(object sender, EventArgs e)
{
Form2 form2 = new Form2(1, null);
form2.ShowDialog();
}
private void button2_Click(object sender, EventArgs e)
{
DataGridViewRow dr = dataGridView1.CurrentRow;//当前行
if (dr == null)
{
MessageBox.Show("请选择要删除的数据");
return;
}
string cno = dr.Cells["cno"].Value.ToString();
string sql ="delete from course where cno=@cno";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@cno", cno) };
if (dbUtil.InsertUpdatedelete(sql, para)==true)
{
MessageBox.Show("删除成功!");
}
}
private void searchGetPageData()
{
// string condition = "";
// 获取页数的
// if (this.tbStaffName.Text.Trim() != "")
// {
// //Trim()去掉空格
// condition += " and staffName like '%" + this.tbStaffName.Text.Trim() + "%'";
// //模糊查询
// }
// condition += " and employeeName='" + this.cbDepartment.Text + "'";
// DbUtil db = new DbUtil();
// string sql = @"select count(1)
// from tb_Staffbbasic s,tb_EmployeeGenre e
// where e.id=s.temployeeId";
// //select count(1)计算一共有多少符合条件的行当两个表中的id相等的时候,所以说是查数据搞页数的;
// sql += condition;
//string sql2 = string.Format(@"select top {0} s.id as id,staffName,folk,birthday,sex,workdate,e.employeeName
// from tb_Staffbbasic s,tb_EmployeeGenre e
// where e.id=s.temployeeId {1}
// and s.id not in
// (
// select top {2} s.id from tb_Staffbbasic s,tb_EmployeeGenre e where e.id=s.temployeeId {3}
// )", pageData.PageSize, condition, (pageData.CurrentPageNo - 1) * pageData.PageSize, condition);
//PageUtil.getDataofCurrentPage(sql, sql2, pageData);
getDataofCurrentPage获取当前页数的方法
//this.dgvStaff.DataSource = pageData.RecordsOfCurrentPage.Tables[0];
//this.linfoNav.Text = string.Format("当前页面第{0}页,总共{1}页,总记录数:{2}条",
//pageData.CurrentPageNo, pageData.PageCount, pageData.TotalRecords);
//this.tbCurrentpageNo.Text = pageData.CurrentPageNo.ToString();
}
private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
}
public void createCell(IRow row, int index, string value)
{
ICell cell = row.CreateCell(index);
cell.SetCellValue(value);
}
private void button5_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.dataGridView1.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();
DataTable dt = (DataTable)this.dataGridView1.DataSource;//先把表中的数据转换到datatable里面(DataTable)是不是强制转换成datatable类型?
//MessageBox.Show(dt.Rows.Count.ToString());
XSSFWorkbook book = new XSSFWorkbook();//新建立一个工作簿 XSSFWorkbook
ISheet sheet = book.CreateSheet("成绩单");//建立一个名叫成绩单的工作表
//接下来就是循环赋值(将)
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i); //IRow 行 新建行
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row.CreateCell(j);//新建列
cell.SetCellValue(dt.Rows[i][j].ToString());/*给该行所在列赋值*/
}
}
//IRow row=sheet.CreateRow(0);
SaveFileDialog dialog = new SaveFileDialog(); //保存文件对话框
dialog.Filter = "xlsx文件|.xlsx"; //要保存的文件类型
string path = "";
if (dialog.ShowDialog() == DialogResult.OK)//假如弹出的对话框返回的点击结果是ok
{
path = dialog.FileName;//获得文件路径
FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);// FileStream文件流 (找到路径,打开文件(没有便创建),写入内容)
book.Write(fs);
fs.Close();
book.Close();
}
}
public static page setPageData( page page)
{
DbUtil dbUtil = new DbUtil();
//得到记录数
string sql = "select COUNT(*) from course";
page.TotalRecords = (int)(dbUtil.Scalar(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 course
where cno not in (select top {0} cno from course)",
(page.CurrentPageNo - 1) * page.PageSize);
page.DataOfPage = dbUtil.selectDataSet(sql, "course", null);//返回的是dataset
return page;
}
private void buttonGo_Click(object sender, EventArgs e)
{
page page = new page();/*创建一个page对象*/
page.CurrentPageNo = Convert.ToInt32(this.textBox1.Text);//将文本框里面的数值给到page对象的当前页
page.PageSize = 10;//显示十条记录
//CourseBll bll = new CourseBll();
//bll.Page = page;//把page对象传过去
setPageData(page);//然后这个方法就开始处理几个之间的逻辑关系(处理后page对象的值应该是改变的)
if (page.TotalPages < page.CurrentPageNo)/*假如总页数小于当前页数的话*/
{
MessageBox.Show("没有该页的数据");
}
else
{
this.dataGridView1.DataSource = page.DataOfPage.Tables[0];//绑定它的数据源
}
}
}
}
form2
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 WindowsFormsApp1
{
public partial class Form2 : Form
{
DbUtil DbUtil = new DbUtil();
int a;
public Form2(int a,Course course)//要调用form2的话要穿a和对象过来
{
InitializeComponent();
if (a == 0)
{
//如果a==0的话按钮的名字就变成修改
button1.Text = "修改";
}
this.a = a;
if (course != null)
{
//如果课程对象不是空的话就绑定到文本里面去
this.textBoxCno.Text = course.Cno.ToString();
this.textBoxCname.Text = course.Name.ToString();
this.textBoxCredit.Text = course.Credit.ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (a == 0)
{
string sql = "update course set cname=@cname,credit=@credit where cno=@cno";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@cno",textBoxCno.Text),
new SqlParameter("@cname",textBoxCname.Text),
new SqlParameter("@credit",textBoxCredit.Text),
};
if (DbUtil.InsertUpdatedelete(sql, para)==true)
{
MessageBox.Show("修改成功!");
this.DialogResult = DialogResult.OK;
}
}
else
{
string sql = @"insert into course
(cno,cname,credit)
values(@cno, @cname,@credit)";
//防SQL注入
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@cno",this.textBoxCno.Text),
new SqlParameter("@cname",this.textBoxCname.Text),
new SqlParameter("@credit",this.textBoxCredit.Text),
};
if (DbUtil.InsertUpdatedelete(sql, paras))
{
MessageBox.Show("添加课程信息成功");
}
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 System.Data.SqlClient;
namespace WindowsFormsApp1
{
public partial class Form2 : Form
{
DbUtil DbUtil = new DbUtil();
int a;
public Form2(int a,Course course)//要调用form2的话要穿a和对象过来
{
InitializeComponent();
if (a == 0)
{
//如果a==0的话按钮的名字就变成修改
button1.Text = "修改";
}
this.a = a;
if (course != null)
{
//如果课程对象不是空的话就绑定到文本里面去
this.textBoxCno.Text = course.Cno.ToString();
this.textBoxCname.Text = course.Name.ToString();
this.textBoxCredit.Text = course.Credit.ToString();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (a == 0)
{
string sql = "update course set cname=@cname,credit=@credit where cno=@cno";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@cno",textBoxCno.Text),
new SqlParameter("@cname",textBoxCname.Text),
new SqlParameter("@credit",textBoxCredit.Text),
};
if (DbUtil.InsertUpdatedelete(sql, para)==true)
{
MessageBox.Show("修改成功!");
this.DialogResult = DialogResult.OK;
}
}
else
{
string sql = @"insert into course
(cno,cname,credit)
values(@cno, @cname,@credit)";
//防SQL注入
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@cno",this.textBoxCno.Text),
new SqlParameter("@cname",this.textBoxCname.Text),
new SqlParameter("@credit",this.textBoxCredit.Text),
};
if (DbUtil.InsertUpdatedelete(sql, paras))
{
MessageBox.Show("添加课程信息成功");
}
else
{
MessageBox.Show("添加课程信息失败");
}
}
}
}
}
form3
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.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
namespace WindowsFormsApp1
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();//打开文件的窗口
dialog.Filter = "Excel文档|*.xlsx";//打开的文件类型
if (dialog.ShowDialog() == DialogResult.OK)/*假如弹窗的结果是ok*/
{
FileStream fs = new FileStream(dialog.FileName, FileMode.Open, FileAccess.ReadWrite);//新建一个文件流
XSSFWorkbook book = new XSSFWorkbook(fs);//创建一个工作薄,将fs的文件写入进去
ISheet sheet = book.GetSheetAt(0);//创建一个工作表将工作表的第一个表的数据赋值过去
//MessageBox.Show(sheet.PhysicalNumberOfRows.ToString());
List<Course> courses = new List<Course>();//创建一个课程list
for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
{
IRow row = sheet.GetRow(i);
Course s = new Course();//创建一个课程对象
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.Cno = row.GetCell(0).NumericCellValue.ToString();// 用ToString()的时候exel的数据是不是不用类型是文本
s.Name= row.GetCell(1).StringCellValue;
s.Credit = (double)row.GetCell(2).NumericCellValue;
//NumericCell,StringCell对应着exel的文本类型,Numeric数据类型是一种精确数字数据类型
}
string sql = "insert into course(cno,cname,credit) values(@cno,@cname,@credit)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@no",s.Cno),
new SqlParameter("@name",s.Name),
new SqlParameter("@credit",s.Credit)
};
DbUtil db = new DbUtil();//创建一个工具类
db.InsertUpdatedelete(sql, paras);//调用插入方法插入数据库
courses.Add(s);//courses list对象绑定course对象
}
this.dataGridView1.DataSource = courses;//绑定数据显示
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 System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
namespace WindowsFormsApp1
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();//打开文件的窗口
dialog.Filter = "Excel文档|*.xlsx";//打开的文件类型
if (dialog.ShowDialog() == DialogResult.OK)/*假如弹窗的结果是ok*/
{
FileStream fs = new FileStream(dialog.FileName, FileMode.Open, FileAccess.ReadWrite);//新建一个文件流
XSSFWorkbook book = new XSSFWorkbook(fs);//创建一个工作薄,将fs的文件写入进去
ISheet sheet = book.GetSheetAt(0);//创建一个工作表将工作表的第一个表的数据赋值过去
//MessageBox.Show(sheet.PhysicalNumberOfRows.ToString());
List<Course> courses = new List<Course>();//创建一个课程list
for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
{
IRow row = sheet.GetRow(i);
Course s = new Course();//创建一个课程对象
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.Cno = row.GetCell(0).NumericCellValue.ToString();// 用ToString()的时候exel的数据是不是不用类型是文本
s.Name= row.GetCell(1).StringCellValue;
s.Credit = (double)row.GetCell(2).NumericCellValue;
//NumericCell,StringCell对应着exel的文本类型,Numeric数据类型是一种精确数字数据类型
}
string sql = "insert into course(cno,cname,credit) values(@cno,@cname,@credit)";
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@no",s.Cno),
new SqlParameter("@name",s.Name),
new SqlParameter("@credit",s.Credit)
};
DbUtil db = new DbUtil();//创建一个工具类
db.InsertUpdatedelete(sql, paras);//调用插入方法插入数据库
courses.Add(s);//courses list对象绑定course对象
}
this.dataGridView1.DataSource = courses;//绑定数据显示
fs.Close();/*关闭文件流*/
book.Close();/*关闭工作薄*/
}
}
}
}
page类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace WindowsFormsApp1
{
public class page
{
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 page()//所以这里写一个公共的才可以访问?
{
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 WindowsFormsApp1
{
public class page
{
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 page()//所以这里写一个公共的才可以访问?
{
CurrentPageNo = 1;/*页码等于一*/
PageSize = 10;/*要显示的数据是10*/
TotalPages = 0;
TotalRecords = 0;
}
}
}