Hangfire.IO – More Complex Application

Background

With our first Application out of the way, let us develop a slightly more involved console App.

Why?

Why such a fascination with HangFire.IO?

One of the tools that we use to monitor SQL Server is Sql Server Management Studio – Activity Monitor.

SQL Server Management Studio ( SSMS )

Activity Monitor

Image

Explanation

  1. delete top (1) from [HangFire].JobQueue with (readpast, updlock, rowlock)
    output DELETED.Id, DELETED.JobId, DELETED.Queue
    where (FetchedAt is null or FetchedAt < DATEADD(second, @timeout, GETUTCDATE()))
    and Queue in (@queues1)

    • 286 per minute
  2. update [HangFire].Server set LastHeartbeat = @now where Id = @id
    • 48 per minute

 

Lab

Data Model

Outline

We have two tables, homework.task and homework.taskDetail.

The homework.task will have the following columns

  1. taskID
  2. taskLabel
  3. interval
  4. cronExpression

And, the homework.taskDetail column will have

  1. taskID
  2. processedCount

The application will store task information in the homework.task table.

Based on time schedule, we increment the processedCount column in the homework.taskDetail table.

 

Database Diagram

SQL Script

Table

Table – homework.task


if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')


end
go

if object_id('[homework].[task]') is null
begin

	create table [homework].[task]
	(

		  [taskID]			int not null
		, [taskLabel]		varchar(60) not null
		, [interval]		int not null

		, [cronExpression]  varchar(60) not null

		, [dateAdded]		datetime not null
			constraint [constraintHWTaskDefaultDateAdded] 
				default getdate()

		, [dateUpdated]		datetime null

		, constraint [PK_HWTask] primary key
			(
				[taskID]
			)
			 			
	)

end
go



 

Table – homework.taskDetail


if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')

end
go

if object_id('[homework].[taskDetail]') is null
begin

	create table [homework].[taskDetail]
	(

		  [taskID]			int not null
		
		, [processedCount]  int not null

		, [dateAdded]		datetime not null
			constraint [constraintHWTaskDetailDefaultDateAdded] 
				default getdate()

		, [dateUpdated]		datetime null

		, constraint [PK_HWTaskDetail] primary key
			(
				[taskID]
			)

		, constraint [FK_HWTaskDetail] foreign key
			(
				[taskID]
			)
			references [homework].[task]
			(
				[taskID]
			)
			 			
	)


end
go



Stored Procedure

SP – homework.ups_taskInit



if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')


end
go


if object_id('[homework].[usp_taskInit]') is null
begin

	exec('create procedure [homework].[usp_taskInit] as ')


end
go

alter procedure [homework].[usp_taskInit]
as

begin

	set nocount on;
	set XACT_ABORT on;

	truncate table [homework].[taskDetail]
	
	delete from [homework].[task]

end
go



 

SP – homework.ups_taskAdd


if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')


end
go

if object_id('[homework].[usp_taskAdd]') is null
begin

	exec('create procedure [homework].[usp_taskAdd] as ')


end
go

alter procedure [homework].[usp_taskAdd]
	  @taskID		   int 
	, @taskLabel	   varchar(60)
	, @interval		   int 

	, @cronExpression  varchar(60)

as

begin

	set nocount on;
	set XACT_ABORT on;

	MERGE [homework].[task] AS tblTarget
    USING 
			(
				SELECT 
						  [taskID] = @taskID
						, [taskLabel] = @taskLabel
						, [interval] = @interval
						, [cronExpression] = @cronExpression
						, [dateAdded] = getdate()
			) AS tblSource
	ON 
			(
				    (tblTarget.taskID = tblSource.taskID )
			)
	WHEN MATCHED THEN
    UPDATE 
	SET 
		  tblTarget.[taskLabel] = tblSource.[taskLabel]
		, [interval]= tblSource.[interval]
		, [cronExpression] = tblSource.[cronExpression]
		, tblTarget.[dateUpdated] = getdate()

	WHEN NOT MATCHED BY TARGET THEN
		INSERT 
		(
			  [taskID]
			, [taskLabel]
			, [interval]
			, [cronExpression]
			, [dateAdded]
		)
		VALUES 
		(
			  [taskID]
			, [taskLabel]
			, [interval]
			, [cronExpression]
			, [dateAdded]

		)
	
	;
end
go




SP – homework.ups_taskDetailAdd



if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')


end
go

if object_id('[homework].[usp_taskDetailAdd]') is null
begin

	exec('create procedure [homework].[usp_taskDetailAdd] as ')


end
go

alter procedure [homework].[usp_taskDetailAdd]
	  @taskID		   int 

as

begin

	set nocount on;
	set XACT_ABORT on;

	MERGE [homework].[taskDetail] AS tblTarget
    USING 
			(
				SELECT [taskID] = @taskID
			) AS tblSource
	ON 
			(
				    (tblTarget.taskID = tblSource.taskID )
			)
	WHEN MATCHED THEN
    UPDATE 
	SET 
		  tblTarget.[processedCount] = isNull(tblTarget.[processedCount], 0) + 1
		, tblTarget.[dateUpdated] = getdate()

	WHEN NOT MATCHED BY TARGET THEN
		INSERT 
		(
			  [taskID]
			, [processedCount]
		)
		VALUES 
		(
			  [taskID]
			, 1
		)
	
	;

end
go



Function

Function – homework.itvf_taskDetail


if schema_id('homework') is null
begin

	exec('create schema [homework] authorization [dbo]')


end
go

if object_id('[homework].[itvf_taskDetail]') is null
begin

	exec('create function [homework].[itvf_taskDetail]() returns table return ( select [shell] = 1/0 ) ')


end
go

alter function [homework].[itvf_taskDetail]()
returns TABLE
return
(
	select	
	
			  tblT.[taskID]
			, tblT.[taskLabel]
			, tblT.[interval]
			, tblT.[cronExpression]

			, tblTD.[processedCount]
			, tblTD.[dateAdded]
			, tblTD.[dateUpdated]

	from    [homework].[task] tblT

	left outer join [homework].[taskDetail] tblTD

		on tblT.[taskID] = tblTD.[taskID]

)
go


C# Code


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using Hangfire;
using Hangfire.SqlServer;


namespace consoleApp
{
    class Program
    {
		static Thread  t;
		static DateTime dtStartTime;
		static DateTime dtCurrent;
		static TimeSpan tsElapsed;
		static String strLog;

		static DateTime dtExpectedEndTS;
			
		const int MAX_TIME_ELAPSED_MINUTES = 60;
		
		const string FORMAT_MAX_TIME_ELAPSED = "Exiting after {0} seconds";
		const string FORMAT_CRONTAB_DISPLAY = "TASK ID {0}, CRON EXPRESSION {1}";
		
		static int iMaxDurationInMinutes = MAX_TIME_ELAPSED_MINUTES ;
			
		// 1 second
		static TimeSpan	tsPeriodicCheck = new TimeSpan(0, 0, 1);
		
		static public bool IsCancelled { get; set; } 

		const String connectionStringDemoID = "connectionStringDemo";
		static String conStringDemo = null;
			
		const String connectionStringHFID = "connectionStringHF";
		
		static DateTime dtStartTS = (DateTime.Now);
			
		static String dtStartTSAsString = (dtStartTS).ToString(@"hh\:mm\:ss"); 

			
        static void Main(string[] args)
        {
			
			getDBConnectionString();
			
			getAppConfig();
			
			IsCancelled = false;
			
			//Instanciate Thread
			t = new Thread(threadBackgroundJobScheduler);
			
			//Start Thread
			t.Start();
		
			Console.WriteLine("Hangfire Server started.");			
			
			Console.WriteLine("On Main thread, wait 30 seconds");			
			
			Thread.Sleep(30);
				
    	
			//wait for thread to finish
			if (t != null)
			{
			
				t.Join();
				
			}		
			
			
            
		}

		static void threadBackgroundJobScheduler()
		{
						
			useHangFire();
	
		}	

		static void scheduledTaskInitDB()
		{	
		
			string dbSQL = "[homework].[usp_taskInit]";
			using(SqlConnection sqlConnection = new SqlConnection(conStringDemo))
			using(SqlCommand sqlCommand = new SqlCommand(dbSQL , sqlConnection))
			{

				sqlCommand.CommandType = CommandType.StoredProcedure;    

				sqlConnection.Open();
				sqlCommand.ExecuteNonQuery();
			}

		}
		
		static void scheduledTaskIndividualDB(scheduleTask objScheduleTask )
		{	
		
			string dbSQL = "[homework].[usp_taskAdd]";
			using(SqlConnection sqlConnection = new SqlConnection(conStringDemo))
			using(SqlCommand sqlCommand = new SqlCommand(dbSQL , sqlConnection))
			{

				sqlCommand.CommandType = CommandType.StoredProcedure;    
				
				sqlCommand.Parameters.Add("@taskID", SqlDbType.Int).Value = objScheduleTask.taskID;
				sqlCommand.Parameters.Add("@taskLabel", SqlDbType.VarChar).Value = objScheduleTask.taskLabel;
				sqlCommand.Parameters.Add("@interval", SqlDbType.Int).Value = objScheduleTask.interval;
				sqlCommand.Parameters.Add("@cronExpression", SqlDbType.VarChar).Value = objScheduleTask.cronExpression;
				
				sqlConnection.Open();
				sqlCommand.ExecuteNonQuery();
			}

		}
		
        static void scheduleTaskIndividual(int id, int interval)
        {
			

			String strCronExpression;
			String taskLabel;
			scheduleTask objScheduleTask;
			String strTaskEntry;
		
			objScheduleTask = new scheduleTask(id, interval);
			taskLabel = objScheduleTask.taskLabel;
			strCronExpression = objScheduleTask.cronExpression;
		
			strTaskEntry = String.Format(FORMAT_CRONTAB_DISPLAY, taskLabel, strCronExpression);
			
			Console.WriteLine(strTaskEntry);
			
			scheduledTaskIndividualDB(objScheduleTask );
			
			RecurringJob.AddOrUpdate
				(
					  taskLabel
					, () => backgroundTask.processTask(id)
					, strCronExpression
				);
				
			
		}
		
        static void scheduleTasks()
        {
			scheduledTaskInitDB();
			
			scheduleTaskIndividual(1, 1);
			
			scheduleTaskIndividual(2, 3);
			
			scheduleTaskIndividual(3, 5);
			
			scheduleTaskIndividual(4, 10);
			
			scheduleTaskIndividual(5, 15);
			
			scheduleTaskIndividual(6, 30);
			
			scheduleTaskIndividual(7, 45);
			
			scheduleTaskIndividual(8, 60);
			
			
		}
		
        static void useHangFire()
        {

			//get Current Time
			dtStartTime = DateTime.Now;
					
            GlobalConfiguration.Configuration.UseSqlServerStorage(connectionStringHFID);
			
            using (var server = new BackgroundJobServer())
            {

		
				BackgroundJob.Schedule(() => backgroundTask.sayHello(), TimeSpan.FromSeconds(2));
				
				scheduleTasks();
				
			
				while 
				(
					IsCancelled==false
				) 
				{ 
					
					dtCurrent = DateTime.Now;

					tsElapsed = dtCurrent - dtStartTime;
				
					if (dtCurrent >= dtExpectedEndTS)
					{
						
						IsCancelled=true;
						
						strLog = String.Format(FORMAT_MAX_TIME_ELAPSED, tsElapsed.TotalSeconds);  
						
						Console.WriteLine(strLog);
						
						
						break;
						
					}
				
					//wait a little longer
					Thread.Sleep(tsPeriodicCheck); 
			
				} //while not cancelled
			
			}//using	
			

		} // useHangFire()
	 
	 
		static private void getDBConnectionString()
		{
			
			if (ConfigurationManager.ConnectionStrings[connectionStringDemoID] != null)
			{
				
				conStringDemo = ConfigurationManager.ConnectionStrings[connectionStringDemoID].ConnectionString;
			
			}
			else
			{
				 throw new System.Exception("connectionStringDemoID not found in app.config");
				
			}
			
		} //getDBConnectionString()
		

		static private void getAppConfig()
		{
			
			String strMaxDurationInMinutes;
			bool    bRC = false;

			String   dtExpectedEndTSAsString;
			
			System.TimeSpan tsDurationInMins;
			
			iMaxDurationInMinutes = MAX_TIME_ELAPSED_MINUTES;
			
			Console.WriteLine("Start Date :- " + dtStartTSAsString);
			
			
			if (ConfigurationManager.AppSettings["maxDurationInMinutes"] != null)
			{
				
				strMaxDurationInMinutes = ConfigurationManager.AppSettings["maxDurationInMinutes"];
				
				try
				{
					bRC = Int32.TryParse(strMaxDurationInMinutes, out iMaxDurationInMinutes);
				}
				catch			
				{
					iMaxDurationInMinutes = MAX_TIME_ELAPSED_MINUTES;
				}
				
				tsDurationInMins = new System.TimeSpan(0, iMaxDurationInMinutes, 0);
				
				dtExpectedEndTS = dtStartTS.Add(tsDurationInMins);
				
				dtExpectedEndTSAsString = (dtExpectedEndTS).ToString(@"hh\:mm\:ss"); 
				
				Console.WriteLine("Expected End Date :- " + dtExpectedEndTSAsString);

				
			}
			else
			{
				
				iMaxDurationInMinutes = MAX_TIME_ELAPSED_MINUTES;
				
				tsDurationInMins = new System.TimeSpan(0, iMaxDurationInMinutes, 0);
				
				dtExpectedEndTS = (DateTime.Now).Add(tsDurationInMins);
				
			}
			
		} //getAppConfig()
				
		
    } // class program
	
	
	class backgroundTask
	{
		
		static String FORMAT_DATETIME = "{0:ddd, MMM d, yyyy  hh:mm tt}";
		
		static String connectionStringDemoID = "connectionStringDemo";
		static String conStringDemo = null;
				
		
		[AutomaticRetry(Attempts = 0)]
		static public void sayHello()
		{
			
			String strTimeStamp;
			String strLog;
			
			strTimeStamp = String.Format(FORMAT_DATETIME, DateTime.Now);
			
			strLog = "At " + strTimeStamp + " ... Saying Hello";

			//Say Time
			Console.WriteLine(strLog);
		
			
		} //sayHello
		
		static private void getDBConnectionString()
		{
			
			if (conStringDemo == null)
			{
				
				
				if (ConfigurationManager.ConnectionStrings[connectionStringDemoID] != null)
				{
					
					conStringDemo = ConfigurationManager.ConnectionStrings[connectionStringDemoID].ConnectionString;
				
				}
				else
				{
					 throw new System.Exception("connectionStringDemoID not found in app.config");
					
				}
			}
			
		} //getDBConnectionString()
		
		
		[AutomaticRetry(Attempts = 0)]
		[DisableConcurrentExecution(timeoutInSeconds: 10 * 60)]
		static public void processTask(int taskID)
		{
	
			string dbSQL = "[homework].[usp_taskDetailAdd]";
			
			
			if(conStringDemo == null)
			{
				getDBConnectionString();
			}
			
			using(SqlConnection sqlConnection = new SqlConnection(conStringDemo))
			using(SqlCommand sqlCommand = new SqlCommand(dbSQL , sqlConnection))
			{

				sqlCommand.CommandType = CommandType.StoredProcedure;    
				
				sqlCommand.Parameters.Add("@taskID", SqlDbType.Int).Value = taskID;
				
				sqlConnection.Open();
				sqlCommand.ExecuteNonQuery();
			}
			
		} //processTask
		
		
	}
	
}




 

 

Run

Here is the output from running the app..

Run Output

 

SQL Track

Code


select *

from   [homework].[itvf_taskDetail]()

SQL Output

 

Source Code

GitHub

As alway posted to GitHub.

Here is the URL.

 

Summary

Will come back and share our findings in terms of reliability and performance.

It will be a bit long and so will save it for another post.

Reference

  1. How to write your first multi-threaded application with c#
    Link
  2. How do I get the time difference between two DateTime objects using C#?
    Link

 

Hangfire.io – Installation & Hello World Application

Background

One of the applications that we are hosting relies on Hangfire to perform some background processing.

It is time to get smart quick!

What Are we going To Do

We will take the minimalist approach.

Checklist

  1. No Visual Studio IDE.
  2. Just an editor, notepad++
  3. Nuget, Packet Manager
  4. Hangfire packages
  5. Validate that MSBuild is installed
    • If not download and install it
  6. Create Source Files
    • Simple File
    • Build File
  7. Invoke Build
  8. Trial Runs

Installation

Hangfire is based on Microsoft .Net and we can install it by laying down Nuget and Hangfire specific packages.

Nuget

Outline

  1. Download nuget

 

Download nuget

Download nuget.exe from here.

The current version is 3.5.

And, we reached for the file under the “Windows x86 CommandLine” section.

 

Hangfire

Outline

  1. Download Hangfire packages
    • Install Hangfire.core
    • Install Hangfire.sqlserver

hangfire.core

Code


rem installHangFireCore

set _folderNuget=N:\downloads\nuget\v3.5\
set _logger=N:\downloads\wtee\wtee.exe

if not exist log mkdir log

%_folderNuget%\nuget.exe install Hangfire.Core | %_logger% log\Hangfire.core.log

Output

hangfire.SqlServer

Code


rem installHangFire.SqlServer

set _folderNuget=N:\downloads\nuget\v3.5\
set _logger=N:\downloads\wtee\wtee.exe

if not exist log mkdir log

%_folderNuget%\nuget.exe install Hangfire.SqlServer | %_logger% log\Hangfire.sqlserver.log

Output

 

Files

 

Package Description Links  Files
 Hangfire.core Core components for Hangfire (background job system for ASP.NET applications). Link (Nuget Gallery )  a) Hangfire.Core
b) Owin
c) Newtonsoft.Json
 Hangfire.sqlServer  SQL Server 2008+ (including Express), SQL Server LocalDB and SQL Azure storage support for Hangfire (background job system for ASP.NET applications).  Link ( Nuget Gallery )  a) Hangfire.Core
b) Hangfire.SqlServer
c) Owin
d) Microsoft.Owin
e) Microsoft.Owin.Host.SystemWeb
f) Newtonsoft.Json

 

 

 

MS Build

Download Build

Microsoft Build Tools 2015

Download Site

Download MSBuild from here.

Install
Welcome

Installing

Setup Completed

 

Source Code

Source File

Source File

hfSimple.cs

Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Hangfire;
using Hangfire.SqlServer;


namespace consoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            useHangFire();
        }


        static void useHangFire()
        {


            GlobalConfiguration.Configuration.UseSqlServerStorage("connectionString");
			
			
            using (var server = new BackgroundJobServer())
            {

		
				BackgroundJob.Schedule(() => backgroundTask.sayHello(), TimeSpan.FromSeconds(2));
				
				RecurringJob.AddOrUpdate("IDStopClock", () => backgroundTask.sayTime(), Cron.Minutely);
				
				Console.WriteLine("Hangfire Server started. Press any key to exit...");			
				
                Console.ReadKey();
            }
			

	 }
	 
	 


    } // class program
	
	
	class backgroundTask
	{
		
		static String FORMAT_DATETIME = "{0:ddd, MMM d, yyyy  hh:mm tt}";
		
		
		static long lIteration = 0;
		

		static public void sayHello()
		{
			
			String strTimeStamp;
			String strLog;
			
			strTimeStamp = String.Format(FORMAT_DATETIME, DateTime.Now);
			
			strLog = "At " + strTimeStamp + " ... Saying Hello";

			//Say Time
			Console.WriteLine(strLog);
		
			
		} //sayHello
		
		[AutomaticRetry(Attempts = 5)]
		[DisableConcurrentExecution(timeoutInSeconds: 10 * 60)]
		static public void sayTime()
		{
			
			String strTimeStamp;
			String strLog;
			
			strTimeStamp = String.Format(FORMAT_DATETIME, DateTime.Now);
			
			strLog = lIteration + ")" + strTimeStamp;

			//Say Time
			Console.WriteLine(strLog);
		
			lIteration = lIteration + 1;
			
		} //sayTime
		
		
	}
	
}


 

Configuration

App.config

Code


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>

  <connectionStrings>
    <add name="connectionString" connectionString="Trusted_Connection=Yes;Server=.;Application Name=hangfire;database=labHangFire;" />

  </connectionStrings>
</configuration>
Explanation
  1. ConnectionStrings
    • Trusted_Connection=Yes
      • Integrated Security; as opposed to SQL Server username/password combination
    • Application Name
      • Given name for the Application
    • database
      • Name of the database on the specified SQL Instance

 

Build Files

Build Configuration File

hfsimple.csproj

Code

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" />
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <ProjectGuid>{2957BCCD-03ED-4FF3-868D-8A07226516D7}</ProjectGuid>
    <OutputType>Exe</OutputType>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <RootNamespace>hfsimple</RootNamespace>
    <AssemblyName>hfsimple</AssemblyName>
    <TargetFrameworkVersion>v4.5.2</TargetFrameworkVersion>
    <FileAlignment>512</FileAlignment>
    <AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <OutputPath>bin\Debug\</OutputPath>
    <DefineConstants>DEBUG;TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <PlatformTarget>AnyCPU</PlatformTarget>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <OutputPath>bin\Release\</OutputPath>
    <DefineConstants>TRACE</DefineConstants>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <ItemGroup>
  
    <Reference Include="Hangfire.Core">
      <HintPath>package\Hangfire.Core.1.6.12\lib\net45\Hangfire.Core.dll</HintPath>
      <Private>True</Private>
    </Reference>
	
	<Reference Include="Hangfire.SqlServer">
	  <HintPath>package\Hangfire.SqlServer.1.6.12\lib\net45\Hangfire.SqlServer.dll</HintPath>
	  <Private>True</Private>
	</Reference>
	<Reference Include="Newtonsoft.Json">
      <HintPath>package\Newtonsoft.Json.5.0.1\lib\net45\Newtonsoft.Json.dll</HintPath>
      <Private>True</Private>
    </Reference>
    
	<Reference Include="System" />
    <Reference Include="System.Core" />
    <Reference Include="System.Xml.Linq" />
    <Reference Include="System.Data.DataSetExtensions" />
    <Reference Include="Microsoft.CSharp" />
    <Reference Include="System.Data" />
    <Reference Include="System.Net.Http" />
    <Reference Include="System.Xml" />
  
  </ItemGroup>
  
  <ItemGroup>
    <Compile Include="hfSimple.cs" />
    <Compile Include="Properties\AssemblyInfo.cs" />
  </ItemGroup>
  
  <ItemGroup>
    <None Include="App.config" />
    <None Include="packages.config" />
  </ItemGroup>
  
  <Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
  <!-- 
  
	  To modify your build process, add your task inside one of the targets below and uncomment it. 
      Other similar extension points exist, see Microsoft.Common.targets.
	
	  <Target Name="BeforeBuild">
	  </Target>
	  <Target Name="AfterBuild">
	  </Target>
	  
  -->
</Project>

Build Script

buildhf.cmd

Code

set "_appFolder=C:\Program Files (x86)\MSBuild\14.0\Bin\"

"%_appFolder%\msbuild" hfSimple.csproj /t:Rebuild 

 

Usage

Sample Run

Explanation

  1. Each minute, we display the current time on the console

 

Review

SQL Server

Upon initial run, Hangfire creates its tables.

Object Explorer

Image

Explanation

  1. Objects / Tables created

 

Errors

Error Signature Error Sample Resolution
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database “labHangFire” requested by the login. The login failed.
Login failed for user ‘dadeniji’.
Create database referenced for hangfire

 

 

Source Code Version Control

GitHub

DanielAdeniji/hangfireConsoleSimple
Link

Summary

What we have is a simple, console application.

In later posts, we will dig more into profiling.

SQL Server – SSMS – Options – “Override connection string time-out value for table designer updates”

 

Background

This is the third in a series of posts on timeouts

SQL Server Management Studio ( SSMS)

Designers

For those that use the Designer built into SSMS to make structural table changes, the Designer Option panel is a good place to go and make sure that the default settings are what you want them to be.

Screen Shot

Here is what that screen looks:

Initial

Post Changes

Image

Explanation
  1. Here we changed the “Transaction Time-out after:” from the default of 30 seconds to 90 seconds

Misnomer

I think outside of the Database World and specifically SQL Server, it is OK to use the phrase & words, but I think for a tool that targets SQL Server and only that table, a bit more care should be taken.

And, you likely ask why.

Grouping :- Override connection string time-out value for table designer updates
Phrase :- Transaction time-out after

Here are the difficulties that I have with the Group Label “Override connection string time-out value

  1. We all know that the Connection String does not expose command execution nor lock timeout
    • Command Execution can be set in the Connection or Command Object, but not in the actual connection string
    • Because of this understanding, coders have to look into the code and set the timeouts
    • They are not able to do so declaratively in the Web.config and thus not suffer the pain of recompile
  2. There is more than enough misappropriation that Connection Open timeouts and Command Execution timeouts, are one the same

Microsoft – Connect

  1. Item #  :- 3130697
    • Link :- Link
      Opened By: Daniel Adeniji
      Date Opened:- 2017-March-25
      Type :- Suggestion
      Status :- Active

Sql Server Management Studio ( SSMS ) – Query Results – Max Column Length

Background

Ran a query that gives me the most expensive queries.

As I try to capture the SQL Column I am finding out that the results is chopped off.

 

TroubleShooting

SQL Server Management Studio

Query Results

Access the Options Menu and transversed to Query Results \ SQL Server \ Results to Grid

resultstogrid

And, determine the max Non XML data is 65535 Characters.

 

What is Column Length?

Changed the query to emit the column length…

statementlength

Explanation

  1. Our most expensive query is 147,020 characters
  2. Some other queries are 16,736 and another is 11,133

 

StackOverflow

Googled for a fix and as always found one via Stackoverflow.

Link

Copy Current Cell

stackoverflow_question-11897950

Visualize

stackoverflow_question-11897950_visualize

 

SSMSBoost

Journeyed to SSMS Boost web site here

Available Binaries

Downloaded version Specific

download

Download & Install

Downloaded and Installed SSMS Boost

 

License

Retrieve User/machine Has Token

Retrieved User/Machine Token through running SSMS and accessing menu items SSMSBoost / About License.

ssmsboost-aboutlicense-croppedup

 

Request Community License

Requested Community License by going here.

requestingfreelicense-cropped-up

Here is the Free License Request Form that comes up.

requestlicense-cropped

 

Received License

Received license over email

 

Applied License

 

Usage

Ran the query again.

Grid

copycurrentcell1to1-cropped-up

 

Select Grid Cell

Selected the Grid Cell and right clicked on our selection.

Chose “Copy current cell 1:1” from the dropdown menu.

We were able to capture our cell’s content.

 

Editor

NotePad++

Was able to paste into Notepad++.

Contents

notepadplusplus-summary

Summary

In Notepad++, accessed the menu item View \ Summary.

Here is what our summary window looks like:

notepadplusplus-summary-20170216-0604pm

Explanation:

We have about over 43 thousand characters.

 

Dedicated

Dedicated to SSMSBoost’s directory Andrei Rantsevich.

 

Summary

From the Query’s contents:

(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 bit,@p__linq__3 uniqueidentifier,@p__linq__6 bit,@p__linq__4 uniqueidentifier,@p__linq__5 bit)SELECT
[Project40].[Id] AS [Id],
[Project40].[C1] AS [C1],
[Project40].[Id2] AS [Id1],
[Project40].[Id5] AS [Id2],
[Project40].[Id1] AS [Id3]

we can see that the query is not handwritten, but generated via Microsoft .Net Linq.

Simple .Net ASPX/DB Sample

Objective

Needed a sample C# code to track .Net Application and its interaction with the database.

 

Files

  1. Database
    • MS SQL Server
      • Stored Procedure dbo.TimeDelay_hh_mm_ss;
  2. .Net
    • Web.config
    • testDB.aspx
    • testDB.cs

 

Stored Procedure dbo.TimeDelay_hh_mm_ss;

Stolen from WAITFOR (Transact-SQL) ( link )


IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL  
    DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;  
GO  
CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss   
    (  
    @DelayLength char(8)= '00:00:00'  
    )  
AS  
DECLARE @ReturnInfo varchar(255)  
IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0  
    BEGIN  
        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength   
        + ',hh:mm:ss, submitted.';  
        -- This PRINT statement is for testing, not use in production.  
        PRINT @ReturnInfo   
        RETURN(1)  
    END  
BEGIN  
    WAITFOR DELAY @DelayLength  
    SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ',   
        hh:mm:ss, has elapsed! Your time is up.'  
    -- This PRINT statement is for testing, not use in production.  
    PRINT @ReturnInfo;  
END;  
GO  

Web.config


<!--l version="1.0"-->

<configuration>
	
	<connectionStrings>

		<clear />
	 
		<add name="dbConn" connectionString="Server=DBDEV;Database=DBLAB;Trusted_Connection=Yes;" >

		</add>
		
	</connectionStrings>

	
</configuration>

testDB.aspx



<%@ Page language="c#" Codefile="testDB.cs" AutoEventWireup="true" Inherits="_Default" Debug="true" %>

<title>
Simple
</title>


<form id="form1" runat="server">


<div>

		<asp:button id="btnTestDb" onclick="btnTestDbClick" runat="server" text="Test Database Connection" />
		
		</BR>
		&nbsp;&nbsp;
		
		Timeout (HH:MM:SS)
		<asp:TextBox runat="server" ID="txtTimeoutValue" text="00:01:00" >
		</asp:TextBox>
		
		<asp:button id="btnTestDbSP" onclick="btnTestDbSPClick" runat="server" text="Test Database Stored Procedure" />
		
		<asp:Label runat="server" ID="labelStatus" >
		</asp:Label>
		
		
	</div>


</form>


 

 

testDB.cs


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
 public partial class _Default : System.Web.UI.Page
 {
	 
	private String strDBConnectionString = null;
	private SqlConnection connection = null;
	private String CHAR_NEWLINE = "</BR>";
	
	protected void Page_Load(object sender, EventArgs e)
	{
		
	}
	
	protected Boolean connectDB()
	{
		
		Boolean bDBConnected = false;
		
		try
		{
			
			//strDBConnectionString = ConfigurationManager.ConnectionStrings["EarthConn"].ConnectionString;
			
			if (ConfigurationManager.ConnectionStrings["dbConn"] == null)
			{

				Response.Write("ConfigurationManager.ConnectionStrings['dbConn']");
				
			}
			
			strDBConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
			
			if (strDBConnectionString == null)
			{
				return bDBConnected;
			}

				
			connection = new SqlConnection
							  (
								strDBConnectionString
							  );
											
			
			if (connection == null)
			{
				return bDBConnected;				
			}
				
			connection.Open();

			
			if (
					(connection.State == ConnectionState.Open)
				)			
			{
				
				labelStatus.Text += "Connection OK!"  + CHAR_NEWLINE;
				
				bDBConnected = true;
			}
			else
			{
				
				//Response.Write("No Connection!" + CHAR_NEWLINE);
				labelStatus.Text += "Connection OK!"  + CHAR_NEWLINE;				
			}
			
			
		}
		catch (Exception ex)
		{
			
			//Response.Write("No Connection!" + ex.Message + CHAR_NEWLINE);
			
			labelStatus.Text += "No Connection!" + ex.Message + CHAR_NEWLINE;
			
		}
		
		return (bDBConnected);

	}
 
	protected void btnTestDbClick
					(
						  object sender
						, EventArgs e
					)
	{
		
		Boolean bDBConnected = false;
		
		labelStatus.Text = "";
		
		bDBConnected = connectDB();
		
		if (bDBConnected)
		{
				connection.Close();
		}			
		
	}    
	
 
	protected void btnTestDbSPClick
					(
						  object sender
						, EventArgs e
					)
	{
		
		Boolean bDBConnected = false;
		String strDelayLength;
		
		try
		{
		
			labelStatus.Text = "";
		
			//strDelayLength = "00:05:00";
			strDelayLength = txtTimeoutValue.Text;
			
			bDBConnected = connectDB();
			
			// 1.  create a command object identifying the stored procedure
			SqlCommand cmd  = new SqlCommand("dbo.TimeDelay_hh_mm_ss", connection);

			// 2. set the command object so it knows to execute a stored procedure
			cmd.CommandType = CommandType.StoredProcedure;

			// 3. add parameter to command, which will be passed to the stored procedure
			cmd.Parameters.Add(new SqlParameter("@DelayLength", strDelayLength));

			labelStatus.Text += "DB SP Started @ " + DateTime.Now.ToString() + CHAR_NEWLINE;

			labelStatus.Text += "Waiting " + strDelayLength + CHAR_NEWLINE;
			
			// execute the command
			cmd.ExecuteNonQuery();
			
			labelStatus.Text += "DB SP Ended @ " + DateTime.Now.ToString() + CHAR_NEWLINE ;		
			
			if (bDBConnected)
			{
					connection.Close();
			}		

		}
		catch (Exception ex)
		{
			labelStatus.Text += ex.Message;
			throw ex;
		}		
    }
		
	
 }	

 

Output

Processing Wait 15 Seconds

testDB-ReqestWait15seconds

 

Processed Wait 15 Seconds

 

testDB-ProcessedWait15seconds

 

Diagnostics

Failed Request Tracing

Sample files from “Failed Request Tracing“.

FailedRequestTracing-fr000039

 

Why you can’t be a good .Net Developer // Postgres and MySQL

Day Jobs

Day jobs aside, there are some people that have had a thing or two to say in response to an Industry’s rant on “Why you can’t be a good .Net developer“.

Here are some of them….

  1. Enrico, Italian programmer in Sweden.
    On Being a Good .NET Developer
    Link
  2. Ted Neward
    It is too possible
    Link
  3. Ayende @ Rehein
    Why you can’t be a good .Net Developer
    Link

 

Ayende Rahien & Oren Eini

I read through a couple of Oren Eini’s other postings and found out his take on Databases quite promising.

BTW, he blogs under the pseudonym, Ayende Rahien.

 

Evan Klitzke

In a recent post, Ayende summarizes and reflects on Evan Klitzke’s post on “Why Uber Engineering switched from Postgres to MySQL“.

 

  1. WHY UBER ENGINEERING SWITCHED FROM POSTGRES TO MYSQL
    2016-07-26
    Link
  2. Migrating Uber from MySQL to PostgreSQL
    2013-03-13
    Link

Back to Real

Enrico’s Take

“I never compromise on excellence. It’s just that with some teams, the way to get there is longer than with others.

To me the solution isn’t to run away from beginners. It’s to inspire and mentor them so that they won’t stay beginners forever and instead go on to do the same for other people. That applies as much to .NET as it does to any other platform or language.

If you aren’t the type of person who has the time or the interest to raise the lowest common denominator, that’s perfectly fine. I do believe you’re better off moving somewhere else where your ambitions aren’t being held back by inexperienced team members. As for myself, I’ll stay behind — teaching.”

.Net – ASPX – Avoid Parser Error when commenting out code

Background

While trying out different code snippets as I worked on tweaking an ASPX app, I ran into a parser error.

BTW, the enhancement is detailed here.

Let us quickly go over the parser error.

 

Parser Error

 

Code


<asp:TemplateColumn HeaderText="Link" >

	<ItemTemplate>		

	<!--
		
		 <asp:HyperLinkColumn 
				HeaderText="Link" 		
				Text="Link" 
				NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "eventLink") %>'
				Visible="True"
				Target="_blank"
				runat="server"												
				> 	
				
		</asp:HyperLinkColumn>   		    

	-->
	
	 <asp:HyperLink
			HeaderText="Link" 		
			Text="Link" 
			NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "eventLink") %>'
			Visible="True"
			Target="_blank"
			runat="server"
			> 	
	 </asp:HyperLink>   		    		                         																				
	
	</ItemTemplate>
	
</asp:TemplateColumn>


 

Parser Error

Image

ParserError-20160730-0731AM

Textual


Parser Error

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. 

Parser Error Message: Databinding expressions are only supported on objects that have a DataBinding event. System.Web.UI.WebControls.HyperLinkColumn does not have a DataBinding event.

Source Error: 

Correct

Here is the change.

Basically, it is to replace “<!–” and “–>” with “<%–” and “–%>“.


<asp:TemplateColumn HeaderText="Link" >

	<ItemTemplate>		

	<%--
		
		 <asp:HyperLinkColumn 
				HeaderText="Link" 		
				Text="Link" 
				NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "eventLink") %>'
				Visible="True"
				Target="_blank"
				runat="server"												
				> 	
				
		</asp:HyperLinkColumn>   		    

	--%>
	
	 <asp:HyperLink
			HeaderText="Link" 		
			Text="Link" 
			NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "eventLink") %>'
			Visible="True"
			Target="_blank"
			runat="server"
			> 	
	 </asp:HyperLink>   		    		                         																				
	
	</ItemTemplate>
	
</asp:TemplateColumn>


 

Acknowledge

Crediting Scott Guthrie

Tip/Trick: Using Server Side Comments with ASP.NET 2.0
Link