This is my prefered method because it has the data access in a separate layer without any unnecessary intermediate code or files and no run time interpretation that is also version dependent.
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.
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;
}
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