Like I posted last time, we can use cool feature of SQL Server called “File Stream” which practically allows user to store any kind of file. But as just like any other SQL syntax, it lacks UI to interact and also it really doesn’t have any use just sitting there in SQL Server. So, with that in mind, I thought to make an application which have atleast an understandable UI and some good feature that can leverage both SQL Server and .NET Framework.
So, with some “research” on Google, I finally was able to create an ASP.NET application which can be used to both read and write files in SQL Server using filestream. It basically accepts First Name, Last Name and path to document which is then passed to database. And in another part, same information is retrieved back in data grid from where user can simply click on link to download the file.
To make it Work …
to make my application, I have used IIS (I have not included web.config file as it only contains db login string). Which has access to my database for this test. But you should remember that SQL Server will not allow any other user (even IIS User have login with enough rights) , so DB connection must be using Windows Authentication (no SA !!) and also, you need to use impersonation for ASP application to access NTFS file system (which is used and hence managed by SQL Server for filestream) … it can be done from IIS –>application_Name->Authentication
Future …
there is a kind of bug with application or say flaw of UI ?? When you load the page, I have binded my database with datagrid to display list of files. Now, if you add new file, it won’t show immediately as all results are fetched at the moment page loads so no new result gets displayed … and for that you have to refresh the page !!! I think simple work around will be creating separate pages for both Uploading and Downloading … or use AJAX to asynchronously postback that datagrid to fetch new results … anyways … I have not done it so it looks little bad but I think may be in future I will do it. Its already late and I am feeling tired …
Reference …
following are some of the links that I used for my “research”
Filestream Data in SQL Server 2008 (ADO.NET)
Code can be downloaded from my Samples+Codes Page …
Its Just a Thought … 