Streaming data from SQL-Server to StreamObject

gfunk1016 · Apr 28, 2012

Hey There, Can anyone tell me how to get binary data stored in an SQL-Server into a Stream object in ASP.net I have created a system that allows the user to upload any kind of file to the database. The file's binary data is stored in a BLOB field in the SQL-Server. I can output these data to a browser showing the image (if gif/jpg) or opening the acociated program (msword, acrobat). Since I need to do a bit of image editing (on valid images) using the graphics class, I would like to get the filedata out as a physical file, but this gives me troubles. I have tried to use different combinations of streamobjects, but cannot figure out how to get the data from the sqlDataReader object to the Stream object. Please help. Short answer: use the command.ExecuteReader(CommandBehavior.SequentialAc cess); Long answer (docs): An important thing to note about setting the DataReader to use SequentialAccess is the sequence in which you access the fields returned. The default behavior of the DataReader, which loads an entire row as soon as it is available, allows you to access the fields returned in any order until the next row is read. When using SequentialAccess, you must access the different fields returned by the DataReader in order. That is, if your query returns three columns, the third of which is a BLOB, you must return the values of the first, then second fields returned before accessing the BLOB data. This is because the data is now returned in sequence and will not be available once the DataReader has read past it. When accessing the data in the BLOB field, use the GetBytes typed accessor of the DataReader, which fills a byte array with the binary data. You can specify a specific buffer size of data to be returned, and a starting location for the first byte read from the returned data. GetBytes will return a long value, which represents the number of bytes returned. If you pass a null byte array to GetBytes, the long value returned will be the total number of bytes in the BLOB. You can optionally specify an index in the byte array as a start position for the data being read. The following example returns the publisher id and logo from the pubs sample database in Microsoft SQL Server. The publisher id (pub_id) is a character field, and the logo is an image, which is a BLOB. Notice that the publisher id is accessed for the current row of data before the logo, as the fields must be accessed sequentially. SqlConnection pubsConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;"); SqlCommand logoCMD = new SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn); FileStream fs; // Writes BLOB to a file (*.bmp). BinaryWriter bw; // Streams BLOB to FileStream object. int bufferSize = 100; // Size of BLOB buffer. byte[] outbyte = new byte[bufferSize]; // BLOB byte[] buffer to be filled by GetBytes. long retval; // Bytes returned from GetBytes. long startIndex = 0; // Starting position in BLOB output. string pub_id = ""; // Publisher id use in file name. // Open connection and read data into DataReader. pubsConn.Open(); SqlDataReader myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAc cess); while (myReader.Read()) { // Get publisher id. Must be before logo. pub_id = myReader.GetString(0); // Create file to hold output. fs = new FileStream("logo" + pub_id + ".bmp", FileMode.OpenOrCreate, FileAccess.Write); bw = new BinaryWriter(fs); // Reset starting byte for new BLOB. startIndex = 0; // Read bytes into outbyte[] and retain number of bytes returned. retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize); // Continue reading and writing while there are bytes beyond the size of the buffer. while (retval == bufferSize) { bw.Write(outbyte); bw.Flush(); // Reposition start index to end of last buffer and fill buffer. startIndex+= bufferSize; retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize); } // Write remaining buffer. bw.Write(outbyte); bw.Flush(); // Close output file. bw.Close(); fs.Close(); } // Close reader and connection. myReader.Close(); pubsConn.Close(); As I'm better at coding VB than C# i tried to convert the example you gave to VB. It worked... sorta. I generates the files, but the files won't show correctly?!? The gif files have wierd filesizes, so something is wrong ! Here's the code i use (can you see whats wrong?): Sub Create_File() Dim fs As FileStream Dim bw As BinaryWriter Dim buffersize As Int32 = 100 Dim outbyte As Byte() ReDim outbyte(buffersize) Dim retval As Long Dim pubid As String Dim startindex As Long Dim SQLconn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("adminSQLconn")) Dim logoCMD As New SqlCommand("SELECT ID, FileData FROM FileVersions", SQLconn) ' FileData is an IMAGE datatype in the SQL-Server. SQLconn.Open() Dim dbread As SqlDataReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAc cess) While dbread.Read pubid = dbread.GetInt32(0).ToString fs = New FileStream("c:inetpubwwwrootatdotnetadminlogo" + pubid + ".bmp", FileMode.OpenOrCreate, FileAccess.Write) bw = New BinaryWriter(fs) startindex = 0 retval = dbread.GetBytes(1, startindex, outbyte, 0, buffersize) While retval = buffersize bw.Write(outbyte) bw.Flush() startindex += buffersize retval = dbread.GetBytes(1, startindex, outbyte, 0, buffersize) End While bw.Write(outbyte) bw.Flush() bw.Close() fs.Close() End While dbread.Close() SQLconn.Close() End Sub

Streaming data from SQL-Server to StreamObject

gfunk1016

New Member