มันลด ทรัพยากรได้มากกว่านี้มั้ยครับ C# (.NET) WinApp (Windows Application)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Drawing.Drawing2D;
using System.Diagnostics;
using books.Class;

namespace books
{
    public partial class EditBook : Form
    {
        FileStream fs;
        DateTime Today;
        Bitmap CBMP;
        byte[] CurrentImage = null;
        string picFileName;
        SqlConnection Conn;
        SqlCommand com;
        SqlDataReader dr;
        DataTable dtBookType;
        DataTable dtBooks;
        DataTable dtPub;
        StringBuilder sb;
        SqlTransaction tr;
        
        List<RadioButton> RadioArray;

        public EditBook()
        {
            InitializeComponent();
            RadioArray = new List<RadioButton>();
        }

        private void EditBook_Load(object sender, EventArgs e)
        {

            this.WindowState = FormWindowState.Maximized;

            string strConn;
            strConn = DBConnString.strConn;
            Conn = new SqlConnection();
            if (Conn.State == ConnectionState.Open)
            {
                Conn.Close();
            }
            Conn.ConnectionString = strConn;
            Conn.Open();
            tsAdd.Enabled = true;
            tsOK.Enabled = false;
            tsCancel.Enabled = false;
            picbook.Image = picbook.ErrorImage;
          
        }
        private void ShowBooks()
        {
            string sqlIni;
            sb = new StringBuilder();

            sb.Append("SELECT * FROM Books ORDER BY IDBook");
            
            sqlIni = sb.ToString();
            com = new SqlCommand();
            com.CommandText = sqlIni;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;
            dr = com.ExecuteReader();

            if (dr.HasRows)
            {
                dtBooks = new DataTable();
                dtBooks.Load(dr);
                dgvPositionList.DataSource = dtBooks;

            }
            FormatDgvPositionList();
            dr.Close();
        }


        private void FormatDgvPositionList()
        {
            if (dgvPositionList.RowCount > 0)
            {
                dgvPositionList.Columns[0].HeaderText = "รหัสหนังสือ";
                dgvPositionList.Columns[1].HeaderText = "ชื่อหนังสือ";
                dgvPositionList.Columns[2].HeaderText = "วันที่ซื้อ";
                dgvPositionList.Columns[3].HeaderText = "ผู้แต่ง";
                dgvPositionList.Columns[4].HeaderText = "สำนักพิมพ์";
                dgvPositionList.Columns[5].Visible = false;

            }
        }

        private void ShowBooktype()
        {

            sb = new StringBuilder();

            sb.Append("SELECT * FROM BookType ORDER BY IDBookType");
            string sqlIni;
            sqlIni = sb.ToString();
            com = new SqlCommand();
            com.CommandText = sqlIni;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;
            dr = com.ExecuteReader();

            if (dr.HasRows)
            {

                dtBookType = new DataTable();
                dtBookType.Load(dr);
                checkedListBox1.BeginUpdate();
                checkedListBox1.DataSource = dtBookType;
                checkedListBox1.DisplayMember = "NameBookType";
                checkedListBox1.ValueMember = "IDBookType";
                checkedListBox1.EndUpdate();

            }
            dr.Close();
        }
        private void ShowPub()
        {
            sb = new StringBuilder();
            sb.Append("SELECT * FROM Publishing ORDER BY IDPub");
            string sqlIni;
            sqlIni = sb.ToString();
            com = new SqlCommand();
            com.CommandText = sqlIni;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;
            dr = com.ExecuteReader();

            if (dr.HasRows)
            {
                int point_y = 0;
                dtPub = new DataTable();
                dtPub.Load(dr);

                foreach (DataRow DS in dtPub.Rows)
                {

                    RadioButton aRadioButton = new RadioButton();
                    aRadioButton.Name = DS["IDPub"].ToString();
                    aRadioButton.Text = DS["NamePub"].ToString();
                    aRadioButton.AutoSize = true;
                    aRadioButton.Location = new Point(0, 0 + point_y);

                    listBox1.Controls.Add(aRadioButton);
                    listBox1.Height = point_y + 40;
                    listBox1.Items.Add("");

                    point_y += 20;
                    RadioArray.Add(aRadioButton);

                }
            }

        }

      
        private void picbook_Click(object sender, EventArgs e)
        {
            ofDlg.Title = "เลือกไฟล์รูปภาพ";
            ofDlg.Filter = "JPEG (*.jpg)|*.jpg";
            ofDlg.FileName = "";
            ofDlg.Multiselect = false;
            ofDlg.FilterIndex = 2;

            if (ofDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                picFileName = ofDlg.FileName;
                CBMP = new Bitmap(picFileName);
                if (CBMP != null)
                {
                    if (CBMP.Height > 182 || CBMP.Width > 135)
                    {
                        MessageBox.Show("ไฟล์รูปภาพที่ใช้ ต้องมีขนาดความกว้างไม่เกิน 135 และความยาวไม่เกิน 182 pixels !!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
                }

                picbook.Image = Image.FromFile(picFileName);
            }
            else
            {
                picFileName = "";
                picbook.Image = picbook.ErrorImage;
            }
        }

        private void tsAdd_Click(object sender, EventArgs e)
        {
            
            ShowBooks();
            ShowBooktype();
            ShowPub();
            Clearbook();


            tsAdd.Enabled = false;
            tsOK.Enabled = true;
            tsCancel.Enabled = true;
            txtNameBook.Focus();
        }
        private void Clearbook()
        {
            Today = DateTime.Today;
            txtIDBook.Text = "";
            txtNameBook.Text = "";
            txtAuther.Text = "";
            dtpbuy.Value = Today;
            foreach (int checkedItemIndex in checkedListBox1.CheckedIndices)
            {
                checkedListBox1.SetItemChecked(checkedItemIndex, false);

            }
            foreach (RadioButton aRadioButton in RadioArray)
            {
                aRadioButton.Checked = false;
            }

            picFileName = "";
            picbook.Image = picbook.ErrorImage;
        }

        private void tsCancel_Click(object sender, EventArgs e)
        {
            dgvPositionList.DataSource = null;
            dataGridView2.DataSource = null;
            checkedListBox1.DataSource = null;
            listBox1.Controls.Clear();
            
            Clearbook();
            tsAdd.Enabled = true;
            tsOK.Enabled = false;
            tsCancel.Enabled = false;
            txtNameBook.Focus();
        }
        private void FormatdataGridView2()
        {
            if (dataGridView2.RowCount > 0)
            {
                dataGridView2.Columns[0].HeaderText = "รหัสประเภท";
                dataGridView2.Columns[1].HeaderText = "ประเภทหนังสือ";

            }
        }

        private void dgvPositionList_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            txtIDBook.Text = dgvPositionList.Rows[e.RowIndex].Cells[0].Value.ToString();
            txtNameBook.Text = dgvPositionList.Rows[e.RowIndex].Cells[1].Value.ToString();
            txtAuther.Text = dgvPositionList.Rows[e.RowIndex].Cells[3].Value.ToString();
            dtpbuy.Value = Convert.ToDateTime(dgvPositionList.Rows[e.RowIndex].Cells[2].Value.ToString());
            
            sb.Remove(0, sb.Length);
            sb.Append("SELECT TOP 1 IDBook,Pic");
            sb.Append(" FROM Books");
            sb.Append(" WHERE (IDBook=@IDBook)");
            string sqlPicture;
            sqlPicture = sb.ToString();

            com.CommandText = sqlPicture;
            com.CommandType = CommandType.Text;
            com.Parameters.Clear();
            com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = dgvPositionList.Rows[e.RowIndex].Cells[0].Value.ToString();
            dr = com.ExecuteReader();

            if (dr.HasRows)
            {
                DataTable dtPicture = new DataTable();
                dtPicture.Load(dr);

                picbook.DataBindings.Clear();
                picbook.DataBindings.Add("Image", dtPicture, "Pic", true, DataSourceUpdateMode.Never, picbook.ErrorImage);
               
                picbook.Enabled = true;

            }
            else
            {
                picbook.Image = picbook.ErrorImage;

                picbook.Enabled = true;

            }
            dr.Close();

            sb = new StringBuilder();
            sb.Append("SELECT TypeDetail.IDBookType,BookType.NameBookType  FROM BookType");
            sb.Append(" INNER JOIN TypeDetail ON BookType.IDBookType = TypeDetail.IDBookType");
            sb.Append(" INNER JOIN Books ON TypeDetail.IDBook = Books.IDBook");
            sb.Append(" WHERE  TypeDetail.IDBook = @IDBook");
            string sqlIni;
            sqlIni = sb.ToString();

            com.Parameters.Clear();
            com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = dgvPositionList.Rows[e.RowIndex].Cells[0].Value.ToString();
            com.CommandText = sqlIni;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;
            dr = com.ExecuteReader();
            if (dr.HasRows)
            {
                DataTable dttype = new DataTable();
                dttype.Load(dr);

                dataGridView2.DataSource = dttype;
            }
            else
            {
                dataGridView2.DataSource = null;
            }

            int NRow = dataGridView2.RowCount;
            if (NRow != 0)
            {
                foreach (int checkedItemIndex in checkedListBox1.CheckedIndices)
                {
                    checkedListBox1.SetItemChecked(checkedItemIndex, false);
                }
                for (int i = 0; i < NRow; i++)
                    this.checkedListBox1.SetItemChecked(Convert.ToInt32(dataGridView2.Rows[i].Cells[0].Value.ToString()) - 1, true);


            }
            else
            {

                foreach (int checkedItemIndex in checkedListBox1.CheckedIndices)
                {
                    checkedListBox1.SetItemChecked(checkedItemIndex, false);
                }

            }
            int mRow = dgvPositionList.RowCount;
            if (mRow != 0)
            {

                foreach (RadioButton aRadioButton in RadioArray)
                {
                    aRadioButton.Checked = false;
                }
                foreach (RadioButton aRadioButton in RadioArray)
                {
                    if (aRadioButton.Name == dgvPositionList.Rows[e.RowIndex].Cells[4].Value.ToString())
                    {
                        aRadioButton.Checked = true;
                    }
                    else
                    {
                        aRadioButton.Checked = false;
                    }
                }
            }
            FormatdataGridView2();
            dr.Close();
        }
        private void ShowHumanPicture()
        {
            
        }

        private void tsOK_Click(object sender, EventArgs e)
        {
          if ((txtNameBook.Text.Trim() == "") || (txtAuther.Text.Trim() == ""))
            {
                MessageBox.Show("กรุณาป้อนข้อมูลให้ครบทุกช่อง !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtNameBook.Focus();
                return;
            }
                        
            int IsChecked = 0;

            foreach (RadioButton aRadioButton in RadioArray)
            {
                if (aRadioButton.Checked == true)
                {
                   IsChecked++;
                }
            }
            if (IsChecked == 0)
            {
                MessageBox.Show("กรุณาเลือกสำนักพิมพ์", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtNameBook.Focus();
                return;
            }
            int IsCheckedbox = 0;
            foreach (System.Data.DataRowView itm in checkedListBox1.CheckedItems)
            {
                IsCheckedbox++;
            }
            if (IsCheckedbox == 0)
            {
                MessageBox.Show("กรุณาเลือกประเภทหนังสือ", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtNameBook.Focus();
                return;
            }

            if (picFileName != "")
            {

                fs = new FileStream(picFileName, FileMode.Open, FileAccess.Read);
                CurrentImage = new byte[((int)fs.Length) + 1];
                fs.Read(CurrentImage, 0, ((int)fs.Length) - 1);
                fs.Close();



                if (MessageBox.Show("คุณต้องการแก้ไข ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
                {

                    tr = Conn.BeginTransaction();

                    try
                    {
                        sb.Remove(0, sb.Length);
                        sb.Append("UPDATE Books");
                        sb.Append(" SET ");
                        sb.Append(" NameBook=@NameBook,");
                        sb.Append(" DayBook=@DayBook,");
                        sb.Append(" Auther=@Auther,");
                        sb.Append(" IDPub=@IDPub,");
                        sb.Append(" Pic=@Pic");
                        sb.Append(" WHERE (IDBook=@IDBook)");
                        string sqlAdd;
                        sqlAdd = sb.ToString();

                        com.CommandText = sqlAdd;
                        com.CommandType = CommandType.Text;
                        com.Connection = Conn;
                        com.Transaction = tr;
                        com.Parameters.Clear();
                        com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                        com.Parameters.Add("@NameBook", SqlDbType.NVarChar).Value = txtNameBook.Text;
                        foreach (RadioButton aRadioButton in RadioArray)
                        {
                            if (aRadioButton.Checked == true)
                            {
                                com.Parameters.Add("@IDPub", SqlDbType.NVarChar).Value = aRadioButton.Name;
                            }
                        }
                        com.Parameters.Add("@DayBook", SqlDbType.DateTime).Value = dtpbuy.Value;
                        com.Parameters.Add("@Auther", SqlDbType.NVarChar).Value = txtAuther.Text;
                        com.Parameters.Add("@Pic", SqlDbType.VarBinary).Value = CurrentImage;
                        com.ExecuteNonQuery();
                        string sqlOK2;
                       
                            sb.Remove(0, sb.Length);
                            sb.Append("DELETE TypeDetail WHERE IDBook=@IDBook");
                            
                            sqlOK2 = sb.ToString();

                            com.CommandText = sqlOK2;
                            com.Parameters.Clear();
                            com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                         
                            com.ExecuteNonQuery();
                   
                        string ListChecked = "";
                        foreach (System.Data.DataRowView itm in checkedListBox1.CheckedItems)
                        {
                            string sqlOK1;
                            ListChecked = itm.Row["IDBookType"].ToString();
                            sb.Remove(0, sb.Length);
                            sb.Append("INSERT INTO TypeDetail (IDBook,IDBookType)");
                            sb.Append(" VALUES (@IDBook,@IDBookType)");
                            sqlOK1 = sb.ToString();

                            com.CommandText = sqlOK1;
                            com.Parameters.Clear();
                            com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                            com.Parameters.Add("@IDBookType", SqlDbType.NVarChar).Value = ListChecked;
                            com.ExecuteNonQuery();
                        }
                        tr.Commit();

                        dgvPositionList.DataSource = null;
                        dataGridView2.DataSource = null;
                        

                        MessageBox.Show("แก้ไข เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ShowBooks();
                    }
                    catch
                    {
                        MessageBox.Show("คุณป้อนรหัสซ้ำ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        tr.Rollback();
                    }
                }
            }
            else {
                if (MessageBox.Show("คุณต้องการแก้ไข ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
                {

                    tr = Conn.BeginTransaction();

                    try
                    {
                        sb.Remove(0, sb.Length);
                        sb.Append("UPDATE Books");
                        sb.Append(" SET ");
                        sb.Append(" NameBook=@NameBook,");
                        sb.Append(" DayBook=@DayBook,");
                        sb.Append(" Auther=@Auther,");
                        sb.Append(" IDPub=@IDPub");
                        sb.Append(" WHERE (IDBook=@IDBook)");
                        string sqlAdd;
                        sqlAdd = sb.ToString();

                        com.CommandText = sqlAdd;
                        com.CommandType = CommandType.Text;
                        com.Connection = Conn;
                        com.Transaction = tr;
                        com.Parameters.Clear();
                        com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                        com.Parameters.Add("@NameBook", SqlDbType.NVarChar).Value = txtNameBook.Text;
                        foreach (RadioButton aRadioButton in RadioArray)
                        {
                            if (aRadioButton.Checked == true)
                            {
                                com.Parameters.Add("@IDPub", SqlDbType.NVarChar).Value = aRadioButton.Name;
                            }
                        }
                        com.Parameters.Add("@DayBook", SqlDbType.DateTime).Value = dtpbuy.Value;
                        com.Parameters.Add("@Auther", SqlDbType.NVarChar).Value = txtAuther.Text;
                        
                        com.ExecuteNonQuery();

                        string sqlOK2;
                        sb.Remove(0, sb.Length);
                        sb.Append("DELETE TypeDetail WHERE IDBook=@IDBook");
                      
                        sqlOK2 = sb.ToString();

                        com.CommandText = sqlOK2;
                        com.Parameters.Clear();
                        com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                       
                        com.ExecuteNonQuery();

                        string ListChecked = "";
                        foreach (System.Data.DataRowView itm in checkedListBox1.CheckedItems)
                        {
                            string sqlOK1;
                            ListChecked = itm.Row["IDBookType"].ToString();
                            sb.Remove(0, sb.Length);
                            sb.Append("INSERT INTO TypeDetail (IDBook,IDBookType)");
                            sb.Append(" VALUES (@IDBook,@IDBookType)");
                            sqlOK1 = sb.ToString();

                            com.CommandText = sqlOK1;
                            com.Parameters.Clear();
                            com.Parameters.Add("@IDBook", SqlDbType.NVarChar).Value = txtIDBook.Text;
                            com.Parameters.Add("@IDBookType", SqlDbType.NVarChar).Value = ListChecked;
                            com.ExecuteNonQuery();
                        }
                        tr.Commit();

                        dgvPositionList.DataSource = null;
                        dataGridView2.DataSource = null;
                        
                        MessageBox.Show("แก้ไข เรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ShowBooks();
                   
                    }
                    catch
                    {
                        MessageBox.Show("คุณป้อนรหัสซ้ำ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        tr.Rollback();
                    }
                }
            
            }
            txtIDBook.Focus();
        
        }

  

    }
}