If you go this route, I think you'll want to use the DbProviderFactories class to get a DbProviderFactory that you can use to construct the connection. I haven't tried this code out, but I think it will work. It's possible that you may need to look up the provider name using the GetFactoryClasses method on the DbProviderFactories class and use the InvariantName.
public DbConnection GetConnection(String connectionName)
{
//Get the connection string info from web.config
ConnectionStringSettings cs=
ConfigurationManager.ConnectionStrings[connectionName];
//documented to return null if it couldn't be found
if (cs == null)
throw new ConfigurationErrorsException("Invalid connection name \""+connectionName+"\"");
//Get the factory for the given provider (e.g. "System.Data.SqlClient")
DbProviderFactory factory =
DbProviderFactories.GetFactory(cs.ProviderName);
//Undefined behaviour if GetFactory couldn't find a provider.
//Defensive test for null factory anyway
if (factory == null)
throw new Exception("Could not obtain factory for provider \""+cs.ProviderName+"\"");
//Have the factory give us the right connection object
DbConnection conn = factory.CreateConnection();
//Undefined behaviour if CreateConnection failed
//Defensive test for null connection anyway
if (conn == null)
throw new Exception("Could not obtain connection from factory");
//Knowing the connection string, open the connection
conn.ConnectionString = cs.ConnectionString;
conn.Open()
return conn;
}
If you use an appropriate class or library, they will do the escaping for you. Many XML issues are caused by string concatenation.
XML escape characters
There are only five:
" "
' '
< <
> >
& &
Escaping characters depends on where the special character is used.
The examples can be validated at the W3C Markup Validation Service.
Text
The safe way is to escape all five characters in text. However, the three characters "
, '
and >
needn't be escaped in text:
<?xml version="1.0"?>
<valid>"'></valid>
Attributes
The safe way is to escape all five characters in attributes. However, the >
character needn't be escaped in attributes:
<?xml version="1.0"?>
<valid attribute=">"/>
The '
character needn't be escaped in attributes if the quotes are "
:
<?xml version="1.0"?>
<valid attribute="'"/>
Likewise, the "
needn't be escaped in attributes if the quotes are '
:
<?xml version="1.0"?>
<valid attribute='"'/>
Comments
All five special characters must not be escaped in comments:
<?xml version="1.0"?>
<valid>
<!-- "'<>& -->
</valid>
CDATA
All five special characters must not be escaped in CDATA sections:
<?xml version="1.0"?>
<valid>
<![CDATA["'<>&]]>
</valid>
Processing instructions
All five special characters must not be escaped in XML processing instructions:
<?xml version="1.0"?>
<?process <"'&> ?>
<valid/>
XML vs. HTML
HTML has its own set of escape codes which cover a lot more characters.
Best Answer
Use
"
instead of"
to escape it.web.config is an XML file so you should use XML escaping.
See this forum thread.
Update:
"
should work, but as it doesn't, have you tried some of the other string escape sequences for .NET?\"
and""
?Update 2:
Try single quotes for the connectionString:
Or:
Update 3:
From MSDN (SqlConnection.ConnectionString Property):
So:
The issue is not with web.config, but the format of the connection string. In a connection string, if you have a
"
in a value (of the key-value pair), you need to enclose the value in'
. So, whilePassword=somepass"word
does not work,Password='somepass"word'
does.