Well, after receiving lots of feedback about the code used in the MSDN sessions in November, I seem to have forgotten something. In the title generation sample in which we retrieve one of several images from the database (the one currently selected as the site title template) and use a handler to write the string with the desired title over it, most users seem to be faced with the following problem: how do we get images in a database in the first place.
Well, it's not really difficult: you can create a stream that points to the file, read the bytes and store them in an Image column in the db (assuming SQL Server or MSDE for this). Here is an example on how to do this in an ASP.NET application, more specifically, in a File Upload page. This makes sense because one of the other samples was about a digital photo sharing web application, so you might want to integrate both things.
The first thing you need to do is create a new webform in your asp.net application, and add the following piece of code to your HTML form declaration:
enctype="multipart/form-data"
The next thing you need to do is add an HtmlInputFile. To do this, open the toolbox, choose the Html Tag and add the File Field control. Then, right-click the control and check "Run as a server control" (this basically just adds the runat="server" in the HTML declaration and declares the control in the code-behind file).
You're almost there! What we just did was preparing the web form to receive uploaded files.
Now add a Button control (ASP.NET button control) and doble click it to write the event handler for the click event.
Add this code to your web form:
private void Button1_Click(object sender, System.EventArgs e)
{
SqlConnection cn = new SqlConnection("server=<server_name>;database=<database_name>;user ID=<user_id>;password=<password>");
SqlCommand cmd = new SqlCommand("INSERT INTO Pictures (name,type,picture_data) VALUES (@name,@type,@data)", cn);
cmd.Parameters.Add("@name", SqlDbType.VarChar,50);
cmd.Parameters.Add("@type", SqlDbType.VarChar,50);
cmd.Parameters.Add("@data", SqlDbType.Image);
byte[] data = new byte[filMyFile.PostedFile.InputStream.Length];
Request.Files[0].InputStream.Read(data, 0, (int) Request.Files[0].InputStream.Length);
cmd.Parameters["@data"].Value = data;
string aux = filMyFile.PostedFile.FileName;
string name = aux.Substring(aux.LastIndexOf("\\")+1);
cmd.Parameters["@name"].Value = name;
cmd.Parameters["@type"].Value = filMyFile.PostedFile.ContentType;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Ok, this code doesn't perform any error handling so I leave that your exercise. Be sure to replace the correct values in the connection string.
The InputStream property that we use here is in fact a System.IO.Stream. In this case the stream is created for us through the HtmlInputFile control. If you want to do this in a console or windows forms application, you can create the Stream yourself, point it to a file and use the rest of the code in a similar fashion.
So, in the database we will store the name of the file, the type (jpeg, gif, etc) and the data itself. This actually can be used to store any type of file in the database. We keep the content type stored with the file because when we use a page or handler to retrieve one of these files, we need to set the correct response content type before writing the data to the response stream, so that the browser can understand what is sent to him.
That is all. If you have any questions about the code feel free to comment the post or send me an email.
Cheers
Edit: Slight correction: if you intend to put other content besides image in the database, the column type used should not be "image" but "binary". Of course you would have to change the parameter in the code above.
