数据库原理实践2

实验二： C/S 结构的数据库编程(6学时)

【实验目的】

学会通过ADO.NET 访问数据库，熟悉使用ADO.NET 技术进行 C/S 结构的数据库应用程序的设计，通过ADO.NET 接口对数据库进行操作。

【实验环境】

实验环境：Windows 11、Microsoft Visual Studio 2022 、SQL Server 2024

【实验任务与结果】

通过编程工具 (VS2022)编写访问数据库的应用程序，编程序设计良好的一个人机交互界面，列出实验一中的查询，将SQL语句嵌入 VS2022中完成“实验一”中的11个语句的结果显示。也可选择其他编程工具开发。

环境搭建

在VS Installer中安装所需环境 .NAT开发环境

屏幕截图 2024-05-21 152920 屏幕截图 2024-05-21 153001

VS2022 中通过 DataGridView 显示数据
启动 VS2022后，单击“文件”菜单，选择“新建”,“项目”，新建一个.NAT项目。

屏幕截图 2024-05-21 153042

选择 Visual C#——>Windows应用程序——>确定，会自动生成窗体 Form1。

屏幕截图 2024-05-21 153116

工具箱——>数据——>DataGridView ,拖入 Form1。

屏幕截图 2024-05-21 153130

在此控件上单击右键——>属性——>DataSource。

屏幕截图 2024-05-21 153157

点击此属性值下箭头——>添加项目数据源。
点击数据库——>下一步。

屏幕截图 2024-05-21 153232

点击新建连接。

屏幕截图 2024-05-21 153246

服务器名填入“.”或服务器名称，链接到一个数据库处点击下拉菜单选择数据库。
点击更改可选择数据源：“scdb”。

选择表，点击完成。

点击运行程序

屏幕截图 2024-05-21 225023

VS2022 中通过 RichTextBox 显示数据(重点)
启动 VS2022 后，单击“文件”菜单，选择“新建”,“项目”。
选择 VisualC#——>Windows 应用程序——>确定,会自动生成窗体 Form1。

屏幕截图 2024-05-21 153116

工具箱——>添加两个控件 RichTextBox1 和 Button1

屏幕截图 2024-05-21 225730

选择 “添加新数据源”,copy连接字符串，程序里用。

双击控件 “button1”,进入后台代码页代码如下

屏幕截图 2024-05-21 230950

进入后台代码页代码如下,上边通过控件已经生成过连接字符串，可以复制

private void button1_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";//" Data Source=.;Initial Catalog=SCDB;Integrated Security=True "; //连接字符串

SqlConnection conn = new SqlConnection(ConStr); //建立一个数据库连接实例

conn.Open(); //打开数据库连接

SqlCommand cmd = new SqlCommand(); //用于执行SQL语句

cmd.Connection = conn;

cmd.CommandText = "select * from teacher";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader(); //执行

while (sdr.Read()) //每次读一条记录读完停止

{

//读出的的字符串赋值给控件

//richTextBox1.Text += sdr.GetString(0) + " " + sdr.GetString(1) + " " + sdr.GetString(2) + " " +

// sdr.GetString(3) + " " + sdr.GetString(4)+"\n";

richTextBox1.Text += sdr[0].ToString() + " " + sdr[1].ToString() + " " + sdr[2].ToString() + " " +

sdr[3].ToString() + " " + sdr[4].ToString() + "\n";

}

conn.Dispose(); //销毁连接

}

按照如上方法完成11个按钮：
求全体学生的学号、姓名和出生年份。

private void button2_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = "SELECT Sno, Sname, 2024 - SAGE AS 出生年份 FROM student";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += sdr[0].ToString() + " " + sdr[1].ToString() + " " + sdr[2].ToString() + "\n";

}

conn.Dispose();

}

求每个系的学生总人数并按降序排列。

private void button3_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT Sdept, COUNT(*) AS TotalStudents

FROM Student

GROUP BY Sdept

ORDER BY TotalStudents DESC";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += sdr[0].ToString() + ": " + sdr[1].ToString() + "\n";

}

conn.Dispose();

}

求选修了课程号为 002 或 003 的学生的学号、课程名和成绩。

private void button4_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT

Student.SNO,

Course.CNAME,

Score.Grade

FROM

Student,

Score,

Course

WHERE

Student.SNO = Score.SNO

AND Score.CNO = Course.CNO

AND Score.CNO = '003'";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += sdr[0].ToString() + ": " + sdr[1].ToString() + ": " + sdr[2].ToString() + "\n";

}

conn.Dispose();

}

检索选修某课程的学生人数多于 3 人的教师姓名。

private void button5_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT DISTINCT

t.Tname

FROM

Teacher t

WHERE

T.cno IN (

SELECT

SC.cno

FROM

Score sc

GROUP BY

SC.CNO

HAVING

COUNT(DISTINCT SC.SNO) > 3

)";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += sdr[0].ToString() + "\n";

}

conn.Dispose();

}

查询所有未选课程的学生姓名和所在系。

private void button6_Click(object sender, EventArgs e)

{

string ConStr = "Data Source = MQLEGION; Initial Catalog = scdb; Integrated Security = True; Encrypt = False;";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT

Student.SName,

Student.SDept

FROM

Student

LEFT JOIN Score ON Student.SNO = Score.SNO

WHERE

Score.SNO IS NULL;";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += sdr[0].ToString() + ": " + sdr[1].ToString() + "\n";

}

conn.Dispose();

}

求每个同学的课程成绩的最高分，查询结果项包括：学生姓名、课程号及最高分。

private void button7_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

WITH StudentMaxGrades AS (

SELECT

SNO,

MAX(Grade) AS MaxGrade

FROM

Score

GROUP BY

SNO

)

SELECT

Student.SName AS 学生姓名,

Score.CNO AS 课程号,

Score.Grade AS 最高分

FROM

Student,

StudentMaxGrades,

Score

WHERE

Student.SNO = StudentMaxGrades.SNO

AND Score.SNO = Student.SNO

AND Score.Grade = StudentMaxGrades.MaxGrade;";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += "学生姓名: " + sdr[0].ToString() + "\n";

richTextBox1.Text += "课程号: " + sdr[1].ToString() + "\n";

richTextBox1.Text += "最高分: " + sdr[2].ToString() + "\n";

richTextBox1.Text += "-------------------\n";

}

conn.Dispose();

}

求所有讲授数据结构课程的教师姓名

private void button8_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT

Teacher.TName AS 教师姓名

FROM

Teacher,

Course

WHERE

Teacher.CNO = Course.CNO

AND Course.CNO = '004';";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += "教师姓名: " + sdr[0].ToString() + "\n";

}

conn.Dispose();

}

查询所有选修了李正科老师的课程的学生信息

private void button9_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

cmd.CommandText = @"

SELECT

Student.*

FROM

Student,

Score,

Course,

Teacher

WHERE

Student.SNO = Score.SNO

AND Score.CNO = Course.CNO

AND Course.CNO = Teacher.CNO

AND Teacher.TName = '李正科';";

cmd.CommandType = CommandType.Text;

SqlDataReader sdr = cmd.ExecuteReader();

richTextBox1.Text = ""; // 清空上一次的显示记录

while (sdr.Read())

{

richTextBox1.Text += "学生编号: " + sdr["SNO"].ToString() + "\n";

richTextBox1.Text += "学生姓名: " + sdr["SName"].ToString() + "\n";

richTextBox1.Text += "学生性别: " + sdr["Ssex"].ToString() + "\n";

richTextBox1.Text += "学生年龄: " + sdr["Sage"].ToString() + "\n";

richTextBox1.Text += "学生专业: " + sdr["Sdept"].ToString() + "\n";

richTextBox1.Text += "-------------------\n";

}

conn.Dispose();

}

新建一个关系表 S1(SNO,SNAME,SD,SA)，其字段类型定义与 Student 表中的相应字段(SNO,SNAME,SDEPT,SAGE)的数据类型定义相同。将表 Student 中在计算机系‘CS’的学生数据插入到表 S1 中。

private void button10_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

// 删除已存在的S1表

SqlCommand cmd1 = new SqlCommand();

cmd1.Connection = conn;

cmd1.CommandText = "IF OBJECT_ID('S1', 'U') IS NOT NULL DROP TABLE S1;";

cmd1.CommandType = CommandType.Text;

cmd1.ExecuteNonQuery();

// 创建关系表S1

SqlCommand cmd2 = new SqlCommand();

cmd2.Connection = conn;

cmd2.CommandText = @"

CREATE TABLE S1 (

SNO varchar(50),

SNAME varchar(50),

SD varchar(50),

SA INT

);";

cmd2.CommandType = CommandType.Text;

cmd2.ExecuteNonQuery();

// 插入数据到表S1

SqlCommand cmd3 = new SqlCommand();

cmd3.Connection = conn;

cmd3.CommandText = @"

INSERT INTO S1 (SNO, SNAME, SD, SA)

SELECT SNO, SNAME, SDEPT, SAGE

FROM Student

WHERE SDEPT = 'CS';";

cmd3.CommandType = CommandType.Text;

cmd3.ExecuteNonQuery();

// 读取S1表内容

SqlCommand cmd4 = new SqlCommand();

cmd4.Connection = conn;

cmd4.CommandText = "SELECT * FROM S1";

SqlDataReader reader = cmd4.ExecuteReader();

// 显示内容在RichTextBox1中

richTextBox1.Text = "S1表内容:\n";

while (reader.Read())

{

richTextBox1.Text += $"{reader["SNO"]}, {reader["SNAME"]}, {reader["SD"]}, {reader["SA"]}\n";

}

conn.Dispose();

}

建立计算机系的学生的视图 STUDENT_CS。利用视图 STUDENT_CS ，将学生的 001 号课程的成绩提高 5 分。

private void button11_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

// 删除已存在的STUDENT_CS视图

SqlCommand cmd1 = new SqlCommand();

cmd1.Connection = conn;

cmd1.CommandText = "IF OBJECT_ID('STUDENT_CS', 'V') IS NOT NULL DROP VIEW STUDENT_CS;";

cmd1.CommandType = CommandType.Text;

cmd1.ExecuteNonQuery();

// 创建STUDENT_CS视图

SqlCommand cmd2 = new SqlCommand();

cmd2.Connection = conn;

cmd2.CommandText = @"

CREATE VIEW STUDENT_CS AS

SELECT *

FROM Student

WHERE SDEPT = 'CS';";

cmd2.CommandType = CommandType.Text;

cmd2.ExecuteNonQuery();

// 更新001号课程的成绩

SqlCommand cmd3 = new SqlCommand();

cmd3.Connection = conn;

cmd3.CommandText = @"

UPDATE Score

SET Grade = Grade + 5

WHERE SNO IN (SELECT SNO FROM STUDENT_CS)

AND CNO = '001';";

cmd3.CommandType = CommandType.Text;

int rowsAffected = cmd3.ExecuteNonQuery();

if (rowsAffected > 0)

{

// 显示更新后的成绩在RichTextBox1中

SqlCommand cmd4 = new SqlCommand();

cmd4.Connection = conn;

cmd4.CommandText = @"

SELECT Score.SNO, Student.SNAME, Score.CNO, Course.CNAME, Score.Grade

FROM Score

INNER JOIN Student ON Score.SNO = Student.SNO

INNER JOIN Course ON Score.CNO = Course.CNO

WHERE Score.SNO IN (SELECT SNO FROM STUDENT_CS)

AND Score.CNO = '001';";

SqlDataReader reader = cmd4.ExecuteReader();

richTextBox1.Text = "更新后的成绩:\n";

while (reader.Read())

{

richTextBox1.Text += $"{reader["SNO"]}, {reader["SNAME"]}, {reader["CNO"]}, {reader["CNAME"]}, {reader["Grade"]}\n";

}

else

{ MessageBox.Show("没有符合条件的学生或课程。");}

conn.Dispose();

}

创建一个带参数的存储过程，将数据结构课程的成绩在 Low 与 High 分数段的学生信息提取出来，查询后的结果包括：姓名、所在的系、成绩。

private void button12_Click(object sender, EventArgs e)

{

string ConStr = "Data Source=MQLEGION;Initial Catalog=SCDB;Integrated Security=True";

SqlConnection conn = new SqlConnection(ConStr);

conn.Open();

// 删除之前的存储过程 GetScoreRangeStudents（如果存在）

SqlCommand cmd0 = new SqlCommand();

cmd0.Connection = conn;

cmd0.CommandText = "IF OBJECT_ID('GetScoreRangeStudents', 'P') IS NOT NULL DROP PROCEDURE GetScoreRangeStudents;";

cmd0.CommandType = CommandType.Text;

cmd0.ExecuteNonQuery();

// 创建新的存储过程 GetScoreRangeStudents

SqlCommand cmd1 = new SqlCommand();

cmd1.Connection = conn;

cmd1.CommandText = @"

CREATE PROCEDURE GetScoreRangeStudents

@Low INT,

@High INT

BEGIN

SELECT

Student.SNAME AS 姓名,

Student.SDEPT AS 所在的系,

Score.Grade AS 成绩

FROM

Student

JOIN

Score ON Student.SNO = Score.SNO

JOIN

Course ON Score.CNO = Course.CNO

WHERE

Course.CName = '数据结构' AND Score.Grade BETWEEN @Low AND @High;

END;";

cmd1.CommandType = CommandType.Text;

cmd1.ExecuteNonQuery();

// 执行存储过程 GetScoreRangeStudents 并显示结果在 richTextBox1 中

SqlCommand cmd2 = new SqlCommand();

cmd2.Connection = conn;

cmd2.CommandText = "EXEC GetScoreRangeStudents @Low=80, @High=90;";

cmd2.CommandType = CommandType.Text;

SqlDataReader reader = cmd2.ExecuteReader();

richTextBox1.Text = "成绩在80到90分之间的学生信息:\n";

richTextBox1.Text = ""; // 清空上一次的显示记录

while (reader.Read())

{

richTextBox1.Text += $"{reader["姓名"]}, {reader["所在的系"]}, {reader["成绩"]}\n";

}

conn.Dispose();

}

posted @ 2024-10-31 09:51 风花赏秋月阅读(4) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 数据库原理实践3

· 数据库原理实践1

· 【数据库系统概论】实验二 SQL数据查询

· 实验二交互式 SQL 语句的使用

· 关于数据库

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？

公告

昵称：风花赏秋月
园龄： 4个月
粉丝： 0
关注： 1

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

大学(27)

随笔档案

文章分类

大学(6)

数据库原理实践2

实验二： C/S 结构的数据库编程(6学时)

公告

搜索

常用链接

随笔分类

随笔档案

文章分类

阅读排行榜