Methods Executereader(), Executenonquery() and Executescalar(), what are the differences and how to use them?

Question

I’m working with a database SQL Server in C#, but I noticed that the object SqlCommand has several methods to execute a query, are they: ExecuteReader(), ExecuteNonQuery() and ExecuteScalar().

My doubt is as follows, what are the differences between these methods and how should I use them?

Answer 1

Executereader

Used to perform a field-to-field reading on top of a data extraction. This is done through the type object SqlDataReader returned by the method.

Example:

using (var connection = new SqlConnection(
           connectionString))
{
    connection.Open();

    var command = new SqlCommand("Select * from MinhaTabela", connection);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        for(int i = 0 ; i < count ; i++) {
            Console.WriteLine(reader.GetValue(i));
        }
    }
}

It is a rather prolix method for direct access to columns.

ExecuteNonQuery

Executes a command that does not carry a row and column relation. Its return is actually the amount of rows affected by the command.

It is used for sentences like INSERT, UPDATE, DELETE, etc..

Example:

using (var connection = new SqlConnection(
           connectionString))
{
    var command = new SqlCommand("UPDATE MinhaTabela set MeuCampo = 1 where OutroCampo = 2", connection);
    command.Connection.Open();
    var registrosAfetados = command.ExecuteNonQuery();
}

ExecuteScalar

Executes a command and brings as a result the first column of the first row returned. It is usually useful to run, for example, Stored Procedures which return a specific value or functions such as counting records.

Example:

using (var connection = new SqlConnection(connString))
{
    var cmd = new SqlCommand(conn);
    cmd.CommandText = "SELECT COUNT(*) FROM MinhaTabela";

    try
    {
        conn.Open();
        var contagem = (Int32)cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}