CLR函数不会返回超过4000个字符。

Question

CLR函数不会返回超过4000个字符。

7

第一次发帖，请多多关照！:-) 我是新手，正在尝试修复调用Web服务的CLR函数。我拼凑出了以下代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Globalization;

// For the SQL Server integration
using Microsoft.SqlServer.Server;

// Other things we need for WebRequest
using System.Net;
using System.Text;
using System.IO;

public partial class UserDefinedFunctions
{

// Function to return a web URL as a string value.
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlChars GET(SqlString uri, SqlString username, SqlString passwd)
{
    // The SqlPipe is how we send data back to the caller
    SqlPipe pipe = SqlContext.Pipe;
    SqlChars document;

    // Set up the request, including authentication
    WebRequest req = WebRequest.Create(Convert.ToString(uri));
    if (Convert.ToString(username) != null & Convert.ToString(username) != "")
    {
        req.Credentials = new NetworkCredential(
            Convert.ToString(username),
            Convert.ToString(passwd));
    }
    ((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";

    // Fire off the request and retrieve the response.
    // We'll put the response in the string variable "document".
    WebResponse resp = req.GetResponse();
    Stream dataStream = resp.GetResponseStream();
    StreamReader rdr = new StreamReader(dataStream);
    document = new SqlChars(rdr.ReadToEnd());
    if (document.IsNull)
    {
        return SqlChars.Null;
    }

    // Close up everything...
    rdr.Close();
    dataStream.Close();
    resp.Close();

    // .. and return the output to the caller.
    return (document);
}

尽管它是编码为sqlChars，但此操作仅返回4000个字符。在进行完整库存拉取时，API调用可能会潜在地返回高达14MB的数据量。如果我理解正确，sqlChars转换为varchar（max）（或nvarchar（max））...

我错过了什么？如果有帮助的话，这是在SQL Server 2005上编译的.NET 3.0版本... Andy

- A. Guattery

查看 resp.StatusCode 是否等于 206 而不是 200。 - Jimi

尝试使用new SqlChars(rdr.ReadToEnd().ToCharArray())。如果可行，我会为此做一个答案。 - hatchet - done with SOverflow

顺便提一下，你可能不想从CLR过程内部使用WebRequest。 (参考链接：https://dev59.com/tUfRa4cB1Zd3GeqP7Tkx#R94XoYgBc1ULPQZFQ_FJ) - GSerg

可能是如何创建具有Nvarchar（max）参数的CLR存储过程？的重复问题。 - GSerg

状态为200 OK。ToCharArray返回了相同的4000个字符。 - A. Guattery

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Solomon Rutzky · Accepted Answer

SqlChars和SqlString并不一定与NVARCHAR(MAX)或NVARCHAR(4000)绑定。在.NET中，string是NVARCHAR(MAX)，因此SqlChars和SqlString都可以映射到NVARCHAR(MAX)和NVARCHAR(4000)。唯一确定它是MAX还是4000（或技术上的1到4000）的是在CREATE [PROCEDURE | FUNCTION | AGGREGATE]语句中声明输入参数、返回类型或结果集列的数据类型。所以，你首先需要检查那里。它被明确定义为NVARCHAR(4000)。如果你简单地将其更改（即使用ALTER FUNCTION）为NVARCHAR(MAX)，它将返回所有内容。

其他注意事项：

The SQLCLR API only allows for NVARCHAR, not VARCHAR.
If you are using Visual Studio / SSDT, you can decorate your method to provide a hint for the SSDT DDL generation process that will tell it to use NVARCHAR(MAX). You do that by adding the following above the [SqlFunction()] decorator:
```
[return: SqlFacet(MaxSize = -1)]
```
Had you wanted the return type to be NVARCHAR(250), you would use MaxSize = 250.
You need to be very careful when using external resources in this manner within SQLCLR. The AppDomain can stay loaded for a long time and is shared by all Sessions. So if you have an exception and there is no using() construct and no try ... finally in which you call Dispose() on the opened resource(s), then you can be orphaning those handles for a long time. That can result in locking of files (if it's a file system operation) or can use up networking sockets. You need to be very thorough in your error handling and resource cleanup here! VERY! I have some other answers here with additional notes, such as: SQL CLR Web Service Call: Limiting Overhead.

This means that, in its current form, the code shown in the question is very risky due to not using either the using() construct or try...catch...finally. AND IN FACT, you are already doing a VERY BAD thing by returning return SqlChars.Null; before the 3 .Close() statements. If document.IsNull ever returns true, then this code will orphan that network connection and those external resources!!!!!
If this UDF will only ever be called by one session / process at a time, and never multiple, then you are ok. Otherwise, you are going to run into the default number of external connections being 2 problem that causes additional connections to wait until one of the two allowed connections closes. In that case, you will need to set the default connection count in the ServicePointManager class.
There is no reason to call Convert.ToString({input_parameter}) as you are doing. All Sql* types have a Value property that returns the passed in value in the expected .NET type. So you would instead simply use uri.Value and username.Value, etc.
There is no reason to have this method attribute property specified:
```
DataAccess = DataAccessKind.Read
```
You only specify Read if you are making a connection to the DB and accessing data. There is no SqlConnection in this code so you don't need this property which is a performance hit. Along those same lines, you also do not need using System.Data.SqlClient;.
For more info on working with SQLCLR in general, please see the list of resources I have posted on SQLCLR Info
For anyone interested in a fully functional, pre-done implementation of a WebRequest SQLCLR UDF, such a function already exists in the SQL# library (that I wrote). The function is named INET_GetWebPages and supports passing in all (or at least most) HTTP headers including custom headers, sending POST data, getting back binary data as VARBINARY, overriding the default 2 connection per URI maximum, etc. Please note that while most functions are available in the Free version, this particular function is only available in the Full (i.e. paid) version.