store a custom class in an access database

Question

I need to store an instance of a class in an access database. Kind of like binary serialization, but to an access field instead of a file. There has to be a reliable way to do this, right? Perhaps there is an alternative that will work just as well. I am using binary serialization now, but I am running into problems when 2 computer access the same binary file.

Eamonn · Answer

Here's one way of doing it. NOTE the methods below are bare bones, you will need to add rigorous exception handling to make them robust. You will also need to modify the code to handle the IDs. private static void WriteBufferToDatabase(byte[] buffer) { using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\temp\test.accdb;Persist Security Info=False;")) { dbConn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = dbConn; cmd.CommandType = System.Data.CommandType.Text; System.Data.OleDb.OleDbParameter parm1 = cmd.CreateParameter(); parm1.DbType = System.Data.DbType.Binary; parm1.ParameterName = "@parm1"; parm1.Value = buffer; cmd.Parameters.Add(parm1); cmd.CommandText = "INSERT INTO table1 ([Object]) VALUES (@parm1)"; cmd.ExecuteNonQuery(); } } private static byte[] ReadBufferFromDatabase(int id) { byte[] buffer = new byte[0]; using (System.Data.OleDb.OleDbConnection dbConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\temp\test.accdb;Persist Security Info=False;")) { dbConn.Open(); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(); cmd.Connection = dbConn; cmd.CommandText = string.Format("SELECT [Object] FROM Table1 WHERE [ID]={0}",id); System.Data.OleDb.OleDbDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { buffer = (byte[])rdr[0]; } } return buffer; } private static byte[] SerializeToBuffer(object serializeableObject) { System.Runtime.Serialization.Formatters.Binary.BinaryFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); System.IO.MemoryStream stream = new System.IO.MemoryStream(); formatter.Serialize(stream, serializeableObject); System.IO.BinaryReader brdr = new System.IO.BinaryReader(stream); byte[] buffer = new byte[stream.Length]; stream.Position = 0; brdr.Read(buffer, 0, buffer.Length); return buffer; } private static object DeserializeFromBuffer(byte[] buffer) { System.Runtime.Serialization.Formatters.Binary.BinaryFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); System.IO.MemoryStream stream = new System.IO.MemoryStream(); stream = new System.IO.MemoryStream(); System.IO.BinaryWriter bwtr = new System.IO.BinaryWriter(stream); bwtr.Write(buffer, 0, buffer.Length); stream.Position = 0; object obj = formatter.Deserialize(stream); return obj; } There are 4 methods here 2 to serialize and 2 to deserialize: MY table has 2 columns, ID, an autonumber, and Object, an OLEObject. To Serialize, first call the SerializeToBuffer() method passing in the object you want to serialize. This returns a byte[] containing the serialized object. Next call WriteBufferToDatabase() passing in the byte[]. byte[] buffer = SerializeToBuffer(mySerializableObject); WriteBufferToDatabase(buffer); To Deserialize first call the ReadBufferFromDatabase() method which will return a byte[] containing the deserialized object data. THen call DeserializeFromBuffer() which will return an object. byte[] buffer = ReadBufferFromDatabase(objectId); List deserializedList = (List )DeserializeFromBuffer(buffer); ; I hope this makes sense, if you have any questions on it, just add a reply to this post.

patrick · Answer

Perfect! used a guid, object and integer for a custom index, works exactly how I need it to. Thanks again.

patrick · Answer

Thanks a lot, I think it is exactly what I am looking for, will post after I give it a try.