【数据库系统概论】实验七 嵌入式SQL查询

一、实验目的

（1）了解嵌入式SQL的使用方法。
（2）设计用户界面，能对数据表进行查询。

二、实验内容

设计一个应用程序，实现对教学管理数据库中所选定的表按指定条件进行查询。用户界面如图1所示。

图1 用户界面
完成以下功能：
（1）在学号、姓名、性别、年龄等输入框中输入一个或多个值，点击搜索按钮，能够查询相应记录。如果不输入任何数据，则查询学生表中所有记录。

三、实验指导

（一）Visual C# 嵌入式SQL语句

  1. 新建一个网站；
  2. 在web.config中增加配置项：
<connectionStrings>
	<add name="conn" connectionString="Data Source=127.0.0.1;Initial Catalog=stu_db;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
	<add name="conn" connectionString="Data Source=localhost;Initial Catalog=stu_db;User ID=sa;Password=123456789" providerName="System.Data.SqlClient"/>
</connectionStrings>

3．绘制程序界面，如图1所示，使用表格进行布局。代码如下：

<html>
 <head></head>
 <body>
  <table style="width: 800;" border="1" cellpadding="0" cellspacing="0" frame="border"> 
   <tbody>
    <tr> 
     <td class="style1"> 
      <asp:label id="Label1" runat="server" text="学号："></asp:label> 
      <asp:textbox id="TextBox1" runat="server"></asp:textbox> </td> 
     <td class="style2"> 
      <asp:label id="Label2" runat="server" text="姓名：" width="50"></asp:label> 
      <asp:textbox id="TextBox2" runat="server"></asp:textbox> </td> 
     <td> &nbsp;
      <asp:label id="Label3" runat="server" text="性别："></asp:label> &nbsp;
      <asp:dropdownlist id="DropDownList1" runat="server"> 
       <asp:listitem> 
       </asp:listitem> 
       <asp:listitem value="1">
        男
       </asp:listitem> 
       <asp:listitem value="2">
        女
       </asp:listitem> 
      </asp:dropdownlist> </td> 
    </tr> 
    <tr> 
     <td class="style1"> 
      <asp:label id="Label4" runat="server" text="年龄："></asp:label> 
      <asp:textbox id="TextBox3" runat="server"></asp:textbox> </td> 
     <td> 
      <asp:label id="Label5" runat="server" text="岁到：" width="50px"></asp:label> 
      <asp:textbox id="TextBox4" runat="server"></asp:textbox> </td> 
     <td> &nbsp; 
      <asp:button id="Button1" runat="server" onclick="Button1_Click" text="搜索" width="92px" /> </td> 
    </tr> 
    <tr> 
     <td colspan="3"> &nbsp; 
      <asp:gridview id="GridView1" runat="server" backcolor="White" bordercolor="#E7E7FF" borderstyle="None" borderwidth="1px" cellpadding="3" gridlines="Horizontal" width="100%"> 
       <alternatingrowstyle backcolor="#F7F7F7" /> 
       <footerstyle backcolor="#B5C7DE" forecolor="#4A3C8C" /> 
       <headerstyle backcolor="#4A3C8C" font-bold="True" forecolor="#F7F7F7" /> 
       <pagerstyle backcolor="#E7E7FF" forecolor="#4A3C8C" horizontalalign="Right" /> 
       <rowstyle backcolor="#E7E7FF" forecolor="#4A3C8C" /> 
       <selectedrowstyle backcolor="#738A9C" font-bold="True" forecolor="#F7F7F7" /> 
       <sortedascendingcellstyle backcolor="#F4F4FD" /> 
       <sortedascendingheaderstyle backcolor="#5A4C9D" /> 
       <sorteddescendingcellstyle backcolor="#D8D8F0" /> 
       <sorteddescendingheaderstyle backcolor="#3E3277" /> 
      </asp:gridview> </td> 
    </tr> 
   </tbody>
  </table>
 </body>
</html>
  1. 在命令按钮的click事件中执行以下代码：
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
string strconn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        SqlConnection con = new SqlConnection(strconn);
        con.Open();
 
        string condition = "";
        string sql;
        if (TextBox2.Text != "")
            condition = condition + " sname like'" + TextBox2.Text + "%'";
        if (DropDownList1.SelectedItem.ToString() != "")
            condition = condition + " and sex='" + DropDownList1.SelectedItem + "'";
        if (condition != "")
            sql = "select * from T.student where" + condition;
        else
            sql = "select * from T.student ";
        //Response.Write(sql);
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        con.Close();

四、实验结果

1、主要界面
QQ截图20221129000734QQ截图20221129000712baQQ截图20221129000756

2．主要事件及代码

<html xmlns="http://www.w3.org/1999/xhtml">
 <head></head>
 <body>
  &lt;%@ Page Language=&quot;C#&quot; AutoEventWireup=&quot;true&quot; CodeBehind=&quot;Default2.aspx.cs&quot; Inherits=&quot;ceShi.Default2&quot; %&gt;    
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
  <title></title>   
  <form id="form1" runat="server"> 
   <div> 
    <table style="width: 800;" border="1" cellpadding="0" cellspacing="0" frame="border"> 
     <tbody>
      <tr> 
       <td class="style1"> 
        <asp:label id="Label1" runat="server" text="学号："></asp:label> 
        <asp:textbox id="TextBox1" runat="server"></asp:textbox> </td> 
       <td class="style2"> 
        <asp:label id="Label2" runat="server" text="姓名：" width="50"></asp:label> 
        <asp:textbox id="TextBox2" runat="server"></asp:textbox> </td> 
       <td> &nbsp;
        <asp:label id="Label3" runat="server" text="性别："></asp:label> &nbsp;
        <asp:dropdownlist id="DropDownList1" runat="server"> 
         <asp:listitem> 
         </asp:listitem> 
         <asp:listitem value="1">
          男
         </asp:listitem> 
         <asp:listitem value="2">
          女
         </asp:listitem> 
        </asp:dropdownlist> </td> 
      </tr> 
      <tr> 
       <td class="style1"> 
        <asp:label id="Label4" runat="server" text="年龄："></asp:label> 
        <asp:textbox id="TextBox3" runat="server"></asp:textbox> </td> 
       <td> 
        <asp:label id="Label5" runat="server" text="岁到：" width="50px"></asp:label> 
        <asp:textbox id="TextBox4" runat="server"></asp:textbox> </td> 
       <td> &nbsp; 
        <asp:button id="Button1" runat="server" onclick="Button1_Click" text="搜索" width="92px" /> </td> 
      </tr> 
      <tr> 
       <td colspan="3"> &nbsp; 
        <asp:gridview id="GridView1" runat="server" backcolor="White" bordercolor="#E7E7FF" borderstyle="None" borderwidth="1px" cellpadding="3" gridlines="Horizontal" width="100%"> 
         <alternatingrowstyle backcolor="#F7F7F7" /> 
         <footerstyle backcolor="#B5C7DE" forecolor="#4A3C8C" /> 
         <headerstyle backcolor="#4A3C8C" font-bold="True" forecolor="#F7F7F7" /> 
         <pagerstyle backcolor="#E7E7FF" forecolor="#4A3C8C" horizontalalign="Right" /> 
         <rowstyle backcolor="#E7E7FF" forecolor="#4A3C8C" /> 
         <selectedrowstyle backcolor="#738A9C" font-bold="True" forecolor="#F7F7F7" /> 
         <sortedascendingcellstyle backcolor="#F4F4FD" /> 
         <sortedascendingheaderstyle backcolor="#5A4C9D" /> 
         <sorteddescendingcellstyle backcolor="#D8D8F0" /> 
         <sorteddescendingheaderstyle backcolor="#3E3277" /> 
        </asp:gridview> </td> 
      </tr> 
     </tbody>
    </table> 
   </div> 
  </form>  
 </body>
</html>

3、点击事件实现

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
 
namespace ceShi
{
    public partial class Default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
 
        }
 
        protected void Button1_Click(object sender, EventArgs e)
        {
            string strconn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            SqlConnection con = new SqlConnection(strconn);
            con.Open();
            string sql = "select * from T.Student where 1=1";
            if (TextBox1.Text != "")
                sql = sql + " and sno = '" + TextBox1.Text + "'";
            if (TextBox2.Text != "")
                sql = sql + " and sname like'" + TextBox2.Text + "%'";
            if (DropDownList1.SelectedItem.ToString() != "")
                sql = sql + " and ssex = '" + DropDownList1.SelectedItem + "'";
            if (TextBox3.Text != "")
                sql = sql + " and sage > " + TextBox3.Text;
            if (TextBox4.Text != "")
                sql = sql + " and sage < " + TextBox4.Text;
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            con.Close();
        }
    }
}
posted @   明金同学  阅读(15)  评论(0)  编辑  收藏  举报  
相关博文：
· 【数据库系统概论】实验二 SQL数据查询
· 【数据库系统概论】实验一 SQL数据定义
· 数据库系统（一）——数据查询
· 关于数据库
· 2023年5月17号
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！

阅读目录(Content)

此页目录为空

点击右上角即可分享
微信分享提示