ADO.NET Amocana N1

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

namespace AdoMarketDev
{
    public partial class MyForm : Form
    {
        // category list
        private List<string> categoryList = new List<string>();

        // price
        private int price;

        private SqlConnection con = new SqlConnection("Server=VAKHO-PC; Database=MarketDev; Integrated Security=true;");
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        private DataSet dataSet = null;

        public MyForm()
        {
            InitializeComponent();
        }

        private void MyForm_Load(object sender, EventArgs e)
        {
            // display categories inside the combobox
            this.updateCategoryList();
            comboBox1.Items.Clear();
            foreach (string name in categoryList)
            {
                comboBox1.Items.Add(name);
            }
            comboBox1.SelectedIndex = 0;
        }

        // refresh category list
        private void button2_Click(object sender, EventArgs e)
        {
            this.updateCategoryList();
            comboBox1.Items.Clear();
            foreach (string name in categoryList)
            {
                comboBox1.Items.Add(name);
            }
            comboBox1.SelectedIndex = 0;
        }

        // update categoryList function
        private void updateCategoryList()
        {
            categoryList.Clear(); // remove old data
            con.Open();
            SqlDataReader reader = new SqlCommand("SELECT * FROM Category", con).ExecuteReader();
            while (reader.Read())
            {
                categoryList.Add(reader["name"].ToString());
            }
            con.Close();
        }

        private void buttonSelect_Click(object sender, EventArgs e)
        {
            string priceStr = textBox2.Text.Trim();
            if (priceStr != "")
            {
                try
                {
                    price = Convert.ToInt32(priceStr); // may throw FormatException

                    // getting id by category name
                    con.Open();
                    int category_id = Convert.ToInt32(new SqlCommand("SELECT id FROM Category WHERE name=N'" + categoryList[comboBox1.SelectedIndex] + "'", con).ExecuteScalar());
                    dataAdapter.SelectCommand = new SqlCommand("SELECT name, price FROM Product WHERE price < " + this.price + " AND category_id = " + category_id, con);
                    MessageBox.Show(category_id.ToString());
                    dataSet = new DataSet("Products");
                    dataAdapter.Fill(dataSet, "Products");

                    // draw (redraw) dataGridView1
                    dataGridView1.ClearSelection();
                    dataGridView1.DataSource = dataSet.Tables["Products"];
                }
                catch (FormatException ex)
                {
                    MessageBox.Show("Unsupported price format!\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                catch (SqlException ex)
                {
                    MessageBox.Show("SQL Error!\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    con.Close();
                }
            }
            else
            {
                MessageBox.Show("The price box is empty!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        // add category button event
        private void button1_Click(object sender, EventArgs e)
        {
            string name = textBox1.Text.Trim();
            if (name != "")
            {
                con.Open();
                new SqlCommand("INSERT INTO Category (name) VALUES (N'" + name + "')", con).ExecuteNonQuery();
                con.Close();

                MessageBox.Show("New category has been added successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

                // refresh categoryList and comboBox
                updateCategoryList();
                comboBox1.Items.Clear(); // empty comboBox
                foreach (string tmp_name in categoryList)
                {
                    comboBox1.Items.Add(tmp_name);
                }
                comboBox1.SelectedIndex = 0;
            }
            else
            {
                MessageBox.Show("Category name box is empty!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
}