Tuesday, September 4, 2012

Test & Debug WCF service using WCFTestClient.exe


VS.NET 2010 provides WCFTestClient.exe for us to test and debug the WCF service. 
Please follow me to test and debug the service.
Generally, first debug and then test the service, but here first I will tell you how to test it and then how to debug it J

Test the WCF service using WCFTestClient.
1.       Open “Visual Studio Command Prompt (2010)”
2.       Go to “c:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\”
3.       Run WcfTestClient.exe
4.       It will open WCF Test Client, now add WCF Service (URL)
5.       Select the service method, from left panel
6.       Provide request data (i.e. values to parameters) on right panel and click on Invoke button. It gives you the response.




Debug the WCF service, follow the commands.
1.       Go to WCF service project properties
2.       Go to Web menu on left menu
3.       Select WcfTestClient.exe from “c:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\” for “Start external program”
4.       Provide service in command line arguments, for which the service to be debug.
5.       Place break points on the service source code
6.       Set WCF project as “Startup Project” from properties
7.       Run the project (F5).
8.       Now it will build the project(s), and start debugging the WCF service.





Alternate to Dynamic SQL for Search SPs


Today had a chance to work on ‘Search’ functionality using SQL Server stored procedure. Usually we write dynamic SQL in stored procedure for ‘Search’ing purpose. End user might consider few or all attributes as search criteria. To execute the SQL statement, we use sp_executesql
We have an alternate to dynamic SQL, using simple select query. Below is the sample.

Dynamic SQL Sample:
CREATE PROCEDURE [SearchOffcie]     
(  @OfficeName VARCHAR( 100 )     
 , @OfficeType VARCHAR( 50 )     
 , @Address1 VARCHAR( 100 )     
 , @Address2 VARCHAR( 100 )     
 , @City VARCHAR( 50 )     
 , @State CHAR( 2 )     
 , @OfficeTypeID INT     
 )
AS     
    
BEGIN     
SET NOCOUNT ON 

    DECLARE @SqlQry NVARCHAR( 4000 ) = ''     
    DECLARE @WhereCondition VARCHAR( 200 ) = ''     
    DECLARE @SearchCriteria VARCHAR( 200 ) = ''     
    -- Criteria     
 BEGIN      
  IF @OfficeName <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.OfficeName LIKE ' + CHAR( 39 ) + '%' + @OfficeName + '%' + CHAR( 39 )     
   END     
     
  IF @OfficeType <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.OfficeType LIKE ' + CHAR( 39 ) + '%' + @OfficeType + '%' + CHAR( 39 )     
   END     
     
  IF @Address1 <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.Address1 LIKE ' + CHAR( 39 ) + '%' + @Address1 + '%' + CHAR( 39 )     
   END     
     
  IF @Address2 <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.Address2 LIKE ' + CHAR( 39 ) + '%' + @Address2 + '%' + CHAR( 39 )     
   END     
     
  IF @City <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.City LIKE ' + CHAR( 39 ) + '%' + @City + '%' + CHAR( 39 )     
   END     
     
  IF @State <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.State LIKE ' + CHAR( 39 ) + '%' + @State + '%' + CHAR( 39 )     
   END     
 
 END     
      
 -- Prepare query for dynamic execution based on the given criteria     
    SET @SqlQry = N'     
 SELECT Office.OfficeID     
       , Office.OfficeName     
       , Office.OfficeType     
         , (IsNull(Office.Address1,'''') + '', '' + IsNull(Office.Address2,'''') + '', '' +  Office.City + '', '' + Office.[State] ) [Address]
       , Office.OfficeEMail   
  FROM [MyOfficeDetails] Office WITH (NOLOCK)      
  INNER JOIN [MainOfficesRef] ORCRef WITH (NOLOCK) ON ORCREF.OfficeID = Office.OfficeID   '     
     
 -- Applying criteria
  SET @WhereCondition = ' WHERE 1 = 1 '
  SET @WhereCondition = @WhereCondition + ' AND Office.OfficeType <> 3 '     
  SET @SqlQry = @SqlQry + @WhereCondition     
     
    IF @SearchCriteria <> ''     
        BEGIN     
            SET @SqlQry = @SqlQry + @SearchCriteria     
        END     
     
    -- Execute sql query     
    EXEC sp_executesql @SqlQry     
         
    Select @@ROWCOUNT [ResultCount] 
    -- Record Count  
     
END

Sample on “Alternate to dynamic SQL using simple select”

Note:
1.       Search is of two types –
a.       Specific search – exact values should match
b.       Contains search – given value could match on the column somewhere. i.e. Name like ‘%Srinivas%’
2.       So we had an assumption that, end user will either prepend or append ‘%’ for Contains search.
 
CREATE PROCEDURE [SearchOffice1]     
(  @OfficeName VARCHAR(100)     
 , @OfficeType VARCHAR(50)     
 , @Address1 VARCHAR(100)     
 , @Address2 VARCHAR(100)     
 , @City VARCHAR(50)     
 , @State CHAR(2)     
 , @OfficeTypeID INT     
 , @MaxRecordCountToReturn INT = 50
 , @MessageToUser NVARCHAR(MAX) = '' OUTPUT )     
AS  
BEGIN     
  
 DECLARE @Spacer Varchar(1)= SPACE(0) 
 DECLARE @ResultSetCount BIGINT =
 SET NOCOUNT ON 
 
  SELECT TOP (@MaxRecordCountToReturn) 
      O.[OfficeID] 
     ,O.[OfficeTypeID] 
     ,O.[OfficeName] 
     ,ISNULL(O.[OfficeType],@Spacer) [OfficeType] 
     ,(IsNull(O.Address1,'') + ', ' + IsNull(O.Address2,'') + ', ' +  O.City + ', ' +  
     O.[State]) [Address]       
     ,ISNULL(O.[OfficeEMail],@Spacer) [OfficeEMail] 
     ,OST.OfficeStatusTypeDesc 
  FROM  
    [Office] O WITH (NOLOCK) 
    INNER JOIN [OfficeRegionCrossRef] ORCR WITH (NOLOCK) ON O.OfficeID=ORCR.OfficeID
     
    AND (O.[OfficeName] LIKE @OfficeName OR @OfficeName IS NULL) 
    AND (O.[OfficeType] LIKE @OfficeType OR @OfficeType IS NULL) 
    AND (O.[Address1] LIKE @Address1 OR @Address1 IS NULL) 
    AND (O.[Address2] LIKE @Address2 OR @Address2 IS NULL) 
    AND (O.[City] LIKE @City OR @City IS NULL) 
    AND (O.[State] LIKE @State OR @State IS NULL) 
     
  ORDER BY O.DateCreated DESC
   
  SET @ResultSetCount = @@ROWCOUNT 
   
  IF (@MaxRecordCountToReturn>0) -- A VALUE OF 0 INDICATES RETURN ALL ROWS 
    IF (@ResultSetCount>@MaxRecordCountToReturn) 
     SET @MessageToUser = 'Attention: Search results exceed the maximum defined threshold. Please refine your search criteria' 
     --RAISERROR ('Error: Search results exceed the maximum defined threshold. Please refine your search criteria', 16, 1 ) 
END

-- Thanks to my collegue.