CODEWITHSUNDEEP
c#.netms-access
Ekta
Ekta

Reputation: 438

Can I add objects to my Access database?

I have a class which stores several values. Currently I make a list of the objects and then use them, but now I have to migrate to an Access database. Can someone tell me if I can put these objects into the database?

Upvotes: 1

Views: 61

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123429

You might be able to serialize your objects to XML and then write them to a Memo field (called Long Text field in Access 2013). The following code works for me:

public class MyClass
{
    public string Name { get; set; }
    public List<string> FavoriteThings { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var myObject = new MyClass();
        myObject.Name = "Gord";
        var myFavs = new List<string>();
        myFavs.Add("bicycles");
        myFavs.Add("ham");
        myObject.FavoriteThings = myFavs;

        var xs = new System.Xml.Serialization.XmlSerializer(myObject.GetType());
        var sw = new System.IO.StringWriter();
        xs.Serialize(sw, myObject);

        using (var con = new OleDbConnection())
        {
            con.ConnectionString =
                    @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                    @"Data Source=C:\Users\Public\Database1.accdb;";
            con.Open();
            using (var cmd = new OleDbCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "INSERT INTO tblObjects (ObjectID, ObjectXML) VALUES (?,?)";
                cmd.Parameters.AddWithValue("?", 1);
                cmd.Parameters.AddWithValue("?", sw.ToString());
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
}

When I open the Access table, select the Memo field, and hit ShiftF2 to "zoom in" on it I see

XmlMemo.png

Upvotes: 1

Related Questions