我是微软Dynamics 365 & Power Platform方面的工程师/顾问罗勇,也是2015年7月到2018年6月连续三年Dynamics CRM/Business Solutions方面的微软最有价值专家(Microsoft MVP),欢迎关注我的微信公众号 MSFTDynamics365erLuoYong ,回复452或者20210803可方便获取本文,同时可以在第一间得到我发布的最新博文信息,follow me!
首先我们来看Web API查询,如果不需要进行特殊处理的话,类似的查询如下:
var accountName = "A. Datum Corporation (sample)";
var queryExp = `$filter=name eq '${accountName}'`;
Xrm.WebApi.retrieveMultipleRecords("account", `?$select=name&${queryExp}&$top=1`).then(
function success(result) {
for (var i = 0; i < result.entities.length; i++) {
console.log(result.entities[i]);
}
},
function (error) {
console.log(error.message);
}
);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
上面的查询没有问题,但是如果要查询的account的name包括了特殊字符,比如英文输入法下的单引号如何处理呢?比如下面的查询会报错:
Syntax error at position 30 in 'name eq 'A. Datum' Corporation (sample)''
var accountName = "A. Datum' Corporation (sample)";
var queryExp = `$filter=name eq '${accountName}'`;
Xrm.WebApi.retrieveMultipleRecords("account", `?$select=name&${queryExp}&$top=1`).then(
function success(result) {
for (var i = 0; i < result.entities.length; i++) {
console.log(result.entities[i]);
}
},
function (error) {
console.log(error.message);
}
);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
如何解决呢?解决办法就是将单引号替换成两个单引号,如下:
function encodeQueryStr(queryStr) {
if (queryStr) {
return queryStr.replace("'", "''");
}
else {
return "";
}
}
var accountName = "A. Datum' Corporation (sample)";
var queryExp = `$filter=name eq '${encodeQueryStr(accountName)}'`;
Xrm.WebApi.retrieveMultipleRecords("account", `?$select=name&${queryExp}&$top=1`).then(
function success(result) {
for (var i = 0; i < result.entities.length; i++) {
console.log(result.entities[i]);
}
},
function (error) {
console.log(error.message);
}
);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
那你可能会问还有别的特殊字符需要替换吗?双引号算不算?答案是双引号不用替换。还有其他的吗?我尝试了一些我认为可能需要处理的字符找到如下需要处理的,写成了函数:
function encodeQueryStr(queryStr) {
if (queryStr) {
return queryStr.replace("'", "''").replace("&", "%26").replace("+","%2B");
}
else {
return "";
}
}
1.
2.
3.
4.
5.
6.
7.
8.
对于一些特殊的查询操作符,也就是下列的查询操作符
containsnot contains
startswith
not startswith
endswith
notendswith
下面这些字符有特殊含义,需要进行转义:
Characters
Description
T-SQL Documentation and examples
%
Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix.
Percent character (Wildcard - Character(s) to Match) (Transact-SQL)
_
Use the underscore character to match any single character in a string comparison operation that involves pattern matching.
_ (Wildcard - Match One Character) (Transact-SQL)
[]
Matches any single character within the specified range or set that is specified between brackets.
[ ] (Wildcard - Character(s) to Match) (Transact-SQL)
[^]
Matches any single character that is not within the range or set specified between the square brackets.
[^] (Wildcard - Character(s) Not to Match) (Transact-SQL)
如何转义请参考 Using Wildcard Characters As Literals ,基本就是用 [] 将这个字符(比如 [, % ,_ 等字符)包括起来,更多信息请参考官方文档: Use wildcard characters in conditions for string values 。
欢迎各位补充其他需要特殊处理的字符,确认后,我会更新本博文。
-----------------------------------
©著作权归作者所有:来自51CTO博客作者luoyong0201的原创作品,请联系作者获取转载授权,否则将追究法律责任
Dynamics 365中Web API执行查询时特殊字符的处理
https://blog.51cto.com/luoyong/3259979
标签:function,console,name,111,result,error,var From: https://blog.51cto.com/u_15375427/5930332