ACCP C#Windows 第三章课后5

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 a
{
    
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            flv();
        }
        private void flv()
        {
            if (listView1.Items.Count > 0)
            {
                listView1.Items.Clear();
            }
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("select name,type,number,price from shop");
            sql.AppendFormat("where type like '%{0}%'", this.comboBox1.Text.Trim());
            DBHelper d = new DBHelper();
            try
            {
                SqlCommand com = new SqlCommand(sql.ToString(), d.Connection);
                d.OpenConnection();
                SqlDataReader r = com.ExecuteReader();
                if (!r.HasRows)
                {
                    MessageBox.Show("没有要查找的记录!", "CAPTION", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    while (r.Read())
                    {
                        string no = r["name"].ToString();
                        string name = r["type"].ToString();
                        string gender = r["number"].ToString();
                        string gradename = r["price"].ToString();
                        ListViewItem i = new ListViewItem(no);
                        i.SubItems.Add(name);
                        i.SubItems.Add(gender);
                        i.SubItems.Add(gradename);
                        listView1.Items.Add(i);
                    }
                }
                r.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("系统出现错误!", "CAPTION", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                d.CloseConnection();
            }
        }

        private void Form2_Load(object sender, EventArgs e)
        {

        }
    }
    public class DBHelper
    {
        // 数据库连接字符串
        private string connString = @"Data Source=.;Initial Catalog=MySchool;Integrated Security=True";

        // 数据库连接 Connection 对象
        private SqlConnection connection;

        /// <summary>
        /// Connection对象
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connString);
                }
                return connection;
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if (Connection.State == ConnectionState.Closed)
            {
                Connection.Open();
            }
            else if (Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
                Connection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
            }
        }
    }
}