Best method to locate values in a Datatable

Question

In a unit of measure registration screen, I have to check if the unit that the user is wanting to register, if it already exists, then as I have a DataGrid populated by a DataTable, I thought I’d walk the DataTable and check if this already exists.

So I made the code like this:

 //botão salvar
 private void simpleButton2_Click(object sender, EventArgs e)
    { 
        //Verifico se os dois textbox estao preenchidos
        if (textBox1.Text != string.Empty && textBox2.Text != string.Empty)
        {
            // defino minha string de filtro
            string exp = "SIGLA = " + "'" + textBox2.Text + "'";
            // Executo a busca com base no meu filtro
            DataRow[] find_sigla = dat.Select(exp);

            // se o retorno da busca for 0 faz a ação
            if (find_sigla.Count() == 0)
            {
                //Insiro o valor no banco
                Classes.Cadastro.Cadastro_estqUn cad_estqun = new Classes.Cadastro.Cadastro_estqUn();
                if (cad_estqun.cadastro_unidade(textBox1.Text, textBox2.Text) > 0)
                {
                    textBox1.Text = "";
                    textBox2.Text = "";
                }
            }
            else
            {
                MessageBox.Show("Unidade de medida já cadastrada.", "Salvar", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }
    }

So I heard, that this method I lose performance, proceeds? someone has some better solution?

Thank you.

Answer 1

I wouldn’t trust what’s on Datagrid, but what’s in the database.

A hypothetical example but applies to other real situations:

"Your system has a database server and 3 workstations. Your user opened this screen and had 5 units of measures, but went to lunch. While he was having lunch someone registered the unit of measure that he was going to register. It will duplicate in the database if it registers after lunch, because Datagrid is outdated."

In this class of yours Cadastro_estqUn make an implementation that checks in the database if the record already exists with the description. It would be something like this:

public bool UnidadeMedidaUnica(string nome) 
{
  string sql = "SELECT COUNT(*) FROM tbUnidadeMedida WHERE unidade_nome = @nome";
  SqlCommand cmd = new SqlComman(sql, connectionString);
  cmd.Parameters.AddWithValue("@nome", nome);
  SqlDataReader mySqlDataReader = cmd.ExecuteReader();
  int qtde = (int)cmd.ExecuteScalar();
  return qtde == 0;

}

To improve you can add a "Unique index", which will ensure that even SQL statements fired directly into the database duplicate records.

CREATE UNIQUE INDEX idx_unidade_nome ON tbUnidadesMedida(unidade_nome)