Over a million developers have joined DZone.
Platinum Partner

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

The Performance Zone is brought to you in partnership with New Relic. Quickly learn how to use Docker and containers in general to create packaged images for easy management, testing, and deployment of software.

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

 

The Performance Zone is brought to you in partnership with New Relic. Read more about providing a framework that gets you started on the right path to move your IT services to cloud computing, and give you an understanding as to why certain applications should not move to the cloud.

Topics:

Published at DZone with permission of Erik Ejlskov Jensen , DZone MVB .

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}