I need to create an "ad-hoc" filtering page in a web app we are
building. The page would display a number of drop down lists, text
boxes, and radio lists and allow the user to select (enter) some data
that would be used to filter the dataset we return. STANDARD STUFF.
Below is simplified version (not compete) of how we solved this with
ADO using javascript on the client to build a where clause that we
would use on our SQL SELECT statement.
//build SQL statement with element values - % means don't add to
select statement
MyForm.SQL.value = "Select DISTINCT XMID, Description, RecordType,
TagDesc, SZGROUPNAME, KSZTAG, VENDOR_NAME, DEVICE_NAME,KSZSERIALNUM
from " +
FromClause + " Where ("
// Partial Tag
if (MyForm.TagPrefixes.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "SubString(KSZTAG,1,3) = '" +
MyForm.TagPrefixes.value + "' And ";
lRetVal = true;
}
// Transmitter Vendor
if (MyForm.Manufacturers.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "VENDOR_CODE = " +
MyForm.Manufacturers.value + " And ";
lRetVal = true;
}
// Transmitter Model
if (MyForm.ShortModels.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "Substring(DEVICE_NAME,1,4) =
'" + MyForm.ShortModels.value + "' And ";
lRetVal = true;
}
// Group
if (MyForm.Groups.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "WGROUPCODE = " +
MyForm.Groups.value + " And ";
lRetVal = true;
}
// Span - Range
if (MyForm.RangeUnits.value != "%"){
MyForm.SQL.value = MyForm.SQL.value + "(RRANGELOWER >= " +
MyForm.LRange.value + " And " +
"RRANGEUPPER <= " + MyForm.URange.value + ") And ";
// Units
MyForm.SQL.value = MyForm.SQL.value + "UNIT_NAME = '" +
MyForm.RangeUnits.value + "' And ";
lRetVal = true;
}
As you can see we are just building a string depending on whether the
user chose a particular piece of data to use in the filtering. We
would then pass this SQL "statement" to our .asp page in a POST and
run aqainst the data connection.
Is it possible to build a where clause somehow using LINQ to SQL on
the fly? I am new to LINQ, and have seem mostly simple examples of
filtering with LINQ. The LINQ I need to run would look something
like:
CStoneDataContext cs = new CStoneDataContext();
var query = from q in cs.CalServices
where q.VENDOR_CODE ==
Convert.ToInt16(ddlVendor.SelectedValue)
select new
{
q.XMID,
q.KSZTAG,
q.Description,
q.SZGROUPNAME,
q.VENDOR_NAME,
q.DEVICE_NAME,
q.KSZSERIALNUM
};
grdInstList.DataSource = query;
grdInstList.DataBind();
However I need to make the where clause dynamic so I can build it
depending on the user's choices. Any ideas, anyone?
Thanks