Parameterizing a ServiceStack custom SQL query

Question

I have the following custom SQL Query with OrmLite:

var results = db.Select<Tuple<Customer,Purchase>>(@"SELECT c.*, 0 EOT, p1.*
    FROM customer c
    JOIN purchase p1 ON (c.id = p1.customer_id)
    LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
        (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
    WHERE p2.id IS NULL;");

What is the best way to add an optional WHERE clauses to, for instance, filter a given customer name field when the field has a value or to add pagination to the query?

stackoverflow.com/a/37847762/193634

Rosdi Kasim
– Rosdi Kasim

2017-04-06 02:53:08 +00:00
Commented Apr 6, 2017 at 2:53 — Rosdi Kasim
– Rosdi Kasim, Commented Apr 6, 2017 at 2:53
@RosdiKasim how do I make a WHERE clause optional?

kolrie
– kolrie

2017-04-06 02:57:01 +00:00
Commented Apr 6, 2017 at 2:57 — kolrie
– kolrie, Commented Apr 6, 2017 at 2:57

mythz · Accepted Answer · 2017-04-06 02:59:30Z

2

If you're working with Custom SQL you just need to construct the additional queries yourself, e.g:

var sql = @"SELECT c.*, 0 EOT, p1.*
    FROM customer c
    JOIN purchase p1 ON (c.id = p1.customer_id)
    LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
        (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
    WHERE p2.id IS NULL";

//string name = "Customer Name";
string name = null;
if (name != null)
{
    sql += "\nAND name = @name";
}

int? limit = 100;
if (limit != null)
{
    sql += $"\nLIMIT {limit}";
}

var results = db.Select<Tuple<Customer,Purchase>>(sql, new { name });

A Live Example of this is available on Gistlyn.

answered Apr 6, 2017 at 2:59

mythz

144k31 gold badges253 silver badges400 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parameterizing a ServiceStack custom SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related