why get "Cannot read from a closed TextReader"

Question

i have a SQL CLR function that return the Json from a Web Api, here es my code

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Funcion()
{
    SqlString document;
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create("URL");
    request.Method = "GET";
    request.ContentLength = 0;
    request.Credentials = CredentialCache.DefaultCredentials;
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
    Stream receiveStream = response.GetResponseStream();
    StreamReader readStream = new StreamReader(receiveStream);
    document = (SqlString)readStream.ReadToEnd();
    return document;
}

i delete the next code because i think that it was the problem

     //response.Close();
    //readStream.Close();

but i still gettin the error

System.ObjectDisposedException: Cannot read from a closed TextReader System.ObjectDisposedException: 
at System.IO.__Error.ReaderClosed()
at System.IO.StreamReader.ReadToEnd()
at UserDefinedFunctions.Funcion()

i inspect the dll withILSpy and my code looks different someone can helpme please. i tried this code in an application cosole to write the result in a file and works perfectly

Answer 1

It is hard to say exactly as you have separated the current code from the commented-out / removed code. But the general concept of using ReadToEnd into a string to return the string is the correct approach.

One thing you absolutely need to do if you don't want this code to trash your SQL Server is to properly dispose of all disposable objects. You should be using using() constructs as they will include the proper try...finally structure to ensure that all resources are properly disposed no matter where in the nested set of them an exception occurs.

Now, the code shown in the image taken from ILspy shows the immediate problem in that it is returning readStream.ReadToEnd(). Once you put your code into the proper using constructs, and do the document = (SqlString)readStream.ReadToEnd() in the middle, then this will work as expected.

string document;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("URL");
request.Method = "GET";
request.ContentLength = 0;
request.UseDefaultCredentials = true; // don't use CredentialCache.DefaultCredentials;

using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
  using (Stream receiveStream = response.GetResponseStream())
  {
    using (StreamReader readStream = new StreamReader(receiveStream))
    {
      document = readStream.ReadToEnd();
    }
  }
}

return new SqlString(document);

---

UPDATE

• With the code above, O.P. still gets Cannot read from a closed TextReader error.
• O.P. mentioned that the web service returns a response of approximately 8 MB.
• With the code above, I downloaded an 11.6 MB file without a problem.
• With the code above, O.P. was able to successfully retrieve a response from https://jsonplaceholder.typicode.com/ without a problem.

• The problem is not the mapping of the return datatype since mapping it to NVARCHAR(4000) instead of NVARCHAR(MAX) would result in the following error:

  Msg 6522, Level 16, State 1, Line 2
  A .NET Framework error occurred during execution of user-defined routine or aggregate "xxxxxx":
  System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 24233896 bytes to a T-SQL type with a smaller size limit of 8000 bytes.
  System.Data.SqlServer.TruncationException:
        at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)