question

datecasp-6561 avatar image
0 Votes"
datecasp-6561 asked JackJJun-MSFT commented

Sql connection in use issue

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!

dotnet-sqlclient
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@datecasp-6561, is any update? Do you have any time to check if my answer works for you?

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered datecasp-6561 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just adding the while at SELECT query it looks that works fine!!
Big big thanks @Bruce-SqlWork

0 Votes 0 ·

Finally, the while condition doesn't work... First try I forgot to remove the close-open connection statements that made it works.... If I comment those lines, only using the while(reader.Read(), it doesn't work....
Thanks for helping.
I'll keep searching for more efficient solutions

0 Votes 0 ·
JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered

@datecasp-6561, 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.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.