What is the best way to search multiple columns for same value using oledbcommand

Question

Hence OLE DB .NET Provider does not support named parameters, is there any better or faster way to search multiple columns? like just using single parameter?

cmd = new OleDbCommand("SELECT * FROM Table1 WHERE Column1 LIKE ? OR Column2 LIKE ? OR Column3 LIKE ? OR Column4 LIKE ? OR Column5 LIKE ? OR Column6 LIKE ? OR Column7 LIKE ? OR Column8 LIKE ? OR Column9 LIKE ? OR Column10 LIKE ? ", con);

cmd.Parameters.AddWithValue("@Column1", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column2", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column3", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column4", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column5", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column6", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column7", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column8", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column9", "%" + txtSearch.Text + "%");
cmd.Parameters.AddWithValue("@Column10", "%" + txtSearch.Text + "%");

If i use only single parameter, the value for second parameter is not found

Answer 1

One way to send the parameter only once is to use cross apply:

// proper indentation makes for a more readable code
var sql = @"SELECT * 
    FROM Table1 
    CROSS APPLY 
    (
        SELECT '%'+ ? +'%' As searchParam
    ) As s
    WHERE Column1 LIKE searchParam 
    OR Column2 LIKE searchParam 
    OR Column3 LIKE searchParam 
    OR Column4 LIKE searchParam 
    OR Column5 LIKE searchParam 
    OR Column6 LIKE searchParam 
    OR Column7 LIKE searchParam 
    OR Column8 LIKE searchParam 
    OR Column9 LIKE searchParam 
    OR Column10 LIKE searchParam";

// OleDbCommand implements the IDisposable interface...
using (var cmd = new OleDbCommand(sql, con))
{
    // use Add instead of AddWithValue
    cmd.Parameters.Add("@searchParam", OleDbType.VarWChar).Value = txtSearch.Text;
    con.Open();
    // Execute command here
}

Read Can we stop using AddWithValue() already? for an explanation on why AddWithValue is such a bad idea.

Please note that this is still searching multiple columns with a non-SARGable predicate so SQL Server can't use index seek for any of the conditions here.