|
| 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!
|
Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
FREE! Go There Now!
| | | |
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
| | | |
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
FREE! Go There Now!
| | | |
Build secure Web services with transport-level security using IBM Rational Application Developer V7 and IBM WebSphere Application Server V6.1. Follow this three-part series for step-by-step instructions about how to develop Web services and clients, configure HTTP basic authentication, and configure HTTP over SSL (HTTPS). This first part of the series walks you through building a Web service for a simple calculator application. You generate and test two different types of Web services clients: a Java Platform, Enterprise Edition (Java EE) client and a stand-alone Java client. You also handle user-defined exceptions in Web services.
FREE! Go There Now!
| | | |
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
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 Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
| | | |
Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
FREE! Go There Now!
| | | |
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
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!
| |
/ 70 
