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.