2017-D

数据库部分

• 使用Windows 身份验证登录SQL Server ，建立数据库test0322，文件日志保存到一个专门的文件夹
• 建表
• 备份数据库，选定所创建数据库，右键-任务-备份-选择自己建立的文件夹位置，文件名和后缀都要完整的打出来（如：backupInfo.bak)
• 打开VS，创建一个Windows 窗体应用(.NET Framework)，文件夹选择刚才创建的文件夹中的 /source文件夹，框架选择.NET Framework 2.0
• 在应用中创建DB.cs , Intent.cs, 以及自带的Form1窗体
• 写代码

项目解决方案内容：

DB.cs

using System;
using System.Collections.Generic;
using System.Data;//DataTable用到
using System.Data.SqlClient;//一些数据库操作类用到
using System.Text;

namespace test0322
{
    internal class DB : IDisposable
    {
        private SqlConnection sqlConnection;
        public DB() 
        {   //采用Windows 身份验证，关键词Trusted_Connection=SSPI
            sqlConnection = new SqlConnection(@"server=.;database=test0322;Trusted_Connection=SSPI");
            sqlConnection.Open();
        }
        //查询
        public DataTable GetBySql(string sql)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sql,sqlConnection));
            DataTable dt = new DataTable();
            sqlDataAdapter.Fill(dt);
            return dt;
        }
        //相当于析构函数
        public void Dispose()
        {
           sqlConnection.Close();
        }
    }
}

Intent.cs

//本次工程未用到
using System;
using System.Collections.Generic;
using System.Text;

namespace test0322
{
    internal class Intent
    {
        //存数数据的字典容器
        public static Dictionary<string,Object> data = new Dictionary<string,Object>();
    }
}

Form1.cs

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

namespace test0322
{
    public partial class Form1 : Form
    {
        DB db;
        public Form1()
        {
            InitializeComponent();
            db = new DB();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            listView2.Columns.Add("书名", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("出版社", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("借阅次数", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            DataTable dt = db.GetBySql(@"select [books].[b_name] as '书名',[books].[b_press] as '出版社',count([borrows].[b_id])as '借阅次数'  from [borrows],[books] where [borrows].[b_id]=[books].[b_id] group by [books].[b_id],[books].[b_name],[books].[b_press] order by '借阅次数' desc;");
            listView2.BeginUpdate();
            for(int i=0; i<dt.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for(int j=0; j<dt.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = dt.Rows[i][j].ToString();
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dt.Rows[i][j].ToString());
                    }
                }
                listView2.Items.Add(listViewItem);
            }
            listView2.EndUpdate();
        }

        //查询按钮
        private void button1_Click(object sender, EventArgs e)
        {
            listView1.Clear();
            listView1.Columns.Add("书号", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView1.Columns.Add("书名", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView1.Columns.Add("出版社", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            listView1.Columns.Add("借阅日期", listView1.Width / 4 - 1, HorizontalAlignment.Left);
            DataTable dt = db.GetBySql(@"select [books].[b_id] as '书号',[books].[b_name] as'书名',[books].[b_press] as '出版社',[borrows].[b_date] as '借阅日期' from [books],[borrows],[readers] where [readers].[r_name]='"+this.textBox1.Text+"' and [borrows].[r_id]=[readers].[r_id] and [borrows].[b_id]=[books].[b_id];");
            listView1.BeginUpdate();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = dt.Rows[i][j].ToString();
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dt.Rows[i][j].ToString());
                    }
                }
                listView1.Items.Add(listViewItem);
            }
            listView1.EndUpdate();
        }

        //输入完Enter可代替查询按钮
        private void textBox1_KeyDown(object sender, KeyEventArgs e)
        {
            //一旦用户在输入过程中输入Enter，则执行Button1的查询按钮点击事件
            if(e.KeyCode == Keys.Enter)
            {
                this.button1_Click(sender, e);
            }
        }
    }
}

效果