数据库作业汇总试验

1、宁波大学商学院实验报告实验课程名称： 数据库的应用与开发 学 院： 商学院 专 业： 信息管理与信息系统 指导教师： 张宇萌 报告人姓名： 余雅春 学号： 144173745 班 级： 14信管 学 期： 2016-2017年第二个学期 商学院商科实验教学中心制实验成绩实验项目名称导入文件一、实验目的与要求：把选课名单这个文件导入数据库中二、实验设备及软件：Vs2010、电脑实验方法（原理、流程图）四、实验过程、步骤及内容代码：using System;using System.Collections.Generic;using System.ComponentModel;using Syst

2、em.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.OleDb;namespace 添加 public partial class Form1 : Form public Form1() InitializeComponent(); OleDbConnection Database1 = new OleDbConnection(); OleDbCommand CMD = new OleDbCommand(); OleDbData

3、Reader Reader; DataSet dt = new DataSet(); OleDbDataAdapter adpter = new OleDbDataAdapter(); private void Form1_Load(object sender, EventArgs e) private void button1_Click(object sender, EventArgs e) Database1.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UserszDocumentsDatabase

4、1.accdb; CMD.Connection = Database1; Database1.Open(); CMD.CommandText = Select 学号 from students where 学号 = + textBox1.Text + ; object t = CMD.ExecuteScalar(); if (t != null) MessageBox.Show(该学号已存在); return; CMD.CommandText = insert into students(学号,姓名,班级,性别) values( + textBox1.Text + , + textBox2.T

5、ext + , + textBox3.Text + , + textBox4.Text + ); CMD.ExecuteNonQuery(); Database1.Close(); MessageBox.Show(添加成功！); CMD.CommandText = select 姓名，学号，班级，性别 from students ; dt.Clear(); adpter.SelectCommand = CMD; adpter.Fill(dt, stu); dataGridView1.DataSource = dt.Tablesstu; Database1.Close(); private vo

6、id button2_Click(object sender, EventArgs e) Database1.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UserszDocumentsDatabase1.accdb; CMD.Connection = Database1; Database1.Open(); CMD.CommandText = Select 课程号 from course where 课程号 = + textBox5.Text + ; object t = CMD.ExecuteScala

7、r(); if (t != null) MessageBox.Show(该课程号已存在); return; CMD.CommandText = insert into course(课程号,课程名,学分) values( + textBox5.Text + , + textBox6.Text + , + textBox7.Text + ); CMD.ExecuteNonQuery(); Database1.Close(); MessageBox.Show(添加成功！); private void 查询_Click(object sender, EventArgs e) dt.Clear();

8、string name, No; Database1.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UserszDocumentsDatabase1.accdb; CMD.Connection = Database1; Database1.Open(); CMD.CommandText = select 学号,姓名,班级,性别 from students where 学号 like % + textBox1.Text + % order by 学号; adpter.SelectCommand = CMD;

9、adpter.Fill(dt, stu); dataGridView1.DataSource = dt.Tablesstu; Database1.Close(); private void 修改_Click(object sender, EventArgs e) Database1.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UserszDocumentsDatabase1.accdb; CMD.Connection = Database1; Database1.Open(); CMD.CommandTe

10、xt = update students set 姓名= + textBox2.Text + where 学号= + textBox1.Text + ; CMD.ExecuteNonQuery(); dt.Clear(); CMD.CommandText = select 姓名,学号,班级，性别 from students where 学号 like % + textBox1.Text + % order by 学号; adpter.SelectCommand = CMD; adpter.Fill(dt, stu); dataGridView1.DataSource = dt.Tablesst

11、u; Database1.Close(); private void 删除_Click(object sender, EventArgs e) Database1.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UserszDocumentsDatabase1.accdb; CMD.Connection = Database1; Database1.Open(); CMD.CommandText = delete from students where 学号= + textBox1.Text + ; CMD.

12、ExecuteNonQuery(); CMD.CommandText = select 姓名，学号，班级，性别 from students ; dt.Clear(); adpter.SelectCommand = CMD; adpter.Fill(dt, stu); dataGridView1.DataSource = dt.Tablesstu; Database1.Close(); private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e) textBox2.Text = da

13、taGridView1姓名, e.RowIndex.Value.ToString(); textBox1.Text = dataGridView1学号, e.RowIndex.Value.ToString(); textBox3.Text = dataGridView1班级, e.RowIndex.Value.ToString(); textBox4.Text = dataGridView1性别, e.RowIndex.Value.ToString(); private void dataset_Click(object sender, EventArgs e) dt.Tables.Add(a

14、bc); dt.Tablesabc.Columns.Add(姓名); dt.Tablesabc.Columns.Add(学号); dt.Tablesabc.Columns.Add(班级); dt.Tablesabc.Columns.Add(性别); dt.Tablesabc.Rows.Add(); dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1姓名 = 余雅春; dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1学号 = 144173745; dt.Tablesabc.Rowsdt.Tablesabc.Rows.C

15、ount - 1班级 = 14信管; dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1性别 = 女; dt.Tablesabc.Rows.Add(); dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1姓名 = 余雅夏; dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1学号 = 144173746; dt.Tablesabc.Rowsdt.Tablesabc.Rows.Count - 1班级 = 14金融; dt.Tablesabc.Rowsdt.Tablesabc.Rows.

16、Count - 1性别 = 女; dataGridView1.DataSource = dt.Tablesabc; private void button3_Click(object sender, EventArgs e) OpenFileDialog openfileDialog = new OpenFileDialog(); openfileDialog.DefaultExt = xlsx;/默认为xlsx格式 openfileDialog.Filter = xlsx文件(*.xlsx)|*.xlsx|xls文件(*.xls)|*.xls|所有文件(*.*)|*.*; if (openf

17、ileDialog.ShowDialog() != DialogResult.OK) return; try string FilePath = , File_Name = ; int pos = openfileDialog.FileName.LastIndexOf(); FilePath = openfileDialog.FileName.Substring(0, pos); File_Name = openfileDialog.FileName.Substring(pos + 1); OleDbConnection con = new OleDbConnection(Provider=M

18、icrosoft.ACE.OLEDB.12.0;Data Source= + openfileDialog.FileName + ;Extended Properties=Excel 12.0;HDR=YES); con.Open(); DataTable dtSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object null, null, null, Table ); string strTableNames = dtSheetName.Rows0TABLE_NAME.ToString(); string datainput = select * from + strTableNames + ; OleDbDataAdapter adapter = new OleDbDataAdapter(datainput, con); DataTable dt = new DataTable(); dt.Clear(); adapter.Fill(dt); con.C