关于SqlServer数据库连接串在config文件中问题总结

最新推荐文章于 2023-07-11 14:54:19 发布

jiatongtong

最新推荐文章于 2023-07-11 14:54:19 发布

阅读量1.7k

点赞数

分类专栏： SQL SERVER 数据库文章标签： sqlserver connectionstring

本文链接：https://blog.csdn.net/xiaojia_boke/article/details/81386343

版权

SQL SERVER 数据库专栏收录该内容

85 篇文章 1 订阅

订阅专栏

web.config中配置sqlserver 连接字符串

1、如果连接字符串中含有特殊字符应该如何处理?

因为config文件是xml文件，所以一些xml保留的关键字是不能直接写在连接串中的。那么如果在连接串中含有了这些特殊字符，我们应该怎样处理呢？

比如：<add name="DefaultConnection" connectionString="Data Source=databse;Initial Catalog=db;UID=user;PWD='3H]uu4qo<yN;" providerName="System.Data.SqlClient" />

其中，PWD的值中包含有几个字符需要注意：

（1）单引号即 ',单引号正常是作为connection字符串的保留字符使用的，所以这里需要处理；

（2）小于号即 <，小于号正常在xml中是作为保留字符使用的，所以这里也要进行处理；

对于xml保留字符出现在连接串中的情况，可以参照下表，对连接串配置进行修改：其中可以把“显示”列中的对应的内容替换为“实体名称”列中对应的内容既可，即：

源密码为：'3H]uu4qo<yN，替换后的为：'3H]uu4qo<yN

但是如果你认为这样替换完后就万事大吉，那么你就高兴的太早了，因为你紧接着运行程序的时候，会马上碰到第二点“从索引 55 处开始，初始化字符串的格式不符合规范”类似的错误。那么我们要如何处理这个情况呢？参见下面第二点明细。

显示	说明	实体名称	实体编号
	空格
<	小于	<	<
>	大于	>	>
&	&符号	&	&
"	双引号	"	"
©	版权	©	©
®	已注册商标	®	®
™	商标（美国）	™	™
×	乘号	×	×
÷	除号	÷	÷

2、碰到“从索引 55 处开始，初始化字符串的格式不符合规范”类似错误应该如何处理；

这个错误是因为连接字符串解析时报错，那么应该如何处理呢？

在处理这个问题之前我们先看下msdn和网上对connectstring格式相关介绍：

（1）msdn

The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.

The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value. To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotation marks. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotation marks. The single quotation mark is also useful if the value starts with a double-quote character. Conversely, the double quotation mark can be used if the value starts with a single quotation mark. If the value contains both single-quote and double-quote characters, the quotation mark character used to enclose the value must be doubled every time it occurs within the value.

To include preceding or trailing spaces in the string value, the value must be enclosed in either single quotation marks or double quotation marks. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in quotation marks. However, spaces within a string literal keyword or value are preserved. Single or double quotation marks may be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless a quotation mark character is the first or last character in the value.

Keywords are not case sensitive.

（2）网上其他对connectionstring 格式定义的描述

All blank characters, except those placed within a value or within quotation marks, are ignored
Blank characters will though affect connection pooling mechanism, pooled connections must have the exact same connection string
If a semicolon (;) is part of a value it must be delimited by quotation marks (")
Use a single-quote (') if the value begins with a double-quote (")
Conversely, use the double quote (") if the value begins with a single quote (')
No escape sequences are supported
The value type is not relevant
Names are case iNsEnSiTiVe
If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the last occurrence is used
However, if the provider keyword occurs multiple times in the string, the first occurrence is used.
If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.
If a value has preceding or trailing spaces it must be enclosed in single- or double quotes, ie Keyword=" value ", else the spaces are removed.