How to save a picture in a SQL image field and how to show a picture from a SQL image field

HI!

I am searching now a real long time in the forum and I found a lot of stuff to this topic. But please help me it doesn't work!

On my webapplication there is a possibility to save a private picture.
If a new user is saved in the database I want to save a fixed picture as startpicture.
Dim picturePath As String = Request.ServerVariables("APPL_PHYSICAL_PATH") & "img\anonymous.jpg"

Please tell me the way how to save this anonymous.jpg from the picturePath into the database and after that how to display the picture.

Thank you!

.

 

 

 

 

0
Stefan
3/6/2008 8:41:48 AM
asp.net.getting-started 91979 articles. 4 followers. Follow

10 Replies
1266 Views

Similar Articles

[PageSpeed] 7

I'm not that clued up, but I know you must use BLOB (binary large object) datafield in your sql table. Maybe if you run a search on that topic you should come right...

 Here is a nice link

http://www.codersource.net/csharp_read_write_images_database.aspx

 

0
chubbspet
3/6/2008 11:17:55 AM

Thank you. I will try this. My database is not the problem.

If anyone has a little more details to my example and not links to some other sites I will be very happy (its not so easy for me to figure that out - there is never a single file - it is always a uploadfile-component or something).

Thank you very much for more help!

0
Stefan
3/6/2008 3:21:52 PM

Stefan

I also need to do this soon, will let you know if I have any luck...

0
chubbspet
3/7/2008 6:45:57 AM

Hi..below code if for saving the image in the sqldatabase in the image column..

try like this u get it. 

Recently i tried for uploding image

aspx code is

 

<body style="font: 10pt verdana">
    <form id="Form1" enctype="multipart/form-data" runat="server">
            <asp:Table ID="Table1" Runat=server Width=50% BorderWidth=1 BackColor=Beige>
                        <asp:TableRow>
                                    <asp:TableCell ColumnSpan=2 BackColor="#ff0000">
                                    <asp:Label ID="Label1" Font-Name="verdana" Font-size="12px" ForeColor="#ffffff" font-bold="True" Runat=server Text="Add New Person" />
                                    </asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell HorizontalAlign="Right"><asp:Label ID="Label2" Font-Name="verdana" Font-size="12px"  Runat=server Text="Name" /></asp:TableCell>
                                    <asp:TableCell><asp:TextBox id=txtPersonName Runat=server /></asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell HorizontalAlign="Right"><asp:Label ID="Label3" Font-Name="verdana" Font-size="12px"  Runat=server Text="Email" /></asp:TableCell>
                                    <asp:TableCell><asp:TextBox id="txtPersonEmail" Runat=server /></asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell HorizontalAlign="Right"><asp:Label ID="Label4" Font-Name="verdana" Font-size="12px"  Runat=server Text="Sex" /></asp:TableCell>
                                    <asp:TableCell>
                                                <asp:RadioButton GroupName="sex" Font-Name="Verdana" Font-Size="12px" Text="Male" ID="sexMale" Runat=server />
                                                <asp:RadioButton GroupName="sex" Font-Name="Verdana" Font-Size="12px" Text="FeMale" ID="sexFeMale" Runat=server />
                                    </asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell HorizontalAlign="Right"><asp:Label ID="Label5" Font-Name="verdana" Font-size="12px"  Runat=server Text="Date Of Birth" /></asp:TableCell>
                                    <asp:TableCell><asp:TextBox id="txtPersonDOB" Runat=server /></asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell HorizontalAlign="Right"><asp:Label ID="Label6" Font-Name="verdana" Font-size="12px"  Runat=server Text="Image" /></asp:TableCell>
                                    <asp:TableCell><input type="file" id="PersonImage" runat=server /></asp:TableCell>
                        </asp:TableRow>
                        <asp:TableRow>
                                    <asp:TableCell ColumnSpan=2 HorizontalAlign=Center>
                                    <asp:Button ID="Button2" Text="Add Person"  Runat=server />
                                    </asp:TableCell>
                        </asp:TableRow>
            </asp:Table>
        <asp:Button ID="Button1" runat="server" Style="z-index: 100; left: 352px; top: 173px" Text="Button" />
    </form>
  </body>

 

 

and server code for this is

 

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim intImageSize As Int64
        Dim cnn As Data.SqlClient.SqlConnection
        Dim strImageType As String
        Dim ImageStream As Stream
        ' Gets the Size of the Image
        intImageSize = PersonImage.PostedFile.ContentLength
        ' Gets the Image Type
        strImageType = PersonImage.PostedFile.ContentType
        ' Reads the Image
        ImageStream = PersonImage.PostedFile.InputStream
        Dim ImageContent(intImageSize) As Byte
        Dim intStatus As Integer
        intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
        ' Create Instance of Connection and Command Object
        'Dim conn As  SqlConnection(Configuration.ConfigurationManager.AppSettings("SqlConnectionString"))
        'conn = New SqlConnection(Configuration.ConfigurationManager.AppSettings("SqlConnectionString"))
        Dim conn As String = "SERVER=localhost;UID=sa;PWD=;Initial Catalog=saa;Connection Lifetime=30"
        cnn = New Data.SqlClient.SqlConnection(conn)
        'Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        Dim myCommand As New SqlCommand("sp_person_isp", cnn)
        ' Mark the Command as a SPROC
        myCommand.CommandType = CommandType.StoredProcedure
        ' Add Parameters to SPROC
        Dim prmEmail As New SqlParameter("@PersonEmail", SqlDbType.VarChar, 255)
        prmEmail.Value = txtPersonEmail.Text
        myCommand.Parameters.Add(prmEmail)
        Dim prmName As New SqlParameter("@PersonName", SqlDbType.VarChar, 255)
        prmName.Value = txtPersonName.Text
        myCommand.Parameters.Add(prmName)
        Dim prmSex As New SqlParameter("@PersonSex", SqlDbType.Char, 1)
        If sexMale.Checked Then
            prmSex.Value = "M"
        Else
            prmSex.Value = "F"
        End If
        myCommand.Parameters.Add(prmSex)
        Dim prmPersonDOB As New SqlParameter("@PersonDOB", SqlDbType.DateTime)
        prmPersonDOB.Value = txtPersonDOB.Text
        myCommand.Parameters.Add(prmPersonDOB)
        Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
        prmPersonImage.Value = ImageContent
        myCommand.Parameters.Add(prmPersonImage)
        Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)
        prmPersonImageType.Value = strImageType
        myCommand.Parameters.Add(prmPersonImageType)
        Try
            cnn.Open()
            myCommand.ExecuteNonQuery()
            cnn.Close()
            Response.Write("New person successfully added!")
        Catch SQLexc As SqlException
            Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())
        End Try
    End Sub

 

Database table is like this

 

 

Drop Table Person

Go

Create Table Person

(

PersonID Int Identity,

PersonEmail Varchar(255),

PersonName Varchar(255),

PersonSex Char(1),

PersonDOB DateTime,

PersonImage Image,

PersonImageType Varchar(255)

)

Drop Proc sp_person_isp

Go

Create Proc sp_person_isp

@PersonEmail Varchar(255),

@PersonName Varchar(255),

@PersonSex Char(1),

@PersonDOB DateTime,

@PersonImage Image,

@PersonImageType Varchar(255)

As

Begin

            Insert into Person

                        (PersonEmail, PersonName, PersonSex,

                         PersonDOB, PersonImage, PersonImageType)

            Values

                        (@PersonEmail, @PersonName, @PersonSex,

                         @PersonDOB, @PersonImage, @PersonImageType)

End

Go

 


 

0
hlp4al
3/7/2008 8:47:33 AM

Thank you!! I will try this.

Do you have an example how you are displaying the image, too?

0
Stefan
3/7/2008 10:38:01 AM

Hi,

Based on my understanding, you want to achieve writing/retrieving the image from the database and display it on the page.

There is a sample as below. You can try it.

Firstly, you need write the image into database first. 

public void OnUpload(Object sender, EventArgs e)
{
    // Create a byte[] from the input file
    int len = Upload.PostedFile.ContentLength;
    byte[] pic = new byte[len];
    Upload.PostedFile.InputStream.Read (pic, 0, len);
    // Insert the image and comment into the database
    SqlConnection connection = new 
      SqlConnection (@"connection string");
    try
    {
        connection.Open ();
        SqlCommand cmd = new SqlCommand ("insert into table " 
          + "(id, img) values (@id, @img)", connection);
        cmd.Parameters.Add ("@img", pic);
        cmd.Parameters.Add ("@id", Comment.Text);
        cmd.ExecuteNonQuery ();
    }
    finally 
    {
        connection.Close ();
    }
}

The below codes can retrieve all the images from the database and display on the image controls which are created dynamically. Handler.ashx will retrieve the images and write to the client. showpic.aspx will display the image from Handler.ashx on the image control.

showpic.aspx:

        string constr = @"your connection string";

        SqlConnection connection = new SqlConnection(constr);
        SqlDataAdapter sda = new SqlDataAdapter("select * from table", connection);
        DataTable dt= new DataTable();
        sda.Fill(dt);
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            Image ib = new Image();
            ib.ID = "image"+i.ToString();
            ib.ImageUrl = "Handler.ashx?PhotoID=" +dt.Rows[i]["id"].ToString();
            Page.Controls.Add(ib);

        }

Create Handler.ashx(Generic Handler file) 

<%@ WebHandler Language="C#" Class="Handler" %>

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

public class Handler : IHttpHandler {
    
    public bool IsReusable {
        get {
            return true;
        }
    }

    public void ProcessRequest(HttpContext context) {
        // Set up the response settings
        context.Response.ContentType = "image/jpeg";
        context.Response.Cache.SetCacheability(HttpCacheability.Public);
        context.Response.BufferOutput = false;

        int photoId = -1;
        Stream stream = null;

        if (context.Request.QueryString["PhotoID"] != null &&
            context.Request.QueryString["PhotoID"] != "") {
            photoId = Convert.ToInt32(context.Request.QueryString["PhotoID"]);
            stream = GetPhoto(photoId);
        }

        const int buffersize = 1024 * 16;
        byte[] buffer = new byte[buffersize];
        int count = stream.Read(buffer, 0, buffersize);
        while (count > 0) {
            context.Response.OutputStream.Write(buffer, 0, count);
            count = stream.Read(buffer, 0, buffersize);
        }
    }

    public Stream GetPhoto(int photoId) {
        SqlConnection myConnection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        SqlCommand myCommand = new SqlCommand
            ("SELECT img FROM table WHERE id=@PhotoID",
            myConnection);
        myCommand.CommandType = CommandType.Text;
        myCommand.Parameters.Add(new SqlParameter("@PhotoID", photoId));
        myConnection.Open();
        object result = myCommand.ExecuteScalar();
        
        try {
            return new MemoryStream((byte[])result);
        }
        catch (ArgumentNullException e) {
            return null;
        }
        finally {
            myConnection.Close();
        }
    }
}

About ashx:

You can also use aspx instead. It's ok.

The ashx won't create HTML code but aspx will. So response.write some stream into ashx is more efficacious than in aspx.

You can check this link to know about ashx http://cs.jaxdug.com/blogs/dennisbottjer/archive/2005/06/20/730.aspx

 Hope it helps.



Vince Xu
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Vince
3/7/2008 11:06:38 AM

Vince Xu - MSFT:
int len = Upload.PostedFile.ContentLength; byte[] pic = new byte[len]; Upload.PostedFile.InputStream.Read (pic, 0, len);

Hi Vince!  I think there is all I need. One last question. Do you know how I could come to same result (image as byte) if I want to use a fixed filepath (c:\web\img\anonymous.jpg) instead of a FileUpload-component?

 

Thank you so much!

0
Stefan
3/7/2008 12:10:47 PM

Sorry, please forget the question before. My real problem is: 

Vince Xu - MSFT:
Page.Controls.Add(ib);
 

Is there another way to bind the picture? If I do like that the picture is always the last thing on the site.
I tried to bind it on an ASP-Image-Control like that:

imPhoto.ImageUrl = (
"Handler.ashx?PhotoID=" + value)
imPhoto.Databind()

But this is not working only the Page.Controls.Add loads the picture.
I also tried Placeholder1.Controls.Add but this is not working.

Please help me a last time!!

 

 

The question before I solved like that:

Dim picturePath As String = Request.ServerVariables("APPL_PHYSICAL_PATH") & "img\anonymous.jpg"
Dim finfo As IO.FileInfo = New IO.FileInfo(picturePath)
Dim len As Long = finfo.Length
Dim fStream As IO.FileStream = New IO.FileStream(picturePath, IO.FileMode.Open, IO.FileAccess.Read)
Dim br As IO.BinaryReader = New IO.BinaryReader(fStream)
Dim FileByteArray As Byte()
FileByteArray = br.ReadBytes(
CInt(len))

And FileByteArray i save to the image-field in my DB.

0
Stefan
3/7/2008 2:24:18 PM

Hi,

Page.Controls.Add(ib) is not about binding. It will add image one by one by retireve the image from the database.

Besides it, you can retrieve several images from database into GridView/Repeater/DataList at one time which is about bind.

In the below codes, it'll bind the Repeater control with the data from database, and image control will catch the image via Handler.ashx. Rather than using Image.DataBind().

    <asp:Repeater ID="Repeater1" runat="server"
            DataSourceID="SqlDataSource1" >
        <ItemTemplate>
            <asp:Image ID="Image1" ImageUrl='<%# "Handler.ashx?PhotoID=" + Eval("ID") %>' runat="server" />
           
        </ItemTemplate>
    </asp:Repeater>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT ID FROM [photo]"></asp:SqlDataSource>



Vince Xu
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Vince
3/10/2008 2:11:37 AM

Now all is working PERFECT!
THANK YOU!!!

(because of my post before... the path was wrong from the Handler.ashx Embarrassed)

0
Stefan
3/10/2008 9:55:47 AM
Reply:

Similar Artilces:

how to import image into sql image field
Hi Do you have an example for importing images into sql image field?   thanks    INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)  ...from the following page:  http://weblogs.sqlteam.com/peterl/archive/2007/09/26/Insert-binary-data-like-images-into-SQL-Server-without-front-end.aspx      I am sorry I wasn't clear on my question. Even though the sample you provided is great , I need a ASP.VB.NET code. Hi patron, Please refer to the documentations below to stored and retrieve BLOBs (...

how to store images in the image field in a sql server database
hi can anyone tell how to use the image field and add an image in a database. i'm using visual studio web developer express edition 2008 and i want people who visit my website to be able to see the table and the images associated with some of the rows in the table   Thanx Taryn See http://www.shabdar.org/store-save-images-in-sql-server.htmlIf this post was useful to you, please mark it as answer. Thank you! hi i looked at your post and i just wanted to know how one does this in visual studio web developer 2008 express edition where does one put the code since i'...

How to save an image field on SQL Server?
I am developing on Powerbuilder 6.0 I need to save a register on SQL Server that has a image fiel (blob). When I use ODBC it�s possible but whether I use a direct connection to SQL Server with a native driver of PowerBuilder 6.0 it�s not possible. How can I save it ? Is there any way ? Here is the script which I used: lb_foto = ole_1.ObjectData int li_filenum, bytesread, li_ret string ls_file_name, ls_id blob scanned_blob li_filenum = FileOpen(is_foto,StreamMode!,read!) IF li_filenum = -1 THEN messagebox('','Error abriendo archivo') return else bytes...

How to SHOW (JPEG) saved images from SQL Server (image type) ?
Hello, I managed to save some processed JPEG files directly into the database (into an image field) How can I get it back from the database and show it ? Thanks, Yovav.10X N C U L8RBest RegardsYovavG@GMail.com Response.ContentType = GetContentTypeFromDB();Response.BinaryWrite(CreateThumbnail(GetDataFromDB(), width, height));private byte[] CreateThumbnail(byte[] data, double width, double height){System.Drawing.Image g = System.Drawing.Image.FromStream(new MemoryStream(Data));Size ThumbSize = new Size(); ThumbSize = NewThumbSize(g.Width, g.Height, Width, Height);Bitmap ImageOut...

Saving TImage.Picture.Bitmap to SQL BLOB field.
I've been trying all day to get this to work. I want to store a TImage in the SQL database, and the source could be jpg or png (I'm testing with png files). I cannot get the bitmap to savetostream no matter what I try. Here is the procedure to save the image's bitmap via drag and drop of a png file: Note: I added the application.processmessages to make sure Image1 does indeed display the dragged image correctly. Image1 is a TImage on a scrollbox component. If I comment out the whole saving of the bitmap, it is displayed correctly. RS_PartImages is a TADODatas...

linking a sql field to point to the value of another sql field
is it possible to create hyperlinks in powerbuilder such that if you retrieve two fields from a database.. and want to display the first field on the screen and have it link to the value of the other field (when clicked) which is actually a URL address, you can? any help will be appreciated. thank you! ...

How to save image in sql server and display that image in datagrid??
Hay Friend's Can u plese send me the way how to save image in sql server and display that images in datagrid or other control also like Image control or Image control Button?? Plese send the coding in C#.  Thank's Amit try this link...it display image in data grid... http://www.odetocode.com/Articles/172.aspx and for further reference you can refer http://forums.asp.net/p/1122135/1754824.aspx#1754824  RegardsAmit Hi amit, thanks for ur link but I m not fully Satisfy.. so kindly Send me more solutions.. Thank's Amit Hi Amit, Thanks for ur Link,...

How to show pictures in a dw picture object from blob field
I have 1000 product items with it's picture stored in a blob field. I want to show the products in a grid or tab datawindow but I don't find a way to show the picture stored in a blob var in the dw's picture object. I know that I can use p_1.SetPicture(blobvar) but how do I do the same with a dw picture object. I'll be glad to recieve some help TIA 1. The way I do it is to use a picture object. When the user scrolls from one row to the next I set the blob for the current row into the picture object. 2. A second way is to use the bitmap function in a computed...

show image in the image field when it is browse by fileUpload control
hello friends..   i have a query. i hv a file upload control. what i want is if i browse any image from the hard disk it is shown in a image field. how can i able to do it. plz help me.   thanks for ur support.. junior member  [new bie :( ] I dont know if this works you may try it http://www.dmxzone.com/go?4120 http://highoncoding.com/Articles/229_Upload_and_Preview_Images_Using_JavaScript_and_Client_Callbacks.aspxMAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippets...

display image in Gridview from sql image in .Net 2005
Hi all, I need to Display the Image directly from SQL Database in the GridView as Thumnails. This i can do in .Net 2003 with Help of ItemDatabound Event and Template Column. But in the .Net 2005 there no ItemDatabound event in GridView. For Example I need to Show it like this:  Help Me ASAP.Tamil See :http://www.beansoftware.com/ASP.NET-Tutorials/Images-Database.aspxHope this helps. Vikram.S, DeveloperDiscountASP.NETInnovative ASP.NET Hosting & SQL Hosting Thanks a lot Sharma. It was very useful.  Tamil...

LinqDataSource and Image field in SQL Server.
I started this thread on http://www.telerik.com/community/forums/thread/b311D-bbegdh.aspx Here is the main part:   I found some nuances in work of  bundle LINQ and RadGrid (RadGrid it is GridView from Telerik). The table in MSSQL that I use in RadGrid has Image field. I do not use this field in RadGrid . When I set "Delay Loaded = true" for this field in LINQ designer then after updating other fields via grid the RadGrid or LINQ (I don't know which) erases this Image field. If I set "Delay Loaded = false" then no problem - I can update rows in g...

show sql save image to gridview
hi how can i show the image on gridview from the sql database im using this below code which is giving me error<%@ WebHandler Language="VB" Class="Handler" %> Imports System Imports System.Web Imports System.Data Public Class Handler : Implements IHttpHandler     Public Sub ProcessRequest(ByVal context As HttpContext) Dim myConnection As New SqlConnection("Data Source=DANISH\SQLEXPRESS;Initial Catalog=ARBASHHR;Integrated Security=True") myConnection.Open() Dim sql As String = "Select Image_Content from ImageGallery where Img_Id...

How to read image field from sql server
 I am retrieving data from SQL Server into SqlDataReader, one of the fields contains an Image, so I have to questions:1. How can I read an image from  SqlDataReader or what else can be recommended?2.How can I attach it to a ListView control or atleast to Image control? Please advise. Hi You can use HANDLER.ashx to display Images in page that comes from the Database.. see the following links below http://www.codeproject.com/KB/asp/Display_Images_from_Data.aspx http://www.odetocode.com/Articles/172.aspx http://www.worldofasp.net/tut/UserPhoto/Upload_and_retrieve_image_to_and_f...

Direct Display of SQL Image field
Hi I have a SQL 2000 table in which pictures are stored as an Image column. I want to display then onto a c# aspx webpage without storing them to disk. What is the best way to read and display the pictures? In classic ASP I used to do this: Response.ContentType = "image/jpeg" Response.BinaryWrite rs.fields("ThisImage") but I can't get this to work in c#. John South Pangbourne UK Kinda, you should use a <img src="viewImage.aspx?id=RecordID"> and then use some code like this below to read the database: using System; using...

Web resources about - How to save a picture in a SQL image field and how to show a picture from a SQL image field - asp.net.getting-started

Wikipedia:Featured picture candidates/STS-134 launch - Wikipedia, the free encyclopedia
Question Is it my machine or is there a brief loss of sound half way through, and a jump in picture just the the shuttle starts to move? My computer ...

In pictures: Day 3 of the DP World Tour Championship in Dubai
The National In pictures: Day 3 of the DP World Tour Championship in Dubai The National McIlroy is one shot off the leader, Andy Sullivan ...

Phuc Dat Bich posted a picture of his passport on Facebook to prove it was his real name - HeraldSun ...
IF YOU have ever thought your name sounded bad, spare a thought for this guy.

A Late Evening Snack: Pear Tart Tartin – In Pictures!
... tonight, so I though I’d share a bit of a late evening snack. I finally made the pear tart tartin last night. Ingredients followed by pictures ...

J.R. Smith Choked 19-Year-Old Who Wanted His Picture, Police Say
The NBA player refused to take a picture with the&nbsp;fan, then attacked he insulted him, police said.&nbsp;

In Pictures: 2014 Grammy Awards
In Pictures: 2014 Grammy Awards

SPACESHIP BUZZ Astronaut's picture of 'UFO' sparks social media frenzy
SPACESHIP BUZZ Astronaut's picture of 'UFO' sparks social media frenzy

Awesome picture of a V-22 Osprey makes it look like an invisible plane with star engines
The V-22 Osprey is easily one of our favorite aircraft in use today and this picture of it might be the coolest photo of the tiltrotor aircraft ...

AP PHOTOS: A Selection of Pictures From the Past Week
AP PHOTOS: A selection of pictures from the past week

Reese Witherspoon's Family Pictures on Instagram
... mini mes are ridiculously sweet together. Over the years, the actress has offered a glimpse into her family life with adorable Instagram pictures, ...

Resources last updated: 11/22/2015 6:28:12 PM