How can i pull an image and data from a Database?

Question

I am trying to pull data from a Database using C#.net and use a Foreach loop to make it visible on a page. Every time i run the code i only get one item that shows up when i know that there is at least 7 items in the DB. i have placed the code below for the C#.

SqlConnection oConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["HomeGrownEnergyConnectionString"].ToString());
    string sqlEnergy = "Select * from Product p where p.ProductTypeId=3";
    SqlCommand oCmd = new SqlCommand(sqlEnergy, oConnection);
    DataTable dtenergy = new DataTable();
    SqlDataAdapter oDa = new SqlDataAdapter(oCmd);
    try
    {
        oConnection.Open(); ;
        oDa.Fill(dtenergy);
    }
    catch (Exception ex)
    {
        lblnodata.Text = ex.Message;
        return;
    }
    finally
    {
        oConnection.Close();
    }
    DataTableReader results = dtenergy.CreateDataReader();

    if (results.HasRows)
    {
        results.Read();
        foreach(DataRow result in dtenergy.Rows)
        {
            byte[] imgProd = result["ThumnailLocation"] as byte[];
            ID.Text = result["ProductID"].ToString();
            Name.Text = result["Name"].ToString();
            price.Text = FormatPriceColumn(result["Price"].ToString());


        }
    }

Here is the code for the asp.net.

<div>
<asp:Image ID="imgProd" CssClass="ProdImg" runat="server" />
<asp:Label runat="server" ID="ID"  />
<asp:Label runat="server" ID="Name" />
<asp:Label runat="server" ID="price" />
<asp:TextBox ID="txtQty" MaxLength="3" runat="server" Width="30px" />
<asp:Button runat="server" ID="Addtocart" Text="Add To Cart" CommandName="AddToCart" ItemStyle-CssClass="btnCol" />

If someone could please help me that would be great thanks.

I'd create a file handler *.ashx and have the images be returned by that component. Then, you can set the ImageUrl attribute to that handler with the appropriate parameters passed in. — Candide, Nov 25 '12 at 16:27
Every time you see the last item from database. How you plan to see 7 rows in you page with current design? — Hamlet Hakobyan, Nov 25 '12 at 16:30
you are getting the values correctly the problem is each row from the DB you are binding it to same set of controls,so at last only the final row gets displayed to you which will not change as there are no more rows in DB — sajanyamaha, Nov 25 '12 at 17:03

score 1 · Accepted Answer · answered Nov 25 '12 at 16:58

You should set the image column type in database to byte[] and use this tow method to get and set the image:

public BitmapImage ImageFromBuffer(Byte[] bytes)
{
    MemoryStream stream = new MemoryStream(bytes);
    BitmapImage image = new BitmapImage();
    image.BeginInit();
    image.StreamSource = stream;
    image.EndInit();
    return image;
}

public Byte[] BufferFromImage(BitmapImage imageSource)
{
    Stream stream = imageSource.StreamSource;
    Byte[] buffer = null;
    if (stream != null && stream.Length > 0)
        {
            using (BinaryReader br = new BinaryReader(stream))
                {
                    buffer = br.ReadBytes((Int32)stream.Length);
                }
        }

    return buffer;
}

score 0 · Answer 2 · edited May 23 '17 at 11:43

I think your question title should be more like "How do i show a database result set on my page".

And the answer is that you should use data binding for this. This way you can setup how a data item should look on your page. Then you can throw a set of records/objects to this list (with it's layout) and they are all rendered as defined in your layout.

Here is a small example:

In your aspx code:

<asp:DataList id="dlItems" runat="server">
         <ItemTemplate>
            <%# Eval("ID") %>
            <%# Eval("Name") %>
            <%# Eval("price") %>
         </ItemTemplate>
</asp:DataList>

In your cs code:

dlItems.DataSource = dtenergy;
dlItems.DataBind();

For the image-part of your question, please see other threads like these: Display image from database in ASP.net with C#

For more details about data binding you can look here: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.datalist.itemdatabound.aspx

How can i pull an image and data from a Database?

2 Answers2