FileStream storage from vb.net

I am thinking of using the SQL server filestream feature to store PDFs that our dealers upload to our website.  These are usually 2-8mb in size, which supposedly fits the performance case for filestream (file>256k) vs in-table storage.  I have some code that a friend of mine is using, but it lacks a lot of steps that I see in other examples (getting a transaction context and using the sqlfilestream object).  That said, it does seem to work, so I'm wondering if there is any downside to using it that isn't immediately obvious.  My usage is very simple, either extracting the byte array and displaying the PDF or writing a file that the user could download.  

I was also trying to find out if there was any reason not to put other fields into the table that holds the GUIDs for the files, like to hold file names and other metadata.

Any insight from someone more knowledgable about filestream would be greatly appreciated.

Insertion code, wrapped in my upload control:

Using con As New SqlConnection("constr")
                        con.Open()
                        Using com As New SqlCommand("INSERT INTO filesTest (TestFile,ssID,Id) VALUES (@EXE,4,newid())", con)
                            com.Parameters.AddWithValue("@EXE", filedata)
                            com.ExecuteNonQuery()
                        End Using
                        con.Close()
                    End Using

Where filedata is a byte() that comes from the upload control I use.

The retrieval code:

Using con As New SqlConnection("constr")
            con.Open()
            Using com As New SqlCommand("SELECT TestFile FROM filesTest WHERE ssid=@ID", con)
                com.Parameters.AddWithValue("@ID", 4)
                Using reader As SqlDataReader = com.ExecuteReader()
                    While reader.Read()
                        Dim data As Byte() = DirectCast(reader("TestFile"), Byte())
                        ASPxBinaryImage1.ContentBytes = data
                        'File.WriteAllBytes("c:\data\test.jpg", data)
                    End While
                End Using
            End Using
        End Using

In this case I was just testing with images, putting them into an image control.
netadminfafcoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
can you think of using a SQL Server 2012? In this case you would have access to FileTable which is the evolution of FileStream: http://emoreau.com/Entries/Articles/2012/12/Microsoft-SQL-Server-2012-FileTable.aspx
0
angus_young_acdcCommented:
I would actually recommend you don't store the PDF in SQL.  The performance, and size of the table, would be better improved by allowing your users to upload the file (which you would then store on a server somewhere) and in your database replace the column with an internal path to the file.  

If you think of it this way your query takes X amount of time to execute when doing a SELECT for that file, this would be reduced if the amount of data was cut down.  

You can then let the user download the file from you (and view in Browser / standalone).
0
Éric MoreauSenior .Net ConsultantCommented:
angus_young_acdc, filestream/filetable is actually not storing the file in the database but on a drive defined by the database and using SQL syntax. so all the benefits of backup/restore/transactions/full text search/... without the size
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

netadminfafcoAuthor Commented:
Thanks for the advice, but I was really looking for an analysis of the method I'm planning to use, not so much alternatives to the approach generally.
0
Éric MoreauSenior .Net ConsultantCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
netadminfafcoAuthor Commented:
Thanks emoreau, that is a very well constructed post.  It is exactly what I was referring to when I mentioned that I had seen examples with a lot more overhead.  What I'm trying to figure out is what, if anything, I lose by not including all those steps.  Perhaps some of this is dealt with automatically in 2012, as opposed to 2008?  Because one thing I can tell you is that the code above works using an sql user for access instead of integrated security, which most examples insist is required.  So perhaps 2012 has streamlined the operations somewhat.
0
Éric MoreauSenior .Net ConsultantCommented:
it is easier with 2012.

if you use something else then integrated security, you are running after trouble because SQL needs access to folders to store files.
0
netadminfafcoAuthor Commented:
Taking another tack since it appears that filestream is not mature enough for people to be familiar with it.  MSDN had no answer for this either.
0
netadminfafcoAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for netadminfafco's comment #a39580804

for the following reason:

there should be some way to close a question that has no solution.
0
Anthony PerkinsCommented:
The author has chosen to close the question by accepting their own non-solution, this despite getting good feedback from emoreau here: http:#a39543424.  

So I have re-opened the thread to allow them to either award points to emoreau or request the question be deleted.
0
netadminfafcoAuthor Commented:
To me, "good feedback" would be an answer to the question.  As nice as his response may have been, it did not answer the question and I pointed that out.  Go ahead and delete it, or give him points or whatever.
0
Anthony PerkinsCommented:
The information in this thread is useful and should be kept.  Please award points to http:#a39543424.
0
netadminfafcoAuthor Commented:
I'm not going to accept an answer as a "solution", that was not actually a solution.  The fact is that if he had really considered the actual question that I was asking he would have seen that he was not answering it.  Instead he posted an >offsite< link to something that was only tangentially related.  If there were a choice for "wow that was really interesting but it was not an answer" I would happily choose it.  As it is this whole thing has went from potentially useful to pointlessly annoying.
0
Anthony PerkinsCommented:
I'm not going to accept an answer as a "solution"
I would not worry too much about that, the EE Moderator will decide what is best for this site and for future readers.  

Clearly your non-solution you proposed (your own) did not qualify, so that only left deleting the thread or accepting a comment that has valuable information so that it can be saved in the in the PAQ.  My recommendation is for the latter.  But you are more than welcome to differ with my opinion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.