Platinum Partner
sql,high-perf,tips and tricks

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

 

Published at DZone with permission of {{ articles[0].authors[0].realName }}, DZone MVB. (source)

Opinions expressed by DZone contributors are their own.

{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}