How to use Scope_Identify to return .NET id

Question

How to use Scope_Identify to return .NET id

Navigation

#1 by (2 votes)

0

I'm developing an application and I need it when sql does the insert return the value of the id that was entered, I'm 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 looked in some tutorials and found the Scope_Identify function but how should I implement it in my code?

mysql sql c# asp.net .net

asked by anonymous 05.06.2015 / 14:31

1 answer

Place 4 columns in a row Does the cURL request in php resolve a subdomain locally?

score 2 · Answer 1

SCOPE_IDENTITY is a SQL Server function. You need the 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 that you need to use ExecuteScalar instead of ExecuteNonQuery to get the value returned by the DB.

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

Note that your command is open for 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);