793291696 2018查询数据库

查询数据库

用c#连接数据库并实现可视化程序winform查询数据库。
数据库中有以下三个表
学生表：学号，姓名，性别
课程表：课程号，课程名
选课表：学号，课程号，分数

1）根据学生姓名查询其所选的课程信息

2）根据课程名查询学生姓名，分数，平均分数，分数按 降序输出

。

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 see2018
{
    public partial class Form1 : Form
    {
        private const string connStr = "Data Source=localhost;Initial Catalog=scnt;Integrated Security=True";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // 读取输入的学生姓名
            string studentName = textBox1.Text.Trim();

            // 连接数据库，查询学生选课信息
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 构造查询语句
                string sql = @"SELECT c.name, c.id, s.name, x.score
                                FROM score x
                                JOIN student s ON x.student_id = s.id
                                JOIN course c ON x.course_id = c.id
                                WHERE s.name = @studentName";

                // 构造查询命令对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@studentName", studentName);

                    // 执行查询，得到查询结果
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);

                    dataGridView1.DataSource = dataTable;
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string coursename=comboBox1.Text.Trim();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // 构造查询语句
                string sql = @"SELECT s.name, x.score
                                FROM score x
                                JOIN student s ON x.student_id = s.id
                                JOIN course c ON x.course_id = c.id
                                WHERE c.name = @courseName
                                ORDER BY x.score DESC";

                // 构造查询命令对象
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.Parameters.AddWithValue("@courseName", coursename);

                    // 执行查询，得到查询结果
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);


                    double sum = 0;
                    foreach (DataRow row in dataTable.Rows)
                    {
                        sum += Convert.ToDouble(row["score"]);
                    }
                    double avgScore = sum / dataTable.Rows.Count;

                    // Add a new row to show the average score
                    DataRow newRow = dataTable.NewRow();
                    newRow["name"] = "Average Score";
                    newRow["score"] = avgScore;
                    dataTable.Rows.Add(newRow);

                    dataGridView2.DataSource = dataTable;
                }
            }
        }
    }
}