Returning FILESTREAM column using Win32 API

[John]

I am new to OpenAccess/Data Access and am starting to use it because we have upgraded our database to store large files in FILESTREAM columns and as we already use Telerik controls I thought it might be good to use, plus Linq to SQL and Entity Framework don't seem to support FILESTREAM access via the Win32 API.

I have looked at the examples but they aren't suitable to my situation.

I am wondering if any example code can be provided to retrieve the FILESTREAM column and return a byte[] using the Win32 API? I presume I can't just get the column value using a typical Linq query like this:

using (CMSModel dc = new CMSModel(DcConfig.ConnString))
{
byte[] data = (from a in dc.CMS_tbFileVersions
where a.FileVersionId == fileId
select a.FileData).FirstOrDefault();
}

Also, how can I tell if the Win32 API is being used to retrieve the data rather than through T-SQL?

Thank you.

[Boris Georgiev]

Hello John,

Could you please describe us what is your scenario, what you want to achieve and why you want to use Win32 API?

Telerik Data Access is using T-SQL to retrieve the data from the database whether it is varbinary(max), image, int and etc. Though Telerik Data Access provides a special support for working with varbinary(max) column(I assume that with FILESTREAM you mean varbinary(max) column) and uses a special type BinaryStream that is used to expose a stream around a database binary column.

Since this type of columns can contain a gigabytes of data, the idea behind BinaryStream type is that you can read or write the data in chunks. The Read method of the BinaryStream reads a sequence of bytes from the current stream and advances the position within the stream by the number of bytes read. Thus the information from the database is loaded to the client machine in portions instead of loading all the information in the cache. Similarly the Write method writes a sequence of bytes to the current stream and advances the current position within the stream by the number of bytes written.

I hope that helps.

Regards,
Boris Georgiev
Telerik

[John]

My scenario is that I store many files in a varbinary(max) column and they are retrieved regularly and I am trying to improve performance.

I am converting these columns to FILESTREAM type.

I understand from reading Microsoft documentation that storing and retrieving files larger than 1MB is a lot quicker when using the Win32 API rather than T-SQL.

I also refer to another forum post where someone said that OpenAccess does use the Win32 API to access FILESTREAM columns:
http://www.telerik.com/forums/filestream-and-win32-api

Here's some Microsoft documentation on FILESTREAM:
http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx
http://msdn.microsoft.com/library/hh461480
http://technet.microsoft.com/en-us/library/cc645940.aspx

[Boris Georgiev]

Hi John,

Please excuse us for the misunderstanding and the difference in both posts.

The BinaryStream type uses as underlying the Stream API obtained from the ADO Driver. From our observations the ADO Driver Stream API uses a T-SQL to get the information from the Database.

I hope that clarifies the matter. In case you have any questions - do not hesitate to get back to us.

Regards,
Boris Georgiev
Telerik