Author Archives: Protiguous

About Protiguous

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

Magical Methods in C#

Here is a very well written article about various capabilities in C# written by CEZARY PIĄTEK.
The Magical Methods in C#

Enable Windows Firewall for SQL Server

Run these commands on the Windows Server to open the ports for listening on a named instance of SQL Server.
Note: Remember to enable TCP in the SQL Server Configuration Manager.

netsh firewall set portopening protocol=TCP port=1433 name=SQLServerTCP mode=ENABLE scope=SUBNET profile=CURRENT

netsh firewall set portopening protocol=UDP port=1434 name=SQLServerUDP mode=ENABLE scope=SUBNET profile=CURRENT

For more help, refer to SQL Server & Windows Firewall.

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- NOTE: This is an EXAMPLE to show it is possible
-- to achieve moving rows using an implicit transaction
-- from one table into another table.

-- Set up example tables, Foo & Bar.
drop table if exists [dbo].[Bar];
drop table if exists [dbo].[Foo];

create table [dbo].[Bar](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] null index ix_mycol
)

create table [dbo].[Foo](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] index ix_mycol,
)

insert into [dbo].[Foo]( [MyKey], [MyColumn] )
select 1, 11 union all
select 2, 22;

-- Verify the rows before.
select * from [dbo].[Foo];
select * from [dbo].[Bar];

-- NOTE: These move queries create identical execution plans in 2019.

-- Move rows from table Foo into table Bar using two styles.

-- Method 1: Direct move
delete
from [dbo].[Foo]
output [deleted].[MyKey], [deleted].[MyColumn]
into [dbo].[Bar]( [MyKey], [MyColumn] )
where [MyKey] = 2;

-- Method 2: Subquery move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [MyKey] = 2
) [MovedRows]
--where [MovedRows].[MyKey] = 2;
-- Not needed, but it is possible to filter incoming rows.

-- Verify the rows after.
select * from [dbo].[Foo];
select * from [dbo].[Bar];

Curly {braces} Matter!

Blocks like “if“, “for“, “while“, and “using” (and others) should always use the curly braces “{” and “}“.

Code is for humans to read, and compilers to compile. Right?

By properly adding these braces ahead of time before they are needed, you future-proof the code and make the code’s intention (the logic) clear to any future programmer (including yourself).

This is not from an OCD formatting-enthusiast perspective. This is from decades of experience with C# and other {}-like languages.

Sure, one liners do work and they do reduce the total count of lines, but they are prone to bugs that are not immediately apparent to other programmers, compilers, code formatters, or via indentation.

If you use them, that is fine. Just be aware of the potential for issues in the future when someone edits the code.

BTW, If you can provide a reproducible example that causes C# code fail to compile, break, bug out, or crash by adding the proper braces, then please let me know! I’d greatly appreciate it.

Formatters cannot detect the original intent of the programmer. If even a single line of logic is missed (or ran when it should not), then the results of the function, method, or even the whole program could be affected.

Please, Do not write code to the lowest common denominator.

(I’ll link a gist if I ever get a better example completed.)
Here is a simplified example.

public static void RolRorRolRol( ref this UInt64 ul, Int32 n ) {
	if ( n > 2 )
		ul = ( ul << 3 ) | ( ul >> ( 64 - 3 ) );
	if ( ul < 10 )
		ul = ( ul << ( 64 - 4 ) ) | ( ul >> 4 );
	ul = ( ul << 5 ) | ( ul >> ( 64 - 5 ) );
	ul = ( ul << 6 ) | ( ul >> ( 64 - 6 ) );
}

Here is the “corrected” example.

public static void RolRorRolRol( ref this UInt64 ul, Int32 n ) {
	if ( n > 2 ) {
		ul = ( ul << 3 ) | ( ul >> ( 64 - 3 ) );
	}

	if ( ul < 10 ) {
		ul = ( ul << ( 64 - 4 ) ) | ( ul >> 4 );
		ul = ( ul << 5 ) | ( ul >> ( 64 - 5 ) );
	}

	ul = ( ul << 6 ) | ( ul >> ( 64 - 6 ) );
}

Find Duplicate Images with VisiPics

I’ve been using this utility for years. It scans folders and matches up duplicate images, from a strict match to “similar” matches (such as the same image, but in a lower resolution).

It seems to not have been updated since 2013-02-04, but it works really well with Windows 10 (64-bit)., up to tens of thousands of images.

It does have an upper limit (20,000+), but I have not determined it exactly yet..

http://www.visipics.info/

via Ordering of static fields in C# matters

The ordering of static fields does matter.
But per your examples, here is the pattern I always use:

public class Person {
    private static EmptyPerson _emptyPerson;
    public static Person Empty => _emptyPerson ?? ( _emptyPerson = new EmptyPerson() );
}

The allows the Empty value to be initialized once and read-only, yet still used many times.

It also hides away the requirements of the initialization (the value pulled from a database for example) from the public getter. We could even put the value into a Lazy<Person>().

And in C# 8 we now have the compound assignment!

public class Person {
    private static EmptyPerson _emptyPerson;
    public static Person Empty => _emptyPerson ??= new EmptyPerson();
}

Samsung NVMe 3.1 Driver

Download Here: Samsung NVMe 3.1 Driver
Password: “Samsung“.

I created this because sometimes the windows executable from Samsung won’t install if a temp folder is not set correctly.

This archive is the 3 files from the official Samsung NVMe 3.1 driver download page with no modifications, just zipped up.

Remember: Always create a Restore Point before you install, and keep your antivirus ENABLED.. just to be on the safe side.

Here are my results before installing this driver.
nvme standard 1GB

And the results after installing this driver.
nvme samsung 1GB

Conclusion: only slightly better than Microsoft’s NVMe driver on this computer.
Both results are within the margin of error of each other.

Send a text message from SQL Server 2019

NOTE: You must have SQL Server database mail enabled for this stored procedure to work.

create or alter proc [dbo].[SendTextMessage](
	@number sysname
	,@message nvarchar(max)
	,@subject sysname = null
	,@provider nvarchar(20) = N'Verizon'
)
as
begin;
	set nocount on;

	set @number = trim(@number);
	if @number is null or len(@number)<7 begin;
		set @message = N'@number is too short';
		throw 51000, @message, 1;
	end;

	set @message = trim(@message);
	if @message is null or len(@message)<2 begin;
		set @message = N'@message is too short';
		throw 51000, @message, 1;
	end;

	set @subject = trim(@subject);
	if @subject is null set @subject = @@SERVERNAME + N' Notification';

	declare @to nvarchar(255) = @number + N'@'+
		case @provider
			when N'Verizon' then N'vtext.com'
			when N'VZ' then N'vtext.com'
			when N'V' then N'vtext.com'
			when N'ATT' then N'txt.att.net'
			when N'SPRINT' then N'messaging.sprintpcs.com'
			when N'TMOBILE' then N'tmomail.net'
	end;

	if nullif(@provider,N'@') is null begin;
		throw 51000, 'Unknown provider. Message not sent.', 1;
	end;

	begin try;
		declare @mailitem_id int;
		exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @mailitem_id=@mailitem_id output;
	end try
	begin catch;
		throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1;
	end catch;
end;