-4

I'm making a system and i notice something on my codes My code is not optimized but it's working i want to lessen the redundancy of my codes here an example

this is how i load

private void frmCategory_Load(object sender, EventArgs e)
    {
        //Populate
        con.Open();
        SqlCommand sc = new SqlCommand("SELECT ID,Category FROM Category WHERE Active = 1",con);
        SqlDataReader reader;
        reader = sc.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Columns.Add("ID",typeof(int));
        dt.Columns.Add("Category",typeof(string));
        dt.Load(reader);

        for(int x = 0; x <dt.Rows.Count; x++)
        {
            string ID = dt.Rows[x].ItemArray[0].ToString();
            string Category = dt.Rows[x].ItemArray[1].ToString();
            string[] row = { ID,Category };
            dgvCategory.Rows.Add(row);
        }
        con.Close();
    }

this is how i add

ry//Add
        {
            con.Open();
            SqlCommand sc = new SqlCommand("INSERT INTO Category([Category]) VALUES(@Category)", con);
            {
                sc.Parameters.AddWithValue("@Category", txtCategory.Text);
                sc.ExecuteNonQuery();
                MessageBox.Show(txtCategory.Text + " is added");
                txtCategory.Clear();
            }
            con.Close();
        }
        catch (SystemException ex)
        {
            MessageBox.Show(ex.ToString());
        }
        try//Refresh
        {
            dgvCategory.Rows.Clear();
            SqlCommand sc = new SqlCommand("SELECT ID,Category FROM Category WHERE Active = 1", con);
            SqlDataReader reader;
            reader = sc.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Category", typeof(string));
            dt.Load(reader);

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                string ID = dt.Rows[x].ItemArray[0].ToString();
                string Category = dt.Rows[x].ItemArray[1].ToString();
                string[] row = { ID, Category };
                dgvCategory.Rows.Add(row);
            }
            con.Close();
        }
        catch (SystemException ex)
        {
            MessageBox.Show(ex.ToString());
        }

this is how i update

try//Update
        {
            string ID = dgvCategory.SelectedCells[0].Value.ToString();
            con.Open();
            SqlCommand sc = new SqlCommand("UPDATE Category SET Category = '" + txtCategory.Text + "' WHERE ID = '" + ID + "'", con);
            sc.ExecuteNonQuery();
            MessageBox.Show(dgvCategory.SelectedCells[1].Value.ToString() + " is updated to " + txtCategory.Text);
            con.Close();
        }
        catch (SystemException ex)
        {
            MessageBox.Show(ex.ToString());
        }
        try
        {
            dgvCategory.Rows.Clear();
            SqlCommand sc = new SqlCommand("SELECT ID,Category FROM Category WHERE Active = 1", con);
            SqlDataReader reader;
            reader = sc.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Category", typeof(string));
            dt.Load(reader);

            for (int x = 0; x < dt.Rows.Count; x++)
            {
                string ID = dt.Rows[x].ItemArray[0].ToString();
                string Category = dt.Rows[x].ItemArray[1].ToString();
                string[] row = { ID, Category };
                dgvCategory.Rows.Add(row);
            }
            con.Close();
        }
        catch (SystemException ex)
        {
            MessageBox.Show(ex.ToString());
        }

if you can i see. i always use the code from the load to populate my DGV again and that's how i refresh. i wonder if i can just make a block of code and name it LOAD then ill just use LOAD whenever i need to that code again

1 Answers1

0

This is what you call "boilerplate". It's common for SQL code. The best example I know of for eliminating it is Spring's JdbcTemplate.

You need to look at the code in these methods and figure out how to refactor it into several classes. Your goal should be to be able to pass in the stuff that changes into methods containing the boilerplate that doesn't change.

You have a message box doing I/O in one method. You need to remove that. No user interface code.

At minimum you have to be able to pass in SQL and execute it. Start with that.

duffymo
  • 305,152
  • 44
  • 369
  • 561