How to convert a Dataset to Int32?

Question

I’m having an information conversion problem that’s coming from my database to a variable of type Int32.

When I do the select max(cur_id) from tbl_curriculo; I send the id information directly to a Dataset that I call ds.

I send my persistence back this ds and turn into int so I can send this id to another table that will receive this id as foreign key, however I do not know how to do the conversion correctly.

When I pass the command Convert.toInt32() the IDE accuses that everything is ok, but at the time I have run the code I get the following error

Invalid Cast Exception was unhandled by user code An Exception of type 'System.Invalidcastexception' occurred in mscorlib.dll but was not handled in user code Additional information: Cannot convert a 'System.Data.Dataset' type object to 'System.Iconvertible'.

I know that the conversion is not working that way, and I’ve also tried to pass the dataset without doing the conversion to see if it worked and also didn’t work.

Below follows my code from the page where the conversion is being made

protected void btn_Confirmar_Click(object sender, EventArgs e)
    {
        CurriculoDB.InsertCurriculo(Persistencia.Cu[0]);

        int id = Convert.ToInt32(CurriculoDB.SelectCur_Id());

        CandidatoDB.InsertCandidato(Persistencia.Cand[0], Persistencia.En[0], id);

        Persistencia.Cand.Clear();
        Persistencia.Curf.Clear();
        Persistencia.Cur.Clear();
        Persistencia.Expt.Clear();
        Persistencia.Contato.Clear();

        Response.Redirect("ConfirmacaoCandidato.aspx");
    }

Below the code of select

public static DataSet SelectCur_Id()
    {
        DataSet ds = new DataSet();
        IDbConnection objConnection;
        IDbCommand objCommand;
        IDataAdapter objDataAdapter;
        objConnection = Mapped.Connection();
        objCommand = Mapped.Command("select max(cur_id) from tbl_curriculo", objConnection);
        objDataAdapter = Mapped.Adapter(objCommand);

        objDataAdapter.Fill(ds); // O objeto DataAdapter vai preencher o 
        //  DataSet com os dados do BD, O método Fill é o responsável por   preencher o DataSet
        objConnection.Close();
        objCommand.Dispose();
        objConnection.Dispose();
        return ds;        
    }

Below the code of the table where the insert

public static int InsertCandidato(Candidato can, Endereco end, int id)
    {
        int retorno = 0;
        try
        {
            IDbConnection conexao;
            IDbCommand comando;
            string sql = "insert into tbl_candidato values(0, ?can_nome, ?can_data_nascimento, ?can_sexo, ?can_cpf, ?can_cep, ?can_rua, ?can_bairro, ?can_cidade, ?can_estado, ?can_numero, ?can_complemento, ?fk_tbl_candidato_cur_id);";
            conexao = Mapped.Connection();
            comando = Mapped.Command(sql, conexao);
            comando.Parameters.Add(Mapped.Parameter("?can_nome", can.Nome_candidato));
            comando.Parameters.Add(Mapped.Parameter("?can_data_nascimento", can.Data_nascimento));
            comando.Parameters.Add(Mapped.Parameter("?can_sexo", can.Sexo));
            comando.Parameters.Add(Mapped.Parameter("?can_cpf", can.Cpf));
            comando.Parameters.Add(Mapped.Parameter("?can_cep", end.Cep));
            comando.Parameters.Add(Mapped.Parameter("?can_rua", end.Rua));
            comando.Parameters.Add(Mapped.Parameter("?can_bairro", end.Bairro));
            comando.Parameters.Add(Mapped.Parameter("?can_cidade", end.Cidade));
            comando.Parameters.Add(Mapped.Parameter("?can_estado", end.Estado));
            comando.Parameters.Add(Mapped.Parameter("?can_numero", end.Numero));
            comando.Parameters.Add(Mapped.Parameter("?can_complemento", end.Complemento));
            comando.Parameters.Add(Mapped.Parameter("?fk_tbl_candidato_cur_id", id));
            comando.ExecuteNonQuery();
            conexao.Close();
            comando.Dispose();
            conexao.Dispose();
        }
        catch (Exception)
        {
            retorno = -2;
        }
        return retorno;
    }

Answer 1

I will start with three observations that do not go straight to the problem (I could even talk about other things).

I don’t understand why people keep opening and closing connections all the time inside the application. This is unnecessary most of the time and great for creating confusion. The amount of repetitive code there is impressive and this should spread throughout the application. I recommend understanding the DRY. Of course not to do this you have to think about an appropriate architecture, but the tip.

One should avoid calling Close() and Dispose() manually. If there is an exception, everything is open. It is best to use the using. References: It is correct to use a block using within another block using?, Method to execute when destroying class instance, Closing requests, An Exception can cause the closure of an Sqlconnection?.

Capture Exception is not usually cool, especially to do what is being done in this code. I I talk a lot about the wrong use of exceptions, but I’ve noticed that it’s rare for someone to want to learn to do the right thing.

Your main problem

Why create a dataset if the only information you need is the id? Wouldn’t it be simpler to do something like this?

var valorParaRetornar = (int)Mapped.Command("select max(cur_id) from tbl_curriculo", objConnection).ExecuteScalar();

I put in the Github for future reference.

I’m considering that this Mapped, who do not know what it is, does what it should. Obviously the return of this method will already be a int and will not need any conversion. This method could probably have 2 or 3 lines. Actually 1 is even enough if it’s well-architected (eliminate connection opening everywhere).

It is possible to catch the id already in the insertion run together a select last_insert_id();. Or take the property LastInsertedId from the Mysql command to . NET.

Answer 2

Cast.

int id = (int) CurriculoDB.SelectCur_Id();

The cast should only be used if you are sure that the result of the call will correspond to the object or value. Meaning you have to make sure the call:

CurriculoDB.SelectCurt_Id();

will return an int. If you return a Long or other type of this long will suffer loss of content. Example:

long id = 1000000000000000;
int idComValorPerdido = (int) id;
Console.WriteLine(idComValorPerdido); // 1000000000

Notice that you hear loss