none
Opening SQL connection more than once at the same time RRS feed

  • Question

  • Hi,

    I have two winforms that open at the same time, each one when loading, creates a new instance of the data layer, both try to get some data from SQL Server 2017, but I always get an error:

    There is already an open DataReader associated with this Command which must be closed first

    I tried using MultipleActiveResultSets=True in my connection string but no luck, now I get:

    Operation is not valid, connection has been terminated

       System.Data.ProviderBase.DbConnectionClosed.CreateReferenceCollection()
       System.Data.ProviderBase.DbConnectionInternal.AddWeakReference(Object value, Int32 tag)
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
       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)
       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)
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       GlobalDataLayer.DxGLOBALLoadTableSQLDataAdapter(String SqlStr) D:\..........\GlobalDataLayerMSSQL.vb:línea 49

    So, how can I use my connection to accept to queries at the same time?

    Regards


    G.Waters

    Sunday, January 27, 2019 5:24 PM

Answers

  • Yes there is a faster way.

    As mentioned earlier about base classes.

    All of the three classes are in a class project.

    Exceptions class

    Imports System.Data.SqlClient
    
    Public Class BaseExceptionsHandler
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an 
        ''' exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Protected ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' Indicates if there was a sql related exception
        ''' </summary>
        Public ReadOnly Property HasSqlException() As Boolean
            Get
                If LastException IsNot Nothing Then
                    Return TypeOf LastException Is SqlException
                Else
                    Return False
                End If
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in 
        ''' LastException this provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage() As String
            Get
                Return LastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an 
        ''' exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul() As Boolean
            Get
                Return Not HasException
            End Get
        End Property
    
    End Class
    

    Base connection class

    Public MustInherit Class BaseSqlServerConnection
        Inherits BaseExceptionsHandler
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = "NorthWindAzure3"
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    

    Data class (which inherits both exception and connection classes). Note the data reader gets data into a list.

    Imports System.Data.SqlClient
    Public Class Operations
        Inherits BaseSqlServerConnection
        Public Function Countries() As List(Of Country)
            Dim countryNames As New List(Of Country)
            Dim selectStatement = "SELECT id,CountryName  FROM dbo.Countries"
    
            Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    Try
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
    
                        While reader.Read()
                            countryNames.Add(New Country() With 
                            {
                                .Id = reader.GetInt32(0), 
                                .CountryName = reader.GetString(1)
                            })
                        End While
    
                    Catch ex As Exception
    
                        mHasException = True
                        mLastException = ex
    
                    End Try
    
                End Using
            End Using
    
            Return countryNames
        End Function
    End Class
    

    Country class, note the override, the ComboBox picks up on this so no need to set DisplayMember.

    Public Class Country
        Public Property Id() As Integer
        Public Property CountryName() As String
    
        Public Overrides Function ToString() As String
            Return CountryName
        End Function
    End Class
    

    Form project, we add a reference to the class project, add a ComboBox and button.

    Imports DataOperations
    Public Class Form1
        Private dataOperations As New Operations
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    
            countryComboBox.DataSource = dataOperations.Countries
            If Not dataOperations.IsSuccessFul Then
                ' handle issue via dataOperations.LastExceptionMessage
            End If
        End Sub
    
        Private Sub selectedCountry_Click(sender As Object, e As EventArgs) _
            Handles selectedCountry.Click
    
            Dim country = CType(countryComboBox.SelectedItem, Country)
    
            MessageBox.Show($"Id: {country.Id} name: {country.CountryName}")
    
        End Sub
    End Class
    

    Screenshot


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by George Waters Sunday, January 27, 2019 10:48 PM
    Sunday, January 27, 2019 10:40 PM
    Moderator

All replies

  • Hello,

    The connection itself is not the issue, instead the command object is the issue with the reader.

    If you open a connection then on a command object execute (where cmd is the command object) cmd.ExecuteReader, before doing another cmd.ExecuteReader you need to close the first one e.g.

    Dim Reader = cmd.ExecuteReader

    do some work

    Reader.Close

    Now you can execute ExecuteReader again otherwise without Reader.Close you get the current exception. Now with a DataAdapter (which I see using the Fill method), if you do another Fill with the connection open which is linked to the Command object within the DataAdapter this can cause the same issue although usually this is not an issue but sounds like it here.

    BTW the IDbxxxx are the underlying Interface to all data providers.

    These seem to be the problem spots which are all internal but as mentioned the focus is on two many ExecuteReader executions.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Sunday, January 27, 2019 5:45 PM
    Moderator
  • Thanks Karen,

    So this means I cannot have two readers working at the same time in separate threads?

    If I have an application with threads that try to connect to database at the same time and using the same connection, how should I do it?

    I just can't find an scenario like this but there is got to be a way, am I wrong?

    Regards


    G.Waters

    Sunday, January 27, 2019 6:46 PM
  • Accessing the database from threads is frequently used in case of ASP applications, for example.

    Make sure that each thread has separate objects, such as SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, DataTable, or share them in a valid manner.

    Maybe you have some problematic global shared variables. 

    Sunday, January 27, 2019 7:26 PM
  • Thanks Karen,

    So this means I cannot have two readers working at the same time in separate threads?

    If I have an application with threads that try to connect to database at the same time and using the same connection, how should I do it?

    I just can't find an scenario like this but there is got to be a way, am I wrong?

    Regards


    G.Waters

    Is the database server or local as server base will work via MARS but never tried it with a local database and never with any form of data adapter (which is indicated in the stack trace). 

    If server based and not using an adapter there should be no issues otherwise unsure as I have never used data adapters in this type of operation.

    The usual way to work through this is not to share one connection but a connection for each operation, See parallel execution.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 27, 2019 7:58 PM
    Moderator
  • Well, I think that was my problem, because I had a single connection as a global variable and passing it to everywhere I need it

    Public DxCnn as new SqlConnection("<connectionString>")

    Now the problem is that if I use a new SqlConnection everywhere, the application turns really slow, for example, passing the connection as I used, it loads the winform in 5 seconds, using a new connection to each query it takes 16 seconds.

    is there a performance issue using this model?



    G.Waters

    Sunday, January 27, 2019 8:25 PM
  • You have a single connection for each operation.

    For example, setup a base connection (in this case with a base exception class) then use it as shown here. Of course this may not fit into your coding style.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 27, 2019 9:14 PM
    Moderator
  • Well, I think that was my problem, because I had a single connection as a global variable and passing it to everywhere I need it

    Public DxCnn as new SqlConnection("<connectionString>")

    Now the problem is that if I use a new SqlConnection everywhere, the application turns really slow, for example, passing the connection as I used, it loads the winform in 5 seconds, using a new connection to each query it takes 16 seconds.

    is there a performance issue using this model?



    G.Waters


    You can't share a single connection across multiple threads is the bottom line here. You must open the connection,  do the CRUD and close the connection. What are you using a datatable, which is slow in doing the queries?
    Sunday, January 27, 2019 9:36 PM

  • You can't share a single connection across multiple threads is the bottom line here. You must open the connection,  do the CRUD and close the connection. What are you using a datatable, which is slow in doing the queries?

    I use datatables to load catalogs for comboboxes or datagridviews

    Is there a faster way?

    I didn't know loading datatables was slow, versus what?


    G.Waters

    Sunday, January 27, 2019 9:55 PM
  • Yes there is a faster way.

    As mentioned earlier about base classes.

    All of the three classes are in a class project.

    Exceptions class

    Imports System.Data.SqlClient
    
    Public Class BaseExceptionsHandler
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an 
        ''' exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Protected ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' Indicates if there was a sql related exception
        ''' </summary>
        Public ReadOnly Property HasSqlException() As Boolean
            Get
                If LastException IsNot Nothing Then
                    Return TypeOf LastException Is SqlException
                Else
                    Return False
                End If
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in 
        ''' LastException this provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage() As String
            Get
                Return LastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an 
        ''' exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul() As Boolean
            Get
                Return Not HasException
            End Get
        End Property
    
    End Class
    

    Base connection class

    Public MustInherit Class BaseSqlServerConnection
        Inherits BaseExceptionsHandler
        ''' <summary>
        ''' This points to your database server
        ''' </summary>
        Protected DatabaseServer As String = "KARENS-PC"
        ''' <summary>
        ''' Name of database containing required tables
        ''' </summary>
        Protected DefaultCatalog As String = "NorthWindAzure3"
        Public ReadOnly Property ConnectionString() As String
            Get
                Return $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True"
            End Get
        End Property
    End Class
    

    Data class (which inherits both exception and connection classes). Note the data reader gets data into a list.

    Imports System.Data.SqlClient
    Public Class Operations
        Inherits BaseSqlServerConnection
        Public Function Countries() As List(Of Country)
            Dim countryNames As New List(Of Country)
            Dim selectStatement = "SELECT id,CountryName  FROM dbo.Countries"
    
            Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    Try
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
    
                        While reader.Read()
                            countryNames.Add(New Country() With 
                            {
                                .Id = reader.GetInt32(0), 
                                .CountryName = reader.GetString(1)
                            })
                        End While
    
                    Catch ex As Exception
    
                        mHasException = True
                        mLastException = ex
    
                    End Try
    
                End Using
            End Using
    
            Return countryNames
        End Function
    End Class
    

    Country class, note the override, the ComboBox picks up on this so no need to set DisplayMember.

    Public Class Country
        Public Property Id() As Integer
        Public Property CountryName() As String
    
        Public Overrides Function ToString() As String
            Return CountryName
        End Function
    End Class
    

    Form project, we add a reference to the class project, add a ComboBox and button.

    Imports DataOperations
    Public Class Form1
        Private dataOperations As New Operations
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    
            countryComboBox.DataSource = dataOperations.Countries
            If Not dataOperations.IsSuccessFul Then
                ' handle issue via dataOperations.LastExceptionMessage
            End If
        End Sub
    
        Private Sub selectedCountry_Click(sender As Object, e As EventArgs) _
            Handles selectedCountry.Click
    
            Dim country = CType(countryComboBox.SelectedItem, Country)
    
            MessageBox.Show($"Id: {country.Id} name: {country.CountryName}")
    
        End Sub
    End Class
    

    Screenshot


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by George Waters Sunday, January 27, 2019 10:48 PM
    Sunday, January 27, 2019 10:40 PM
    Moderator
  • Thank you all for your help

    G.Waters

    Sunday, January 27, 2019 10:48 PM
  • Hi

     public class SqlDbConnect
        {
            private SqlConnection _con;
            public SqlCommand Cmd;
            private SqlDataAdapter _da;
            private DataTable _dt;
            public SqlDbConnect()
            {
                //_con=new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=TestDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False");
                _con = new SqlConnection(@"Data Source=servernameSQLSRV;Initial Catalog=DB;User ID=sa;Password=xxxxxxxxxxxx");
                _con.Open();
            }
            public void SqlQuery(string queryText)
            {
                Cmd = new SqlCommand(queryText, _con);
            }
            public DataTable QueryEx()
            {
                _da = new SqlDataAdapter(Cmd);
                _dt = new DataTable();
                _da.Fill(_dt);
                return _dt;
            }
            public void NonQueryEx()
            {
                Cmd.ExecuteNonQuery();
            }
        }

    code in form

    private void fillcmbdpt()
            {
                con = new SqlDbConnect();
                con.SqlQuery("SELECT [Id],[Description],[State] FROM [dbo].[Tbl_Department] ");
                cmbDept.Items.Clear();
                cmbDept.DataSource = con.QueryEx();
                cmbDept.DisplayMember = "Description";
                cmbDept.ValueMember = "Id";
                cmbDept.SelectedIndex = 0;
                if (cmbDept.Items.Count>0)
                {
                     ID = Convert.ToInt32(cmbDept.SelectedValue);
                }
              
            }

    Best regards.

    Please click the Mark as answer button and vote as helpful if this reply solves your problem.

    Sunday, January 27, 2019 11:07 PM
  • Hello Abdulhakim,

    Thanks for posting but in the future please use the language of this forum which is VB.NET, not C#. Also all code belongs in code blocks.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, January 27, 2019 11:29 PM
    Moderator
  • Hello Karen

    I'm so sorry
    Greetings.

    Monday, January 28, 2019 12:53 AM

  • You can't share a single connection across multiple threads is the bottom line here. You must open the connection,  do the CRUD and close the connection. What are you using a datatable, which is slow in doing the queries?

    I use datatables to load catalogs for comboboxes or datagridviews

    Is there a faster way?

    I didn't know loading datatables was slow, versus what?


    G.Waters

    versus a collection......

    https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

    https://dzone.com/articles/reasons-move-datatables

    http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

    You could have put a Module.vb in the project that is global to all classes in the project and had functionality to get the sql connection. I was using Module.vb for something else but put the SQL connection stuff in there to test it. It works well it compiled successfully/. :)

    Imports System.Data.Entity Imports System.Data.SqlClient Imports Microsoft.AspNet.Identity Imports Microsoft.AspNet.Identity.EntityFramework Imports ProgMgmntVB.Controllers Imports ProgMgmntVB.Models Imports ProgMgmntVB.WebApi Imports Unity Imports Unity.Injection Imports Unity.Lifetime Imports Unity.Mvc5 Module ModuleUnity public class UnityConfig public shared sub RegisterComponents() dim container = new UnityContainer() container.RegisterType(Of IProjectModel, ProjectModel)() container.RegisterType(Of ITaskModel, TaskModel)() container.RegisterType(Of IWebApi, WebApi.WebApi)() container.RegisterType(Of IModelHelper, ModelHelper)() 'Idenity container.RegisterType(Of DbContext, ApplicationDbContext)(new HierarchicalLifetimeManager()) container.RegisterType(of UserManager(of ApplicationUser))(new HierarchicalLifetimeManager()) container.RegisterType(of IUserStore(of ApplicationUser), UserStore(of ApplicationUser))(New HierarchicalLifetimeManager()) container.RegisterType(of AccountController)(new InjectionConstructor()) DependencyResolver.SetResolver(new UnityDependencyResolver(container)) End sub public Shared Function GetConnectionFactory() as IDbConnection return GetConnection(ConfigurationManager.ConnectionStrings("SomeConnectionString").ConnectionString) end function public Shared function GetConnection(connectionString as string) as IDbConnection return New SqlConnection(connectionString) end function ''this was a little test and it works, and the using statment you see

    '' can be implemented in any class to have the connection retuned. public sub Test() using con = ModuleUnity.UnityConfig.GetConnectionFactory() con.Open() ' Sql stuff e end using End sub End Class End Module


    Monday, January 28, 2019 1:30 AM