|
| 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!
| | | |
Join this Rational Talks to You teleconference, featuring Paul Boustany and Mark Krasovich, to speak to the experts about becoming a Rational ClearCase power user. Get a chance to ask your questions and learn tips and tricks for using Rational ClearCase in Agile development
FREE! Go There Now!
| | | |
Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools.
FREE! Go There Now!
| | | |
Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial of the latest release of IBM Lotus Sametime Standard V8.0. Lotus Sametime Standard V8.0 is a platform for unified communications and collaboration that combines security features with an extensible, open solution including integrated Voice over IP, geographic location awareness, mobile clients, and a robust Business Partner community offering telephony and video integration.
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
FREE! Go There Now!
| | | |
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data.
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!
| | | |
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 65 
