Sqlserver image datatype

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

Storing and Retrieving images in gridview using image datatype

1) First Create Database  ImageDB

2) Create table ImageGallary

CREATE TABLE ImageGallery(
Img_Id int  IDENTITY(1,1) NOT NULL,
Image_Content image NOT NULL,
Image_Type varchar(50)  NOT NULL,
Image_Size bigint NOT NULL
)

3) Create New Asp.net Application

Programs -> Microsoft Visual Studio 2005 or 2008 -> File –>website ->

4) open your web.config file in your project write your connectionstring

<appSettings>
<add key=”ConnectionString” value=”Data Source=localhost name/server name;Initial Catalog=ImageDB;User;Password=xxx”/>
</appSettings>

Ex: localhost name= Raju and Server.168.2.34

5) Storing Image In Sqlserver Database

In Default.aspx

Place FileUploader control and Button ID

Button ID is btnSubmitCustImage

FileUploader ID is FileUpload1

6) In  Button Click event (code will appear in Default.aspx.cs) write this code

First Add Namespaces

using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;

now write code in button click i.e.

protected void btnSubmitCustImage_Click(object sender, EventArgs e)
{

SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != “”)
{

byte[] myimage = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile Image = FileUpload1.PostedFile;
Image.InputStream.Read(myimage, 0, (int)FileUpload1.PostedFile.ContentLength);

SqlCommand storeimage = new SqlCommand(”INSERT INTO ImageGallery(Image_Content, Image_Type, Image_Size) values (@image, @imagetype, @imagesize)”,objConn);
storeimage.Parameters.Add(”@image”, SqlDbType.Image, myimage.Length).Value = myimage;
storeimage.Parameters.Add(”@imagetype”, SqlDbType.VarChar, 100).Value = FileUpload1.PostedFile.ContentType;
storeimage.Parameters.Add(”@imagesize”, SqlDbType.BigInt, 99999).Value = FileUpload1.PostedFile.ContentLength;

objConn.Open();
storeimage.ExecuteNonQuery();
objConn.Close();
}
}

** Run Your website Add image using Fileuploader control and click submit button theck check your database ImageDB open your table Image Gallary you can see values of Img_ID,Image_content,Image_type,Image_size

6) Retrieving Images to Gridview with Handler.aspx Using sqlserver 2005

What is Handler?

An ASP.NET HTTP Handler is a simple class that allows you to process a request and return a response to the browser. Simply we can say that a Handler is responsible for fulfilling requests from the browser. It can handle only one request at a time, which in turn gives high performance. A handler class implements the IHttpHandler interface. //from aspdotnetcodes

a) Create Handler.aspx

Goto website -> Add new item -> choose Generic Handler (i.e Handler.ashx)

The Handler.ashx file to perform image retrieval. This Handler.ashx page will contain only one method called ProcessRequest. This method will return binary data to the incoming request. In this method, we do normal data retrieval process and return only the Image_Content field as bytes of array.

In Handler.aspx the code looks like this

public void ProcessRequest (HttpContext context)

{

context.Response.ContentType = “text/plain”;  //Remove
context.Response.Write(”Hello World”); //Remove

}

Remove above two lines code and Add below code in ProcessRequest

<%@ WebHandler Language=”C#” %>

using System;
using System.Web;
using System.Data.SqlClient;  //Add this namespace
using System.Configuration; // Add this namespace
using System.Data; //Add this namespace
using System.IO; //Add this namespace

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context)

{
SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
myConnection.Open();
string sql = “Select Image_Content, Image_Type from ImageGallery where Img_Id=@ImageId”;
SqlCommand cmd = new SqlCommand(sql, myConnection);
cmd.Parameters.Add(”@ImageId”, SqlDbType.Int).Value = context.Request.QueryString["id"];
cmd.Prepare();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
context.Response.ContentType = dr["Image_Type"].ToString();
context.Response.BinaryWrite((byte[])dr["Image_Content"]);
dr.Close();
myConnection.Close();
}

public bool IsReusable {
get {
return false;
}
}

}

b) now  Place Gridview in Default.aspx page below of Fileuploader control and button already we placed this two controls for storing images in Database below of this controls Add gridview control or you can add some other .aspx page.

c)  To Retrieve images from sqlserver 2005 using query

public DataTable FetchAllImagesInfo()
{
string sql = “Select * from ImageGallery”;
SqlDataAdapter da = new SqlDataAdapter(sql, objConn);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

d) To display Images In gridview template field

Note: To add template Field to Gridview

gridview Template field Adding

First click -> Edit columns.. -> choose TemplateField –> Add –> ok

once see the above figure now click Edit Template (Display Mode must be in ItemTemplate)  –> in that add Image control from toolbox –>click  End Template

First bind the image in gridview like below code

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”>
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Image ID=”Image1″ runat=”server” ImageUrl=’<%# “Handler.ashx?id=” + Eval(”Img_Id”)  %>’ />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

To display images in Default.aspx Page Gridview write code in Page_Load

protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = FetchAllImagesInfo();
GridView1.DataBind();
}

Now Run your website see the result