C# Generate random and unique number, check the database if any equal number has already been generated and if you have to generate another number again

Question

I’m developing a system where I need to generate one random number and single and write to the bank, this system will run every day generating new numbers based on business rules.

With the help of colleagues in the comments I modified my code, now putting in the bank the column "coupon" as unique, so when trying to record will never allow if there is already an equal value in the bank.

My problem now is that when it happens that there already exists a number equal to what was generated, my system generates a recording error and I can’t make it generate a new number to try to record again.

I need that when I can not record because the number already exists it enters in "loop", generating a new coupon until it is not in the bank and can continue the execution.

To generate the random number:

 public string CupomCampanha()
        {
              string numeroAleatorio = "";
              var random = new Random();
              var possibilidades = Enumerable.Range(0, 10).ToList();
              var resultado = possibilidades.OrderBy(number => random.Next()).Take(10).ToArray();
              return numeroAleatorio = String.Join("", resultado);
        }

To do the Insert in the bank

public void InserirIndicacaoContas(string cpf)
        {
              if (cpf != "-")
              {
                    var conexao = AbrirConexao();
                    var comando = conexao.CreateCommand();

                    comando.CommandText =
                    $"INSERT INTO campanhaCupons (cpf, cupon, idProduto, dataImportacao) VALUES (@cpf, @cupon, @idProduto, @dataImportacao)";
                    comando.Parameters.AddWithValue("cpf", cpf);
                    comando.Parameters.AddWithValue("cupon", _geraCupon.CupomCampanha());
                    comando.Parameters.AddWithValue("idProduto", "1");
                    comando.Parameters.AddWithValue("dataImportacao", Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss"));

                    ExecutaComando(comando);
              }

I hope I can explain better now.

Answer 1

As I said in the comments :

• The coupon number you will always have to create, randomly as specified, but making the table column have the restriction UNIQUE DB itself prevents you from adding a duplicate coupon number thus saving time to program a search for duplicity in the database.

• When trying to duplicate a record in a column UNIQUE an exception is made. Use this exception in your favor put the code in a loop within a treatment of exceptions, If you make a mistake repeat the loop and generate another coupon number otherwise it is wrong that the coupon has been saved and leave the loop.

Applied in the example:

while (true){
  var comando = conexao.CreateCommand();      //Cria um novo comando sql.
  comando.CommandText =
  $"INSERT INTO campanhaCupons (cpf, cupon, idProduto, dataImportacao) VALUES (@cpf, @cupon, @idProduto, @dataImportacao)";
  comando.Parameters.AddWithValue("cpf", cpf);
  comando.Parameters.AddWithValue("cupon", _geraCupon.CupomCampanha());
  //Abre um bloco de tratamento de exceções...
  try{
     ExecutaComando(comando);  //Quando não conseguir inserir registro gera uma exceção MySqlException.
  catch {
      continue;                //Se uma exceção foi gerada reitera o loop.    
  }
  break;                       //Caso nenhuma exceção tenha sido gerada abandona o loop
}