C#

Convert string array to string

var array = new string[]{"Hello","Doctor","Name","Continue","Yesterday","Tomorrow"};
string result = string.Join(string.Empty, array);
string result2 = string.Join(", ", array);

var array = new string[]{"Hello","Doctor","Name","Continue","Yesterday","Tomorrow"};

string result = string.Join(string.Empty, array);

string result2 = string.Join(", ", array);

result = HelloDoctorNameContinueYesterdayTomorrow
result2 = Hello, Doctor, Name, Continue, Yesterday, Tomorrow

Format Decimal as currency

decimal amount = 32.95M;
var result = string.Format("{0:C}", amount);}

1 2	decimal amount = 32.95M; var result = string.Format("{0:C}", amount);}

result = $32.95

Format Decimal as string with N places

public string FormatAsDecimalString(decimal? value, int places = 2)
{
        if (value == null) value = 0;
        if (places &gt;= 0) places = 2;
        return String.Format("{0:N" + places.ToString() + "}", value);
}

public string FormatAsDecimalString(decimal? value, int places = 2)

{

if (value == null) value = 0;

if (places >= 0) places = 2;

return String.Format("{0:N" + places.ToString() + "}", value);

}

Optionally Running Debug Code

public void DoSomething()
{
   if (System.Diagnostics.Debugger.IsAttached)
   {
      //called only when debugger is attached
   }

   DoSomethingDebuggey();
}

//called only when set to debug build
[Conditional("DEBUG")]
private void DoSomethingDebuggey();
{
}

public void DoSomething()

{

if (System.Diagnostics.Debugger.IsAttached)

{

//called only when debugger is attached

}

DoSomethingDebuggey();

}

//called only when set to debug build

[Conditional("DEBUG")]

private void DoSomethingDebuggey();

{

}

SQL

Generate Sequential Guids
arguably not the most commonly used capability, but here it is…

declare @tab table ([id] uniqueidentifier default newsequentialid(), [dummy] bit)
declare @generate int = 50
declare @count int = 1
while(@count &lt;= @generate)
begin
	insert @tab ([dummy]) values (1)
	set @count = @count + 1
end
select [id] from @tab order by [id]

declare @tab table ([id] uniqueidentifier default newsequentialid(), [dummy] bit)

declare @generate int = 50

declare @count int = 1

while(@count <= @generate)

begin

insert @tab ([dummy]) values (1)

set @count = @count + 1

end

select [id] from @tab order by [id]

Print immediately to the Messages window when running queries in SQL Management Studio

declare @message nvarchar(1000);
set @message = '...something, something, something, dark side...'
raiserror (@message, 0, 1) with nowait --print immediately (doesn't interrupt query flow)

declare @message nvarchar(1000);

set @message = '...something, something, something, dark side...'

raiserror (@message, 0, 1) with nowait --print immediately (doesn't interrupt query flow)

Delete temp table if exists

if (object_id('tempdb..#temp') is not null) drop table #temp

1	if (object_id('tempdb..#temp') is not null) drop table #temp

SQL Server database stuck in “Restoring” state

use master;
RESTORE DATABASE [DatabaseNameHere] WITH RECOVERY

1 2	use master; RESTORE DATABASE [DatabaseNameHere] WITH RECOVERY

Disable / Enable triggers and constraints for the database

--disable
exec sp_MSforeachtable 'alter table ? disable trigger all;
alter table ? nocheck constraint all;'

--enable
exec sp_MSforeachtable 'alter table ? enable trigger all;
alter table ? check constraint all;'

--disable

exec sp_MSforeachtable 'alter table ? disable trigger all;

alter table ? nocheck constraint all;'

--enable

exec sp_MSforeachtable 'alter table ? enable trigger all;

alter table ? check constraint all;'

Clear the cache without re-starting

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

1 2	DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE

Check if file exists using T-SQL
Note: The identity under which the SQL Server process is running will require permissions to read the file system. Right-click the folder/file from file explorer, from the “Security” tab, assign appropriate permissions to “NetworkService” (or whatever credential SQL Server is using). Don’t forget to remember not to forget to remember that the file system is seen from the perspective of the SQL Server!

create function [dbo].[SIP_NPT_fFileExists](@path varchar(512))
returns bit
as
begin
     declare @result int
     exec master.dbo.xp_fileexist @path, @result output
     return cast(@result as bit)
end

create function [dbo].[SIP_NPT_fFileExists](@path varchar(512))

returns bit

begin

declare @result int

exec master.dbo.xp_fileexist @path, @result output

return cast(@result as bit)

end

Close all database connections
Using setvar in T-SQL

:setvar DB "MyDb"
alter database [$(DB)] set single_user with rollback immediate
alter database [$(DB)] set multi_user

:setvar DB "MyDb"

alter database [$(DB)] set single_user with rollback immediate

alter database [$(DB)] set multi_user

Other

Excel 2013 Cell Limit
Cell XFD1048576 is the max cell in Excel. It’s “right down” there.
That’s 16,383 columns x 1,048,576 rows.
excel-specifications-and-limits

Excel Guid Formula
=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),”-“,DEC2HEX(RANDBETWEEN(0,65535),4),”-“,DEC2HEX(RANDBETWEEN(16384,20479),4),”-“,DEC2HEX(RANDBETWEEN(32768,49150),4),”-“,DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

Remove hiberfil.sys from a server
If it’s a Citrix VM, hibernation must first be enabled in the “firmware” by issuing the following command, and re-start the VM.

xe vm-param-add uuid=<your VM> param-name=platform acpi_s4=true

Run the command interpreter “Run as Administrator” and enter:
powercfg -h off

<jco-blog/>

Miscellaneous stuff (that I can’t remember)

C#

SQL

Other