Stuart Stuart - 7 months ago 19
SQL Question

Save and Display Image from DataBase

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

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