Tiaan Tiaan - 23 days ago 7
C# Question

Connection string stored in web.config file doesn't open SQL Server connection

I am trying to learn to use VS2015 and I have seen a number of similar postings on the internet relating to this problem and none of them have gotten me any closer to a solution.

I am trying to write into a SQL Server database and have tested both my database and connection string and confirm they are both working well.

With that out of the way I went into

web.config
and created a definition for said connection string:

<connectionStrings>
<add name="Name"
connectionString="Data Source=<desktopname>\\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"/>
</connectionStrings>


As far as I can tell this works fine...

However, when I declare the string as follows:

MyConnectionString = ConfigurationManager.ConnectionStrings["Name"].ConnectionString;


Somehow what this returns is not understandable by:

SqlConnection iData = new SqlConnection(myConnectionString);


As when I try and "open" the connection it fails... Despite the exact same connection string works when declared like this within the C# code:

SqlConnection iData = new SqlConnection("Data Source=<desktopname>\\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1");


Does anyone have any ideas?

The problem lies with how the information is handled from
web.config
to the string to the
SqlConnection
as far as I can tell.

Notably everything runs perfectly unless I use the string as the connection string...

Thanks in advance!

Answer

In C# the backslash needs to be doubled, because the compiler treats the backslash as an escape character. And then the \\ compiles to \ in the actual string used at runtime.

In XML that is not the case, it uses a different parser, so in Web.Config you should use a single \:

<add name="Name" connectionString="Data Source=<desktopname>\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"/>

On top of that you may also need to add providerName="System.Data.SqlClient":

<add
    name="Name"
    connectionString="Data Source=<desktopname>\SQLEXPRESS01;Integrated Security=SSPI;Initial Catalog=Database1"
    providerName="System.Data.SqlClient"
/>

Reference: https://msdn.microsoft.com/en-us/library/bf7sd233(v=vs.85).aspx