|
| 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!
|
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
| | | |
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
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 the IBM WebSphere Portal V6.1 beta code and learn more about the rich features and enhancements in IBM WebSphere Portal V6.1. WebSphere Portal provides a composite application or business mashup framework and the advanced tooling needed to build flexible, SOA-based solutions, and scalability to meet the needs of any size organization.
FREE! Go There Now!
| | | |
As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br />
FREE! Go There Now!
| | | |
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
| | | |
Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
FREE! Go There Now!
| | | |
The Eclipse community is constantly working to extend Eclipse's functionality. In this webcast, learn about some of the most important and feature-rich projects under development. From multi-language support to plug-in development, tune in to see what Eclipse is capable of now.
FREE! Go There Now!
| | | |
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
| | | |
The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 65 
