The "Best Answer"

How Can Testing Be Bad?

An imaginary programming test question
This is how I would answer the question if the question was part of a real job.

There are clearly some styling practices that are not flavour of the month.
  • Variable names prefixed with their type because it is helpful for others.

  • Variables declared at the top of the function as declarations near their first use hide code functionality.
There is an unnecessary string assignment for the SQL statement.

All resources are released and there is a try/catch block.

Because It is My Way!

With a whole lot of caveats I would regard this as the best answer as it is the closest of the three answers to being ready to put into a live system.

It is also the closest to how I would do it, hence the problem with tests.

It's the best ways because it is my way and it adds all the little details that I expected to see but the question didn't actually specify.


Answer C#

    protected Boolean LoadDataAnswer1(String strConnectionStringLocal,DataTable dtInteresting)
    {
        Boolean boolRes = false;
        String strSQL;
        SqlConnection connSQL;
        SqlCommand cmdSQL;
        SqlDataReader drSQL;

        try
        {
            // Load the datatable with the relevant tickers
            using (connSQL = new SqlConnection(strConnectionStringLocal))
            using (cmdSQL = new SqlCommand())
            {
                // Main Select
                connSQL.Open();
                strSQL = "[spSelect tab_StockTickers1]";
                cmdSQL.Connection = connSQL;
                cmdSQL.CommandText = strSQL;
                cmdSQL.CommandType = CommandType.StoredProcedure;
                cmdSQL.CommandTimeout = 3600;
                cmdSQL.Parameters.AddWithValue("@ShareIndex", "FTSE 100");

                // Create a datatable for the results
                dtInteresting = new DataTable();

                // Load datatable from an SQL Datareader
                using (drSQL = cmdSQL.ExecuteReader())
                {
                    dtInteresting.Load(drSQL);
                }
            }
            boolRes = true;
        }
        catch (Exception ex)
        {
            HandleErrorMessage(ex.Message);
        }
        return boolRes;
    }


Answer SQL

USE StockTickers
GO

IF OBJECT_ID('spSelect tab_StockTickers1') IS NOT NULL DROP PROCEDURE [spSelect tab_StockTickers1]
GO

/*
PURPOSE
=========
Selects core information about a company and its stock market details

VERSIONS
=========
1.00    Ian Smith   26/Jun/2020 Original
-----------------------------------------------------------------------------------------*/
CREATE PROCEDURE [spSelect tab_StockTickers1]
@ShareIndex VARCHAR(32)

AS
SET NOCOUNT ON
SELECT Company,
       CompanyTicker,
       Keypoints,
       SharePriceCat,
       MinPrice,
       MaxPrice,
       LastReviewPrice,
       LastReviewDate,
       ShareIndex
  FROM StockTickers
WHERE ShareIndex = @ShareIndex 
GO