Tuesday, April 19, 2005

Reading binary data from SQL Server and then De-Serializing it

For one of our applications we needed to serialize Excel 2003 files as XML documents in the database. And then as and when required by the application we were to de-serialize them. Following is the code for de-serializing xml file. One side note for developers, whenever you want to read blob or text or binary data that is massive in size then always make sure that you use propper command behaviour(System.Data.CommandBehaviour.SequentialAccess)for your ExecuteReader method of SqlCommand. Since by default ExecuteReader method loads all the row and provides you (named/indexed) collection of records. But there is one catch with this and that is: If you have opted for sequential access then you will have to be watchful about the sequence in which you will access your data. i.e. for example if you gave read nth column from reader then you can only proceed to read columns greater (>) n and can never read any column less than or equal to (<= ) n
if(l_rdrSqlReader.GetValue(1) != DBNull.Value)
{
/// READ STREAM
l_buffer = (byte[]) l_rdrSqlReader[1];
}
else
{
l_buffer = null;
}

l_buffer is a byte array to hold contents.
if(l_buffer != null && l_buffer.Length >0)
{
// if file is alread there then delete it
if(System.IO.File.Exists(l_strfileName+'.xls'))
{
System.IO.File.Delete(l_strfileName+'.xls');
}



/// create file stream to write data on to

l_fsExcel = new System.IO.FileStream(l_strfileName+".xls",System.IO.FileMode.OpenOrCreate,
System.IO.FileAccess.Write);



/// create binary writer from the byte array buffer

l_txtWriter = new System.IO.TextWriter(l_fsExcel);

/// write the buffer onto stream

l_txtWriter.Write(l_buffer,0,l_buffer.Length);



/// flush the output and close.

l_binWriter.Flush();

l_binWriter.Close();

}

No comments: