How to use Scope_identify to return id . NET

Question

I am developing an application and need that when sql does the Insert return the value of the id that was inserted, I am using the following command

SqlCommand cmd = new SqlCommand("insert into solicitacoes (assunto, mensagem, endereco, anexo, status, id_Departamento) values ('" + valorcadastro.assunto + "', '" + valorcadastro.mensagem + "', '" + valorcadastro.endereco + "','" + valorcadastro.anexo + "','Inicial', 0)", con);
            cmd.ExecuteNonQuery();

I searched in some tutorials and found the Scope_identify function but how should I implement in my code?

Answer 1

SCOPE_IDENTITY is a SQL Server function. Accurate equivalent function for Mysql, last_insert_id:

SqlCommand cmd = new SqlCommand("insert into solicitacoes (assunto, mensagem, endereco, anexo, status, id_Departamento) values ('" + valorcadastro.assunto + "', '" + valorcadastro.mensagem + "', '" + valorcadastro.endereco + "','" + valorcadastro.anexo + "','Inicial', 0);SELECT last_insert_id()", con);

cmd.ExecuteNonQuery();
int ultimoRegistro = cmd.ExecuteScalar();

Note you need to use ExecuteScalar instead of ExecuteNonQuery to obtain the value returned by BD.

Or else, if you use class MySqlCommand, you can access the property LastInsertedId.

Note that your command is open to SQL injection attacks. You should use parameterized queries:

var cmd = new SqlCommand("insert into solicitacoes (assunto, mensagem, endereco, anexo, status, id_Departamento) values ('@assunto', '@mensagem', '@endereco','@anexo','Inicial', 0);SELECT last_insert_id()", con);

cmd.Parameters.AddWithValue("@assunto", valorcadastro.assunto);
cmd.Parameters.AddWithValue("@mensagem", valorcadastro.mensagem);
cmd.Parameters.AddWithValue("@endereco", valorcadastro.endereco);
cmd.Parameters.AddWithValue("@anexo", valorcadastro.anexo);