Storing and Retrieving Images from SQL Server using Microsoft .NET – CodeProject

Introduction

This article is about storing and retrieving images from database in Microsoft .NET using C#.
Tools Used

* SQL Server 2000
* Microsoft .NET Version 1.1
* C# (Windows Forms based application)

Saving and Retrieving Images From SQL Server Using DAAB

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

Saving and Displaying Photos in SQL Server using ASP.NET and FileUpload Control

Saving Photo in SQL Server Using SqlClient

Rather than using the Enterprise Library Data Access Application Block, let’s use the SQL Server .NET Managed Provider to store photos uploaded via a FileUpload Control in ASP.NET.

The code is very simple as shown below:

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
using (BinaryReader reader = new BinaryReader
(FileUpload1.PostedFile.InputStream))
{
byte[] image = reader.ReadBytes
(FileUpload1.PostedFile.ContentLength);
SaveImage(image);
}
}
}

private int SaveImage(byte[] image)
{
int rowsAffected;

using (SqlConnection connection = new SqlConnection(“…“))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = “INSERT INTO Photos
(Photo) VALUES (@Photo)";
command.Parameters.AddWithValue(“@Photo", image);

connection.Open();
rowsAffected = command.ExecuteNonQuery();
}
}

return rowsAffected;
}

Once the user clicks a button, the code verifies that the FileUpload Control indeed has a photo. The photo is converted to a byte array for storage in SQL Server. In SQL Server 2000, the photo will be stored as an Image DataType. In SQL Server 2005, the photo will be stored in the preferred VARBINARY(MAX) DataType. At this point, the database connection is opened and the code executes an INSERT T-SQL Statement that stores the photo as a byte array ( byte[] ) into SQL Server.

Display Photo In SQL Server Using ASP.NET

Displaying the photo in SQL Server using ASP.NET is just as easy. Just reverse the process as shown in the code below:

protected void Page_Init(object sender, EventArgs e)
{
if (Request.QueryString[“id"] != null)
{
int id;

if (Int32.TryParse(Request.QueryString[“id"], out id))
{
Response.Clear();
Response.ContentType = “image/jpeg";

Image image = RetrieveImage(id);
image.Save(Response.OutputStream, ImageFormat.Jpeg);
}
}
}

private Image RetrieveImage(int photoId)
{
Image image = null;

using (SqlConnection connection = new SqlConnection(“…"))
{
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = “SELECT Photo FROM Photos
WHERE PhotoId = @PhotoId";
command.Parameters.AddWithValue(“@PhotoId", photoId);

connection.Open();
byte[] imageData = (byte[])command.ExecuteScalar();

MemoryStream memStream = new MemoryStream(imageData);
image = Image.FromStream(memStream);
}
}

return image;
}

The photo id is read in from a querystring. If it parses correctly, the response buffer is cleared and reset to display a photo as opposed to HTML. The photo is read from the SQL Server Database as a byte array and it gets converted to a System.Drawing.Image with a little help from the MemoryStream Class. I recommend adding a bit of exception handling, but assuming the photo exists, the code works fine by displaying the appropriate image in the browser window.

Source: David Hayden ( ASP.NET Developer )

Filed: ADO.NET Tutorials