Alternatives to DataTable Searches?

[Paul Gates]

Hi All, I have a DataTable in a Windows Forms project that I would like to use a Select statement. The problem is, I'd like to be able to do wildcard searches (including single character searches). The DataTable only supports multiple-character searches (*), and only at the beginning or end of the search criteria. What are my alternatives? I'd rather not have to go back to the database, unless that is the best way. The DataTable was a performance enhancement. Thanks, pagates

[Wjousts]

I encounted a similar problem. It's quite frustrating that searching in a datatable isn't more flexible. I found a partial solution by parsing the search string to replace wildcards in the middle of the search string like this:

private string ParseWildCards(string strWild)
{
	// Look for wildcards that DO NOT occur at the start or end of the string
	Regex reg = new Regex(@"(?<=.)\*(?=.)");
	if (reg.IsMatch(strWild))
	{
		// We have a wildcard in the middle of the expression - check this is actually a LIKE - else it won't work
		int i = strWild.IndexOf("LIKE");
		if (i > 0)		
		{
			string col = strWild.Substring(0,i).Trim();	// get the column name
			string strReplace = "*' AND " + col + " LIKE '*";
			return reg.Replace(strWild,strReplace);
		}
	}
	return strWild;
}

This will replace a string like "col like 'aaa*bbb'" with "col like 'aaa*' and col like *bbb' which kind of works except that there is no way to stop it matching a string like '...bbbaaa...' because you can't specify that it should match the 'aaa*' part before the '*bbb' part. If anybody has a better solution I would love to hear about it.

[Paul Gates]

Thanks for the reply. I will combine this with an answer I saw in the MSDN newsgroups. Basically, the MSDN answer was to manually go through the rows and doing a RegEx pattern search. While that could be slow with tens of thousands of rows, if I implement your solution first, I will at least have some rows filtered out. Again, if anybody has a better idea, I'm all ears. Thanks again, PAGates