Tag Archives: C#

How to save / receive jpg image or any binary data in C# Web Api 2

Searched all through the web for a simple way to save / receive binary data or image file using Web Api 2. Solutions range from very complex to complex:

  • using JSON (30% overhead?)
  • BSON (looks nice, but not widely supported – bsonspec.org)
  • Multipart MIME (why multi-part, if we have only one file?)
  • Complex Web Api / MVC controllers encapsulated by enterprise classess, etc.

They all are missing the simplest form — POST binary data, receive binary data… all in one line of code

HttpContext.Current.Request.SaveAs(filename, false);

Yes! That’s it! One line and data from Binary HTTP POST response is saved.

In case you are looking for the simple way to send binary data using the same Web Api, here is a code we are using.

HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
byte[] binaryData = File.ReadAllBytes(@"C:\full\path\to\image\photo.jpg");

if (binaryData == null) throw new FileNotFoundException("something bad happened!");
response.Content = new ByteArrayContent(binaryData);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("image/jpeg");
return response;

//if your are using IHttpActionResult, use return ResponseMessage(response);

And as always, your comments are welcome! Registration is not required.

Solution for: A transport-level error has occurred when receiving results from the server…

We have a program written in C# using Visual Studio 2012 and .NET 4.0 / .NET 4.5. It rapidly executes many queries in Microsoft SQL Server 2008 R2 and then exits. Executed query count varies from several hundred to many thousand.

Today, when executed particular task with very many queries, I noticed an error in the log file:
A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at MyTestApp.Program.QueryOneLineOneValue(String cmdText)
at MyTestApp.Program.Worker(String[] args)
at MyTestApp.Program.Main(String[] args)

At first, the error message lead me to think that something is wrong with the network. “Physical connection is not usable”… anyway it didn’t say anything useful… and SQL Server log file didn’t have anything useful either.

A quick Google search revealed that many people have the same problem:

  1. Minimizing Connection Pool errors in SQL Azure — it has code example with an serious bug, and it leads to Reliability Update 1 for the .NET Framework 4 which may help judging from the description – “Issue 14 – A transport-level error has occurred when sending the request to the server.” I didn’t tried it, mostly because it talks about Azure services.
  2. Discussion in the Microsoft SQL Server Database Engine forum which leads to MSDN article about SQL Server Connection Pooling which again does not help much, because I have only one connection in my application.
  3. And a couple not very useful pages from the Stack Overflow: One suggests to call ClearAllPools and another one is about Azure again.

Again, I had to find solution by myself. I looked at the code and didn’t see anything unusual. I did another run under the VS debugger, and after a while the same exception was thrown. Looking under debug log file, I noticed that program stopped in the same place that in the production server. And by the same place I mean, the same amount of SQL commands were executed.

I quickly put a counter on right before SQL command execution and found that .NET throws exception always on 32767 command. 32767 is very familiar constant for programmers, and it is obvious that some resource leakage is happening. A quick glance at the code again revealed that a call to Close or Using statement is missing.

SqlConnection conn;
   
private static void QueryOneLineOneValue(String cmdText)
{
      string q = "SELECT ... FROM cmdText...";
      SqlCommand command =
         new SqlCommand(q, conn);
   
      SqlDataReader reader = command.ExecuteReader();
   
      while (reader.Read())
      {
         //do something with the data...
      }
   
      reader.Close(); //this line was missing
}

Executing very large script on Microsoft SQL Server 2008 R2 fails

I was trying to execute very large .sql script in Microsoft SQL Server 2008 R2, that runs on Microsoft Windows Server 2008 R2.

First I tried to copy/paste script into Microsoft SQL Server Management Studio. The server has plenty of memory about 5 GB RAM, and script file is about 150 MB in size and line count is about 1 million, so I do not see problem here. Copy/paste part went ok, however after executing script and after long wait, Management Studio surrendered with “Query completed with errors”:
Microsoft SQL Server Management Studio
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Insufficient memory to continue the execution of the program. (mscorlib)
------------------------------
OK
------------------------------

bl-sql-1

Next I tried various combinations of T-SQL command line tools:
osql -S iDGZdb-dev -i test2.sql -U myuser
sqlcmd -S IDGZDB-DEV -i test2.sql -U myuser
osql -t 100000 -a 65535 ...

Still no success. Various errors appeared in console. Running locally on SQL server:
[SQL Server Native Client 10.0]
Shared Memory Provider: No process is on the other end of the pipe.
[SQL Server Native Client 10.0] Communication link failure

Running on remote machine:
[SQL Server Native Client 10.0]
TCP Provider: An existing connection was forcibly closed by the remote host.
[SQL Server Native Client 10.0]Communication link failure

Tried to search for Google and didn’t find any solution. There were many forums where users ask for help about this issue, but no single answer. Suggested “failed network equipment” is nonsense, if I run script locally, there should be no network involved.

Looked in the System event log under Application logs on the Windows Server where SQL Server resides. Luckily there was an error from the SQL Server:
Log Name: Application
Source: MSSQLSERVER
Date: 2013.02.04. 17:37:41
Event ID: 7836
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: iDGZdb-dev
Description:
A fatal error occurred while reading the input stream from the network. The maximum number of network packets in one request was exceeded. Try using bulk insert, increasing network packet size, or reducing the size of the request. The session will be terminated.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">7836</EventID>
<Level>2</Level>
<Task>2</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2013-02-04T15:37:41.000000000Z" />
<EventRecordID>223808009</EventRecordID>
<Channel>Application</Channel>
<Computer>iDGZdb-dev</Computer>
<Security />
</System>
<EventData>
<Binary>...some hex stuff...</Binary>
</EventData>
</Event>

But unfortunately, the error reveals very little about the problem. Didn’t get this part of suggestion “… or reducing the size of the request”. The request of 150 MB is too big for the latest edition of MS SQL? Seriously?

Note: Ok, ok… Microsoft SQL Server 2012 is out, and I am not an expert in this area, however, if MySQL can handle scripts in size of gigabytes and Oracle too, then I must be doing something very wrong, but again, then where are all suggestions in the net? Even StackOverflow has only guesses about “network equipment”.

Next, I tried to increase the packet size as suggested in System log message. More statements were executed from the SQL script, however, it still stopped with a memory error:
There is insufficient system memory in resource pool 'internal' to run this query.
Network packet size (bytes): 32576

How I solved this? I surrendered, and instead of wasting time with weird T-SQL error messages, I wrote a little C# script, that executes my file line by line. All of my SQL statements were one-liners. It took me about 1/2 hour to write this script and execute it.

Here is relevant code fragment:
SqlConnection connection = new SqlConnection(Properties.Settings.Default.YourConnectionString);
connection.Open();
System.IO.StreamReader file = new System.IO.StreamReader(yourFilename);
while ((line = file.ReadLine()) != null)
{
    try
    {
        SqlCommand command = new SqlCommand(line, connection);
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        LogErrorSomewhere(ex.Message, line);
    }
}

file.Close();

P.S. If you know, how to execute a such script using standard SQL tools, then you are welcome to leave me a comment.