Working with variable from SQL DataReader

Question

I have read the int "anzahl" with DataReader. Now I want to work with this number. The problem is: I cannot access the int from outside the while loop. The error shows "use of unassigned local variable". How can i make "anzahl" available? Here is my code:

int anzahl;
string uid = uidTextbox.Text;
string query1 = string.Format("SELECT Chip_Anzahl FROM chipTable WHERE Chip_ID = '{0}';",
                              uid);

try
{
    sqlConnection = new SqlConnection(connectionString);
    SqlCommand sqlCmd = new SqlCommand(query1, sqlConnection);
    sqlCmd.CommandText = query1;

    sqlConnection.Open();
    SqlDataReader dataReader = sqlCmd.ExecuteReader();

    while(dataReader.Read())
    {
        anzahl = dataReader.GetInt32(0);
    }

    dataReader.Close();
    sqlConnection.Close();
}
catch (Exception E)
{
    MessageBox.Show(E.ToString());
}

int newnumb = anzahl + 5;
MessageBox.Show(newnumb.toString());

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Jun 08 '21 at 19:50
[What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) | [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , Jun 08 '21 at 19:53
Does this answer your question? [How to fix 'Use of unassigned local variable' in C#](https://stackoverflow.com/questions/57343880/how-to-fix-use-of-unassigned-local-variable-in-c-sharp) and [Why did I get the compile error “Use of unassigned local variable”?](https://stackoverflow.com/questions/9233000/why-did-i-get-the-compile-error-use-of-unassigned-local-variable) — , Jun 08 '21 at 19:56
Also, seems like this should be an `ExecuteScalar()` situation, rather than `ExecuteReader()` — Joel Coehoorn, Jun 08 '21 at 20:05

Joel Coehoorn · Answer 1 · 2021-06-09T14:04:19.397

The specific problem you're running into is C# is not able to determine anzahl is definitely assigned. That is, there are scenarios where this line never runs:

anzahl = dataReader.GetInt32(0);

But this line still does:

int newnumb = anzahl + 5;

If that happens anzahl is undefined, which is not permitted.

The naive fix is a simple adjustment to the variable declaration:

int anzahl = 0;

This will allow the code to compile and run (mostly) as you expect. However, it's probably worth your time to understand those edge cases some more... especially the edge cases around SQL INJECTION as described in the comments.

A more complete fix might look like this:

//put this in a separate file, and move ALL database access into this class
public static class DB
{
    private string ConnectionString {get;} = "connection string here";
   
    public static int GetAnzahlByChipUID(string chipUID)
    {
        string query1 = "SELECT Chip_Anzahl FROM chipTable WHERE Chip_ID = @chipUID;",
                            
        using (var sqlConnection = new SqlConnection(ConnectionString))
        using (var sqlCmd = new SqlCommand(query1, sqlConnection))
        {
            sqlCmd.Parameters.Add("@chipUID", SqlDbtype.VarChar, 64).Value = chipUID;

            sqlConnection.Open();
            return (int)sqlCmd.ExecuteScalar();
        }
    }
}

//Then call it from the existing location like this:
try 
{
    int anzahl = DB.GetAnzahlByChipUID(uidTextbox.Text);
    int newnumb = anzahl + 5;
    MessageBox.Show(newnumb.ToString());
}
catch (Exception E)
{
    MessageBox.Show(E.Message);
}

Note the use of sqlCmd.Parameters to set the value for @chipUID. This does NOT do a simple string substitution. Real injection protection quarantines the parameter data in a completely separate area from the SQL command text, to prevent any possibility of injection.

Working with variable from SQL DataReader

1 Answers1