C# Statements and TSQL lesson

  • Create new .NET Core class project named SqlServerCourseLibrary
  • Check project file for targeting .NET Core, C#9
  • Create a folder named Classes
  • Create a class named Operations
  • Add NuGet package: System.Data.SqlClient
  • Add the following field
    •                                             Private static string _connectionString =>
                                            @"Data Source=.\SQLEXPRESS;Initial Catalog=NorthWind2020;Integrated Security=True";
  • Add the following method
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                        
                                            return productList;
                                        }
  • Add the following line above the namespace line. When using ReSharper, Resharper will assist.
    • using System.Data.SqlClient;
  • Add a connection object
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                            
                                            using (var cn = new SqlConnection() { ConnectionString = _connectionString })
                                            {
                                        
                                            }
                                            return productList;
                                        }
  • Add a command object
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                            
                                            using (var cn = new SqlConnection() { ConnectionString = _connectionString })
                                            {
                                                using (var cmd = new SqlCommand() { Connection = cn })
                                                {
                                                   
                                                }
                                            }
                                            
                                            return productList;
                                        }
  • Add a SELECT statement which uses the parameter recordCount with alternate SQL

    SELECT TOP (3) ProductName FROM (SELECT TOP (10) ProductName FROM dbo.Products) a ORDER BY NEWID()(); 
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                            
                                            using (var cn = new SqlConnection() { ConnectionString = _connectionString })
                                            {
                                                using (var cmd = new SqlCommand() { Connection = cn })
                                                {
                                                    // for your code use one line, no string concatenation 
                                                    cmd.CommandText = $"SELECT TOP ({recordCount}) ProductName " + 
                                                                      "FROM (SELECT TOP (10) ProductName FROM dbo.Products) a " + 
                                                                      "ORDER BY  NEWID();";
                                                }
                                            }
                                            
                                            return productList;
                                        }
  • Open the connection
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                            
                                            using (var cn = new SqlConnection() { ConnectionString = _connectionString })
                                            {
                                                using (var cmd = new SqlCommand() { Connection = cn })
                                                {
                                                    // for your code use one line, no string concatenation 
                                                    cmd.CommandText = $"SELECT TOP ({recordCount}) ProductName " + 
                                                                      "FROM (SELECT TOP (10) ProductName FROM dbo.Products) a " + 
                                                                      "ORDER BY  NEWID();";
                                         
                                                    cn.Open();
                                                }
                                            }
                                            
                                            return productList;
                                        }
  • Add code to read random records
    • public static List<string> GetRandomProducts(int recordCount = 3)
                                        {
                                            List<string> productList = new();
                                         
                                            using (var cn = new SqlConnection() { ConnectionString = _connectionString })
                                            {
                                                using (var cmd = new SqlCommand() { Connection = cn })
                                                {
                                                    // for your code use one line, no string concatenation 
                                                    cmd.CommandText = $"SELECT TOP ({recordCount}) ProductName " +
                                                                      "FROM (SELECT TOP (10) ProductName FROM dbo.Products) a " +
                                                                      "ORDER BY  NEWID();";
                                         
                                                    cn.Open();
                                         
                                                    var reader = cmd.ExecuteReader();
                                                    while (reader.Read())
                                                    {
                                                        productList.Add(reader.GetString(0));
                                                    }
                                                }
                                            }
                                         
                                            return productList;
                                        }

  • Create new unit test project or select an existing unit test project
    • Add a reference to SqlServerCourseLibrary
  • Add the following using statement above the namespace line
    • using SqlServerCourseLibrary.Classes;
  • Create a new unit test method to validate the code we just wrote works
    • [TestMethod]
                                    public void SqlRandomRecords()
                                    {
                                        var products = Operations.GetRandomProducts();
                                        Assert.AreEqual(products.Count, 3);
                                    }
  • Run the test
  • Add a new unit test method which can be done by copying the method above and renaming it. This is for learning If/Else
    • [TestMethod]
                                    public void SqlRandomRecords1()
                                    {
                                        var products = Operations.GetRandomProducts();
                                    }
  • Add several else if statements
    • [TestMethod]
                                    public void SqlRandomRecords1()
                                    {
                                        var products = Operations.GetRandomProducts();
                                     
                                        if (products.Contains("Uncle Bob's Organic Dried Pears"))
                                        {
                                            Debug.WriteLine("Found 'Uncle Bob's'");
                                        }
                                        else if (products.Contains("Aniseed Syrup"))
                                        {
                                            Debug.WriteLine("Found 'Aniseed Syrup'");
                                        }
                                        else if (products.Contains("Grandma's Boysenberry Spread"))
                                        {
                                            Debug.WriteLine("Found 'Grandma's Boysenberry Spread'");
                                        }
                                        else
                                        {
                                            Debug.WriteLine("Nothing found");
                                        }
                                     
                                    }
  • Run the test (which is not really a test), once done view the output.
  • Copy the first test, pasted it and rename it, using the product names above, create a list of string and add the items.
    • [TestMethod]
                                    public void SqlRandomRecords2()
                                    {
                                        var products = Operations.GetRandomProducts();
                                     
                                        List<string> expectedList = new List<string>()
                                        {
                                            "Uncle Bob's Organic Dried Pears",
                                            "Aniseed Syrup",
                                            "Grandma's Boysenberry Spread"
                                        };
                                     
                                     
                                    }
  • Add a condition to check if any products in expectedList have been returned from the database
    • [TestMethod]
                                    public void SqlRandomRecords2()
                                    {
                                        var products = Operations.GetRandomProducts();
                                     
                                        List<string> expectedList = new List<string>()
                                        {
                                            "Uncle Bob's Organic Dried Pears",
                                            "Aniseed Syrup",
                                            "Grandma's Boysenberry Spread"
                                        };
                                     
                                        bool hasMatch = products.Any(product1 => 
                                            expectedList.Any(product2 => product2 == product1));
                                        
                                     
                                    }
  • Add a visual check
    • [TestMethod]
                                    public void SqlRandomRecords2()
                                    {
                                        var products = Operations.GetRandomProducts();
                                     
                                        List<string> expectedList = new List<string>()
                                        {
                                            "Uncle Bob's Organic Dried Pears",
                                            "Aniseed Syrup",
                                            "Grandma's Boysenberry Spread"
                                        };
                                     
                                        bool hasMatch = products.Any(product1 => 
                                            expectedList.Any(product2 => product2 == product1));
                                        
                                        Debug.WriteLine(hasMatch);
                                    }

    See also