asp.net - adding MARS to connection from web.config

Question

I have this in webconfig

<add name="dbConn" connectionString="Data Source=PC-PC;Integrated Security=True" />

Then I call them most of the time in my page via

 string connstr = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;

However some page requires two queries. What I have in mind is (or what I like to achieve)

 string connstr = ConfigurationManager.ConnectionStrings["dbConn"+"MultipleActiveResultSets=True"].ConnectionString;

But ofcourse it will not work. Since what stated in here is like the code below.

footnote: I don't wanna use this in most of my page

string connectionString = "Data Source=MSSQL1;" + 
        "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
        "MultipleActiveResultSets=True";

because i have multiple pages, and of course to easily set-up the DB.
footnote2: reason why is that because i don't know maybe it's not robust and using 2 connection in a page that only requires 1 connection maybe is ugly.
EDIT: Sorry for bad english

Akash KC · Accepted Answer · 2017-01-30T03:30:01.643

1

You can have two connection for your scenario :

Without MultipleActiveResultSets=True,

<add name="dbConn1" connectionString="Data Source=PC-PC;Integrated Security=True" />

and with MultipleActiveResultSets=True,

<add name="dbConn2" connectionString="Data Source=MSSQL1; 
        Initial Catalog=AdventureWorks;Integrated Security=SSPI;
        MultipleActiveResultSets=True;" />

You can load above connection string as per your requirement.

edited Jan 30 '17 at 03:30

answered Jan 30 '17 at 02:33

Akash KC

16,057
6
39
59

Hello, I tried adding 2 `connectionstring` in the web.config but ASP doesnt allow it. I would really like that to happen (just switching the connection between the enabled and none MARS connection) but does it matter if I use MARS to a page that doesn't need mars – Fiendcoder1 Jan 31 '17 at 02:08
AFAIK, Multiple connectionstring should be allowed in web.config It would be an issue using MARS in your scenario !!! – Akash KC Jan 31 '17 at 03:32

score 0 · Answer 2 · answered Jan 30 '17 at 02:32

Load the connection string in SqlConnectionStringBuilder, manipulate the builder, then call ToString() to get the manipulated connection string:

string webConfigConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(webConfigConnectionString);
builder.MultipleActiveResultSets = true;
string modifiedConnectionString = builder.ToString();

asp.net - adding MARS to connection from web.config

2 Answers2