代码编织梦想

        最近由于考研复试需要,记录与总结一下自己的学习所得。

        事先备注开发环境(VS2015+Sqlserver2012),主要以分享代码及注意事项为主,具体实现背后的原理,看到此文的有缘者可自行深入学习。

1.如何在sqlserver中建立数据表与插入数据?                                                                                   

 以建立员工关系表EMPLOYEE,工作关系表WORKS和公司关系表COMPANY为例:

初始表数据如下:

EMPLOYEE
EmpNoEmpNmaeEmpSexEmpAge
E01张三32
E02李四28
E03王五42
E04赵六37
WORKS
EmpNoCmpNoSalary
E01C013000
E01C024000
E02C025000
E02C032500
E03C013500
E04C023000
COMPANY
CmpNoCmpName
C01阳光科技
C02晨光科技
C03未来科技

代码段如下:

create table [EMPLOYEE](
        EmpNo varchar(8) not null primary key,
        EmpName varchar(30) not null,
        EmpSex varchar(2) not null,
        EmpAge int check (EmpAge > -1)
)
create table [COMPANY](
        CmpNo varchar(8) not null primary key,
        CmpName varchar(30) not null
)
create table [WORKS](
        EmpNo varchar(8) not null references EMPLOYEE(EmpNo),
        CmpNo varchar(8) not null references COMPANY(CmpNo),
        Salary int check(Salary > -1)
)

 注意事项:1.对于主键的设置,一张表只能有一个主键,使用primary key进行设置;

                    2.建表顺序,前面所给表顺序其实带有一个陷阱,即WORKS中的EmpNo与CmpNo是需依赖于另外两张表的,故建表顺序需进行相应调整,后引用外键可用references;

                    3.表名使用[ ]修饰起来,是为了防止与sql语句关键字可能会发生冲突。

2.窗体程序如何与sqlserver建立连接,且如何对数据库中表信息进行增删改查等操作?

基于面向对象的思想,我们可将与数据库进行交互的这类动作封装到一个类中,类名为:sql_operate,具体实现如下:

using System;
using System.Collections.Generic;
using System.Text;
//下两个使用的是实现功能的关键
using System.Data;
using System.Data.SqlClient;
namespace [自己的项目名]
{
    class sql_operate : iDisposable
    {
        private SqlConnection sqlConnection;
        public DB ()
        {
            sqlConnection = new SqlConnection (@"server=服务器名称;database=数据库                            
                                              名;Trusted_connection=SSPI");
            sqlConnection.Open();
        }
        //获取数据库返回数据(以Datatable类型存储)
        public DataTable getbysql(string sql)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sql,     
                                                               sqlConnection));
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            return dataTable;
        }
        //进行增删改操作
        public void setbyssql(string sql)
        {
            new SqlCommand(sql, sqlConnection).ExecuteNonQuery();
        }
        //将连接进行关闭
        public void Dispose()
        {
            sqlConnection.Close();
        }
    }
}


3.窗体程序实现目标为:

1.实现根据输入的员工名查询员工所在公司及薪水,并在窗体进行显示,且按薪水增序排列;

2.实现根据公司名(下拉选择)查询员工信息,即员工号、员工名、公司名与薪水;

且要求两功能需在不同界面实现。

界面如下所示:

 

 

实现代码端如下:(涉及Listview、comboBox与button等控件的使用)

        DB db;
        public Form1()
        {
            InitializeComponent();
            db = new DB();
        }

        private void Form(object sender, EventArgs e)
        {
            //加载Combobox栏中数据
            //select CmpName 
            // from COMPANY
            DataTable dataTable = db.getbysql(@"select CmpName from COMPANY");
            for(int i = 0;i < dataTable.Rows.Count; i++)
            {
                for(int j = 0;j < dataTable.Columns.Count; j++)
                {
                    comboBox1.Items.Add(dataTable.Rows[i][j] + "");
                }
            }
            comboBox1.SelectedIndex = 0;
        }
        private void button1_click(object sender, EventArgs e)
        {
            if(textBox1.Text == "")
            {
                MessageBox.Show("请输入想要查询的员工名", this.Text);
            }
            else
            {
                /*
                select COMPANY.CmpName, t.Salary
                from COMPANY,
                (select WORKS.CmpNo,WORKS.Salary
                from WORKS
                where WORKS.EmpNo in (
	                select EMPLOYEE.EmpNo
	                from EMPLOYEE
	                where EMPLOYEE.EmpName = '张三')
                ) as t
                where t.CmpNo = COMPANY.CmpNo
                */
               //表头的加载
                listView1.Columns.Add("公司名", listView2.Width / 2 - 1,             
                                      HorizontalAlignment.Left);
                listView1.Columns.Add("薪水", listView2.Width / 2 - 1, 
                                       HorizontalAlignment.Left);
                DataTable dataTable = db.getbysql(@"select COMPANY.CmpName, t.Salary "
                    + "from COMPANY, " + "(select WORKS.CmpNo,WORKS.Salary "
                    + "from WORKS " + "where WORKS.EmpNo in ( " + "select EMPLOYEE.EmpNo " 
                    +"from EMPLOYEE "
                    + "where EMPLOYEE.EmpName = '"+textBox1.Text+"') " + ") as t " 
                    + "where t.CmpNo = COMPANY.CmpNo "
                    );
                //加快listview的更新速度
                listView1.BeginUpdate();
                // 表数据加载至listview
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    ListViewItem listViewItem = new ListViewItem();
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        if (j <= 0)
                        {
                            listViewItem.Text = dataTable.Rows[i][j] + "";
                        }
                        else
                        {
                            listViewItem.SubItems.Add(dataTable.Rows[i][j] + "");
                        }
                    }
                    listView1.Items.Add(listViewItem);
                }
                //结束更新
                listView1.EndUpdate();

            }
        }
        private void button2_click(object sender, EventArgs e)
        {
            if(comboBox1.Text != "")
            {
                listView2.Clear();
                /*
                select EMPLOYEE.EmpNo, EMPLOYEE.EmpName,COMPANY.CmpName,t.Salary
                from EMPLOYEE,
                (select WORKS.EmpNo,WORKS.CmpNo,WORKS.Salary
                from WORKS
                where WORKS.CmpNo in(
                select COMPANY.CmpNo
                from COMPANY
                where COMPANY.CmpName = '晨光科技')) as t,
                COMPANY
                where EMPLOYEE.EmpNo = t.EmpNo
                and t.CmpNo = COMPANY.CmpNo
                */
                listView2.Columns.Add("员工号",listView2.Width/4-        
                                      1,HorizontalAlignment.Left);
                listView2.Columns.Add("员工名", listView2.Width / 4 - 1, 
                                       HorizontalAlignment.Left);
                listView2.Columns.Add("公司名", listView2.Width / 4 - 1, 
                                       HorizontalAlignment.Left);
                listView2.Columns.Add("薪水", listView2.Width / 4 - 1, 
                                       HorizontalAlignment.Left);
                DataTable dataTable = db.getbysql(@"select EMPLOYEE.EmpNo, 
                    EMPLOYEE.EmpName,COMPANY.CmpName,t.Salary "
                    + "from EMPLOYEE, "+ "(select WORKS.EmpNo,WORKS.CmpNo,WORKS.Salary "
                    + "from WORKS "+ "where WORKS.CmpNo in( "+ "select COMPANY.CmpNo "
                    + "from COMPANY "
                    + "where COMPANY.CmpName = '"+comboBox1.Text+"')) as t, "+ "COMPANY "
                    + "where EMPLOYEE.EmpNo = t.EmpNo "
                    + "and t.CmpNo = COMPANY.CmpNo");
                listView2.BeginUpdate();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    ListViewItem listViewItem = new ListViewItem();
                    for(int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        if (j <= 0)
                        {
                            listViewItem.Text = dataTable.Rows[i][j] + "";
                        }
                        else
                        {
                            listViewItem.SubItems.Add(dataTable.Rows[i][j] + "");
                        }
                    }
                    listView2.Items.Add(listViewItem);
                }
                listView2.EndUpdate();
            }
            else
            {
                MessageBox.Show("请选择需查询的公司", this.Text);
            }
            
        }

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weixin_45902468/article/details/129601050