Performance Zone is brought to you in partnership with:

Erik Ejlskov Jensen is a .NET Data Developer, and SQL Server Compact MVP. He is also the author of a number of tools for SQL Server Compact. He has been working in IT for too many years, and enjoy blogging (http://erikej.blogspot.com) and tweeting (@ErikEJ) Data Development related news and tips. Erik Ejlskov is a DZone MVB and is not an employee of DZone and has posted 62 posts at DZone. You can read more from them at their website. View Full User Profile

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

06.26.2013
| 1636 views |
  • submit to reddit

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;
        }

 

Published at DZone with permission of Erik Ejlskov Jensen, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)