C#开发数据库第三章课后题5

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


namespace WindowsFormsApplication2
{
    class DBHelper
    {
        private string connString = @"Data Source=.;Initial Catalog=GoodsMenu;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();
            }
        }
    }
}









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

        private void Form1_Load(object sender, EventArgs e)
        {
            comboBox1.Items.Add("请选择");
            comboBox1.SelectedIndex = 0;
            BindGrade();
            AddGoods();

        }

        DBHelper db = new DBHelper();
        public string CAPTION = "提示输入";

        public bool BindGrade()
        {

            try
            {

                string sql = "SELECT * FROM  type";
                db.OpenConnection();
                SqlCommand comm = new SqlCommand(sql, db.Connection);

                SqlDataReader myReader = comm.ExecuteReader();
                while (myReader.Read())
                {
                    string type = myReader["Type"].ToString();
                    this.comboBox1.Items.Add(type);
                }
                myReader.Close();
                return true;
            }
            catch (Exception)
            {

                MessageBox.Show("系统发生错误!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            finally
            {
                db.CloseConnection();
            }
        }


        
        public void AddGoods()
        {
            string strsql = ("SELECT  [Name],[Type],[Number],[Price] FROM [Godds]");

            try
            {
                SqlCommand comm = new SqlCommand(strsql, db.Connection);
                db.OpenConnection();
                SqlDataReader reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    string Type = reader["Type"].ToString();
                    string Name = reader["Name"].ToString();
                    int number = Convert.ToInt32(reader["Number"]);
                    int price= Convert.ToInt32(reader["Price"]);

                    ListViewItem item = new ListViewItem(Name);
                    item.SubItems.AddRange(new string[] { Type, number.ToString(), price.ToString() });
                    this.listView1.Items.Add(item);
                }
                reader.Close();
            }



            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                db.CloseConnection();
            }
        }

        public string caption = "输入提示";

        public void search()
        {
            if (listView1.Items.Count > 0)
            {
                listView1.Items.Clear();
            }

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT  [Name],[Type],[Number],[Price] FROM [Godds]");
            sb.AppendFormat("  WHERE  [Type] LIKE '{0}'", this.comboBox1.Text);

            SqlCommand comm = new SqlCommand(sb.ToString(), db.Connection);
            db.OpenConnection();
            SqlDataReader reader = comm.ExecuteReader();

            try
            {
                if (!reader.HasRows)
                {
                    MessageBox.Show("没有要查找的记录!", caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {

                    while (reader.Read())
                    {
                        string Type = reader["Type"].ToString();
                        string Name = reader["Name"].ToString();
                        int number = Convert.ToInt32(reader["Number"]);
                        int price = Convert.ToInt32(reader["Price"]);

                        ListViewItem item = new ListViewItem(Name);
                        item.SubItems.AddRange(new string[] { Type, number.ToString(), price.ToString() });
                        this.listView1.Items.Add(item);
                    }
                    reader.Close();
                }

            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
            finally
            {
                db.CloseConnection();
            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            search();
        }

    }
}

`