Sunday, June 23, 2013

SQL Server Compact Code Snippet of the Week #17 : using wildcards with a parameterized query

This “week”’s code snippet simply demonstrates how to use a parameterized query with LIKE and a search string containing wildcards. The simple solution is basically to add the wildcard character (% or ?) directly to the search string.

public static List<string> GetCompletionList(string prefixText = "%orch%")
{
//TODO Add error handling
List<string> Names = new List<string>();
using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\projects\Chinook\Chinook40.sdf"))
{
con.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Artist WHERE Name LIKE @Name", con))
{
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 120).Value = prefixText;
using (SqlCeDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Names.Add(reader[0].ToString());
}
}
}
}
return Names;
}

No comments: