October 4, 2018

ASP.NET Health Monitoring using Custom Provider

ASP.NET Health Monitoring is a useful tool for logging error information that could ease diagnosing problems in a deployed application.

I have found this great article at Microsoft for configuring health monitoring feature in web projects.
Logging Error Details with ASP.NET Health Monitoring (C#)

Although builtin providers are more than enough to provide a rich information about the state of application. But there could be a chance you need to create custom provider to log additional information or additional storage option. Like in my case we need to log data in separate database which our application do not have direct access, but we have to call WCF service which would store data in target database. For this requirement I have written my custom provider.

Before proceeding to write code for custom provider, I recommend you to review the above mentioned article for health monitoring which will give you a strong background information.

Lets start writing custom provider class.

public class WCFCustomWebEventProvider : WebEventProvider
{
 //WCF service's client object.
    MiddleWare.ServiceClient _client = new MiddleWare.ServiceClient();
 
    public override void Initialize(String name, System.Collections.Specialized.NameValueCollection config)
    {
        base.Initialize(name, config);
    }

    public override void Flush()
    {

    }

    public override void ProcessEvent(WebBaseEvent raisedEvent)
    {
  //prepare StringBuilder with required information from WebBaseEvent object
        StringBuilder sb = new StringBuilder();
        sb.Append("EventId: " + raisedEvent.EventID + ", ");
        sb.Append("EventTime: " + raisedEvent.EventTime + ", ");
        sb.Append("EventTimeUtc: " + raisedEvent.EventTimeUtc + ", ");
        sb.Append("EventType: " + raisedEvent.GetType().Name + ", ");
        sb.Append("EventSource: " + raisedEvent.EventSource + ", ");
        sb.Append("EventSequence: " + raisedEvent.EventSequence + ", ");
        sb.Append("EventOccurrence: " + raisedEvent.EventOccurrence + ", ");
        sb.Append("EventCode: " + raisedEvent.EventCode + ", ");
        sb.Append("EventDetailCode: " + raisedEvent.EventDetailCode + ", ");
        sb.Append("Message: " + raisedEvent.Message + ", ");
        sb.Append("ApplicationPath: " + WebBaseEvent.ApplicationInformation.ApplicationPath + ", ");
        sb.Append("ApplicationVirtualPath: " + WebBaseEvent.ApplicationInformation.ApplicationVirtualPath + ", ");
        sb.Append("MachineName: " + WebBaseEvent.ApplicationInformation.MachineName + ", ");
        sb.Append("RequestUrl: " + HttpContext.Current.Request.Url.AbsoluteUri + ", ");
        sb.Append("Details: " + raisedEvent.ToString());

  //pass the string to WCF client object.
        _client.CreateLog(sb.ToString());
    }

    public override void Shutdown()
    {

    }
}

I have tried to keep it simpler to meet basic requirements, and avoid additional changes which you may need for performance requirements like make use of buffering.

Here, in the ProcessEvent override method, I prepared a string variable build up the required information from argument WebBaseEvent raisedEvent. Then the final string message containing all required logging details is then passed to the WCF client. Putting all the logging information in a single string variable is obviously not a good approach, I did here only to keep things simple and try to focus on the logic to add custom provider. A better approach could be to split data of our interest in separate variables and update the WCF method to accept multiple parameters.

Here we are done with custom provider's C# code.

I assumed that you have read the above mentioned article, so now I will move to the web.config part.

Add the following healthMonitoring tag inside system.web.

<healthMonitoring enabled="true">
   <providers>
  <add name="WCFCustomWebEventProvider_1" type="WCFCustomWebEventProvider" />
   </providers>
   <rules>
   <add name="All Events Rule" eventName="All Events" provider="WCFCustomWebEventProvider_1"
    minInstances="1" maxLimit="Infinite" minInterval="00:00:00" custom="" />
   </rules>
   <eventMappings>
  <add name="All Events" type="System.Web.Management.WebBaseEvent,System.Web,Version=4.0.0.0,Culture=neutral,PublicKeyToken=b03f5f7f11d50a3a"
   startEventCode="0" endEventCode="2147483647" />
   </eventMappings>
</healthMonitoring>

For simplicity, I am logging All Events, and also removed the configuration sections for bufferModes and profiles which you can add if you need more control over logging. Note that I have mentioned the custom provider WCFCustomWebEventProvider in the type attribute while adding provider.

At-minimum, health monitoring needs you to configure three basic elements.

  1. Providers: You have to specify a provider which will decide the storage target and do the work to store logging information. You can also define multiple providers like for some critical errors, you need to log information in the database and also want to generate an email to the system administrator. In our exmaple, we have used only one custom provider which will store information by calling WCF client method.

  2. EventMappings: It contains all the events names we are interested to log information about. In our example I placed a single event name All Events, which enables the application to log data about all events. If you dont need to log data about all events, you can define the desired events according to the requirements.

    Some other available events are:

    • All Events
    • Heartbeats
    • Application Lifetime Events
    • Request Processing Events
    • Infrastructure Errors
    • Request Processing Errors
    • All Audits
    • Failure Audits
    • Success Audits
  3. Rules: Rule is basically a mapping you have to define, to decide which event should mapped to which provider. In our example, I have defined that eventName="All Events" should be handled by provider="WCFCustomWebEventProvider_1".

September 17, 2018

jQuery AJAX failed calling ASP.NET WebMethods

Recently I migrated all code files from ASP.NET Web Application to ASP.NET WebSite (with Framework 4.5.2), because of some requirements we need to move towards website template. At the time I faced this issue, all the ajax calls stopped working. When I placed breakpoint on WebMethod, It was not getting fired.

Here is the WebMethod code:


[WebMethod(EnableSession = true)]
public static string GetPageSummary(string pageid)
{
 string html = "";

 if (!string.IsNullOrEmpty(pageid))
 {
  html = PageHelper.GetPageSummaryHtml(pageid);
 }

 return html;
}

And here is the jquery Ajax call for that method:

$.ajax({

  type: "POST",
  url: "MyPage.aspx/GetPageSummary",
  data: "{ 'pageid':'" + id + "'}",
  contentType: "application/json; charset=utf-8",
  dataType: "json",
  success: function (response) {
   showModelPopupForSummary(response.d)
  },
  error: function (response) {   
   //error-handling code...
  }
 });

It all looks fine and working in previous project, but not with new WebSite project.

After spending way too much time on this, I found out the solution for this.

You have to change enum value for AutoRedirectMode. Go to App_Code/RouteConfig.cs, inside RegisterRoutes() method, you will see this line:

 settings.AutoRedirectMode = RedirectMode.Permanent;

Change the enum value to RedirectMode.Off, final statement would be:

 settings.AutoRedirectMode = RedirectMode.Off;

After changing this all my ajax calls started working successfully.

August 27, 2018

How to use table variable in dynamic sql?

I came across a scenario while working with user-defined-table-types where I was need to query from table variable using dynamic SQL. I feel it worth sharing here in case someone might need in future.

I have created a user-defined table-type:

CREATE TYPE [dbo].[UDTT_Items] AS TABLE(    
    [ItemId] int identity(1, 1),
    [ItemCode] [varchar](10) NULL,
    [ItemName] [varchar](255) NULL, 
    [StockQty] decimal(18,3 ) NULL, 
    PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

In my stored procedure I declared a table variable for this UDTT:

declare @tblItems UDTT_Items

Here is how we normally use select statement on table variable.

select * from @tblItems

The problem comes when I tried to put this table variable in dynamic SQL. For example, if I try to run the above select statement from execute clause:

EXECUTE SP_EXECUTESQL N'select * from @tblItems'

It gives me the error message:

Must declare the table variable "@tblItems".

Fortuntely there is work around available by EXECUTE SP_EXECUTESQL. You can pass the table variable as a read-only parameter to dynamic SQL. So the following dynamic sql will work and run the given query as exptected.

DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * FROM @tblItems;';
EXECUTE SP_EXECUTESQL @SQL,N'@tblItems UDTT_Items READONLY',@tblItems;

Here I put my query in @SQL variable, and when calling EXECUTE SP_EXECUTESQL, we have to first define our parameters that need to be passed as second argument, and the actual table variable(i.e. parameter value) in third argument.

August 26, 2018

MS SQL Server - Show Query Execution Time

If you are using MS SQL Server 2008 or higher version, you can use the setting STATISTICS TIME. It will display the execution or run time in separate messages tab.

Setting this option ON will display the number of milliseconds required for complete statement cycle, i.e. parse, compile, and execute.

For example, following query will display order count for each customer who get registered in the month of August 2018.

select c.FirstName, c.LastName, c.RegistrationDate, Count(1) as OrderCount
from dbo.Customers c
 inner join dbo.Orders o on o.OrderId = c.OrderId
where 
 c.RegistrationDate between '1 August 2018' and '31 August 2018'
group by c.FirstName, c.LastName, c.RegistrationDate

Here is the sample output from messages tab, showing the execution time for above statement.

(200 rows affected)

 SQL Server Execution Times:
   CPU time = 1640 ms,  elapsed time = 2259 ms.
   

References:

July 21, 2018

Generating random strings with T-SQL

For testing scenarios you may need to generate random strings. You can use one of the following ways for this purpose.


declare @alphabet varchar(36) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
select
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1) +
substring(@alphabet, convert(int, rand()*36), 1);

This script will generate random string of length 5. If you need to increase or decrease number of characters in the generated string, you can add/remove substring statements in the above script.

A more easier approach can be to use NEWID() function, convert the result to varchar and then you can use LEFT(), RIGHT(), SUBSTRING() or any combination of string functions to generate string of desired length.

SELECT CONVERT(varchar(255), NEWID())

This will convert NEWID() output to varchar(255)

SELECT LEFT(CONVERT(varchar(255), NEWID()), 10)

This will display left 10 characters of the NEWID().

SELECT RIGHT(CONVERT(varchar(35), NEWID()), 10)

This will display 10 characters of the NEWID() from right.

SELECT SUBSTRING(CONVERT(varchar(35), NEWID()), 10, 25)

This will display substring of 15 characters starting from index 10 and ends at index 25.