Clever Geek Handbook

How do I search for the contents of all fields in a query result set?

I am trying to create a generic search function that will search for the contents of each field in a result set.

For example, if I had to write the TSQL function, which returns a result set that contains 100 rows and fields TicketID

, FirstName

, LastName

and Comment

I want to search for each of these fields for each record for the string "Mike."

So, if the word "Mike" is in any of these fields, this entry will be included in my search.

How to do it?

+3


source to share


4 answers


You can create a complete full text index. You can get this index for an entire table or selected columns in a table through a view. http://msdn.microsoft.com/en-us/library/ms142575.aspx#examples This has its own syntax, but is part of a TSQL query and can be combined with a regular column lookup.



+2


source 


from x in someTableOrQuery
where x.FirstName.Contains(str) || x.LastName.Contains(str) || ...
select x

This is normal C #. No tricks required.



Note that this will translate to a LIKE expression in SQL. But you don't need to do manual string concatenation, which is error prone (not SQL injection in this case).

+1


source


Or you can use the SqlMethods.Like method so that you can search your expression for a specific pattern.

Something like that:

where SqlMethods.Like(str,"%Mike%");

Hope this helps!

+1


source


The entry for this method will be a DataTable with the result set of the query execution. Then you need to iterate over the rows and columns like this:

foreach (DataRow dr in yourDataTable.Rows)
{
    foreach(DataColumn col in yourDataTable.Columns)
    {
        try
        {
            if (dr[col].ToString().Contains("Mike"))
            {
                //Save this one!
            }
        }
        catch
        { 
            //it was not a string, go on then.
        }
    }
}
+1


source






More articles:


All Articles