cmd.Commandtype = Commandtype.Text - Pq use?

Question

Good morning guys.

I’m populating a grid, and after searching the internet, I saw that some use the command:

cmd.CommandType = CommandType.Text;

My doubt, is, why use? what’s the difference? I did some tests here, and it works normally with or without this parameter.

Follows my code:

 private void Form1_Load(object sender, EventArgs e)
    {
        string sqlstring, _sql;
        int id_cliente = 3;

        sqlstring = @"Server=tcp:tpspoazsql01.database.windows.net,1433;Data Source=tpspoazsql01.database.windows.net;Initial Catalog=Onee;Persist Security Info=False;User ID=********;Password=*********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
        _sql = string.Empty;

        SqlConnection sqlconn = new SqlConnection(sqlstring);

        try
        {
            _sql = @"SELECT nome,endereco,numero,bairro,cidade,estado,cep FROM Cliente_enderecos_alter WHERE id_cliente = @id_cliente AND tipo = 'Cobrança'";
            SqlCommand cmd = new SqlCommand(_sql, sqlconn);
            cmd.Parameters.Add("@id_cliente", SqlDbType.Int).Value = id_cliente;

            sqlconn.Open();

            cmd.CommandType = CommandType.Text;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable clientes = new DataTable();
            da.Fill(clientes);

            dataGridView1.DataSource = clientes;
        }
        catch
        {

        }
    }

Answer 1

It turns out that the property pattern CommandType is the CommandType.Text. That’s why it works even without you explaining it.

This does not mean that your command will work without it, it will always be necessary to have some value in this property. Possible values are :

1. StoredProcedure - Run a precedent. The command will be the name + parameters of this precedent;
2. TableDirect - Returns information about a table. The command must be the table name;
3. Text - The command must be an SQL.

See in the screenshot that at the moment the object is created the CommandType already is CommandType.Text.

Answer 2

It is not necessary to use in this case. In your example the CommandType makes no difference, because the default value of an Enum of CommandType is the value CommandType.Text.

CommandType can be modified when you want to call a Procedure with the use of Parameters of SqlCommand, to ensure some validation as well as SQL Injection. (CommandType.StoredProcedure for that reason)

You also have another option TableDirect, but I never used it and I don’t know it well, so I’m just commenting on it in case you want to research.

Here in the OR has a question that shows how to use the StoredProcedure with Parameters.