|
| Intro | | In part I of this series, Uploading Images to a Database, we saw how to store images and other binary data in a database. In Part II, Retrieving Images from a Database, we are going to stream binary data out of a database and to a browser client. | | | | Refresher - Table Structure | | Lets quickly refresh ourselves with the table structure we are using for storing images. | CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY]
GO | We have 4 columns in our sql server:
img_pk - our Identity primary key.
img_name - the friendly name we are using for our image
img_data - the binary data field we are storing our image in.
img_contenttype - the Mime type of our image, for example: image/gif. | | | | Getting the data | | Now that we know the structure of our image table, lets begin by getting the data out of the database. | //get the image id from the url
string ImageId = Request.QueryString["img"];
//build our query statement
string sqlText = "SELECT img_data, img_contenttype FROM Image WHERE img_pk = " + ImageId;
SqlConnection connection = new SqlConnection( ConfigurationSettings.AppSettings["DSN"].ToString() );
SqlCommand command = new SqlCommand( sqlText, connection);
//open the database and get a datareader
connection.Open();
SqlDataReader dr = command.ExecuteReader();
| | We start by finding the "ID" of the image we want. The "ID" is really the img_pk field, but we are hiding the real name from our users. Once we have the primary key of the image we want, we write our select statement, sqlText. An example URL would be http://localhost/viewimage.aspx?img=1. Once we have the image ID (or img_pk) we build our Sql Statement and store it in a local variable called sqlText. We define and open a connection to the database using a SqlConnection and use a SqlCommand to execute our sqlText. By executing our SqlCommand, we return a SqlDataReader. We chose a datareader, because of it's speed and low overhead. | | | | The Rest is a Piece of Cake | if ( dr.Read()) //yup we found our image
{
Response.ContentType = dr["img_contenttype"].ToString();
Response.BinaryWrite( (byte[]) dr["img_data"] );
}
connection.Close();
| | We check to see if any records were returned, by if (dr.Read() ). We tell the browser what content type it will be receiving from dr["img_contenttype"].ToString() and we output the binary image data by performing a Response.BinaryWrite, and casting our binary data to a byte array: | | Response.BinaryWrite( (byte[]) dr["img_data"] ); | | | | Lets Test | | So how do we link to our new image source. We put the path of our .aspx page as the "src" attribute of the image. Here is a sample html file that demonstrates this. | <html>
<head>
</head>
<title>View Image from the database</title>
<body bgcolor=#FFFFFF>
Here is the image, viewed from a database:
<BR>
<img src="viewimage.aspx?img=1" border=1>
</body>
</html>
| | | | | Conclusion | Retrieving binary data out of a database isn't really that difficult. We connect to our database, retrieve the content-type, and output the binary data using Response.BinaryWrite().
Cheers!
dave
www.123aspx.com | | | | All The code | | Image Database Sql | CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY]
GO | | | | TestImage.aspx | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
<head>
</head>
<title>View Image from the database</title>
<body bgcolor=#FFFFFF>
Here is the image, viewed from a database:
<BR>
<img src="viewimage.aspx?img=1" border=1>
</body>
</html>
| | | | ViewImage.aspx | <%@ Page language="c#" Src="ViewImage.aspx.cs" Inherits="DBImages.ViewImage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
<head>
</head>
<body MS_POSITIONING="GridLayout">
<form id="ViewImage" method="post" runat="server">
</form>
</body>
</html>
| | | | ViewImage.aspx.cs (CodeBehind file) | using System;
using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace DBImages
{
/// <summary>
/// Summary description for ViewImage.
/// </summary>
public class ViewImage : System.Web.UI.Page
{
public ViewImage() { }
private void Page_Load(object sender, System.EventArgs e)
{
//get the image id from the url
string ImageId = Request.QueryString["img"];
//build our query statement
string sqlText = "SELECT img_data, img_contenttype FROM Image WHERE img_pk = " + ImageId;
SqlConnection connection = new SqlConnection( ConfigurationSettings.AppSettings["DSN"].ToString() );
SqlCommand command = new SqlCommand( sqlText, connection);
//open the database and get a datareader
connection.Open();
SqlDataReader dr = command.ExecuteReader();
if ( dr.Read()) //yup we found our image
{
Response.ContentType = dr["img_contenttype"].ToString();
Response.BinaryWrite( (byte[]) dr["img_data"] );
}
connection.Close();
}
}
}
| | | | |
|
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Articles
More By aspfree  developerWorks - FREE Tools!
|
<a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
FREE! Go There Now!
| | | |
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
| | | |
Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
| | | |
In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
FREE! Go There Now!
| | | |
Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
FREE! Go There Now!
| | | |
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
