Jerry Nixon JerryNixon

🤔

Trying to make a living.

# I am an Engineer working on Azure & other Magic. Big fan of C# & SQL Server.

JerryNixon / SqlConnectionStringValidator.cs

Created June 7, 2024 16:56

Validate a SQL Connection String

	public static class SqlConnectionStringValidator
	{
	public enum ErrorReason { None, ServerNetwork, Database, UserPassword, Other }

	public static bool TryValidateConnectionString(string server, string database, string user, string password, out string connectionString, out ErrorReason reason, out string message)
	{
	connectionString = default;

	if (!TryValidateValues(server, database, user, password, out reason, out message))
	{

JerryNixon / GenerateDabConfig.sql

Last active May 2, 2024 19:45

	DECLARE @JsonOutput NVARCHAR(MAX);

	WITH TablesToExclude AS
	(
	SELECT fk.parent_object_id AS object_id
	FROM sys.foreign_keys AS fk
	GROUP BY fk.parent_object_id
	HAVING COUNT(fk.object_id) = 2

	UNION

JerryNixon / ReturningJson.sql

Last active April 10, 2024 19:57

Sending and returning JSON to accommodate complex types.

	BEGIN TRANSACTION

	GO
	CREATE PROCEDURE FetchObjectsWithMetadata
	@SortBy NVARCHAR(50),
	@PageSize INT = 10,
	@PageNumber INT,
	@NameFilter NVARCHAR(256)
	AS
	BEGIN

JerryNixon / foreign_key.sql

Created February 29, 2024 17:01

Demonstrating a FK does not require a PK in SQL

JerryNixon / UnitTest1.cs

Last active August 1, 2024 21:15

Database Unit Test

	using Microsoft.Data.SqlClient;
	using Microsoft.Extensions.Configuration;

	using System.Data;

	namespace Database.TestRunner;

	public class SqlDatabase : IDisposable, IAsyncDisposable
	{
	private static string ReadConnectionString()

JerryNixon / ApiApp_Program.cs

Last active January 18, 2024 22:13

Call an ASP.NET Minimal API from a Blazor WebAssembly App

	// Change this to your project namespace
	using Sample.Shared;

	var CorsPolicyName = "MyCorsApiPolicy";

	var builder = WebApplication.CreateBuilder(args);
	builder.Services.AddCors(options =>
	{
	options.AddPolicy(name: CorsPolicyName, policy =>
	{

JerryNixon / ConnectionString.sql

Created July 20, 2023 15:33

Build a connection string

	DROP FUNCTION IF EXISTS ConnectionString;
	GO

	CREATE FUNCTION ConnectionString(@includeProperties bit = 1)
	RETURNS nvarchar(1000)
	AS
	BEGIN
	-- Retrieve server name
	DECLARE @hostName nvarchar(128);
	SET @hostName = CAST(host_name() AS nvarchar(128));

JerryNixon / Benchmark.cs

Created July 10, 2023 21:35

Testing parameter types: IEnumerable<string> versus List<string>

	using BenchmarkDotNet.Attributes;
	using BenchmarkDotNet.Engines;
	using BenchmarkDotNet.Running;

	_ = BenchmarkRunner.Run<TestParamType>();

	[MemoryDiagnoser]
	public class TestParamType
	{
	private List<string> list = new();

JerryNixon / ProcedureInfo.sql

Last active May 19, 2023 18:12

Get Parameters and Columns of Stored Procedure in SQL Server

	;WITH types AS
	(
	SELECT system_type_id,
	CASE
	WHEN system_type_id IN (34, 35, 99, 173, 165, 167, 175, 231, 239) THEN 'string'
	WHEN system_type_id IN (36, 189) THEN 'Guid'
	WHEN system_type_id IN (48) THEN 'byte'
	WHEN system_type_id IN (52) THEN 'short'
	WHEN system_type_id IN (56) THEN 'int'
	WHEN system_type_id IN (58, 61) THEN 'DateTime'

JerryNixon / SqlUtilities.GetDotnetType.cs

Last active April 24, 2023 17:22

Convert SQL Type to .NET Type

	using Microsoft.SqlServer.TransactSql.ScriptDom;

	public static class SqlUtilities
	{
	public static string GetDotnetType(this SqlDataTypeOption sqlDataType, bool isNullable = false)
	{
	if (IsUnsupportedType())
	{
	return string.Empty;
	}