C# SQL: System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'

Question

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; // need this for database connection

namespace DatabaseTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void connectButton_Click(object sender, EventArgs e)
        {
         
            string connectionString;
            SqlConnection cnn;

            connectionString = "Server= xxx; Database= nba_database; Integrated Security=True"; // xxx is a placeholder, my connection string is right. Censoring it for privacy 


            cnn = new SqlConnection(connectionString);

            cnn.Open();
            MessageBox.Show("Connection Established!");

            cnn.Close();
        }

        private void displayButton_Click(object sender, EventArgs e)
        {


            string connectionString;
            SqlConnection cnn;

            connectionString = "Server= myServer; Database= nba_database; Integrated Security=True";


            cnn = new SqlConnection(connectionString);

            cnn.Open();
            MessageBox.Show("Connection Established!");

            





           
            // lets query some data from the sql server

            // define variables
            SqlCommand command;
            SqlDataReader dataReader;
            String sql, output = "";

            // define SQL statement!
            sql = "SELECT FirstName, LastName " +
                "FROM Players, Teams " +
                "WHERE Players.TeamID = Teams.TeamID " +
                "AND Teams.Nickname = 'Hawks'";

            String sql2 = "SELECT FirstName, LastName FROM Players WHERE Team = 'Milwaukee Bucks'";

          

            // command statement
            command = new SqlCommand(sql, cnn);
          

            dataReader = command.ExecuteReader();

            // Get table values


            textBox1.Text = command.ExecuteScalar().ToString();


            cnn.Close();
            dataReader.Close();
            command.Dispose();
           
        }

    }
}

I'm trying to connect a C# Visual Studio Application to a SQL Server database and I am getting this error: System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'

When the user clicks the Display button, I want to be able to return the results of a query to a textbox in the c# application.

The error is on this line textBox1.Text = command.ExecuteScalar().ToString();

Answer 1

You must close the datareader before the call to ExecuteScalar(). Try like:

        // command statement
        command = new SqlCommand(sql, cnn);
      

        dataReader = command.ExecuteReader();

        // Get table values
        dataReader.Close(); ///<--close reader 

        textBox1.Text = command.ExecuteScalar().ToString();


        cnn.Close();
        
        command.Dispose();

Answer 2

Here's the two overlapped operations; a data-reader is active until it is consumed/closed/disposed:

// first active operation
dataReader = command.ExecuteReader();

// second active operation
textBox1.Text = command.ExecuteScalar().ToString();

in reality, you aren't using the first one, so... just remove that line?

More generally: it is a good idea to use using on all ADO.NET objects, and frankly: the ADO.NET API is a mess. Consider using Dapper instead, then all this becomes:

string nickname = "Hawks";
using (var cnn = new SqlConnection(connectionString))
{
    textBox1.Text = cnn.ExecuteScalar<string>(@"
SELECT FirstName, LastName
FROM Players, Teams
WHERE Players.TeamID = Teams.TeamID
AND Teams.Nickname = @nickname", new { nickname }");
}

although note that "scalar" only returns one cell (the first column of the first row); you might want to do something with objects instead:

string nickname = "Hawks";
using (var cnn = new SqlConnection(connectionString))
{
    var players = cnn.Query<Player>(@"
SELECT FirstName, LastName
FROM Players, Teams
WHERE Players.TeamID = Teams.TeamID
AND Teams.Nickname = @nickname", new { nickname }).AsList();
}

where Player looks something like:

public class Player
{
    public string FirstName {get;set;}
    public string LastName {get;set;}
}