Saving and Retrieving Images From SQL Server Using DAAB and ADO.NET 2.0
Saving Image to SQL Server
Retrieving an image from the hard disk and inserting it into SQL Server using DAAB:
byte[] image; string fileName = @"c:\davidhayden.jpg"; // Open File and Read Into Byte Array using (FileStream fs = new FileStream(fileName, FileMode.Open)) { BinaryReader reader = new BinaryReader(fs); image = reader.ReadBytes((int)fs.Length); fs.Close(); } // Get Database Database db = DatabaseFactory.CreateDatabase(); // Create DbCommand string insertSql = "INSERT INTO Photos (Photo) VALUES (@Photo)"; DbCommand command = db.GetSqlStringCommand(insertSql); db.AddInParameter(command,"@Photo",DbType.Binary,image); // Store Image int rowsAffected = db.ExecuteNonQuery(command);
Retrieving Image from SQL Server
Retrieving the image from SQL Server:
// Get Database Database db = DatabaseFactory.CreateDatabase(); // Create Example DbCommand string selectSql = "SELECT Photo FROM Photos WHERE PhotoID = 1";
DbCommand selectCommand = db.GetSqlStringCommand(selectSql); // Execute Command byte[] storedImage = (byte[])db. ExecuteScalar(selectCommand); // Convert byte[] to Image Image newImage; using (MemoryStream stream = new MemoryStream(storedImage)) { newImage = Image.FromStream(stream); } // Display to make sure code works pictureBox1.Image = newImage;
FileUpload Control and VB Example
If you are loading the picture from a FileUpload Control in a web page, the code will be slightly different. Here is the same example of saving the image to SQL Server using a FileUpload Control and VB:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click If FileUpload1.HasFile Then Dim reader As BinaryReader = New BinaryReader(FileUpload1. PostedFile.InputStream) Dim image() As Byte = reader.ReadBytes(FileUpload1. PostedFile.ContentLength) Dim db As Database = DatabaseFactory.CreateDatabase() Dim insertSql As String = "INSERT INTO Photos (Photo) VALUES (@Photo)" Dim command As DbCommand = db.GetSqlStringCommand(insertSql) db.AddInParameter(command, "@Photo", Data.DbType.Binary, image) Dim rowsAffected As Integer = db.ExecuteNonQuery(command) End If End Sub
Using ASHX files to retrieve DB images
ASP.NET has a little-known feature that allows you to easily implement what is known as an HTTP Handler. Basically, when a request for a page comes into ASP.NET, eventually that request is handled by an object that implements the IHttpHandler interface. This interface includes a method called "ProcessRequest" that is responsible for writing all of the page content to the HttpContext.Response.Output stream. ASHX files allow you to easily write the IHttpHandler class without even having to pre-compile it. I used ASHX files in a recent project (coming soon) to retrieve images out of a SQL database. Here's how I did it. You need to create a file in your project with an ASHX extension. I'm retrieving data from the Employees table of the Northwind sample database, so I called my page NWEmpPhoto.ashx. The contents of the ASHX file are as follows:
using System; using System.Web; using System.Data; using System.Data.SqlClient; public class NWEmpPhotoHandler : IHttpHandler { public bool IsReusable { get { return true; } }
public void ProcessRequest(HttpContext ctx) { string id = ctx.Request.QueryString["id"];
SqlConnection con = new SqlConnection(>); SqlCommand cmd = new SqlCommand("SELECT Photo FROM Employees WHERE EmployeeID = @EmpID", con); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@EmpID", id);
con.Open(); byte[] pict = (byte[])cmd.ExecuteScalar(); con.Close();
ctx.Response.ContentType = "image/bmp"; ctx.Response.OutputStream.Write(pict, 78, pict.Length - 78); }
}
As you can see, it's basically source code with the special tag at the top. The class implements two methods - IsReusable and ProcessRequest. IsReusable is for handler pooling, and as far as I can tell can be safely hard coded to return true, at least in this scenario. The ProcessRequest method implements the database access. It does some fairly straight forward ADO.NET to retrieve the image from the DB as a byte array. The primary key to the query is passed in on the query string. I'm using ExecuteScalar since I'm retrieving a single column from a single row. The photo column is returned as a byte array. We set the Response's correct content type (image/bmp in this case) and write the byte array containing the picture to the Response.OutputStream. By the way, the 78 byte offset is Northwind database specific. I'm not sure why the bitmap starts 78 bytes into the blob, but I was clued into the fact by an article by Dino Esposito . To use the NW Photo Handler, you simply use an HTML image tag. Since the primary key is passed in on the query string, you need to include it as the src attribute of the image tag. Here's an example of the image tag: Simply need to write the DB image retrieval logic in ASHX handler's ProcessRequest function and assign ASHX Url to image control as..