2017年计算机附属 数据库+c#

数据库创建

create database test120171;
go
use test120171
go
create table Department(
DeptNo int primary key not null,
DeptName varchar(20) not null)
go
create table pGroup(
GroupNo int Primary key,
GroupName varchar(20) not null,
DeptNo int not null,
Month int ,
Number int not null check(number<80),
Foreign key (DeptNo) references Department(DeptNo))
create table Patient(
PID int primary key,
PName varchar(20) not null,
PDate date not null,
GroupNo int,
Foreign key (GroupNo) references pGroup(GroupNo))
insert into Patient values('880101','李一','1980/6/1','11')
insert into Patient  values('880102','王二','1978/2/3','15')
insert into Patient  values('880103','张三','1969/11/2','13')
insert into Patient  values('880104','刘四','1975/7/20','13')
insert into Patient  values('880105','陈五','1986/5/16','14')
insert into Patient  values('880106','杨六','1998/4/23','13')
insert into Patient  values('880107','赵七','2000/3/1','12')
insert into Patient values('880108','周八','1992/7/5','11')
insert into pGroup values('11','心内科','111','3','32')
insert into pGroup values('12','呼吸内科','111','4','45')
insert into pGroup values('13','中医保健科','333','3','68')
insert into pGroup values('14','骨科','222','4','28')
insert into pGroup values('15','心外科','222','4','56')
insert into pGroup values('16','心内科','111','3','32')
insert into Department values('111','内科')
insert into Department values('222','外科')
insert into Department values('333','中医科')
go
select Department.DeptName,pGroup.GroupName,Patient.PName
from Department,pGroup,Patient
where Department.DeptNo=pGroup.DeptNo and pGroup.GroupNo=Patient.GroupNo 
group by Department.DeptName,pGroup.GroupName,Patient.PName
order by Department.DeptName desc,pGroup.GroupName asc,Patient.PName desc
go
SELECT Department.DeptName,SUM(pGroup.Number)人数
FROM Department,pGroup
WHERE Department.DeptNo=pGroup.DeptNo
GROUP BY Department.DeptName
go
select pGroup.Month,sum(Number)当月人数
from pGroup
group by pGroup.Month
go
SELECT MIN(Number)最少人数,MAX(Number)最多人数
from pGroup

c#
DBHelperSQL.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 test
{
    public class DBHelperSQL
    {
        public static string connStr = @"server=.;database=test120171;integrated security=SSPI;";
        public static SqlConnection conn = null;
        public static void TableChange(string sql,SqlParameter[] para=null)
        {
            try {
                SqlCommand cmd = new SqlCommand(sql, DBHelperSQL.conn);//connection=null；
                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, DBHelperSQL.conn); 
                if (para != null)
                    cmd.Parameters.AddRange(para);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);   //连接
                da.Fill(dt);//①Fill：执行存储于SelectCommand中的查询，并将结果存储在DataTable中。
                return dt;
                }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

             

            }
        }
    }

**

1. program.cs

**

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace test
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            try
            {
                DBHelperSQL.conn = new System.Data.SqlClient.SqlConnection(DBHelperSQL.connStr);//连接数据库
                DBHelperSQL.conn.Open();

            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            Application.Run(new Form1());
            DBHelperSQL.conn.Close();
        }
    }
}

多选框的应用

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;
using System.Data.SqlClient;

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

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = string.Format(@"select Department.DeptName,pGroup.GroupName,Patient.PName
                                         from Department,pGroup,Patient
                                         where Department.DeptNo=pGroup.DeptNo and pGroup.GroupNo=Patient.GroupNo ");
            if(checkboxdname.Checked)
            {
                if (textBox1.Text=="")
                {
                    MessageBox.Show("请输入大科室名");
                }
                else
                   sql += string.Format(" AND Department.DeptName='{0}'", textBox1.Text);
            }
            if (checkboxgroupname.Checked)
            {
                if (textBox2.Text == "")
                {
                    MessageBox.Show("请输入小科室名");
                }
                else
                    sql += string.Format(" AND pGroup.GroupName='{0}'", textBox2.Text);
            }
            if (checkboxpname.Checked)
            {
                if (textBox3.Text == "")
                {
                    MessageBox.Show("请输入病人名");
                }
                else
                    sql += string.Format(" AND Patient.PName='{0}'", textBox3.Text);
            }
            sql+=string.Format(@" group by Department.DeptName,pGroup.GroupName,Patient.PName
order by Department.DeptName desc, pGroup.GroupName asc, Patient.PName desc");
            dataGridView1.DataSource = DBHelperSQL.TableSelect(sql);
        }
    }

}