Returning FILESTREAM column using Win32 API

4 posts, 0 answers
  1. John
    John avatar
    31 posts
    Member since:
    May 2011

    Posted 18 Feb 2014 Link to this post

    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.
  2. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 21 Feb 2014 Link to this post

    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
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
  4. John
    John avatar
    31 posts
    Member since:
    May 2011

    Posted 23 Feb 2014 in reply to Boris Georgiev Link to this post

    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
  5. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 27 Feb 2014 Link to this post

    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
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top