Sql connection in use issue

Question

I'm creating an simple exercise app to manage local database.
I'm using WPF app in VS 2022 and Wamp. For SQL management I'm using Microsoft.Data.SqlClient
You can check the whole code in my GitHub https://github.com/datecasp/C-Sharp_database_connect
My problem is that when I'm going to make a second query, I got an exception for SQL connection already in use.
As far as I know I should keep connection alive during the whole session so I don't understand why this is a problem... One by one (closing app after each query and restarting it again for the next one), everything is fine. But if a make two queries in a row (without exit the app) I get the exception
The connection gets closed when I close the app and exit.
Any idea please!!!
Tya!

Answer 1

sql connections only allow one query at a time. a query must complete and all results processed before the next query.

var command = new SqlCommand(queryString, connection); 
var reader = command.ExecuteReader(); 

while (reader.Read()) 
{ 
     // process first result set 
} 
// read all results 
while (reader.NextResult()); 

or use using statements

var command = new SqlCommand(queryString, connection); 
using (var reader = command.ExecuteReader()) 
{ 
     // read only first first result set and only first row 
     reader.Read(); 
} 

if you are using multiple threads, you need to be sure one thread completed before the other uses the connection, or use connection pooling and have each request create a connection.

Answer 2

@datecasp , based my test, as Bruce said, we need to close the connection after we use one command.

I modified your code and the following is code example:

  public partial class MainWindow : Window  
    {  
        SqlConnection connection = new SqlConnection();  
  
        public MainWindow()  
        {  
            InitializeComponent();  
        }  
  
        private void BtnEnviar_Click(object sender, RoutedEventArgs e)  
        {  
            //This should be assigned in a private file for security  
            //Define Datasource and root user in app.config  
  
            string strConn = "Data Source=(localdb)\\MSSQLLocalDB;Integrated Security=True";  
            try  
            {  
                SqlConnectionStringBuilder builder =  
            new SqlConnectionStringBuilder(strConn);  
  
                // Supply the additional values.  
                builder.UserID = txtUserIn.Text;  
                builder.Password = txtPwdIn.Password;  
  
                connection = new(builder.ConnectionString);  
                  
               
                grdMain.Visibility = System.Windows.Visibility.Collapsed;  
                grdTools.Visibility = System.Windows.Visibility.Visible;  
                      
                  
            }  
            catch (SqlException ex)  
            {  
                MessageBox.Show(ex.ToString());  
            }  
          
    }  
  
        //TOOLS UI REGION  
        private void BtnInsert_Click(object sender, RoutedEventArgs e)  
        {  
            grdTools.Visibility = System.Windows.Visibility.Collapsed;  
            grdInsert.Visibility = System.Windows.Visibility.Visible;  
        }  
  
        private void BtnSelect_Click(object sender, RoutedEventArgs e)  
        {  
            grdTools.Visibility=System.Windows.Visibility.Collapsed;  
            stkSelect.Visibility = System.Windows.Visibility.Visible;    
        }  
  
        private void BtnDelete_Click(object sender, RoutedEventArgs e)  
        {  
            grdTools.Visibility=System.Windows.Visibility.Collapsed;  
            stkDelete.Visibility = System.Windows.Visibility.Visible;  
        }  
  
        private void BtnLogout_Click(object sender, RoutedEventArgs e)  
        {  
             
            this.Close();  
        }  
  
  
        //INSERT  
        private void BtnInsertData_Click(object sender, RoutedEventArgs e)  
        {  
            //MessageBox.Show(connection.State.ToString());  
            try  
            {  
                connection.Open();  
                SqlCommand cmd = connection.CreateCommand();  
                cmd.CommandText = "INSERT INTO credenciales VALUES ('" + txtUserInsert.Text +  
                    "','" + txtPassInsert.Text + "');";  
                //MessageBox.Show(cmd.CommandText.ToString());  
  
                cmd.ExecuteReader();  
                MessageBox.Show("INSERT DONE");  
               
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.ToString());  
            }  
            finally  
            {  
                connection.Close();  
  
            }  
        }  
  
        //DELETE  
        private void BtnDeleteData_Click(object sender, RoutedEventArgs e)  
        {  
            try  
            {  
                connection.Open();  
                SqlCommand cmd = connection.CreateCommand();  
                cmd.CommandText = "DELETE FROM credenciales WHERE username='" + txtUserDelete.Text +  
                    "';";  
                //MessageBox.Show(cmd.CommandText.ToString());  
  
                cmd.ExecuteReader();  
                MessageBox.Show("DELETE DONE");  
             
            }     
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.ToString());  
            }  
            finally  
            {  
                connection.Close();  
  
            }  
        }  
  
        //SELECT  
        private void BtnSelectData_Click(object sender, RoutedEventArgs e)  
        {  
            try  
            {  
                 connection.Open();  
                SqlCommand cmd = connection.CreateCommand();  
                cmd.CommandText = "SELECT username, pwd FROM credenciales WHERE username='" + txtUserSelect.Text +  
                    "';";  
  
                SqlDataReader dr = cmd.ExecuteReader();  
  
                if (dr.HasRows)  
                {  
                    dr.Read();  
                    MessageBox.Show("USER ="+dr.GetString(0) +" PWD =" +dr.GetString(1));  
                }  
                else  
                {  
                    MessageBox.Show("USER NOT FOUND");  
                }  
                  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show(ex.ToString());  
            }  
            finally  
            {  
                connection.Close();  
  
            }  
        }  
  
        //BACK  
        private void BtnBack_Click(object sender, RoutedEventArgs e)  
        {  
            grdTools.Visibility = System.Windows.Visibility.Visible;  
            grdInsert.Visibility = System.Windows.Visibility.Collapsed;  
            stkDelete.Visibility = System.Windows.Visibility.Collapsed;  
            stkSelect.Visibility = System.Windows.Visibility.Collapsed;  
        }  
    }  
}  

Note: I changed user to username and tec.credenciales to credenciales. I also used sqlconnection to test it. You could return to your code.

I added connection.open and connection.close for every command.

Based on my test, the above code can be ran successfully.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.