Author: Protiguous

C# Software Developer, SQL Server DBA, Father, and seeker of Truth.

I Recently discovered Jessica Chobot..

ProtiguousLeave a comment

And after watching all 4 seasons of her on Discovery+, I have to say that Jessica Chobot has got to be one of the most beautiful women on this great Earth. Simply stunning!

How to make an Action run only X count of times in C#?

ProtiguousLeave a comment 
// Copyright © Protiguous. All Rights Reserved.
//
// This entire copyright notice and license must be retained and must be kept visible in any binaries, libraries,
// repositories, or source code (directly or derived) from our binaries, libraries, projects, solutions, or applications.
//
// All source code belongs to [email protected] unless otherwise specified or the original license has
// been overwritten by formatting. (We try to avoid it from happening, but it does accidentally happen.)
//
// Any unmodified portions of source code gleaned from other sources still retain their original license and our thanks goes to those Authors.
// If you find your code unattributed in this source code, please let us know so we can properly attribute you and include the proper licenses and/or copyrights.
// If you want to use any of our code in a commercial project, you must contact [email protected] for permission, license, and a quote.
//
// Donations, payments, and royalties are accepted via bitcoin: 1Mad8TxTqxKnMiHuZxArFvX8BuFEB9nqX2 and PayPal: [email protected]
//
// ====================================================================
// Disclaimer:  Usage of the source code or binaries is AS-IS.
// No warranties are expressed, implied, or given.
// We are NOT responsible for Anything You Do With Our Code.
// We are NOT responsible for Anything You Do With Our Executables.
// We are NOT responsible for Anything You Do With Your Computer.
// ====================================================================
//
// Contact us by email if you have any questions, helpful criticism, or if you would like to use our code in your project(s).
// For business inquiries, please contact me at [email protected]
// Our software can be found at "https://Protiguous.com/Software/"
// Our GitHub address is "https://github.com/Protiguous".
//
// File "DelegateExtensions.cs" last formatted on 2022-04-07 at 12:38 PM by Protiguous.

public static class DelegateExtensions {

	private sealed class ContextCallOnlyXTimes {
		internal Int64 CallsAllowed;

		public ContextCallOnlyXTimes( Int64 times ) {
			if ( times <= 0 ) {
				times = 0;
			}
			this.CallsAllowed = times;
		}
	}

	/// <summary>Only allow a delegate to run X times.</summary>
	/// <param name="action"></param>
	/// <param name="callsAllowed"></param>
	/// <example>var barWithBarrier = ActionBarrier(Bar, remainingCallsAllowed: 2 );</example>
	/// <remarks>Calling the delegate more often than <paramref name="callsAllowed" /> should just NOP.</remarks>
	public static Action ActionBarrier( this Action action, Int64 callsAllowed = 1 ) {
		var context = new ContextCallOnlyXTimes( callsAllowed );

		return () => {
			if ( Interlocked.Decrement( ref context.CallsAllowed ) >= 1 ) {
				action();
			}
		};
	}

	/// <summary>Only allow a delegate to run X times.</summary>
	/// <param name="action"></param>
	/// <param name="parameter"></param>
	/// <param name="callsAllowed"></param>
	/// <example>var barWithBarrier = ActionBarrier(Bar, remainingCallsAllowed: 2 );</example>
	/// <remarks>Calling the delegate more often than <paramref name="callsAllowed" /> should just NOP.</remarks>
	public static Action ActionBarrier<T>( this Action<T?> action, T? parameter, Int64 callsAllowed = 1 ) {
		var context = new ContextCallOnlyXTimes( callsAllowed );

		return () => {
			if ( Interlocked.Decrement( ref context.CallsAllowed ) >= 1 ) {
				action( parameter );
			}
		};
	}
}

LEST WE FORGET THE HORRORS: A CATALOG OF TRUMP’S WORST CRUELTIES, COLLUSIONS, CORRUPTIONS, AND CRIMES

ProtiguousLeave a comment

Here is an excellent site for information about Donald J. Trump, former President of the United States of America. The guy who lost the popular vote twice, and lost the 2020 election.

Here is a list of his cruelties, lies, collusions, corruptions, and crimes.
All laid out in a nice chronological format.

https://www.mcsweeneys.net/articles/the-complete-listing-atrocities-1-1-056

Here is the PDF available on their site.
I offer this download in case the original becomes unavailable (i.e. link-rot).
If you would, please consider making a donation to the original authors here.

LEST_WE_FORGET_THE_HORRORS_(8)Download

SQL Server script to create missing indexes on all foreign keys

ProtiguousLeave a comment

Here is a handy script to create missing indexes on all foreign keys.
Based on the script over at lukelowrey.com.

-- Call procedure to retrieve error information.  
CREATE or ALTER proc [Utility].[usp_GetErrorMessage](
	@message nvarchar(max) output,
	@procid int = null
)
as
set @message = 
	case
		when @procid is not null then N'Proc=' + object_schema_name(@procid) + N'.' + object_name(@procid) + N', '
		else N'<unknown procedure>'
	end +
	N'ErrorNumber=' + convert(nvarchar(max),error_number()) +
	N', Severity=' + convert(nvarchar(max),error_severity()) +
	N', State=' + convert(nvarchar(max),error_state()) +
	N', Line=' + convert(nvarchar(max),error_line()) +
	N', Message=' + convert(nvarchar(max),error_message());
GO

CREATE or ALTER proc [Utility].[usp_AutomaticallyCreateIndexesForForeignKeys](
	@Execute bit = null
)
as
begin;
	set nocount on;

	declare @CurrentSQLCommand nvarchar(max);
	declare @IndexName TABLE ( [SQL] nvarchar(450) primary key );
	declare @message nvarchar(max);

	WITH v_NonIndexedFKColumns AS (
		SELECT
			[Table_Name] = OBJECT_NAME(a.[parent_object_id]),
			[Column_Name] = b.[name]
		FROM sys.foreign_key_columns [a]
			,sys.all_columns [b]
			,sys.objects [c]
		WHERE a.[parent_column_id] = b.[column_id]
			AND a.[parent_object_id] = b.[object_id]
			AND b.[object_id] = c.[object_id]
			AND c.[is_ms_shipped] = 0
		EXCEPT
			SELECT
				[Table_Name] = OBJECT_NAME(a.[object_id]),
				[Column_Name] = b.[name]
			FROM sys.index_columns [a]
				,sys.all_columns [b]
				,sys.objects [c]
			WHERE a.[object_id] = b.[object_id]
				AND a.[key_ordinal] = 1
				AND a.[column_id] = b.[column_id]
				AND a.[object_id] = c.[object_id]
				AND c.[is_ms_shipped] = 0
	)
	INSERT INTO @IndexName( [SQL] )
	SELECT
		'CREATE INDEX [ix_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
		+ ' ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
	FROM v_NonIndexedFKColumns [v]
		,sys.all_columns [c]
		,sys.all_columns [c2]
		,sys.foreign_key_columns [fkc]
		,sys.foreign_keys [fk]
	WHERE v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
		AND v.[Column_Name] = c.[name]
		AND fkc.[parent_column_id] = c.[column_id]
		AND fkc.[parent_object_id] = c.[object_id]
		AND fkc.[referenced_column_id] = c2.[column_id]
		AND fkc.[referenced_object_id] = c2.[object_id]
		AND fk.[object_id] = fkc.[constraint_object_id];

	DECLARE sqlCommands CURSOR FOR SELECT [SQL]
	FROM @IndexName;

	OPEN sqlCommands;
	FETCH NEXT FROM sqlCommands INTO @CurrentSQLCommand;

	while @@FETCH_STATUS = 0 begin;
		print @CurrentSQLCommand;

		if @Execute = 1 begin try;
			exec sp_executesql @CurrentSQLCommand;
		end try
		begin catch;
			exec [Utility].[usp_GetErrorMessage] @[email protected] output, @[email protected]@PROCID;
			throw 51000, @message, 1;
		end catch;

		fetch next from sqlCommands into @CurrentSQLCommand;
	end;
	close sqlCommands;
	deallocate sqlCommands;
end;

PowerShell script to shrink & rebuild your SQL Server databases.

ProtiguousLeave a comment

Just change the DB1, DB2, DB3, etc.. to be the actual databases you plan on rebuilding.
Same with the ServerName\InstanceName.

Caveat: Don’t run this script on a regular basis. If your databases keep growing in size, there’s usually a reason for it.

$databases = @('DB1','DB2','DB3')
$watch = New-Object System.Diagnostics.Stopwatch
foreach ($database in $databases) {
 $watch.Start()
 Write-Host "Shrinking - " $database
 Invoke-DbaDbShrink -SqlInstance "ServerName\InstanceName" -Database $database -PercentFreeSpace 10 -FileType Data -StepSize 1GB
 Write-Host $time " done " $database
 Write-Host "Rebuilding " $database
 Invoke-DbaQuery -SqlInstance "ServerName\InstanceName" -Database $database -Query "Exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'"
 $time = $watch.Elapsed.TotalMinutes
 $watch.reset()
 Write-Host $time " - " $database
}