2017机试

Loga6

已于 2023-03-20 20:37:07 修改

阅读量45

点赞数

文章标签：数据库 c#

于 2023-03-19 19:44:25 首次发布

本文链接：https://blog.csdn.net/Loga6/article/details/129654948

版权

机试题目

请添加图片描述

解答

创建数据库

create table Departments(
	DeptNo int primary key,
	DeptName varchar(32)
);

create table Groups(
	GroupNo int primary key,
	GroupName varchar(32),
	DeptNo int references Departments(DeptNo),
	Month int,
	Number int null check(Number<=80)
);

create table Patients(
	PID int primary key,
	PName varchar(32) not null,
	Date varchar(32),
	GroupNo int references Groups(GroupNo)
	on delete cascade
);

insert into Departments values(111,'内科');
insert into Departments values(222,'外科');
insert into Departments values(333,'中医科');

insert into Groups values(11,'心内科',111,3,32);
insert into Groups values(12,'呼吸内科',111,4,45);
insert into Groups values(13,'中医保健科',333,3,68);
insert into Groups values(14,'骨科',222,4,28);
insert into Groups values(15,'心外科',222,4,56);

insert into Patients values(880101,'李一','1980-6-1',11);
insert into Patients values(880102,'王二','1978-2-3',15);
insert into Patients values(880103,'张三','1969-11-2',13);
insert into Patients values(880104,'刘四','1975-7-20',13);
insert into Patients values(880105,'陈五','1986-5-16',14);
insert into Patients values(880106,'杨六','1998-4-23',13);
insert into Patients values(880107,'赵七','2000-3-1',12);
insert into Patients values(880108,'周八','1992-7-5',11);

窗体绘制

主窗口

在这里插入图片描述

查询窗体

在这里插入图片描述

统计窗体

在这里插入图片描述

病人表维护窗体

在这里插入图片描述

小科室表维护窗体

在这里插入图片描述

需要用到的类

数据库连接类 DB.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace _2017test
{
    internal class DB
    {
        public static string connStr = @"server=LAPTOP-FP07RJI9;database=2017;integrated security=true;";
        public static SqlConnection conn = null;
        //增删改
        public static void TableChange(string sql, SqlParameter[] para = null)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(sql, DB.conn);
                if (para != null)
                    cmd.Parameters.AddRange(para);
                if (cmd.ExecuteNonQuery() > 0)
                    MessageBox.Show("操作成功！");
                else
                    MessageBox.Show("操作失败！\n请检查数据库是否有该条数据！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        //查
        public static DataTable TableSelect(string sql, SqlParameter[] para = null)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, DB.conn);
                if (para != null)
                    cmd.Parameters.AddRange(para);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
    }
}

程序运行类 Program.cs

namespace _2017test
{
    internal static class Program
    {
        /// <summary>
        ///  The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            try
            {
                DB.conn = new System.Data.SqlClient.SqlConnection(DB.connStr);
                DB.conn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            Application.Run(new MainForm());
            DB.conn.Close();
        }
    }
}

窗体函数书写

主窗体

namespace _2017test
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void 查询ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormSearch fs = new FormSearch();
            fs.MdiParent = this;
            fs.Show();
        }

        private void 病人表维护ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormPatients fp = new FormPatients();
            fp.MdiParent = this;
            fp.Show();
        }

        private void 小科室维护ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormGroup fp = new FormGroup();
            fp.MdiParent = this;
            fp.Show();
        }

        private void 统计ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormStatistics fs = new FormStatistics();
            fs.MdiParent = this;
            fs.Show();
        }
    }
}

查询窗体

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace _2017test
{
    public partial class FormSearch : Form
    {
        public FormSearch()
        {
            InitializeComponent();
            MyShow();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = @"SELECT d.DeptName,g.GroupName,p.PName
		                FROM [2017].[dbo].[Patients] p ,[2017].[dbo].[Departments] d,[2017].[dbo].[Groups] g
		                WHERE p.GroupNo =g.GroupNo and g.DeptNo=d.DeptNo";
            if (!string.IsNullOrEmpty(textBox1.Text))
            {
                //sql +=  @" and d.DeptName like "  + textBox1.Text + @" ";
                sql += @" and d.DeptName like '%" + textBox1.Text + "%' ";


            }
            if (!string.IsNullOrEmpty(textBox2.Text))
            {
                //sql += @" and g.GroupName like " + textBox2.Text + @" ";
                sql += @" and d.DeptName like '%" + textBox2.Text + "%' ";

            }
            Console.Write(textBox1.Text);
            Console.Write(textBox2.Text);
            Console.Write(textBox3.Text);
            if (!string.IsNullOrEmpty(textBox3.Text))
            {
                //sql += @" and p.PName like " + @" %"+textBox3.Text + @"% ";
                sql += @" and d.DeptName like '%" + textBox3.Text + "%' ";

            }
            sql += @" order by d.DeptNo desc,g.GroupNo ,p.GroupNo desc";

            DB.TableSelect(sql);
            dv.DataSource = DB.TableSelect(sql);
        }
        public void MyShow()
        {
            string sql = @"SELECT d.DeptName,g.GroupName,p.PName
            FROM [2017].[dbo].[Patients] p ,[2017].[dbo].[Departments] d,[2017].[dbo].[Groups] g
            WHERE p.GroupNo =g.GroupNo and g.DeptNo=d.DeptNo
            order by d.DeptNo desc,g.GroupNo ,p.GroupNo desc";
            dv.DataSource = DB.TableSelect(sql);
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}

病人表维护窗体

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;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace _2017test
{
    public partial class FormPatients : Form
    {
        public FormPatients()
        {
            InitializeComponent();
            MyShow();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = @"INSERT INTO [2017].[dbo].[Patients]
                               ([PID]
                               ,[PName]
                               ,[Date]
                               ,[GroupNo])
                                VALUES(@PID,@PName,@Date,@GroupNo)";
            SqlParameter[] para ={    new SqlParameter("@PID", textBox1.Text),
                                      new SqlParameter("@PName", textBox2.Text),
                                      new SqlParameter("@Date", textBox3.Text),
                                      new SqlParameter("@GroupNo",textBox4.Text)};
            DB.TableChange(sql, para);
            MyShow();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = @"DELETE FROM [2017].[dbo].[Patients]
                              WHERE [PID] = @PID;";
            SqlParameter[] para = { new SqlParameter("@PID", textBox1.Text) };
            DB.TableChange(sql, para);
            MyShow();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql = @"UPDATE [2017].[dbo].[Patients]
                          SET  [PName]=@PName
                               ,[Date]=@Date
                               ,[GroupNo]=@GroupNo
                                WHERE [PID]=@PID;";
            SqlParameter[] para ={    new SqlParameter("@PID", textBox1.Text),
                                      new SqlParameter("@PName", textBox2.Text),
                                      new SqlParameter("@Date", textBox3.Text),
                                      new SqlParameter("@GroupNo",textBox4.Text)};
            DB.TableChange(sql, para);
            MyShow();
        }
        public void MyShow()
        {
            string sql = @"SELECT * 
                            FROM [2017].[dbo].[Patients]";
            dv.DataSource = DB.TableSelect(sql);
        }
    }
}

小科室表维护窗体

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 _2017test
{
    public partial class FormGroup : Form
    {
        public FormGroup()
        {
            InitializeComponent();
            MyShow();
        }
        //增
        private void button1_Click(object sender, EventArgs e)
        {
            string sql = @"INSERT INTO [2017].[dbo].[Groups]
                               ([GroupNo]
                               ,[GroupName]
                               ,[DeptNo]
                               ,[Month]
                               ,[Number])
                                VALUES(@GroupNo,@GroupName,@DeptNo,@Month,@Number)";
            SqlParameter[] para ={    new SqlParameter("@GroupNo", textBox1.Text),
                                      new SqlParameter("@GroupName", textBox2.Text),
                                      new SqlParameter("@DeptNo", textBox3.Text),
                                      new SqlParameter("@Month",textBox4.Text),
                                      new SqlParameter("@Number",textBox5.Text)};
            DB.TableChange(sql, para);
            MyShow();
        }
        //删
        private void button2_Click(object sender, EventArgs e)
        {
            string sql = @"DELETE FROM [2017].[dbo].[Groups]
                              WHERE [GroupNo] = @GroupNo;";
            SqlParameter[] para = { new SqlParameter("@GroupNo", textBox1.Text) };
            DB.TableChange(sql, para);
            MyShow();
        }
        //改
        private void button3_Click(object sender, EventArgs e)
        {
            string sql = @"UPDATE [2017].[dbo].[Groups]
                          SET  [GroupName]=@GroupName
                               ,[DeptNo]=@DeptNo
                               ,[Month]=@Month
                               ,[Number]=@Number
                                WHERE [GroupNo]=@GroupNo;";
            SqlParameter[] para ={    new SqlParameter("@GroupNo", textBox1.Text),
                                      new SqlParameter("@GroupName", textBox2.Text),
                                      new SqlParameter("@DeptNo", textBox3.Text),
                                      new SqlParameter("@Month",textBox4.Text),
                                      new SqlParameter("@Number",textBox5.Text)};
            DB.TableChange(sql, para);
            MyShow();
        }
        public void MyShow()
        {
            string sql = @"SELECT *
                            FROM [2017].[dbo].[Groups]  
                            WHERE [2017].[dbo].[Groups].Number=(select MAX([2017].[dbo].[Groups].Number) from [2017].[dbo].[Groups])   
                            OR [2017].[dbo].[Groups].Number=(select MIN([2017].[dbo].[Groups].Number) from [2017].[dbo].[Groups]);";
            dv.DataSource = DB.TableSelect(sql);
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }
    }
}

统计窗口

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _2017test
{
    public partial class FormStatistics : Form
    {
        public FormStatistics()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = @"SELECT a.DeptName 大科室名 ,sum(b.Number) 病人人数
                        FROM [2017].[dbo].[Departments] a ,[2017].[dbo].[Groups] b WHERE a.DeptNo=b.DeptNo
                        Group by a.DeptName";
  
            dv.DataSource = DB.TableSelect(sql);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = @"SELECT a.Month 月份 ,sum(a.Number) 病人人数
                        FROM [2017].[dbo].[Groups] a 
                        Group by a.Month";
  
            dv.DataSource = DB.TableSelect(sql);
        }
    }
}