How to enable MultipleActiveResultSets

I have the following connection string in my code:

SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["RaiseFantasyLeagueConnectionString"].ConnectionString);

My webconfig for this looks like this:

    <connectionStrings>
<add name="RaiseFantasyLeagueConnectionString" connectionString="Data Source=MATT-PCSQLEXPRESS;Initial Catalog=Raise;Integrated Security=True" providerName="System.Data.SqlClient"/>

Can somebody tell me where I can enable MultipleActiveResultSets for my connection?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It is really simple, just add

MultipleActiveResultSets=true;

so change, in your web.config, the connection string in this way:

connectionString="Data Source=MATT-PCSQLEXPRESS;" + 
                 "Initial Catalog=Raise;Integrated Security=True;" + 
                 "MultipleActiveResultSets=true;"

Method 2

Try this code

<connectionStrings>
<add name="RaiseFantasyLeagueConnectionString" connectionString="Data Source=MATT-PCSQLEXPRESS;Initial Catalog=Raise;Integrated Security=True ;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient";/>

Must refer this Msdn article

Method 3

 public static class ConfigurationService
    {
        static public string ConnectionString
        {

            get
            {

                try
                {               
                    // Specify the provider name, server and database.
                    string providerName = "System.Data.SqlClient";
                    string serverName = @"192.168.1.106SQLEXPRESS";
                    string databaseName = "MyDatabaseName";

                    // Initialize the connection string builder for the
                    // underlying provider.
                    var sqlBuilder = new SqlConnectionStringBuilder();

                    // Set the properties for the data source.
                    sqlBuilder.DataSource = serverName;
                    sqlBuilder.InitialCatalog = databaseName;
                    sqlBuilder.IntegratedSecurity = false;
                    sqlBuilder.UserID = "Bob";
                    sqlBuilder.Password = "Bob1234";
                    sqlBuilder.MultipleActiveResultSets = true;
                    sqlBuilder.ApplicationName = "EntityFramework";

                    // Build the SqlConnection connection string.
                    string providerString = sqlBuilder.ToString();

                    // Initialize the EntityConnectionStringBuilder.
                    var entityBuilder = new EntityConnectionStringBuilder();

                    //Set the provider name.
                    entityBuilder.Provider = providerName;

                    // Set the provider-specific connection string.
                    entityBuilder.ProviderConnectionString = providerString;                 

                    // Set the Metadata location.
                    entityBuilder.Metadata = @"res://*/Models.MyDatabaseNameModel.csdl|res://*/Models.MyDatabaseNameModel.ssdl|res://*/Models.MyDatabaseNameModel.msl";

                    var result = entityBuilder.ToString();
                    return result;
                }
                catch (Exception)
                {

                }

                return string.Empty;
            }

        }
    }

Please note that Models in Models.MyDatabaseNameModel is a Folder name of your VS project.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0