CODEWITHSUNDEEP
subsonic
Brandon Morales
Brandon Morales

Reputation: 140

Querying with conditional where parameters

Before 2.1 I was able to write code like this (using the AddWhere):

Query q = new Query(Search.Schema);
q.BuildSelectCommand();
if (!String.IsNullOrEmpty(barcode)) q.AddWhere("Barcode", Comparison.Like, "%" +     barcode.Trim() + "%");
if (!String.IsNullOrEmpty(fromDate)) q.AddWhere("FromDate", Comparison.GreaterOrEquals, fromDate);
if (!String.IsNullOrEmpty(toDate)) q.AddWhere("ToDate", Comparison.LessOrEquals, fromDate);
if (!String.IsNullOrEmpty(department)) q.AddWhere("DeptNo", Comparison.Like, "%" + department.Trim() + "%");
if (!String.IsNullOrEmpty(series)) q.AddWhere("SeriesNo", Comparison.Like, "%" + series.Trim() + "%");
if (!String.IsNullOrEmpty(altcode)) q.AddWhere("AltCode", Comparison.Like, "%" + altcode.Trim() + "%");

This allowed me to create nice search forms and only search on variables that users entered into the form, I am trying to reproduce this functionality in a simple manner with 2.1 and can't figure it out. Does anyone have any suggestions?

Upvotes: 0

Views: 401

Answers (2)

Brandon Morales
Brandon Morales

Reputation: 140

the solution I came up with is:

SqlQuery q = new Select("syKey").From<Search>().Where("1").IsEqualTo("1");
if (!String.IsNullOrEmpty(barcode)) q.And("Barcode").Like("%" + barcode.Trim() + "%");
if (!String.IsNullOrEmpty(fromDate)) q.And("FromDate").IsGreaterThanOrEqualTo(fromDate);
if (!String.IsNullOrEmpty(toDate)) q.And("ToDate").IsLessThanOrEqualTo(fromDate);
if (!String.IsNullOrEmpty(department)) q.And("DeptNo").Like("%" + department.Trim() + "%");
if (!String.IsNullOrEmpty(series)) q.And("SeriesNo").Like("%" + series.Trim() + "%");
if (!String.IsNullOrEmpty(altcode)) q.And("AltCode").Like("%" + altcode.Trim() + "%");

Notice with the default Where("1").IsEqualTo("1") I can dynamically add "And" to the query to make it work. It would be nice to see AddWhere brought back though so you can keep stacking WHERE commands on top of each other.

Upvotes: 2

user1151
user1151

Reputation: 

IDataReader rdr=new Select().From<Search>().Where("barcode").Like(..)
.And("FromDate").GreaterThan(..)
.And("ToDate").LessOrEqualTo(..)
.And("DeptNo").Like()
...
.ExecuteReader();

http://blog.wekeroad.com/2008/01/10/subsonic-version-21-pakala-preview-the-new-query-tool/

Now, if you're adventurous, you can use 3.0 (which is in the works) and you can use Linq to do this:

http://code.google.com/p/subsonicthree/

I'm happy to help here...

Upvotes: 0

Related Questions