Experimenting with SQL

readme.md

About

This code is about experimenting with writing SQL without a database.

In Test1, the statement defines a string for json data followed by creating a temporary table under master, insert data, select data.

In Test2, create a temporary table under master, insert data, do a group by select.

Note for test purposes, both methods use the same model.

Requires

• NET8 Framework
• Microsoft Visual Studio 2022
• Dapper

<ItemGroup>
   <PackageReference Include="Dapper" Version="2.1.28" />
   <PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.5" />
</ItemGroup>

TestContainer.cs

public class TestContainer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

TestOperations.cs

public class TestOperations
{
    public static void Test1()
    {
        const string connectionString = 
            "Data Source=.\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;Encrypt=False";
        
        const string sql = 
            """
            DECLARE @json VARCHAR(MAX) = 
            '[ { "ID": 123,"Name": "Karen"},{ "ID": 124, "Name": "Jim"},{ "ID": 125,"Name": "Sally"} ]';
            
            DECLARE @blah TABLE (ID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT NULL);
            
            INSERT INTO @blah (ID,Name)
            SELECT ID,Name FROM OPENJSON(@json) WITH (ID INT, Name VARCHAR(100));
            
            SELECT ID, Name FROM @blah;
            """;

        using SqlConnection cn = new(connectionString);
        var list = cn.Query<TestContainer>(sql);

        foreach (var json in list)
        {
            Console.WriteLine($"{json.Id,-4}{json.Name}");
        }
    }
    public static void Test2()
    {
        const string connectionString =
            "Data Source=.\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;Encrypt=False";
        
        const string sql =
            """
            DECLARE @blah TABLE (id INT,name VARCHAR(10),email VARCHAR(50));
            
            INSERT @blah VALUES (1, 'John', 'John-email');
            INSERT @blah VALUES (2, 'John', 'John-email');
            INSERT @blah VALUES (3, 'fred', 'fred-email');
            INSERT @blah VALUES (4, 'fred', 'fred-email');
            INSERT @blah VALUES (5, 'sam', 'sam-email');
            INSERT @blah VALUES (6, 'sam', 'sam-email');
            INSERT @blah VALUES (7, 'kate', 'kate-email');
            
            SELECT y.id AS Id, y.name AS Name, y.email AS Email
            
            FROM @blah y
                INNER JOIN
                (
                    SELECT  name,email,COUNT(*) AS CountOf
                    FROM @blah
                    GROUP BY name, email
                    HAVING COUNT(*) > 1
                ) dt ON y.name = dt.name AND y.email = dt.email;
            """;

        using SqlConnection cn = new(connectionString);
        var list = cn.Query<TestContainer>(sql);

        foreach (var item in list)
        {
            Console.WriteLine($"{item.Id,-4}{item.Name,-8}{item.Email}");
        }
    }
}