Use varbinary to retrieve image to database using FileUpload

Question

Okay so I'm using adding it to the database by using

  HttpPostedFile postedFile = eventImage.PostedFile;
        string fileExtension = Path.GetExtension(postedFile.FileName);

        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(@"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-StudentMoneySaver-20160203040444.mdf;Initial Catalog=aspnet-StudentMoneySaver-20160203040444;Integrated Security=True");
            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 trying to call just the image by using

  byte[] imgByte = null;
        con = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-StudentMoneySaver-20160203040444.mdf;Initial Catalog=aspnet-StudentMoneySaver-20160203040444;Integrated Security=True");
        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)
        {
            string str = Convert.ToBase64String(imgByte);
            imageTest.Src = "data:Image/png;base64," + str;
        }

with the front end code being

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

I'm getting the error message "Value cannot be null. Parameter name: inArray

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: inArray" I think I'm close!

Could you check in the database, is Image column properly set. For example with SQL Server Managment Studio — Antonio Bakula, Apr 14 '16 at 00:47
You are not clearly stating what is not working and what is happening with what you are doing. Is the Save operation working, if so what is the filename property? What happens when you try to load it? Please post what is happening when you run your code. — Adam Heeg, Apr 14 '16 at 01:12
Your save appears to only save the file name, not the actual file data. Your load appears to set the binary representation of the file name as the src property of an image which is actually supposed to be a url. I could be wrong, but based on memory those are a couple issues i see. — Adam Heeg, Apr 14 '16 at 01:17
http://stackoverflow.com/questions/7324190/how-to-store-images-to-a-varbinarymax-column — UnhandledExcepSean, Apr 14 '16 at 01:25
I've updated my code. I am able to store it now, just not retrieve and display it — Stuart, Apr 14 '16 at 01:26
Thank you for this! Quick question. I'm getting an error under imageIDtagName.src saying (it does not exist in current context. My aspHTML code for the image is: — Stuart, Apr 14 '16 at 01:38

JC Borlagdan · Accepted Answer · 2016-04-14T03:09:10.747

4

If you want to load Image from SQL you do it like this:

byte[] imgByte = null;

SqlCommand cmd = new SqlCommand("SELECT Image FROM tableName",yourConnectionStringHere);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.fill(ds);
foreach(DataRow dr in ds.Tables[0].Rows)
{
  imgByte = (byte[])(dr["ImageColumnNameHere"].ToString());
  string str = Convert.ToBase64String(imgByte);
  imageIDtagName.Src = "data:Image/png;base64," + str;
}

Saving Image using the File Upload is done like this:

HttpPostedFile postedFile = imgFile.PostedFile;
string fileExtension = Path.GetExtension(postedFile.FileName);

 if (fileExtension.ToLower() == ".jpg" || fileExtension.ToLower() == ".png")
 {
            Stream stream = postedFile.InputStream;
            BinaryReader reader = new BinaryReader(stream);
            byte[] imgByte = reader.ReadBytes((int)stream.Length);
     SqlCommand cmd = new Sqlcommand("INSERT INTO tableName(Image) VALUES(@img)",yourConnectionStringHere);
cmd.Parameters.Add("@img",SqlDbType.VarBinary).Value = imgByte;
     cmd.ExecuteNonQuery();
 }

P.S. Surround your sqlconnection with using statement like

using(SqlConnection con = new SqlConnection(""))
{
   //your code here
}

to save you from doing the con.Open() and con.Close()

Edited: To access src property of your html tags, just add runat="server" in your html tag.

edited Apr 14 '16 at 03:09

answered Apr 14 '16 at 01:30

JC Borlagdan

3,318
5
28
51

thank you so much for this. I'm having a slight error and I've edited my code. I appreciate your help! – Stuart Apr 14 '16 at 02:11
aaah so you have a column that is named as inArray? – JC Borlagdan Apr 14 '16 at 02:19
No? The only column names are the ones that I've added. (EventID, EvtType, EvtName, EvtDescription, EvtVote, EvtDate and EvtImage) – Stuart Apr 14 '16 at 02:27
no i mean do you have a column named inArray in your Events table – JC Borlagdan Apr 14 '16 at 02:30
I don't. I've edited the code to show a picture of my events table – Stuart Apr 14 '16 at 02:35
and try changing the way you're adding parameters, use cmd.Parameters.Add on others.. there's issue sometimes in AddWithValue – JC Borlagdan Apr 14 '16 at 02:37
So I've checked everything and it's definitely the image. It's occurring at this line "string str = Convert.ToBase64String(imgByte);" – Stuart Apr 14 '16 at 02:45
Perfect thank you so much! The only thing is that I had to remove the .ToString() at the end of that line but other than that it's perfect. Thanks :) – Stuart Apr 14 '16 at 03:33

Use varbinary to retrieve image to database using FileUpload

1 Answers1