Saving and Retrieving Images From SQL Server Using DAAB and ADO.NET 2.0
by David Hayden ( .NET Developer )
An interesting question in my Inbox about how to save and retrieve images using the Enterprise Library 2.0 Data Access Application Block ( DAAB ) and SQL Server.
I am not sure using DAAB changes the code all that much, since most of the code is really converting images to byte arrays and vice versa.
I, honestly, don't store images in SQL Server, because if you serve up those images a lot I think you may experience a performance hit in your applications. My gut says that streaming large binary images across a tier ( database to webserver for example ) quite often will make your application less scalable and just plain slow. However, if those images are small and/or you incorporate the right amount of caching, you certainly can get away with it. And, of course, if the application is small, you can do just about anything :)
I did some searching and I couldn't find a good example on saving images to SQL Server, so here is my take on a good way to do it. This example is using SQL Server 2000 and the Image Data Type. In SQL Server 2005 you would use the VARBINARY(MAX) Data Type as the Image Data Type is no longer preferred in SQL Server 2005.
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
Conclusion
Storing Image in SQL Server using System.Data.SqlClient wouldn't really be that much different. Hopefully this helps.
Source: David Hayden ( .NET Developer )
Filed: Enterprise Library 2.0, ADO.NET 2.0, SQL Server