C# how to do database search using parameters

Question

I have the following code

public DataTable PesquisarPorNome(string NomePesquisado)
{
    try
    {
        DataTable tabela = new DataTable();
        SqlDataAdapter adaptador = new SqlDataAdapter("SELECT * FROM tbEspecialidades WHERE NomeEspecialidade LIKE '%" + NomePesquisado + "%' ", conexao.StringConexao);
        adaptador.Fill(tabela);
        return tabela;
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
    finally
    {
        conexao.DesconectarDoBanco();
    }
}

I know that if I make this available, the cow goes to the swamp, because SQL Injection is there for this, but how to use parameters ? Thank you

Answer 1

See the example below,

public DataTable PesquisarPorNome(string NomePesquisado)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = myConnString;
        try
        {
            var SQL = string.Format("SELECT * FROM tbEspecialidades WHERE NomeEspecialidade  LIKE @NomePesquisado");

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = SQL;
            cmd.Parameters.Add("@NomePesquisado", SqlDbType.NVarChar).Value = "%" + NomePesquisado + "%";
            SqlDataAdapter sqlA = new SqlDataAdapter();
            DataTable tabela = new DataTable();

            sqlA.SelectCommand = cmd;

            conn.Open();
            sqlA.Fill(tabela);

            return tabela;
        }
        finally
        {
            conn.Close();
        }
    }

Only you need to specify the database you are using..

Answer 2

As soon as you consult with Parameters and LIKE clause.

var cmd = new SqlCommand("SELECT * FROM tbEspecialidades WHERE NomeEspecialidade LIKE '%'+ @NomePesquisado +'%'", connection);
cmd.Parameters.Add("@NomePesquisado", SqlDbType.VarChar, 50).Value = NomePesquisado;
var dr = cmd.ExecuteReader();