Save and Display Image from DataBase

Question

Not sure if this is close or not. I'm creating an image field within the database table Events using the code

public string EvtImage { get; set; }

For a start I'm not even sure if it should be a string. I am then trying to add the Image to the database by using the code

SqlCommand cmd = new SqlCommand("insert into Events (AspNetUsersId,EvtName,EvtType,EvtDescription,EvtDate,EvtVote, EvtImage) values (@AspNetUsersId, @EvtName, @EvtType, @EvtDescription, @EvtDate, @EvtVote, @EvtImage)");

cmd.Parameters.AddWithValue("@AspNetUsersId", userId);
cmd.Parameters.AddWithValue("@EvtName", eventName.Text);
cmd.Parameters.AddWithValue("@EvtType", eventType.Text);
cmd.Parameters.AddWithValue("@EvtDescription", eventDescription.Text);
cmd.Parameters.AddWithValue("@EvtDate", datetimepicker.Value);
cmd.Parameters.AddWithValue("@EvtVote", 0);

if (eventImage.HasFile)
{
    var  imagename = eventImage.FileName;
    cmd.Parameters.AddWithValue("@EvtImage", imagename);
}

loadDatabase(cmd);

And once this is added I'm trying to display it within a Repeater in ASP.NET using the code

<asp:Repeater runat="server" ID="repeaterEvent">
    <ItemTemplate>
        <div class="jumbotron">

            <h2><asp:Label ID="lblEventTest" runat="server" Text='<%#Bind("EvtName") %>'></asp:Label></h2>
            <h3><asp:Label ID="Label1" runat="server" Text='<%#Bind("EvtType") %>'></asp:Label></h3>
            <h4><asp:Label ID="Label3" runat="server" Text='<%#Bind("EvtDate") %>'></asp:Label></h4>
            <h4><asp:Label ID="Label2" runat="server" Text='<%#Bind("EvtDescription") %>'></asp:Label></h4>   
            <h4><asp:Label runat="server">Amount Attending: </asp:Label>
                <asp:Image ID="label6" runat="server" ImageUrl='<%#Bind("EvtImage") %>' />
            <asp:Label ID="Label4" runat="server" Text='<%#Bind("EvtVote") %>'></asp:Label></h4>
            <asp:Button runat="server" ID="eventButtonTest" Text="Attending" class="btn btn-primary" OnClick="EventVote_Click"/>
        </div>
    </ItemTemplate>
</asp:Repeater>

I am creating the Repeater by using the code:

SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-StudentMoneySaver-20160203040444.mdf;Initial Catalog=aspnet-StudentMoneySaver-20160203040444;Integrated Security=True");

string query;
SqlCommand SqlCommand;
SqlDataReader reader;

SqlDataAdapter adapter = new SqlDataAdapter();
//Open the connection to db
conn.Open();

//Generating the query to fetch the contact details
query = "SELECT EvtName, EvtType, EvtDescription, EvtDate, EvtVote, EvtImage FROM Events";
SqlCommand = new SqlCommand(query, conn);
adapter.SelectCommand = new SqlCommand(query, conn);
//execute the query
reader = SqlCommand.ExecuteReader();
//Assign the results 
repeaterEvent.DataSource = reader;
//Bind the data
repeaterEvent.DataBind();

Answer 1

Follow below steps,

In Database, you should have,

EvtImage image (datatype)

Declare as,

public Byte[] EvtImage { get; set; }

Change while save,

if (eventImage.HasFile && eventImage.PostedFile != null)
{
    //To create a PostedFile
    HttpPostedFile f = eventImage.PostedFile;
    //Create byte Array with file len
    EvtImage = new Byte[f.ContentLength];
    //force the control to load data in array
    f.InputStream.Read(EvtImage, 0, f.ContentLength);

    cmd.Parameters.AddWithValue("@EvtImage", EvtImage);
}

To display image, create handler as below, (change your table/columns as required)

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

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

public class ShowImage : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
       Int32 empno;
       if (context.Request.QueryString["id"] != null)
            empno = Convert.ToInt32(context.Request.QueryString["id"]);
       else
            throw new ArgumentException("No parameter specified");

       context.Response.ContentType = "image/jpeg";
       Stream strm = ShowEmpImage(empno);
       byte[] buffer = new byte[4096];
       int byteSeq = strm.Read(buffer, 0, 4096);

       while (byteSeq > 0)
       {
           context.Response.OutputStream.Write(buffer, 0, byteSeq);
           byteSeq = strm.Read(buffer, 0, 4096);
       }       
       //context.Response.BinaryWrite(buffer);
    }

    public Stream ShowEmpImage(int empno)
    {
 string conn = ConfigurationManager.ConnectionStrings     ["EmployeeConnString"].ConnectionString;
        SqlConnection connection = new SqlConnection(conn);
        string sql = "SELECT empimg FROM EmpDetails WHERE empid = @ID";
        SqlCommand cmd = new SqlCommand(sql,connection);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddWithValue("@ID", empno);
        connection.Open();
        object img = cmd.ExecuteScalar();
        try
        {
            return new MemoryStream((byte[])img);
        }
        catch
        {
            return null;
        }
        finally
        {
            connection.Close();
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }


}

Finally add asp image in aspx and add image url as,

Image1.ImageUrl = "~/ShowImage.ashx?id=" + id;

Let me know if you have any difficulties.

Answer 2

In the code behind write a method called Collection() to retrieve images and other fields as a List of Events like below (Also it is better to use Using statements):

public IEnumerable<Events> Collection()
{
    string address = "YourConnectionString";
    using (SqlConnection con = new SqlConnection(address))
    {
        con.Open();
        string qry = "select * from Events";
        SqlCommand cmd = new SqlCommand(qry, con);
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            if (!dr.HasRows) yield break;
            while (dr.Read())
            {
                Events evt = new Events
                {
                    Id = int.Parse(dr["Id"].ToString()),
                    EvtName = dr["EvtName"].ToString(),
                    EvtType = dr["EvtType"].ToString(),
                    EvtImage = dr["EvtImage"].ToString()
                };
                yield return (evt);
            }
        }
    }
}

And also a class:

public class Events
{
    public int Id { get; set; }
    public string EvtName { get; set; }
    public string EvtType { get; set; }
    public string EvtImage { get; set; }
}

Then to show this images you have two choice. You can either use asp:Repeater with an asp:ObjectDataSource like this:

<asp:Repeater ID="repeaterEvent" runat="server" DataSourceID="imgCats">
  <ItemTemplate>
     <div>
        <asp:Label ID="lblEventTest" runat="server" Text='<%#Bind("EvtName") %>'></asp:Label>
        <asp:Label ID="Label1" runat="server" Text='<%#Bind("EvtType") %>'></asp:Label>
        <img src='<%# Eval("EvtImage") %>' alt="" width="50"/>
     </div>                  
  </ItemTemplate>
</asp:Repeater>
<asp:ObjectDataSource ID="imgCats" runat="server" SelectMethod="Collection" 
      TypeName="WebApplication1.WebForm8">
</asp:ObjectDataSource>

Just don't forget to change the WebApplication1 in the TypeName to the name of your project and WebForm8 to the name of your page.

Finally:To test this code you could create a new folder in your project and add some images to it then in your database in the EvtImage column store them like this:\NewFolder1\yourfirstpicinnewfolder.png and... . If you do this steps your images should be shown beside the other fields in the Repeater.

Answer 3

If your image is being stored as a string database64 the database it should look like: "data:image/png;base64,dataImage" . Otherwise you must convert your byte array (image in bytes) in database64 string and write to the bank so : "data:image/png;base64,dataImage".

Where DataImage will be the conversion data. In asp.net you should to use Convert.ToBase64String(byte[]).

https://www.base64-image.de/tutorial

Answer 4

Using a string is fine. I recommend you save the virtual path of the image in the database; meaning the path .net can resolve to a physical path on your server. E.g.

~/Images/myImage.png

where "Images" is a folder in the root of your .net project.

Then you can display the image by using

<asp:Image ID="label6" runat="server" ImageUrl='<%# ResolveUrl(Bind("EvtImage")) %>' />

Answer 5

If Image save in your solution

<%# Eval("EvtImage", "~/{0}") %>

Answer 6

You can save the image file as string indicating the path of Image being saved in the server folder.

This is my implementation using MVC, you may need a little bit of modification, but I think most of the parts are just the same.

The View page:

<input id="EvtImage" title="Upload An Event Image" type="file" name="EvtImage" required="true" />
<img width="160" height="90" id="preview" src="#" alt="preview upload" hidden />

Use the ViewModel to store the Image :

 public HttpPostedFileBase EvtImage { get; set; }
 // other fields in your model
 ....

And then at the Controller class, bind the ViewModel at the place receiving your submitted form

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(YourViewModel model)
{
    var imgName = Path.GetFileName(model.EvtImage.FileName);
    var img = new Bitmap(model.EvtImage.InputStream);
    var imgPath = "";
    // combine file name of event image and the folder path in server
    imgPath = Path.Combine(Server.MapPath("~/Images/Uploads/YourEventName"), imgName);
    img.Save(imgPath);

    cmd.Parameters.AddWithValue("@EvtImage", imgPath);

    // the rest of your implementation
    ....
}

Answer 7

Just an update. I used varbinary in the end. I added the image to the database by using

  if (fileExtension.ToLower() == ".jpg" || fileExtension.ToLower() == ".png")
        {
            Stream stream = postedFile.InputStream;
            BinaryReader reader = new BinaryReader(stream);
            byte[] imgByte = reader.ReadBytes((int)stream.Length);
            con = new SqlConnection("MyConnectionString");
            SqlCommand cmd = new SqlCommand("insert into Events (AspNetUsersId,EvtName,EvtType,EvtDescription,EvtDate,EvtVote, EvtImage) values (@AspNetUsersId, @EvtName, @EvtType, @EvtDescription, @EvtDate, @EvtVote, @EvtImage)", con);

            cmd.Parameters.AddWithValue("@AspNetUsersId", userId);
            cmd.Parameters.AddWithValue("@EvtName", eventName.Text);
            cmd.Parameters.AddWithValue("@EvtType", eventType.Text);
            cmd.Parameters.AddWithValue("@EvtDescription", eventDescription.Text);
            cmd.Parameters.AddWithValue("@EvtDate", datetimepicker.Value);
            cmd.Parameters.AddWithValue("@EvtVote", 0);
            cmd.Parameters.Add("@EvtImage", SqlDbType.VarBinary).Value = imgByte;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

And displayed it in an image tag by using

            byte[] imgByte = null;
        con = new SqlConnection("MyConnectionString");
        SqlCommand cmd = new SqlCommand("SELECT * FROM Events", con);
        con.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            imgByte = (byte[])(dr["EvtImage"]);
            string str = Convert.ToBase64String(imgByte);
            imageTest.Src = "data:Image/png;base64," + str;
        }

Front-End code:

<img runat="server" id="imageTest" src="imageIDtagName" />

Thanks for everyone's help