Converting a .NET Datetime to a DateTime2 in T-SQL

This article presents a handy T-Sql that extracts a DateTime value stored in .NET in a numerical field and converts it into a Sql Server DateTime (DateTime2 column). The T-SQL will convert into a DateTime2 with a SECOND precision. An assumption here is that any numerical value larger than 100000000000 contains a DateTime value. This is an acceptable assumption when you log data, as very large values usually indicate a datetime value. But you might want to have additional checking here of course in addition to what i show in the example T-SQL script. Here is the T-SQL that shows how we can convert the .NET DateTime into a SQL DateTime.

-- Last updated: March 18, 2025
-- Synopsis: This script retrieves detailed change log information from the ObjectChanges, PropertyChanges, and ChangeSets tables.
-- It filters the results based on specific identifiers stored in a table variable, in this example Guids. 

-- In this example T-Sql the library FrameLog is used to store a log

-- DateTime columns are retrieved by looking at number of ticks elapsed since DateTime.MinValue as 
-- DateTime columns are stored in SQL Server as a this numeric value. 


DECLARE @EXAMPLEGUIDS TABLE (ID NVARCHAR(36))

INSERT INTO @EXAMPLEGUIDS (Id)
VALUES
('1968126a-64c1-4d15-bf23-8cb8497dcaa9'), 
('3e11aad8-95df-4377-ad63-c2fec3d43034'),
('acbdd116-b6a5-4425-907b-f86cb55aeedd') --tip: define which form Guids to fetch the ChangeLog database tables which 'FrameLog' uses The form Guids can each be retrieved from url showing the form in MRS in the browser

SELECT 
o.Id as ObjectChanges_Id, 
o.ObjectReference as ObjectReference, 
o.TypeName as ObjectChanges_TypeName, 
c.Id as Changeset_Id, 
c.[Timestamp] as Changeset_Timestamp,
c.Author_UserName as Changeset_AuthorName,
p.[Id] as PropertyChanges_Id,
p.[PropertyName],
p.[Value],
p.[ValueAsInt],
CASE 
    WHEN p.Value IS NOT NULL 
         AND ISNUMERIC(p.[Value]) = 1 
         AND CAST(p.[Value] AS decimal) > 100000000000 
    THEN 
        DATEADD(SECOND, 
            CAST(CAST(p.[Value] AS decimal) / 10000000 AS BIGINT) % 60, 
            DATEADD(MINUTE, 
                CAST(CAST(p.[Value] AS decimal) / 10000000 / 60 AS BIGINT), 
                CAST('0001-01-01' AS datetime2)
            )
        )
    ELSE NULL
END AS ValueAsDate,
o.ChangeType as ObjectChanges_ChangeTypeIfSet
FROM propertychanges p
LEFT OUTER JOIN ObjectChanges o on o.Id = p.ObjectChange_Id
LEFT OUTER JOIN ChangeSets c on o.ChangeSet_Id = c.Id
WHERE ObjectChange_Id in (
 SELECT ObjectChanges.Id
  FROM PropertyChanges
  LEFT OUTER JOIN ObjectChanges on ObjectChanges.Id = PropertyChanges.ObjectChange_Id
  LEFT OUTER JOIN ChangeSets on ObjectChanges.ChangeSet_Id = ChangeSets.Id
  WHERE ObjectChange_Id in (SELECT Id FROM ObjectChanges where ObjectReference IN (
   SELECT Id FROM @EXAMPLEGUIDS
   ))) --find out the Changeset where ObjectChange_Id equals the Id of ObjectChanges where ObjectReference equals one of the identifiers in @EXAMPLEGUIDS
ORDER BY ObjectReference, Changeset_Id DESC, Changeset_Timestamp DESC

The T-Sql is handy in case you come across datetime columns from .NET that are saved as ticks in a column as numerical value and shows how we can do the conversion.

Rebuild indexes in all tables in Sql Server for a set of databases

Here is a convenient Sql script to rebuild all indexes in a set of databases in Sql Server.

RebuildAllDatabaseIndexesInSetOfDatabases.sql

/*
This script loops through all databases with names containing a specified prefix or matching a specific name.
For each database, it rebuilds indexes on all tables to improve performance by reducing fragmentation.

Variables:
- @Prefix: The prefix to match database names.
- @SomeSpecialSharedDatabaseName: The specific database specific shared database name to include in the loop.
- @DatabaseName: The name of the current database in the loop.
- @TableName: The name of the current table in the loop.
- @SQL: The dynamic SQL statement to execute.

Steps:
1. Declare the necessary variables.
2. Create a cursor to loop through the databases.
3. For each database, use another cursor to loop through all tables and rebuild their indexes.
4. Print progress messages for each database and table.
*/

DECLARE @SomeAcmeUnitDatabaseNamePrefix NVARCHAR(255) = 'SomeAcmeUnit';
DECLARE @SomeSpecialSharedDatabaseName NVARCHAR(255) = 'SomeAcmeShared';
DECLARE @DatabaseName NVARCHAR(255);
DECLARE @TableName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DatabaseCount INT;
DECLARE @CurrentDatabaseCount INT = 0;
DECLARE @TableCount INT;
DECLARE @CurrentTableCount INT = 0;
DECLARE @StartTime DATETIME2;
DECLARE @EndTime DATETIME2;
DECLARE @ElapsedTime NVARCHAR(100);

SET @StartTime = SYSDATETIME();

-- Get the total number of databases to process
SELECT @DatabaseCount = COUNT(*)
FROM sys.databases
WHERE [name] LIKE @SomeAcmeUnitDatabaseNamePrefix + '%' OR [name] = @SomeSpecialSharedDatabaseName;

DECLARE DatabaseCursor CURSOR FOR
SELECT [name]
FROM sys.databases
WHERE [name] LIKE @SomeAcmeUnitDatabaseNamePrefix + '%' OR [name] = @SomeSpecialSharedDatabaseName;

OPEN DatabaseCursor;
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentDatabaseCount = @CurrentDatabaseCount + 1;
    SET @SQL = '
	RAISERROR(''*****************************************************************'', 0, 1) WITH NOWAIT;
	RAISERROR(''REBUILDING ALL INDEXES OF TABLES INSIDE DB: %s'', 0, 1, @DatabaseName) WITH NOWAIT;
	RAISERROR(''*****************************************************************'', 0, 1) WITH NOWAIT;

    DECLARE @TableName NVARCHAR(255);
    DECLARE @SQL NVARCHAR(MAX);

    -- Get the total number of tables to process in the current database
    SELECT @TableCount = COUNT(*)
    FROM sys.tables;

    DECLARE TableCursor CURSOR FOR
    SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' + QUOTENAME(name)
    FROM sys.tables ORDER BY QUOTENAME(name) ASC;

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CurrentTableCount = @CurrentTableCount + 1;
        PRINT ''Rebuilding database indexes on table: '' + @TableName +  ''... (DB: '' + CAST(@CurrentDatabaseCount AS NVARCHAR) + ''/'' + CAST(@DatabaseCount AS NVARCHAR) + '')''  +  ''... (Table: '' + CAST(@CurrentTableCount AS NVARCHAR) + ''/'' + CAST(@TableCount AS NVARCHAR) + '')'';
		--RAISERROR(''..Indexing (hit Ctrl+End to go to latest message inside this buffer)'',0,1) WITH NOWAIT;
        SET @SQL = ''ALTER INDEX ALL ON '' + @TableName + '' REBUILD'';
        EXEC sp_executesql @SQL;
        FETCH NEXT FROM TableCursor INTO @TableName;
        --PRINT ''Rebuilt database indexes on table: '' + @TableName + ''.'';
    END;

    CLOSE TableCursor;
    DEALLOCATE TableCursor;';

    EXEC sp_executesql @SQL,
		N'@CurrentDatabaseCount INT, @DatabaseCount INT, @TableCount INT, @CurrentTableCount INT, @DatabaseName NVARCHAR(255)',
		@CurrentDatabaseCount, @DatabaseCount, @TableCount, @CurrentTableCount, @DatabaseName;

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;
END;

SET @EndTime = SYSDATETIME()

-- Calculate the elapsed time
DECLARE @ElapsedSeconds INT = DATEDIFF(SECOND, @StartTime, @EndTime);
DECLARE @ElapsedMilliseconds INT = DATEPART(MILLISECOND, @EndTime) - DATEPART(MILLISECOND, @StartTime);

-- Combine seconds and milliseconds
SET @ElapsedTime = CAST(@ElapsedSeconds AS NVARCHAR) + '.' + RIGHT('000' + CAST(@ElapsedMilliseconds AS NVARCHAR), 3);

-- Print the elapsed time using RAISERROR
RAISERROR('Total execution time: %s seconds', 0, 1, @ElapsedTime) WITH NOWAIT;


CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

The SQL Script uses loops defined by T-SQL database cursors and uses the sys.databases and sys.tables system views to loop through all the databases matching the set prefixes of 'unit databases' like 'SomeAcmeUnit_0', 'SomeAcmeUnit_1' and 'shared database like 'SomeAcmeShared'. This matches a setup of many databases used in companies, where you just want to rebuild all the indexes in all the tables in all the set of databases. It can be handy to be sure that all indexes are rebuilt.

Generating dropdowns for enums in Blazor

This article will look into generating dropdown for enums in Blazor. The repository for the source code listed in the article is here: https://github.com/toreaurstadboss/DallEImageGenerationImgeDemoV4 First off, a helper class for enums that will use the InputSelect control. The helper class will support setting the display text for enum options / alternatives via resources files using the display attribute.

Enumhelper.cs | C# source code

using DallEImageGenerationImageDemoV4.Models;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Components.Forms;
using System.ComponentModel.DataAnnotations;
using System.Linq.Expressions;
using System.Resources;

namespace DallEImageGenerationImageDemoV4.Utility
{
  
    public static class EnumHelper
    {
      
        public static RenderFragment GenerateEnumDropDown<TEnum>(
            object receiver,
            TEnum selectedValue,
            Action<TEnum> valueChanged) 
            where TEnum : Enum
        {
            Expression<Func<TEnum>> onValueExpression = () => selectedValue;
            var onValueChanged = EventCallback.Factory.Create<TEnum>(receiver, valueChanged);
            return builder =>
            {
                // Set the selectedValue to the first enum value if it is not set
                if (EqualityComparer<TEnum>.Default.Equals(selectedValue, default))
                {
                    object? firstEnum = Enum.GetValues(typeof(TEnum)).GetValue(0);
                    if (firstEnum != null)
                    {
                        selectedValue = (TEnum)firstEnum;
                    }
                }

                builder.OpenComponent<InputSelect<TEnum>>(0);
                builder.AddAttribute(1, "Value", selectedValue);
                builder.AddAttribute(2, "ValueChanged", onValueChanged);
                builder.AddAttribute(3, "ValueExpression", onValueExpression);
                builder.AddAttribute(4, "class", "form-select");  // Adding Bootstrap class for styling
                builder.AddAttribute(5, "ChildContent", (RenderFragment)(childBuilder =>
                {
                    foreach (var value in Enum.GetValues(typeof(TEnum)))
                    {
                        childBuilder.OpenElement(6, "option");
                        childBuilder.AddAttribute(7, "value", value?.ToString());
                        childBuilder.AddContent(8, GetEnumOptionDisplayText(value)?.ToString()?.Replace("_", " ")); // Ensure the display text is clean
                        childBuilder.CloseElement();
                    }
                }));
                builder.CloseComponent();
            };
        }

        /// <summary>
        /// Retrieves the display text of an enum alternative 
        /// </summary>
        private static string? GetEnumOptionDisplayText<T>(T value)
        {
            string? result = value!.ToString()!; 

            var displayAttribute = value
                .GetType()
                .GetField(value!.ToString()!)
                ?.GetCustomAttributes(typeof(DisplayAttribute), false)?
                .OfType<DisplayAttribute>()
                .FirstOrDefault();
            if (displayAttribute != null)
            {
                if (displayAttribute.ResourceType != null && !string.IsNullOrWhiteSpace(displayAttribute.Name))
                {
                    result = new ResourceManager(displayAttribute.ResourceType).GetString(displayAttribute!.Name!);                    
                }
                else if (!string.IsNullOrWhiteSpace(displayAttribute.Name))
                {
                    result = displayAttribute.Name;
                }           
            }
            return result;          
        }


    }
}

The following razor component shows how to use this helper.

 <div class="form-group">
     <label for="Quality" class="form-class fw-bold">GeneratedImageQuality</label>
     @EnumHelper.GenerateEnumDropDown(this, homeModel.Quality,v => homeModel.Quality = v)
     <ValidationMessage For="@(() => homeModel.Quality)" class="text-danger" />
 </div>
 <div class="form-group">
     <label for="Size" class="form-label fw-bold">GeneratedImageSize</label>
     @EnumHelper.GenerateEnumDropDown(this, homeModel.Size, v => homeModel.Size = v)
     <ValidationMessage For="@(() => homeModel.Size)" class="text-danger" />
 </div>
 <div class="form-group">
     <label for="Style" class="form-label fw-bold">GeneratedImageStyle</label>
     @EnumHelper.GenerateEnumDropDown(this, homeModel.Style, v => homeModel.Style = v)
     <ValidationMessage For="@(() => homeModel.Style)" class="text-danger" />
 </div>

It would be possible to instead make a component than such a helper method that just passes a typeref parameter of the enum type. But using such a programmatic helper returning a RenderFragment. As the code shows, returning a builder which uses the RenderTreeBuilder let's you register the rendertree to return here. It is possible to use OpenComponent and CloseComponent. Using AddAttribute to add attributes to the InputSelect. And a childbuilder for the option values. Sometimes it is easier to just make such a class with helper method instead of a component. The downside is that it is a more manual process, it is similar to how MVC uses HtmlHelpers. What is the best option from using a component or such a RenderFragment helper is not clear, it is a technique many developers using Blazor should be aware of.