In our programming practice we often face a situation, when we should search and select some data according to parameters, which are defined by users. In this case we, as usual, have some form (screen) with special fields, which allow to a user to select parameters for his own choice. For example, we have some form to view data of a table (named Persons) with fields ID, FirstName, LastName, (some other fields), HomeSite. Our users want to find in one case only person with HomeSite London1 and FirstName Mike; in another case a user want to see all persons, that there are in our table. In this article I will show how you can use LINQ for such situations.
For simplicity we will consider a little project with only two tables: C_Person and NC_Site (fig.1) :
Fig. 1.
In our solution (named So) there are three projects: project with all our web forms etc. (So), project for our business logic (SoBL, output type class library) and project for data access layers (SoDal, output type class library). In SoDal project there is dbml file (LINQ to SQL class), named SoDC.dbml, with our tables (fig. 2):
Fig. 2.
Our WebFormPerson (in So project) has four field to enter parameters, a GridView and a button (now we are interested only in selection of some data and not in inserting etc.) and looks like this (fig. 3):
Fig.3.
Of course, without LINQ (!) we should use a stored procedure with the Case function (where, if some parameter equals, for example, -999, we just don't do any select for this parameter) like to following:
ALTER PROCEDURE dbo.usp_Persons
@PersonID nchar(10) = '-999',
@FirstName nchar(20) = '-999',
@LastName nchar(20) = '-999',
@Site smallint = -999
AS
select
PersonNum,
PersonId,
FirstName,
LastName,
Email,
Telephone as Telephon,
Note,
SiteHome,
b.SiteName as SiteName,
a.LastUpdate as LastUpdate
from dbo.C_Person a
LEFT OUTER join NC_Site b
ON a.SiteHome = b.SiteNum
where
PersonID =
case
when @PersonID = '-999' then
PersonID
else ltrim(rtrim(@PersonID))
end
and
FirstName =
case
when @FirstName = '-999' then
FirstName
else ltrim(rtrim(@FirstName))
end
and
LastName =
case
when @LastName = '-999' then
LastName
else ltrim(rtrim(@LastName))
end
and
SiteHome =
case
when @Site = -999 then
SiteHome
else (@Site)
end
Then (without LINQ !) we should call our stored procedure in some PersonDal class of SoDal project and use some DataObjectMethod in, for example, PersonBL class of SoBL project, where we can make some changing for business logic (for example: if FirstName = M, then site=3). But, in fact, main part of our business logic just hide in stored procedure.
Of course, we can use a stored procedure with LINQ to SQL class and just add our stored procedure to SoDC.dbml (fig. 4):
Fig 4.
Then we just call it in our PersonBL class of SoBL project (as method of DataContext):
SoDCDataContext dc = new SoDCDataContext();
var persons = (dc.usp_Persons(personId, firstName, lastName, site)).ToList();
But it is better to see all our logic in the PersonBL class (here really all our business logic has to be!) and use stored procedure in, may be, very special and complicated cases. So, we just change our stored procedure to some LINQ code. Suppose, we use some special class to see persons selected data:
public class C_PersonDGV
{
public short PersonNum { get; set; }
public string PersonID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Telephon { get; set; }
public string Note { get; set; }
public short? SiteHome { get; set; }
public string SiteName { get; set; }
public DateTime? LastUpdate { get; set; }
}
Our DataObjectMethod of the PersonBL class (in SoBL project) will look so:
[
DataObjectMethod(DataObjectMethodType.Select, true)]
public static List<C_PersonDGV> FindPersonsDGV(
string personId, string firstName,
string lastName, short site)
{
SoDCDataContext dc = new SoDCDataContext();
IEnumerable<C_PersonDGV> personsDGV =
from p in dc.C_Persons
where
p.PersonID.Equals((personId.Equals("-999")) ?
p.PersonID : personId)
&&
p.FirstName.Equals((firstName.Equals("-999")) ?
p.FirstName : firstName)
&&
p.LastName.Equals((lastName.Equals("-999")) ?
p.LastName : lastName)
&&
p.SiteHome.Equals(site == -999 ?
p.SiteHome : site)
select new C_PersonDGV
{
PersonNum = p.PersonNum,
PersonID = p.PersonID,
FirstName = p.FirstName,
LastName = p.LastName,
Email = p.Email,
Telephon = p.Telephone,
Note = p.Note,
SiteHome = p.SiteHome,
SiteName = p.NC_Site.SiteName,
LastUpdate = p.LastUpdate
};
////------or lamda----------
//IEnumerable<C_PersonDGV> personsDGV = dc.C_Persons.Where
// (
// p =>
// p.PersonID.Equals((personId.Equals("-999")) ?
// p.PersonID : personId)
// &&
// p.FirstName.Equals((firstName.Equals("-999")) ?
// p.FirstName : firstName)
// &&
// p.LastName.Equals((lastName.Equals("-999")) ?
// p.LastName : lastName)
// &&
// p.SiteHome.Equals(site == -999 ?
// p.SiteHome : site)
// ).Select(p => new C_PersonDGV
// {
// PersonNum = p.PersonNum,
// PersonID = p.PersonID,
// FirstName = p.FirstName,
// LastName = p.LastName,
// Email = p.Email,
// Telephon = p.Telephone,
// Note = p.Note,
// SiteHome = p.SiteHome,
// SiteName = p.NC_Site.SiteName,
// LastUpdate = p.LastUpdate
// }
// );
////---------------------
return personsDGV.ToList();
}
Now we are ready to return to the WebFormPerson in our So project and configure (of course, using the method FindPersonDGV) ObjectDataSource of our GridView (fig. 5, fig. 6):
Fig. 5.
Fig. 6.
At last, we bind our GridView on click event:
protected void ButtonFind_Click(object sender, EventArgs e)
{
if (GridViewGeneral.DataSource != null )
{
GridViewGeneral.DataBind();
}
}
and if we, for example, select site London1 and click Find button we get the following (fig. 7):
Fig. 7.
Good luck in programming !