PTracker: View project doesn't filter by name (like %2005%) - C#

tiago posted on Sunday, February 11, 2007

Filter projects in the database, not in C#. When you are handling a very large collection of projects, this takes the load off the network.

1-Create new stored procedure

/****** Object: StoredProcedure [dbo].[listProjects] Script Date: 02/12/2007 00:24:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[listProjects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[listProjects]
GO
/****** Object: StoredProcedure [dbo].[listProjects] Script Date: 02/12/2007 00:24:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[listProjects]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[listProjects]
(
   @mask varchar(MAX)
)
AS
   SELECT Id, Name
   FROM Projects
   WHERE Name LIKE @mask
   RETURN
'
END
GO

2-Go to project ProjectTracker.Library and edit ProjectList.cs, method Fetch
2.1-By default, show everything. Don't do this if your collection is really large
Replace

private void Fetch(string nameFilter)
{
   this.RaiseListChangedEvents = false;

with

private void Fetch(string nameFilter)
{
   if (nameFilter.Length == 0)
   {
      nameFilter = "%";
   }
   this.RaiseListChangedEvents = false;

2.2-Pass the filter to the new stored procedure
Replace

cm.CommandText = "getProjects";

with

cm.CommandText = "listProjects";
cm.Parameters.AddWithValue("@mask", nameFilter);

2.3-Comment out un necessary code
Replace

// apply filter if necessary
if ((nameFilter.Length == 0) || (info.Name.IndexOf(nameFilter) == 0))

with (comment the second line)

// apply filter if necessary
//if ((nameFilter.Length == 0) || (info.Name.IndexOf(nameFilter) == 0))

 

That's it!

RockfordLhotka replied on Monday, February 12, 2007

Of course, however as I've noted on this forum many times before, as an author it is my job to show various ways to accomplish the same task, because one way is not always correct for all situations.

Virtually everyone knows how to do the filter in the database, so showing that teaches few people anything of value. But illustrating that you can, if necessary, do some of the work on the application server is perhaps of more value.

This is especially true if you consider more complex scenarios than filtering, where it may be simpler to build or maintain the code in C#/VB than in SQL. I've worked on apps before where the SQL code was pages in length and took minutes to run, but the equivalent VB code was just a few lines and took seconds.

Now there's no doubt that some super-SQL-expert could have done as well with SQL. But those people are hard to find, and hard to retain. And when they leave (as they always do) then you have no one left to maintain that SQL code. But good C#/VB people are much easier to find, so there's a lot less risk in writing the code in these languages.

Again, for something trivial like filtering or sorting, obviously the issue is not great. But there are case where this really is an issue, and it is good to illustrate how such logic can fit into the overall CSLA .NET coding patterns.