Using SQL Database images in the ASP.NET ReportViewer

One of the criteria for a project was for the user to input data into the database for viewing in a report. The user entered data, and uploaded an image for the report. Now, I've added images to an SQL database before. But years and years ago, way back when there wasn't an 'image' type for a field in SQL. Instead, I believe the type for the field was called 'binary' or something along those lines. The process for inserting an image into an MSSQL image field is a little different than it was before.

I start off with the ASP.NET System.Web.UI.WebControls.FileUpload control for uploading a file through the web page. Back in the ASP days, there wasn't a process for this built into ASP, so we had to 1) buy a DLL for doing this or 2) make a DLL for doing this. Either way, it wasn't integrated, and required either and ActiveX control or a Java applet to get the job done.

The data the user entered was to be available in a report created in ReportViewer. The Reporting Services report has an image object, which has the option for defining it as a database image, and define the field in the database that has the image. Now, the implication is that one would have to define the field and the report would get the data, and display the image. Easy as pie. But the reality is something wholly different. After days on end of searching and searching and reading forum posts and tutorials ad-infinitum, I finally found (although I have to admit, I don't know where now) that it isn't as simple and easy as they let on. You can't just put:

=Fields!ImageField.Value

and have it display an image. No. The report must start with a string for the image, and then the report converts that string to an image. So the question becomes 'how do I get the the data into string format?' Oddly, you have to convert the data to base 64 string, then convert it FROM base 64 string. I don't know why, but I do know this works.

=System.Convert.FromBase64String(System.Convert.ToBase64string(Fields!ImageField.Value))

I don't pretend to know why this is the way it is, but I know that it works, and the image appears. And frankly, since I know how to make it work, I don't really care why I have to do it. Oh, crap, I almost forgot. To use the source Database for a report, you've got to set the MIMEType to either image/jpg, image/png or image/bmp. Again, I don't know why this is, I just know that it is, and that is enough for me.

I spent oodles of time trying to get the image to display, but it just seemed like it would never ever work properly. Finally, I decided to create a quick and dirty (and extremely ugly) desktop application where I would pull the image from the database and assign it to a PictureBox control. What I found was as I would pull the image out of the database, and using a MemoryStream to convert it to an actual image.

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "SELECT * FROM [REPORT_TABLE]";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = _connectionString;
cmd.Connection = conn;
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd.CommandText, conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataRow dr = ds.Tables[0].Rows[0];
Byte[] b = (Byte[])dr["IMAGEFIELD"];
MemoryStream ms = new MemoryStream(b);
this.pictureBox1.Image = Image.FromStream(ms);

This code, while it appears on the surface that it should work, didn't. It would kick out the ambiguous "Parameter is not valid" error message at the "Image.FromStream(ms)" line. After several days of googleing and reading what was said by others regarding this error, I discovered that my code was syntactically correct and there was no real reason why it wasn't working. That is when it hit me. I wonder if I'm putting the data into the database correctly?

For inserting the data, at first I was using a stored procedure, through an SqlDataSource and it appeared to be working. That is to say, it didn't kick out any errors. Then I figured I would just go through the process of creating the SqlDataSource and, for lack of a better description, manually insert the data. Again, this seemed to work fine. No errors. But still the image was not visible in the report, and in my desktop application, it would error when trying to create the image "FromStream(Stream stream)". This got me to thinking, so I took a step back and instead of using the SqlDataSource, I decided to try using System.Data.SqlClient to insert the data and image. And lo' and behold this method worked. I don't know what is funky with SqlDataSource method, but what I know is that it didn't work - but that using SqlClient does work, and that is all that really matters to me.

string uploadFileName = string.Empty;
byte[] imageBytes = null;
if(imageUpload != null&& imageUpload.HasFile)
{
    uploadFileName = imagUpload.FileName;
    imageBytes = imageUpload.FileBytes;
}
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = _connectionString;
cmd.CommandText = "INSERT INTO REPORT_TABLE (IMAGEFIELD,IMAGENAME) VALUES (@image,@filename)";
cmd.Parameters.Add("@image", SqlDbType.Image, imageBytes.Length).Value = imageBytes;
cmd.Parameters.Add("@filename", SqlDbType.VarChar, 50).Value = uploadFileName;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Now, just inserting the imageBytes object into the command text did not work. I had to add it as a Parameter to the SqlCommand object. Why? Hell, I don't know, but I know it works, and that is what counts for me at this point. So I put added 23 parameters to the Command object (one for each column of course) and set the values accordingly

cmd.Parameters.Add("@image", SqlDbType.Image, _imageBytes.Length).Value = _imageBytes;

This put the image into the database properly. I'm guessing the parameter is required because it actually defines exactly what DBType we are dealing with, where an normal insert statement wouldn't. At any rate it WORKS! Now the image goes into the database, and get extracted by the ReportViewer and displays properly in the report.

w00t!



Technorati Tags: ASP.NET, MS SQL, ReportViewer, Database Image, SqlDbType.Image