CODEWITHSUNDEEP
c#t-sql
nik.shornikov
nik.shornikov

Reputation: 1935

t-sql command parameter wildcard

I've read numerous similar questions, but the answers simply didn't work. Running this function in sql select * from HelloRow('o') returns no rows. Running HelloRow('one') returns the row with name "one". The fillrow method doesn't do anything (just reads the strings).

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "HelloFillRow", DataAccess = DataAccessKind.Read, TableDefinition="message nchar(20)")]
public static IEnumerable HelloQuery(SqlString like)
{
    ArrayList resultCollection = new ArrayList();

    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();

        using (SqlCommand select = new SqlCommand(
            "select name from test where name like @par"
            , connection))
        {
            select.Parameters.Add("par", SqlDbType.NChar, 10).Value = like + "%";
            using (SqlDataReader selectReader = select.ExecuteReader())
            {
                while (selectReader.Read())
                {
                    SqlString nextName = selectReader.GetString(0);
                    resultCollection.Add(nextName.ToString());
                }
            }
        }
    }
    return resultCollection;
}

Upvotes: 0

Views: 879

Answers (1)

Dimitri
Dimitri

Reputation: 7013

Change command text to:

select name from test where name like @par + '%'

and add parameter as such: 

select.Parameters.Add("par", SqlDbType.NVarChar, 10).Value = like;

Upvotes: 5

Related Questions