Video: SQL Server 2008 - Writing data with FileStream

Presenter:Mike Taulty

SQL Server 2008 has a new extension to the varbinary(max) datatype which allows you to store arbitrary large blobs "in the database" from the perspective of data access and backup but the actual data is stored in the filesystem.In this session we follow on from a previous video and take a look at how we can use FileStream and write data into it using standard .NET FileStream API's.

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video


Minute 0

Hello and welcome to short videos exhibited as make money as my Chelsea and I were from Microsoft in the UK and this a feature of 60 000 a core file stream and its continuation of a previous video about file stream that was core file stream reading this follicle file stream writing essentially file stream here s an extension of binary Max is a data type you can define a column to be of type file stream it means you can store blobs of arbitrary size even beyond the two gigs limit inside of that are faster in column you can access them in a traditional relation always looks like a blob it backs up with the database and so on however the actual data is stored in the file system and the other aspect of it is you can access that data as though it was file based data to constrain data from the sequel server as though you were reading or writing file and the previous video we looked at Reading I would suggest you watch that before you watch this otherwise it probably will make any sense in this video will take

Minute 1

About writing case in the previous video about week we wrote a simple client that would pick one photograph from the database using a file stream to read it and have extended a little bit in the meantime the client is grown somewhat in that now we have this form as a list box at the top and it brings back all the images that we got on the database using faster into each one and we can just select each of us for and we can resize and so on a plywood that it hasn t changed dramatically with also that this insert one which is what went much to write home in this particular video now just have a look at how the code is changed and since the first video to show where things have gone money trying single step this century what happens in here though if it is not have a good code and we will simply celebrate when their impressive five history her things are looking up so we do is we have this function popular image list is a step in their idiocies a few things out and then it goes today

Minute 2

Racing gets all the photo IDs so this is very simple and we have a simple function that makes commands for is making connections and so on and we do select all the goods from the photos table and user reader and we bring that stuff back 10 into the list of goods this is really quite trivial or we then do gays we make a transaction because of a groin together files Finlay the transaction context or make a transaction we go through each of those goods we had lunch and go get image of photo ID giving it ago it were interested in and then we had that image and delay submit the transaction at the end so that step into this function what is this function do well a cause function called get Falstone voter ID giving it be to it and the access road and then it simply contains and takes that fasting and turned into an image so that selectors without closing fast and afterwards so that step into this function

Minute 3

Only used in the first video facts and a much identical we select that hath name of the file stream the transaction context of the file stream for the particular role that were looking for we execute that to get a reader we read that reader to get the first row were on the expecting one and we then go ahead and change the slightly from what we had in the first video is the safe I ll handle their I change these signature of my open file stream in drop was worth it had a slightly wrong and we go ahead in calls and sorry open files opens equal file stream with the path and the access roads in this case it s read we get a handle back we turn it into a file stream we tell the file stream appeared that it owns the handle so with Roddy said you on that handle would close the reader back to the caller with the file stream in this case the caller turns out to an image and closes a closing handle the return of the image put it on the screen

Minute 4

We go round the loop getting more images and press five Central on screen so that s what we re doing in order to get those images of the screen are reading them using open sequel file stream in order to make that work should shut that down from seven now we ve also got this site is functionality know we have insert here and it is really do very much everyone look at it what it does is that you deserve file dialogue just to get firemen from a user and then afterwards every populist image list so I want this to insert a photograph into the database now what am they do in order to make that work ears will do it in a two part step really the first thing that it is to insert a road is using the regular relational stuff into the photos table in the database will just go out and do that and I wanted at that row in the data it will go back to that role and we will ground its file stream and we will override the image file that the user selected

Minute 5

At the top of the file straight to that carry the approach of doing it in two stages but let s begin phase 1 man let s say herself good little function that wrapped transactions for a similar begin a transaction at the end of this would commit my transaction and what would I do is first off let s say Druids new ID sake in search photo indeed be Amanita pass and the name of the photograph at Lisa Wyld is a use path farming without extension on that filename in order to give it a name so and a function that is to run and generator stopped for at Soviet function to say Druids GE equals and good on you at the end of this month s return that do it and in the meantime almost is just use a command lets a single command I got this logical make and we get a text on my to do insert

Minute 6

Photos and values never value I want is my row ideas that s going to be a row ID and I want name now he is a trick and you would think that we wouldn t be inserting a value to the file stream here however if we don t insert any value whatsoever than naturally returns null and we will not be able to get a fast name from it at a later point so my trick here is to insert a single bite of zero into that file stream and that will give us something at me so much forehead and it that that gives a commanding figure syntax right there and onto a Cdr want to add a parameter that adds the rule row ID with the value of Gee which is argue it let s add the value of name which is going to be name and that s piece very bad name of the photo name sites and will have photo name their and their machete command or execute non query

Minute 7

And we should be okay returning arguing that he had naturally of course assuming that everything works which is a highly dubious strategy when writing any colour code and insert works and what we want to do next is we would want to essentially take this file will be captured from the file dialogue appear and overwrite into the file streams of this new identifier the value of that file silicide advancement is that let s say something like copy if Pam filed to database something like that and what Lolita Bass is the new identifier and the full filename so slow and right that function is just against a generated for a Soviet function now we ve already got functions here which will give us and a file stream for a particular value so the facility was room read all of this file is listed as the cheapest most cheerful way we can say Alice Gullit image file

Minute 8

Messages filed Regal bites on that fireman is we got that already captured and get the file stream using the function we already wrote to read file streams we can say using the file stream I get file simple photo ID went past the new ID going to pass the fact that we want to be right when we done little than to close it and in the meet up what we want to do is say SS write want past the image file that we ve got out from a zero to the full length and we should be in business in that case at present five and that if we can run a state where breakpoint silages of the air we got to them for a marvellous insert must go to add tulips and we could see tulips it has been inserted into the database as were fans and certain other one let s go for and ripple something like that

Minute 9

And that s in the database now all we can do of course because this is transactional we could have wanted to am that as far back over here and at the point where we acted it that in search such as find it again was lost at my son Michael who is a fine insert so we could do is I having done the insert of the Royal and then the overwrite of the file string we could ask the user whether they want to commit an is always some very dodgy practice to as the user anything in the middle of a transaction but it is only a demonstration selector if message box stock show how would you like to commit and will add on to that thing than that of the buttons yes no answer is that equals at yes and will commit as described in their otherwise naturally we will run

Minute 10

Rollback and will refresh the screen either way and you could argue it only really need to do the average committed but let s do it either way let s build us go run let s insert a new picture which shall it be and the red Moon desert let s roll that back and we can see that we don t get a red Moon desert let s inserted again with the Redmond deserts that commit it we can see that we do get a Redmond desert so you can see how integrating what were doing a relation of it which is the insert here with what were doing in the files drink and a world which is their Aberdeen are sticking together so summery here is very much the same summary as when we looked at Reading with file strain need to switch s lives on at the server level the store procedures are that you need to create a files streamed file group either when you create in the database are altering the database you need to use a function called open sequel files

Minute 11

And the two important things ago have the pasta that is a path to the final string you get that by calling pathname on the day to type in the database and you need to pass it to their transaction context of that what the work is done in the file system can be bundled up with the work was done in the database and is a function or get final string transaction context which gives you that now in this particular session we saw that we could commit rollback changes that we had made both relation only and through the files streaming kind of API so we saw that we could do that by committing or rolling back and the other thing that we did here was one wanted to insert a photograph into a database we had three columns in our table we had the idea we had a name and we have the file stream and we did and insert into sections such that we could use the streaming API to override the file rather than just loading the blog in the first instance so that was ready to demonstrate how we could do things were in that particular case what we did was we did a

Minute 12

A relational insert first and then we went back as the subsequent step got the file stream data and over wrote it with the contents of a file now when we did our initial insert we put a dummy value into the file stream column is such that we could call path name on it at a later point behind the dummy value in there we do got null when the call path name and then we can t use it from the fast API so that was the something else at the came up here what you are writing insomuch it if you want more information on developing sequel server and as a developer Centre in MSDN its MSN Microsoft com sequel is a more general purpose data access centre at data and if you are more information on this video feel free to mail me at my e mail address is just on screen other than that thanks love listening that we found it useful and hope to catch up with you I do virtually or in person at a UK event in the future

Click here to go to original video page