How to Save Image in Database Using C#

How to save image in database using C#

You'll need to serialize the image to a binary format that can be stored in a SQL BLOB column. Assuming you're using SQL Server, here is a good article on the subject:

http://www.eggheadcafe.com/articles/20020929.asp

C# Saving Image to database (Not working)

See below code. I am hopeful that it will help- (I have only added the code to save the image in DB)

        FileStream fs = new FileStream(selectedFile, FileMode.Open, FileAccess.Read);
        byte[] bimage = new byte[fs.Length];
        fs.Read(bimage, 0, Convert.ToInt32(fs.Length));

        SqlConnection cn;
        cn = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated 
        Security=True");
        cn.Open();
        SqlCommand cmd = new SqlCommand("insert into MyTable (Image) 
        values(@imgdata)", cn);
        cmd.Parameters.AddWithValue("@imgdata", SqlDbType.Image).Value = bimage;
        cmd.ExecuteNonQuery();
        cn.Close();

How to save a combination of text and image in SQL Server database

You can convert an image to text with Base64, for example:

var image = File.ReadAllBytes("image.jpg");

var base64String = Convert.ToBase64String(image);

And you can convert it back to an image again:

var image = Convert.FromBase64String(base64String);

File.WriteAllBytes("image.jpg", image);

You just need to figure out how to split/concatenate your text as a whole...

Store image in database and retrieve it

It is possible of course to store your images in db. However it is not recommended. It is better to store them in a file system.

Your code is a bit messy. The following is a better example.

MemoryStream ms =new MemoryStream(); 
byte[] PhotoByte=null; 
pictureBox1.Image.Save(ms, ImageFormat.Jpeg); 
PhotoByte =ms.ToArray(); 
// I'm not sure whether you are able to create an sql by simply concating the string
Str = "insert into Experimmm Values('@PhotoBytes','@MyTextValue')"; 
// You have to parametrize your query
cmd.Parameters.AddWithValue("PhotoBytes", PhotoByte);
// This also helps you to avoid syntactical corruption in case of ' and sql injection
cmd.Parameters.AddWithValue("MyTextValue", textBox1.Text );
Conn.Open(); 
cmd.Connection = Conn; 
cmd.CommandText = Str; 
cmd.ExecuteNonQuery(); 
Conn.Close(); 

When you retrieve you could use a binary writer in some handler

namespace TestNS
{
   public class MyHttpHandler : IHttpHandler
   {
      // Override the ProcessRequest method.
      public void ProcessRequest(HttpContext context)
      {
         // Your preparations, i.e. querystring or something
         var conn = new SqlConnection("Your connectionstring");
         var command = new SqlCommand("Your sql for retrieval of the bytes", conn);
         conn.Open();
         var data = (Byte[])command.ExecuteScalar();
         conn.Close();
         context.Response.BinaryWrite(data);      }

      public Boolean IsReusable
      {
         get { return false; }
      }
   }
}

Save and Display Image from DataBase

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

Saving image in sql database C#

ExecuteReader returns data. In your case, you are not. You just try insert a row in your database. That's why you need to use ExecuteNonQuery instead.

And parameterize your other insert values as you did for image variable. Also use using statement to dispose your database connections and commands.

int insertedRowCount = command.ExecuteNonQuery();

if(insertedRowCount > 0)
   MessageBox.Show("Added Successfully!!!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

Saving image to the database

Just a side comment: I think is not a good idea to store images in db.

In general is not a good idea store images in db as dbs are designed to store text not big binary chunks. Is much better to store paths for images and have images in a folder. If you want to get sure of 1 to 1 relationship name image with ID of entity (1323.jpg).

If you want to have image paths you should follow some guidelines (In general code defensively):

• On upload of image check that image is valid (even made a binary check of image header)
• Don't allow to overwrite an existing image in case of a INSERT of a new entity.
• Name images as primary key (1.jpg, 2.jpg)
• On load of image don't assume that image is going to be there.
• Do not allow (if possible) manual interaction with images (No remoting in machine and copying images from one place to other). Manual interaction can cause inconsistencies.

But I assume that for some reason you should do it. So in order to achieve what you want:

DB design

• Create a binary column (binary or varbinary) in your table
• It is better if you create it in a different table with 1-1 relationship. However the idea is avoiding to load image when hydrating entity. Use a lazy load approach to load your image only when you want.
• You have to avoid to load images when you make a big select (for example if you want to load all your entities in a combo avoid SELECT * From whatever) as it will load thousands of images for nothing. As I said this can be done by having images in a different table, or loading only proper columns in SELECT or by making lazy load. (Or even better by NOT having images in DB, only paths)

C# Code

• Use BynaryReader to read it

• User Byte array to store it

• Check this link for code example: http://www.codeproject.com/Articles/21208/Store-or-Save-images-in-SQL-Server

C# : Saving an Image into DB

If you MUST store the actual file in SQL then make sure you put some restrictions around the size of the image and make sure you properly plan the database sizing - how many images and of what size are you expecting?

If you must store it in SQL my preferences is to automatically have it sitting in its own SQL database. This way any performance issues I run into with these images is not affecting the rest of my database.

NOW, if you do not have to store the images in a SQL database then I think this is just calling out to be done with Azure Blob Storage. Go take a look at it - it will give you what you want in a very efficient system.

If you cannot use Azure then you should put together a class that will handle reading/writing/lookup of images to a file system. Saving to the file system (done properly) will work much better than saving to SQL.

---

Related Topics