In this article we will insert image into the Ms SQL server 2005 Database and we will show it by clicking on show button. This article is for beginners and its very very simple.
Example:
After reading this article you will be.
- Able to upload an Image into Ms SQL server 2005 Data base.
- Able to create image handler.
- Display image in page.
Asp net image upload.
Here we will use Asp.net and MS SQL Database.
Below is table schema used to store images in MS SQL server. You need to copy and paste it in MS SQL query analyzer. Execute the script to crate my_Images table.
|
CREATE TABLE [dbo].[my_Images](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[Images] [varbinary](max) NOT NULL,
CONSTRAINT [PK_my_Images] PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] |
- Open web project and add aspx page named Images.aspx.
Add Asp.net file upload control and one ASP.net button control in to your Images.aspx
web page.
Your Images.aspx page will look like below one. <body>
<form id="form1" runat="server">
<div>
Upload your image:
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload" />
<br />
<br />
Show Image by Id:
<asp:TextBox ID="TextBox1" runat="server">1</asp:TextBox> <asp:Button ID="Button2"
runat="server" OnClick="Button2_Click" Text="show" />
<br />
<br />
<asp:Image ID="Image1" runat="server" /></div>
</form>
</body>
On button click we will upload the image into MS SQL DB table My_Image.
below is the code on click of button
Code behind:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class images : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings["pppConnectionString"]
.ConnectionString;
byte[] binaryImage = FileUpload1.FileBytes;
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command =
new SqlCommand("Insert into My_Images(Images) values(@image)", connection);
command.Parameters.Add(new SqlParameter("@image", binaryImage));
try
{
command.Connection.Open();
int i = command.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
Image1.ImageUrl = ("~/Handler2.ashx?id=" + TextBox1.Text);
}
}
Description:
We have crated table schema to collect image data. The sql connection string is used to connect
the Database. The parameter @image is passed as a binary image data. The byte array holds
the actual content of image. []binaryImage is byte array which we have passed to Sqlparameter.
VB.Net code
|
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Public Partial Class images
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim binaryImage As Byte() = FileUpload1.FileBytes
Dim connectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("pppConnectionString").ConnectionString
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand("Insert into My_Images(Images) values(@image)", connection)
command.Parameters.Add(New SqlParameter("@image", binaryImage))
Try
command.Connection.Open()
Dim i As Integer = command.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs)
Image1.ImageUrl = ("~/Handler2.ashx?id=" & TextBox1.Text)
End Sub
End Class
|
We will add new item GenericHandler to our web project to our project which will handle the Image type. Below is Image handler code.
| <%@ WebHandler Language="C#" Class="Handler2" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.IO;
public class Handler2 : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
context.Response.ContentType = "image/gif";
string connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["pppConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("select images from my_images where imageid = @Id",connection);
command.Parameters.Add(new SqlParameter("@Id",context.Request.QueryString["id"]));
try
{
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
byte[]binaryImage = (byte[])dt.Rows[0][0];
reader.Close();
context.Response.BinaryWrite(binaryImage);
}
catch(Exception ex)
{
throw ex;
}
finally
{
command.Connection.Close();
}
}
public bool IsReusable {
get {
return false;
}
}
}
|
VB.Net Code
|
<%@ WebHandler Language="C#" Class="Handler2" %>
Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Data
Imports System.IO
Public Class Handler2
Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext)
context.Response.ContentType = "image/gif"
Dim connectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("pppConnectionString").ConnectionString
Dim connection As New SqlConnection(connectionString)
Dim command As New SqlCommand("select images from my_images where imageid = @Id", connection)
command.Parameters.Add(New SqlParameter("@Id", context.Request.QueryString("id")))
Try
command.Connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
Dim dt As New DataTable()
dt.Load(reader)
Dim binaryImage As Byte() = DirectCast(dt.Rows(0)(0), Byte())
reader.Close()
context.Response.BinaryWrite(binaryImage)
Catch ex As Exception
Throw ex
Finally
command.Connection.Close()
End Try
End Sub
Public ReadOnly Property IsReusable() As Boolean
Get
Return False
End Get
End Property
End Class
|
Remarks: use handlers to handle .jpg or jpeg or images request. This will prevent malicious .jpg extension script execution.
http://www.dotnetexpertguide.com/2011/05/validate-uploaded-image-content-in.html?m=1
-Satalaj