Build C# string length validation from SQL Server

If you need a quick and dirty solution to validate string lengths in a .NET model against exact-match columns in SQL Server, you can use this script. This will find all of the varchar and nvarchar columns for a table, and build code to ensure the model doesn’t exceed the max length for any of the columns.

Obviously this isn’t a best practice, and you should use proper validation whenever you can.

CREATE TABLE [dbo].[Foo] (
	[ID] INT NOT NULL IDENTITY PRIMARY KEY
	,[FirstName] NVARCHAR(100)
	,[LastName] NVARCHAR(100)
);
GO

DECLARE @TABLE_SCHEMA NVARCHAR(128) = 'dbo'
DECLARE @TABLE_NAME NVARCHAR(128) = 'Foo'

SELECT FORMATMESSAGE(
	'if (model.%s?.Length > %i) { errors.Add("%s exceeds max length of %i"); }'
	,[COLUMN_NAME]
	,[CHARACTER_MAXIMUM_LENGTH]
	,[COLUMN_NAME]
	,[CHARACTER_MAXIMUM_LENGTH]
)
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_SCHEMA] = @TABLE_SCHEMA
AND [TABLE_NAME] = @TABLE_NAME
AND [DATA_TYPE] IN (N'varchar', N'nvarchar')
ORDER BY [ORDINAL_POSITION];