ASP.NET MVC C# Linq to Stored procedure with Dynamic Query (a quick phone book, piece of Cake !)

Salut les Gars !

I am back with a little code I wrote today for my business. I had to create a little phone book.

The db I had to query was pretty complicated and I did not want to create all the stuff you need for an effective Linq ot Sql classes. Therefore, I decided to use a stored procedure who retrieve all the users (about 5000) with all the information I need. This application will be a one-shot application and  his time life will be about 1 or 2 years. Also, the release date is very tight.

Step 1 : Create the stored procedure :

I am not going to teach you how to create one. All you need to know is the result of the query. My stored prod will return a row with the Email column, the city, the office address,…

Here, an example of a stored prod you might deal with :

SELECT uEmail, Offices.ofPostalCode , uTelExtension, uMobilePhone, uDefaultLang,
Offices.ofTelephone, ofFax, rsName.value, rsCity.value as 'City', rsAdd.value as 'Address', rsAddSuite.value as 'Suite',
(Select Resources.value
FROM States
join Resources ON Resources.rsID = States.rsStName AND Resources.loID = Users.uDefaultLang
WHERE States.stID = Offices.stID) as 'State/Province',
(Select Resources.value
FROM Countries
join Resources ON Resources.rsID = Countries.rsCnName AND Resources.loID = Users.uDefaultLang
WHERE Countries.cnID = Offices.cnID) as 'Country'
FROM Users
join Offices ON Users.ofID = Offices.ofID
join Resources AS rsName ON rsName.rsID = Offices.rsOfName AND rsName.loID = Users.uDefaultLang
join Resources AS rsCity ON rsCity.rsID = Offices.rsOfCity AND rsCity.loID = Users.uDefaultLang
join Resources AS rsAdd ON rsAdd.rsID = Offices.rsOfAddress AND rsAdd.loID = Users.uDefaultLang
join Resources AS rsAddSuite ON rsAddSuite.rsID = Offices.rsOfAddressSuite AND rsAddSuite.loID = Users.uDefaultLang
join States ON States.stID = Offices.stID
join Countries ON Countries.cnID = Offices.cnID
WHERE uTelExtension != ''

I know, there is no parameter in the stored prod but we could add some later (the language for example).

Step 2 : Add the Stored Procedure in a Sql to Linq Class :

Create an Linq to Sql class to you solution or use one you already have. Open you Server Explorer, add a connection to the db you want simply drag and drop the stored procedure in the dbml.

Step 3 : Create your pseudo Factory :

So I added this in a SqlUserRepository class which I will use in my controller to retrieve data and filter it.

public IEnumerable<UserDB> getUserFromDb()

{

LinqToUsersDataContext db = new LinqToUsersDataContext();

var res = from Users in db.spUserGetUserDB()

select new UserDB()

{

Email = Users.uEmail,

Address = Users.Address,

City = Users.City,

Country = Users.Country,

StateProvince = Users.State_Province,

MobilePhone = Users.uMobilePhone,

ofTelephone = Users.ofTelephone,

Suite = Users.Suite,

TexExtension = Users.uTelExtension

};
return res.AsEnumerable();
}

Step 4 : Create Filter :

For this example, I will create two filters, one which will filter by Email and the other which will filter by City Name. Note that we do not speak here about best design and performance. We want something quickly. I guess when you read the stored prod, you said “Oh what for a crap”, yes I know but it was an old db with bad design and I must deal with…

Back to our Filters, the magic word here is “this”. You create a static class with an Ubiquitous Language (DDD style, traduction explicit name) and you create some static methods. In this example, it is “this IQueryable<UserDB>” and if you play with an object of that type, the intellisense will detect automatically and will show it to you (Like we saw in a previous post, here).

public static class UserDBFilter
{
public static IQueryable<UserDB> LikeEmail(this IQueryable<UserDB> qry, string Email )
{
return from user in qry where user.Email.Contains(Email) select user;

}

public static IQueryable<UserDB> WithCity(this IQueryable<UserDB> qry, string City)
{
return from user in qry where user.City == City select user;

}
}

Step 5 : Create the dynamic query :

To test if everything works fine, I created a little integration test.

[TestMethod]
public void GetRetrieveUserFromDbwithparam()
{
string email;
string city;

email = "pierre@dervalp.com";
city = "Tokyo";
var res = _service.getUserFromDb().AsQueryable();

if (!string.IsNullOrEmpty(email))
res = res.LikeEmail(email);

if (!string.IsNullOrEmpty(city))
res = res.WithCity(city);

Assert.IsNull(res);

}

It tried to put the string email to null, after the sting city to null, after both. In every case I receive what I wanted to have.

Step 6 : Create the controller and the view:

We create a basic controller and add a function ListUsers(). Per default, we render all the users (for example).

private readonly SqlRepositoryService _dbService;
//CTOR (instantiate the service you need)
public MergeController()
{
_dbService = new SqlRepositoryService();
}

[AcceptVerbs(HttpVerbs.Get)]
public ActionResult ListUsers()
{
// PagedList<UserDB> userlist = new PagedList<UserDB>(_dbService.getUserFromDb().ToList(),0, 5);

IList<UserDB> userlist = new List<UserDB>(_dbService.getUserFromDb());
return View(userlist);
}

Right-click on ListUsers() and create a strong typely-type view List. If you run the application, you will receive all the user in a List.

I cannot show you the result (private information inside).

Step 7 : Add the search form in you view:

I add my little form on the view :

<% using (Html.BeginForm("List","Merge"))
{%>

<label for="Email">Email:</label>
<%=Html.TextBox("Email")%>

<label for="City">City:</label>
<%=Html.TextBox("City")%>

<input id="searchform"  type="submit" value="Search" />
<%}%>

We add a method for the post :

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult List(FormCollection forms)
{

string Email = Request.Form["Email"];
string City = Request.Form["City"];

var res = _dbService.getUserFromDb().AsQueryable();

if (!string.IsNullOrEmpty(Email))
res = res.LikeEmail(Email);

if (!string.IsNullOrEmpty(City))
res = res.WithCity(City);

return View(res);
}

Run the application and make a search with  an email and it works !

Step 8 : Conclusion and improvements :

Of course, the design of this little application could be discussed but I make a phone book in 10 minutes and I am sure that after one day of refactoring, I could put this application in a production status (Add pagination, add improvemnts for query, some javascript,…).

So, I am waiting for your feedback…

Bookmark It

Hide Sites