C# connected to SQL not returning a value

I connected to SQL Server with C#. Now, I have to do 3 options. One is to execute a procedure, which works perfectly, the second one is to retrieve some data, which is also done and the last option is to select a specific country, which is chosen by the user, and show an average of the scores of the restaurants that are located in that country.

Now, the query works on SSMS as I tested it. However, when I tried to run it like the other 2 options, it gave me a connection error.

The code below is the C# code of the second option that works:

public static void showStatistics()
{
    string connetionString = "Data Source=DESKTOP-RV4VR9I;Initial 
Catalog=Assignment;User ID=Jake;Password=1234;Integrated Security=True";

    SqlConnection conn = new SqlConnection(connetionString);
    SqlCommand mySQL = new SqlCommand("BEGIN TRANSACTION SET     
TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT TOP 1 country, score, 
year FROM main.[stats] ORDER BY [Statistics Generated] DESC COMMIT         
TRANSACTION", conn);

    try
    {
        conn.Open();

        SqlDataReader dataReader = mySQL.ExecuteReader();

        string holderCountry = "";
        string holderScore = "";
        string holderYear = "";

        while (dataReader.Read())
        {
             holderCountry += dataReader["country"];
             holderScore += dataReader["score"];
             holderYear += dataReader["year"];
        }

        Console.WriteLine("\nCountry: "+holderCountry);
        Console.WriteLine("Score: " + holderScore);
        Console.WriteLine("Year: " + holderYear+"\n");

        conn.Close();
    }    
    catch (Exception e)
    {
        Console.WriteLine("Can not open connection ! ");
    }
}

The next chunk of code is the code that is giving me an error:

public static void showCountryAverage(string countryChoice)
        {
            string connetionString = "Data Source=DESKTOP-RV4VR9I;Initial 
Catalog=Assignment;User ID=Jake;Password=1234;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connetionString);
            SqlCommand mySQL = new SqlCommand("BEGIN TRANSACTION SET 
TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT c.country, 
AVG(CAST(re.score AS DECIMAL(8, 6))) FROM main.country c JOIN 
main.restaurant r ON(c.countryId = r.countryId) JOIN main.review re 
ON(r.restaurantId = re.restaurantId) WHERE c.country = "+countryChoice+" 
GROUP BY c.country COMMIT TRANSACTION", conn);
            try
            {
                conn.Open();
                SqlDataReader dataReader = mySQL.ExecuteReader();
                string holderCountry = "";
                while (dataReader.Read())
                {

                    holderCountry += dataReader["country"];
                    holderCountry += dataReader["AVG(CAST(re.score AS     
DECIMAL(8, 6)))"];
                }
                Console.WriteLine("\nCountry: " + holderCountry);
                conn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Can not open connection ! ");
            }
        }

Thanks!

This is the error

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Italy'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Database_Number_6.Program.showCountryAverage(String countryChoice) in C:\Users\User\Desktop\000SWD6.1B\2ndSemester\Database 2\Assignment\Database_Number_6\Database_Number_6\Program.cs:line 133 ClientConnectionId:74fbcdc0-47ec-41cc-839d-0b05b13661df Error Number:207,State:1,Class:16

The problem Invalid column name Italy is caused by your concatenation of the string Italy to your sql command. In SQL, if you want to express a constant string value, you should put it between single quotes like 'Italy'. Without the quotes the sql parser engine thinks that you want to compare the values in the country column with the values in a Italy column. So, adding quotes can resolve the problem, but this is a well known source of parsing bugs (what if the value contains itself a single quote? and I don't even start to talk about dates) Worse this approach allows an easy way to hack a database through Sql Injection.

The only correct way to create queries that needs code's values is using parameters

SqlCommand mySQL = new SqlCommand(@".....
                                   SELECT c.country, 
                                   ....
                                   FROM main.country c 
                                   ....
                                   WHERE c.country = @country
                                   GROUP BY .....", con);
mySql.Parameters.Add("@country", SqlDbType.NVarChar).Value = countryCode;
 ....