Img SQL
Img SQL
Img SQL
Database
By vivekthangaswamy | 25 Feb 2005
.NET1.1SQL2000VS.NET2003C#ASP.NETSQLWindowsDBADevIntermediate
How to upload files to Web pages in ASP.NET? How to read an image from a database using
ADO.NET and display it in a Web page?
Print Article
Digg
Del.icio.us
Stumbleupon
Newsvine
Technorati
Mr. Wong
Yahoo!
Windows Live
Send as Email
Add to your CodeProject bookmarks
Discuss this article
72
Report this article as inappropriate
1 2 3 4 5
Sponsored Links
Top of Form
/w EPDw UKMTAy
Abstract
.NET is the new distributed computing platform developed by Microsoft and ASP.NET is its
programming model for web development. The intent of this article is to get a good experience
in developing data driven ASP.NET Web Forms applications by means of a real world
application. This application will teach you how to save an image file into a database and how to
retrieve it from the database. It uses ADO.NET as the data access mechanism, C# as the
development language, and SQL Server 2000 as the backend database.
Overview of the Solution
Normally, images are saved in the web server folder, not in the database; this is for larger file
size category. In some cases like, in bank, for example, they scan the user signatures as image
files and save that file into the database.
• Database schema
MS SQL Server 2000 is used as the backend database for this small demonstration. And I
used a special data type in SQL Server called image. The image data type is used to save
the image into the database.
• Controls used in this application
○ System.Web.UI.HtmlControls.HtmlInputFile
○ System.Web.UI.WebControls.TextBox
○ System.Web.UI.WebControls.Button
• Namespaces used in this application:
Collapse
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.IO;
using System.Drawing.Imaging;
Collapse
// Source Code for Save the image file into the database
Save and Retrieve Images from the Database using ASP.NET 2.0 and ASP.NET 3.5
1retweet
At some point or the other, we as ASP.NET developers, have faced the requirement of
reading and writing images to the database. We have seen loads of articles floating on the
internet which discusses about storing and retrieving images from the database. Some of
them are good. However, I have personally found that the solutions offered are those,
where images are displayed in a ‘standalone fashion’; that is on a separate page containing
only the image. What if we have to show an online form, with the person’s details and his
photo along with it, or for that case, display the image in an ASP.NET server control along
with other controls? In this article, we will explore how to store images in the database and
then display those images along with the other server controls.
To keep the article simple and easy to understand, we will place only a few controls on the
page. I have also not covered any validations associated with image control. In this article,
we will only discuss how to read and write images into the database, and that would be the
focus for this article. If you are interested in discussing validation and other stuff, I would
suggest you to browse through the ASP.NET section of this website to view an article that
discusses that.
So let us get started. We will first see how to upload an image and then display the
uploaded image on the same page. You can extend this sample to create a photo album as
well!! I assume you have some knowledge of creating ASP.NET 2.0 websites.
Let us start off by first creating a sample database and adding a table to it. We will call the
database ‘Employee’ and the table will be called ‘EmpDetails’. This table will contain an
image column along with some other columns. Run the following script in your SQL 2005
Query window (or server explorer) to construct the database and the table.
Database Script
Step 1: Create a new asp.net website. In the code-behind, add the following namespace
C#
using System.Data.SqlClient;
VB.NET
Imports System.Data.SqlClient
Step 2: Drag and drop two label and one textbox control. Also drag drop a FileUpload
control and a button control to upload the selected image on button click. As mentioned
earlier, there are no validations performed. The source would look similar to the following:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Save Retrieve Images</title>
</head>
<body>
<form id="form1" runat="server">
<div>
  <asp:Label
ID="lblResult" runat="server" ForeColor="#0066FF"></asp:Label>
<br />
<hr />
</div>
</form>
</body>
</html>
C#
connection.Open();
string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg)
SELECT @@IDENTITY";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());
cmd.Parameters.AddWithValue("@eimg", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblResult.Text = String.Format("Employee ID is {0}", id);
}
catch
{
lblResult.Text = "There was an error";
}
finally
{
connection.Close();
}
VB.NET
connection.Open()
Dim sql As String = "INSERT INTO EmpDetails(empname,empimg)
VALUES(@enm, @eimg) SELECT @@IDENTITY"
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim())
cmd.Parameters.AddWithValue("@eimg", imgByte)
Dim id As Integer = Convert.ToInt32(cmd.ExecuteScalar())
lblResult.Text = String.Format("Employee ID is {0}", id)
Catch
lblResult.Text = "There was an error"
Finally
connection.Close()
End Try
End Sub
In the code above, we are creating a byte array equal to the length of the file. The byte
array will store the image. We then load the image data into the array. The record
containing the Employee Name and Image is then inserted into the database using the
ADO.NET code. The ID inserted is returned back using the @@Identity. We will shortly use
this ID and pass it as a query string parameter to the ShowImage handler. The image will
then be fetched against the EmployeeID (empid).
Step 4: In order to display the image on the page, we will create an Http handler. To do so,
right click project > Add New Item > Generic Handler > ShowImage.ashx. The code shown
below, uses the Request.QueryString[“id”] to retrieve the EmployeeID from it. The ID is
then passed to the ‘ShowEmpImage()’ method where the image is fetched from the
database and returned in a MemoryStream object. We then read the stream into a byte
array. Using the OutputStream.Write(), we write the sequence of bytes to the current
stream and you get to see your image.
C#
using System;
using System.Configuration;
using System.Web;
using System.IO;
using System.Data;
using System.Data.SqlClient;
context.Response.ContentType = "image/jpeg";
Stream strm = ShowEmpImage(empno);
byte[] buffer = new byte[4096];
int byteSeq = strm.Read(buffer, 0, 4096);
VB.NET
Imports System
Imports System.Configuration
Imports System.Web
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
context.Response.ContentType = "image/jpeg"
Dim strm As Stream = ShowEmpImage(empno)
Dim buffer As Byte() = New Byte(4095) {}
Dim byteSeq As Integer = strm.Read(buffer, 0, 4096)
End Class
Step 5: One final step. Add the following code in the button click (just above the catch
block) to call the handler and display the newly inserted image from the database. In the
code below, we pass the EmployeeID as a query string parameter to the Http Handler.
C#
VB.NET
That’s it. Run the code and check out the functionality. Just change the connection string in
the web.config to point to your database. The code works fine for .jpg, .gif and .bmp
images. I would encourage you to extend the sample and include validations in it. Some
validations could be to check the size of the image uploaded, make sure that only images
are uploaded, check the length of the Employee name, prevent the user from entering
numeric and special characters, so on and so forth.
Share
Add to
Technorati
Kick It
More...
More...
More...
How to calll stored procedure in ADO.NET
How to Generate Fibonacci Series with an Algorithm
How to create JQuery Tooltip Plugins
How to find the "Factorial of an Number" an Algorithm
How to find the Quadrant an Algorithm
More...
Storing and retrieving Images from Access
database using VB.Net
• Download source files (38.52 kb)
• Download demo project (52.11 Kb)
Introduction
This is a simple code snippet which is used to store and retrieve images from Access database
using VB.net.
Code
view source
print?
2 Try
Dim cn As New
3
OleDb.OleDbConnection
7 cn.ConnectionString = mstrConnection
8 cn.Open()
10 cmd = cn.CreateCommand()
12
13 dr = cmd.ExecuteReader
14
15 If dr.Read Then
17
18 Try
Dim ms As New
20
System.IO.MemoryStream(bytImage)
23
24 pbI_Image.Image = bmImage
25 pbI_Image.Refresh()
26 Catch ex As Exception
27 MsgBox(ex.ToString)
28 End Try
29 End If
30
31 dr.Close()
32 cn.Close()
33
34 cmd.Dispose()
35 cn.Dispose()
36 Catch ex As Exception
37 MsgBox(ex.ToString)
38 End Try
39 End Sub
40
42 Try
Dim cn As New
43
OleDb.OleDbConnection
45
46 cn.ConnectionString = mstrConnection
47 cn.Open()
48
49 cmd = cn.CreateCommand()
50
55 End If
56
58
59 Try
Dim ms As New
60
System.IO.MemoryStream
62
63 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
64 bytImage = ms.ToArray()
65 ms.Close()
66 Catch ex As Exception
67 MsgBox(ex.ToString)
68 End Try
69
cmd.Parameters.Add(New OleDb.OleDbParameter("@I_Name",
70
OleDb.OleDbType.VarChar, 120))
cmd.Parameters.Add(New OleDb.OleDbParameter("@I_Image",
71
OleDb.OleDbType.Binary))
72 cmd.Parameters("@I_Name").Value = cbI_Name.Text
73 cmd.Parameters("@I_Image").Value = bytImage
74
77 End If
78
79 cmd.Dispose()
80 cn.Dispose()
81 Catch ex As Exception
82 MsgBox(ex.ToString)
83 End Try
84
85 FillData()
86 End Sub
Share
Add to
Technorati
Kick It
Introduction
More...
How to calll stored procedure in ADO.NET
How to Generate Fibonacci Series with an Algorithm
How to create JQuery Tooltip Plugins
How to find the Quadrant an Algorithm
More...
Print Article
Digg
Del.icio.us
Stumbleupon
Newsvine
Technorati
Mr. Wong
Yahoo!
Windows Live
Send as Email
Add to your CodeProject bookmarks
Discuss this article
15
Report this article as inappropriate
1 2 3 45
Sponsored Links
Top of Form
/w EPDw UKMTAy
Collapse
private void cmdSave_Click(object sender, EventArgs e)
{
try
{
//Read Image Bytes into a byte array
byte[] imageData = ReadFile(txtImagePath.Text);
Collapse
//Open file in to a filestream and read data in a byte array.
byte[] ReadFile(string sPath)
{
//Initialize byte array with a null value initially.
byte[] data = null;
return data;
}
Collapse
void GetImagesFromDatabase()
{
try
{
//Initialize SQL Server connection.
//Initialize Dataset.
DataSet DS = new DataSet();
Collapse
//When user changes row selection, display image of selected row in picture
box.
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs
e)
{
try
{
//Get image data from gridview column.
byte[] imageData =
(byte[])dataGridView1.Rows[e.RowIndex].Cells["ImageData"].Value;
//set picture
pictureBox1.Image = newImage;
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
If you want, you can extend this code to save image from PictureBox to a local image file.
Collapse
//Store image to a local file.
pictureBox1.Image.Save("c:\test_picture.jpg",System.Drawing.Imaging.ImageForm
at.Jpeg);
Points of Interest
If you see frmImageStore in design mode, I have placed picturebox1 into a panel. This
panel's AutoScroll property is set to True and SizeMode property of PictureBox1 is set to
True. This allows picturebox to resize itself to the size of the original picture. When
picturebox's size is more than Panel1's size, scrollbars becomes active for Panel.
Requirements
• Visual Studio .NET 2005
• .NET Framework 2.0
• Microsoft SQL Server 2000 database or Microsoft SQL Server 2005 database
Bottom of Form
Bottom of Form