C# - OleDb - Access(MDB) : storing and retrieving images

C# - OleDb - Access(MDB) : storing and retrieving images

• rgrid

  Hi VcDeveloper,
  
  As Matt says, and dont forget to use parameters with your database connection
  
  Just a note.
  
  I dont belive you need to serialize to file, so you could streamline the process by doing something like :
  
  (in) - bytes is the resultant value to be placed in access as 'OleDbType.LongVarBinary'
  
  MemoryStream memStream1 = new MemoryStream();
  BinaryFormatter formatter = new BinaryFormatter();
  formatter.Serialize(memStream1,MyObject);
  byte [] bytes = memStream1.GetBuffer(); //you could use the result of this method directly
  memStream1.Close();
  
  (out)
  
  MemoryStream memStream1 = new MemoryStream((byte[])aReader.GetValue(0));  //change to your relevance
  BinaryFormatter formatter = new BinaryFormatter();
  obj = (MyClass)formatter.Deserialize(memStream1);
  memStream1.Close();
  
  Regards
  Gav
  
  
  
  
  

• SNOMan

  Thanks.. that is a good idea...., may I add, encrypting the file for security

  ...about overhead performance..., for instance if I had a student who wanted to review all his past exams which could be around 60+.. which way would be faster, reading and loading from access or disk

  
  

• Rits

  Well you would have to serialize the structure to some format. In C# you could use the binary serialization support, just add Serializable attribute to your class like so:

  [Serializable]
  public class MyObject {
   public int n1 = 0;
   public int n2 = 0;
   public String str = null;
  }

  Then serialize like so (I lifted this code from online help):

  MyObject obj = new MyObject();
  obj.n1 = 1;
  obj.n2 = 24;
  obj.str = "Some String";
  IFormatter formatter = new BinaryFormatter();
  Stream stream = new FileStream("MyFile.bin", FileMode.Create, FileAccess.Write, FileShare.None);
  formatter.Serialize(stream, obj);
  stream.Close();
   
  Then all you have to do is take the bytes from serialization and store these in Access OLE OBJECT field, this is the easy part.
   

  
  
  

• Giv Afshar

  Just an addition tomother posts. Story binary content in Access database could lead to the fact that you will get upper limit of the database very quickly. I have seen applications, which get this limit in a couple of days. I believe it is better to store location of the image file rather than storing actual content
  
  

• Steven D

  You need to use a parameterized OleDbCommand. Inlining the SQL will not work here.

  So see for example this code ->

  http://www.atalasoft.com/products/dotimage/docs/Database.html

  [C#]
  OleDbConnection myConnection = null;
  try
  {
  //save image to byte array and allocate enough memory for the image
  byte[] imagedata = image.ToByteArray(new Atalasoft.Imaging.Codec.JpegEncoder(75));
  
  //create the SQL statement to add the image data
  myConnection = new OleDbConnection(CONNECTION_STRING);
  OleDbCommand myCommand = new OleDbCommand("INSERT INTO Atalasoft_Image_Database (Caption, ImageData) VALUES ('" + txtCaption.Text + "', )", myConnection);
  OleDbParameter myParameter = new OleDbParameter("@Image", OleDbType.LongVarBinary, imagedata.Length);
  myParameter.Value = imagedata;
  myCommand.Parameters.Add(myParameter);
  
  //open the connection and execture the statement
  myConnection.Open();
  myCommand.ExecuteNonQuery();
  }
  finally
  {
  myConnection.Close();
  }
  
  

  
  
  

• Zephyr12345

  In general the ability to store images inside Access db is just a convenience factor. It allows you to store everything in one file. If you wanted you could create your own file format and store a bunch of images in the same file, right Or you could store the images in a zip archive for example. There is no big difference. The key thing is Access db has a 2 GB limit in size, so this could be a limiting factor. Also, if the Access db becomes corrupt, there is no easy way to recover the images.

  So the key feature is the images are all stored in one file. If you like this feature then use it, if this feature is not as important, don't use it.

  
  
  

• Wingnut_233

  C# 2005

  How do you store objects into a Access.mdb. I want to take a struct with different types variables and constants and store it in the .mdb.

  I know my .mdb variable type has to be OLE Object. All I need is an example how to store and retreave it.

  Also can my struct have an CollectList and be store in the .mdb with no problems

  Thanks for you help!...

  
  

• santaclause585

  Thanks Matt,
  
  Works a treat.
  
  I was being a bit silly really you have my gratitude.
  
  Gav
  
  
  
  

• Tamilmannan

  Thanks for the helpful info on this thread.  In case anyone else comes by and wants to see a complete example.  Here it is:

  public class ChapterExam
  {
     public ExamConfigData m_ExamData;

     public ChapterExam()
     {
         m_ExamData = new ExamConfigData();
         m_ExamData.m_MarkBooks = new System.Collections.ArrayList();

         System.Data.OleDb.OleDbCommand dbCmd1;
         System.Data.OleDb.OleDbDataReader dbReader;

        dbCmd1 = new System.Data.OleDb.OleDbCommand();
        dbCmd1.Connection = AccessDatabase.GetDbConnection();
        dbCmd1.CommandText =
                    String.Format("SELECT * FROM {0} 
                    WHERE (ExamKey = 'T1B10C1' AND ExamNo = 367 AND ExamName = 'History')", 
                                  ReferenceExamsConst.COMPLETEEXAMS);
        dbReader = dbCmd1.ExecuteReader();

         if (dbReader.HasRows)
         {
             dbReader.Close();
             OpenBooksExaminations(dbReader, dbCmd1);
         }
         else
         {
             dbReader.Close();
             CreateBooksExaminations(dbCmd1);
         }

      }

      void OpenBooksExaminations(System.Data.OleDb.OleDbDataReader dbReader,
                                               System.Data.OleDb.OleDbCommand dbCmd)
     {
         byte[] ClassObjArray;
         MemoryStream memStream;
         BinaryFormatter formatter;

         dbCmd.CommandText =
                     String.Format("SELECT ClassObj FROM {0} 
                     WHERE (ExamKey = 'T1B10C1' AND ExamNo = 367 AND ExamName = 'History')",
                                    ReferenceExamsConst.COMPLETEEXAMS);
         ClassObjArray = (byte[]) dbCmd.ExecuteScalar();
         if (ClassObjArray != null)
         {
             memStream = new MemoryStream(ClassObjArray);
             formatter = new BinaryFormatter();

             m_ExamData = (ExamConfigData) formatter.Deserialize(memStream);
             memStream.Close();
         }
     }

     void CreateBooksExaminations(System.Data.OleDb.OleDbCommand dbCmdSave)
     {
         System.Data.OleDb.OleDbCommand dbCmdExams;
         System.Data.OleDb.OleDbDataReader dbReaderExams;
         System.Data.OleDb.OleDbParameter parameter;
         byte[] ClassObjArray;
         MemoryStream memStream;
         BinaryFormatter formatter;

         dbCmdExams = new System.Data.OleDb.OleDbCommand();
         dbCmdExams.Connection = AccessDatabase.GetDbConnection();
         dbCmdExams.CommandText = String.Format("SELECT * FROM {0}
               WHERE (Book = 'ExamBook') ORDER BY SortKey", ReferenceExamsConst.EXAMS);
         dbReaderExams = dbCmdExams.ExecuteReader();

         if (!dbReaderExams.HasRows) return;

         while (dbReaderExams.Read())
         {
             m_ExamData.m_MarkBooks.Add(dbReaderExams["Exam"].ToString());
             System.Console.WriteLine("{0}", dbReaderExams["Exam"].ToString());
         }

         memStream = new MemoryStream();
         formatter = new BinaryFormatter();

         formatter.Serialize(memStream, m_ExamData);
         ClassObjArray = memStream.GetBuffer();
         memStream.Close();

         dbCmdSave.CommandText = String.Format("INSERT INTO {0} (ExamKey, ExamNo, 
                            ExamName, ClassObj) VALUES ('T1B10C1', 367, 'History', )",
                            ReferenceExamsConst.COMPLETEEXAMS);
         parameter = new
                        OleDbParameter("@ClassObj", OleDbType.LongVarBinary, ClassObjArray.Length);
         parameter.Value = ClassObjArray;
         dbCmdSave.Parameters.Add(parameter);
         dbCmdSave.ExecuteNonQuery();
     }
  }
  

  
  
• C# - OleDb - Access(MDB) : storing and retrieving images