CODEWITHSUNDEEP
c#anonymous-types
BWhite
BWhite

Reputation: 853

How to build up anonymous array of parameters

I have a query 

string sQuery = string.Format("SELECT {0} FROM vwweb_Orders WHERE CustFID = ?", columns);

that gets executed here

var result = await conn.QueryAsync<Order>(sQuery, new { ID = Custid });

But say that I'm searching based on parameters chosen by the user. I can build up the where clause, but how do I build up the anonymous array?

new { ID = Custid }

I want something like 

var params = new {};
if (!String.IsNullOrWhiteSpace(username)) {
   params += {username}
}

Upvotes: 0

Views: 101

Answers (2)

Ashish
Ashish

Reputation: 520

Maybe you can write your query to check for null/empty/non-zero values first otherwise evaluate the real values as follows:

public async Task<List<Order>> Execute(OrderQuery query)
{

    var sql = $@"SELECT
                    ...
                FROM vwweb_Orders
                WHERE @{nameof(query.CustomerId)} <= 0 OR customer_id = @{nameof(query.CustomerId)}
                    AND ISNULL(@{nameof(query.CustomerName)}, '') = '' OR customer_name = @{nameof(query.CustomerName)}";

    return await conn.QueryAsync<Order>(sql, new { query.CustomerId, query.CustomerName});

}

public class OrderQuery
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
}

public class Order
{
}

Upvotes: 0

Aleks
Aleks

Reputation: 1689

If you really want to have params like an anonymous type, you can use an ExpandoObject:-

dynamic params = new ExpandoObject();

if (!string.IsNullOrWhiteSpace(username)) {
    params.Username = username;
}

Or if you want an array (and you don't know the length ahead of time), use a List<string>:-

var paramlist = new List<string>();

if (!string.IsNullOrWhiteSpace(username)) {
    paramlist.Add("username");
}

var params = paramlist.ToArray();

However, if you are constructing the WHERE clause, you will always have a fixed number of parameters in your SQL statement anyway (or you'll have to construct it dynamically too).

One other method you can use when dynamically building a query for filtering is this:-

SELECT *
FROM vwweb_Orders
WHERE 1=1
AND (@custid IS NULL OR CustFID = @custid)
AND (@xyz IS NULL OR XYZ = @xyz)
-- etc

Then supply all the parameters to your QueryAsync call, and if any are null, they'll be skipped in the WHERE clause.

Upvotes: 1

Related Questions