第一次发帖,请多多关照!:-) 我是新手,正在尝试修复调用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
resp.StatusCode
是否等于 206 而不是 200。 - Jiminew SqlChars(rdr.ReadToEnd().ToCharArray())
。如果可行,我会为此做一个答案。 - hatchet - done with SOverflowWebRequest
。 (参考链接:https://dev59.com/tUfRa4cB1Zd3GeqP7Tkx#R94XoYgBc1ULPQZFQ_FJ) - GSerg