dynamicweb8-15webform1.aspx
webform1.aspx.cs<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="dynamicweb8_15.WebForm1" %><!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/><title></title></head><body><form id="form1" runat="server"><div style="width:100%;height:400px;overflow:scroll;text-align:center;"><h4>查询专业课程成绩</h4>选择专业：<asp:DropDownList ID="ddlMajor" runat="server" DataSourceID="sdsMajor" DataTextField="major" DataValueField="major"></asp:DropDownList><asp:SqlDataSource ID="sdsMajor" runat="server" ConnectionString="<%$ ConnectionStrings:cnStudent %>" SelectCommand="SELECT DISTINCT [major] FROM [student]"></asp:SqlDataSource><br /><br />选择课程：<asp:CheckBoxList ID="cblCourse" runat="server" RepeatDirection="Horizontal" RepeatColumns="3" RepeatLayout="Flow" DataSourceID="sdsCourse" DataTextField="course_name" DataValueField="course_id"></asp:CheckBoxList><asp:SqlDataSource ID="sdsCourse" runat="server" ConnectionString="<%$ ConnectionStrings:cnStudent %>" SelectCommand="SELECT * FROM [course]"></asp:SqlDataSource><br /><br /><asp:Button ID="btnSure" runat="server" Text="查询" OnClick="btnSure_Click" /><br /><asp:Label ID="lblTips" runat="server" Text=""></asp:Label><asp:GridView ID="gvResult" runat="server" HorizontalAlign="Center" Width="800px" Font-Size="Small" OnDataBound="gvResult_DataBound" PageSize="6"></asp:GridView></div></form></body></html>
web.configusing System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;namespace dynamicweb8_15{public partial class WebForm1 : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){}protected void btnSure_Click(object sender, EventArgs e){//左连接，以学生表为主string sql = "select s.stu_name,c.course_name,r.mark from student s";sql += " left join result r on s.stu_id = r.stu_id ";sql += " left join course c on r.course_id= c.course_id ";sql += "where s.major=@major";//选择课程，含在单引号中，用逗号分开string courseSelect = "";for (int i = 0; i < cblCourse.Items.Count; i++){if (cblCourse.Items[i].Selected){courseSelect += "\'" + cblCourse.Items[i].Text + "\',";}}//没选课程时，提示，并把网格隐藏if (courseSelect == ""){lblTips.Text = "请选择课程";gvResult.Visible = false;return;}else{//去掉最后一个逗号courseSelect = courseSelect.Substring(0, courseSelect.Length - 1);gvResult.Visible = true;}sql += " and c.course_name in (" + courseSelect + ")";//string connect = System.Configuration.ConfigurationManager.ConnectionStrings["cnStudent"].ToString();System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sql, connect);adapter.SelectCommand.Parameters.AddWithValue("@major", ddlMajor.SelectedValue);System.Data.DataSet ds = new System.Data.DataSet();adapter.Fill(ds);//绑定之前先判断是否有数据if (ds.Tables[0].Rows.Count <= 0) return;gvResult.DataSource = ds;gvResult.DataBind();lblTips.Text = "";}protected void gvResult_DataBound(object sender, EventArgs e){//绑定表头GridViewRow r = gvResult.HeaderRow;r.Cells[0].Text = "姓名";r.Cells[1].Text = "课程";r.Cells[2].Text = "成绩";}}}
sql<?xml version="1.0" encoding="utf-8"?><!--有关如何配置 ASP.NET 应用程序的详细信息，请访问http://go.microsoft.com/fwlink/?LinkId=169433--><configuration><connectionStrings><add name="cnStudent" connectionString="Data Source=LAPTOP-AQKEN65V\SQLEXPRESS08;Initial Catalog=studentMIS;Persist Security Info=True;User ID=sa;Password=123456"providerName="System.Data.SqlClient" /></connectionStrings><system.web><compilation debug="true" targetFramework="4.5" /><httpRuntime targetFramework="4.5" /></system.web></configuration>
--学生管理系统库create database studentMIS;use studentMIS;--学生表create table student(stu_id nvarchar(8) not null,stu_name nvarchar(8) not null,sex nvarchar(2) not null,birth_date datetime,major nvarchar(50) not null,isCPC bit,email nvarchar(20),primary key(stu_id));--课程表create table course(course_id smallint identity(1,1) not null,course_name nvarchar(50) not null);--成绩表create table result(stu_id nvarchar(8) not null,course_id smallint not null,mark decimal(6,0) not null,primary key(stu_id,course_id));insert into student values('20160101','王霞','女','1998-10-23','计算机信息管理','true','wx@163.com');insert into student values('20160102','张大力','男','1998-07-02','计算机信息管理','false','zdf@163.com');insert into student values('20160103','李长军','男','1998-5-14','计算机信息管理','false','lcj@163.com');insert into student values('20160104','韩华东','男','1998-12-07','计算机信息管理','false','8562478@qq.com');insert into student values('20160105','万芳','女','1998-01-27','软件技术','false','5478518@qq.com');insert into student values('20160106','赵中千','男','1998-08-12','软件技术','true','5582178@qq.com');insert into student values('20160107','吴空俊','男','1998-05-21','软件技术','false','4845512@qq.com');insert into student values('20160108','方文其','男','1998-06-05','软件技术','false','8412563@qq.com');insert into student values('20160109','唐汉','男','1998-04-18','计算机应用','false','1546315@qq.com');insert into student values('20160110','刘含斌','男','1998-10-07','计算机应用','false','741526@qq.com');insert into course values('高等数学');insert into course values('大学英语');insert into course values('邓小平理论概述');insert into course values('毛泽东思想概述');insert into course values('C语言程序设计');insert into course values('数据结构');insert into result values('20160101',1,'71');insert into result values('20160101',2,'72');insert into result values('20160101',3,'73');insert into result values('20160101',4,'74');insert into result values('20160101',5,'75');insert into result values('20160101',6,'76');insert into result values('20160102',1,'60');insert into result values('20160102',2,'61');insert into result values('20160102',3,'60');insert into result values('20160102',4,'62');insert into result values('20160102',5,'60');insert into result values('20160102',6,'59');
