|
| Intro | | Uploading images to a Sql Server database is extremely easy using ASP.NET and C#. A couple of months ago I wrote a similar article, using VB.NET. This article will show you how to upload Images (or any Binary Data ) to a Sql Server database using ASP.NET and C#. Part II, Retrieving Images from a Database ( C# ) , will show you how extract images from a database. | | | | Building the Database Table | | We start out by building our database table. Our image table is going to have a few columns describing the image data, plus the image itself. Here is the sql required to build our table in SQL Server or MSDE. | 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 | | I'm a great fan of having a single column primary key, and making that key an Identity column, in our example, that column is img_pk. The next column is img_name, which is used to store a friendly name of our image, for example "Mom and Apple Pie". img_data is actually our image data column, and is where we will be storing our binary image data. img_contenttype will be used to record the content-type of the image, for example "image/gif" or "image/jpeg" so we will know what content-type we need to output back to the client, in our case the browser. | | | | Building our Webform | | Now that we have a warm, fuzzy place to store our images, lets build a webform to upload our images into the database. | Enter A Friendly Name<input type=text id=txtImgName runat="server" >
<asp:RequiredFieldValidator id=RequiredFieldValidator1 runat="server" ErrorMessage="Required" ControlToValidate="txtImgName"></asp:RequiredFieldValidator>
<br>Select File To Upload:
<input id="UploadFile" type=file runat=server>
<asp:button id=UploadBtn Text="Upload Me!" OnClick="UploadBtn_Click" runat="server"></asp:button>
</form> | | The first interesting point about our webform, is the attribute "enctype". Enctype tells the browser and server that we will be uploading some type of binary data. This binary data needs to be parsed, using a different mechanism from our normal text data. The next control we of interest is the type=file control. This control will present the user with an upload file dialog box. The user browses for the file they want to upload. | | | | Working with the Uploaded Image | | Once the user posts the data, we have to be able to parse the binary data and send it to the database. Along with the main body of the code, we use a helper function called SaveToDB() to achieve this. | private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
{
//use the web.config to store the connection string
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );
SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0 );
SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1 );
SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
param2.Value = imgcontenttype;
command.Parameters.Add( param2 );
connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;
}
| In this function we are passing in 3 different parameters
imgName - the friendly name we want to give out image data
imgbin -- the binary or Byte array of our data
imgcontenttype - the content type of our image. For example: image/gif or image/jpeg
There are 3 parameters as SQLParameters and defines the type. Our first SQLParameter is @img_name and is defined as a VarChar with a length of 50. The 2nd parameter, @img_data, is the binary or Byte() of data and is defined with a data type of Image. The last parameter is @img_contenttype, is defined as a VarChar with a length of 50 characters. The remainder of the function opens a connection to the database and executes the command by calling command.ExecuteNonQuery(). | | | | Calling our Functions | | Ok, now that we have our worker functions written, let's go ahead and get our image data. | Stream imgStream = UploadFile.PostedFile.InputStream;
int imgLen = UploadFile.PostedFile.ContentLength;
string imgContentType = UploadFile.PostedFile.ContentType;
string imgName = txtImgName.Value;
byte[] imgBinaryData = new byte[imgLen]; | We need to access three important pieces of data for our example. We need the image:
Name (imgName_value)
Content-Type (imgContentType)
and the Image Data. (imgBindaryData)
First we access to the image stream, which we are able to get by using the property UploadFile.PostedFile.InputStream. (Remember, UploadFile was the name of our upload control on the webform). We also need to know how long the Byte array we are going to create needs to be. We can get this number by calling UploadFile.PostedFile.ContentLength, and storing it's value in imgLen. Once we have the length of the image, we create a byte array by byte[] imgBinaryData = new byte[imgLen]; We access the content type of the image by accessing the ContentType property of UploadFile.PostedFile. Lastly we need the friendly name we are going to use for the image. | | | | The Good Stuff | | Ok, we know how to connect to the database, we know how to insert data into the database, and we have access to the uploaded image's properties. But how do we pass the stream of the image to SaveToDB(). Again, .NET comes to the rescue. With 1 line of code we are able to access the image stream and convert it to a Byte array. | | int n = imgStream.Read(imgBinaryData,0,imgLen); | The stream object provides a method called Read(). Read() takes 3 parameters:
buffer - An array of bytes. A maximum of count bytes are read from the current stream and stored in buffer.
offset -The byte offset in buffer at which to begin storing the data read from the current stream.
count - The maximum number of bytes to be read from the current stream.
So we pass in our Byte array, imgBinaryData; the place to start at, 0; and the amount of bytes we want to read. n number of bytes read into our array is returned. | | | | Extending Beyond Images | | Because we are able to access the binary stream of data, images are not the only object we can store in the database. Some other objects might be streaming video, com objects, or sound clips. As an example I also uploaded a streaming avi into my database. I ran a select query to show the results. | | | | | Conclusion | | So there we have it, ASP.NET provides us some easy functionality for uploading images into a database. In Part II, we will actually look at pulling these images out of a database and sending them to a browser. The complete code used for this article can be found below. | | | Cheers!
Dave
www.123aspx.com | | | | Image 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 | | | | UploadImage.aspx | <%@ Page language="c#" Src="UploadImage.aspx.cs" Inherits="DBImages.UploadImage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
</HEAD>
<body bgcolor=#ffffff>
<form enctype="multipart/form-data" runat=server id=form1 name=form1>
<h3>The ASPFree Friendly Image Uploader</h3>
Enter A Friendly Name<input type=text id=txtImgName runat="server" >
<asp:RequiredFieldValidator id=RequiredFieldValidator1 runat="server" ErrorMessage="Required" ControlToValidate="txtImgName"></asp:RequiredFieldValidator>
<br>Select File To Upload:
<input id="UploadFile" type=file runat=server>
<asp:button id=UploadBtn Text="Upload Me!" OnClick="UploadBtn_Click" runat="server"></asp:button>
</form>
</body>
</HTML> | | | | UploadImage.aspx.cs ( codebehind file) | using System;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.IO;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace DBImages
{
public class UploadImage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button UploadBtn;
protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
protected System.Web.UI.HtmlControls.HtmlInputText txtImgName;
protected System.Web.UI.HtmlControls.HtmlInputFile UploadFile;
public UploadImage() { }
private void Page_Load(object sender, System.EventArgs e){ }
public void UploadBtn_Click(object sender, System.EventArgs e)
{
if (Page.IsValid) //save the image
{
Stream imgStream = UploadFile.PostedFile.InputStream;
int imgLen = UploadFile.PostedFile.ContentLength;
string imgContentType = UploadFile.PostedFile.ContentType;
string imgName = txtImgName.Value;
byte[] imgBinaryData = new byte[imgLen];
int n = imgStream.Read(imgBinaryData,0,imgLen);
int RowsAffected = SaveToDB( imgName, imgBinaryData,imgContentType);
if ( RowsAffected>0 )
{
Response.Write("<BR>The Image was saved");
}
else
{
Response.Write("<BR>An error occurred uploading the image");
}
}
}
private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
{
//use the web.config to store the connection string
SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );
SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0 );
SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1 );
SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
param2.Value = imgcontenttype;
command.Parameters.Add( param2 );
connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;
}
}
}
| | | | Web.Config | <configuration>
<appSettings>
<add key="DSN" value="server=localhost;uid=sa;pwd=;Database=aspfree"/>
</appSettings>
<system.web>
<customErrors mode="Off" />
</system.web>
</configuration> | | | | | | | | |
|
| 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 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!
| | | |
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies 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!
| | | |
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!
| | | |
Ken Krugler, co-founder of code search company Krugle, and Laura Merling, vice president of Marketing and Business Development for Krugle, join to talk about the ins and outs of code search and what it means as a new feature for developerWorks users.
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!
| | | |
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
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!
| | | |
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
FREE! Go There Now!
| | | |
In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP 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!
| |
