none
Object Reference Column in a SqlCe Data Table

    Question

  • This may be a strange question...

    I've defined a table in a SqlCe database (using VS C#2010 Express) that will never have any data persisted in it.  I did this so I can instantiate versions of it using the designer generated strongly typed table definition without ever directly using the one that's in the database.  (That may be obtuse, but it seems to work quite well, I don't need to worry about the database overhead, and I get all the documentation and code creation the designer provides).

    I've now run into a situation where I'd like one of the columns in the instantiated tables to contain references to C# objects (always requiring casting of course to use them).  Can I do that and, if so, what SqlCe datatype would I use to get a column that can contain an object reference?

    Thx.  Steve

    Friday, May 23, 2014 2:22 PM

Answers

  • Yes, as stated in my first reply, you can serialize/deserialize into a byte array, and store this in a column of type "image", if that does not fulfill your requirements, then no, but there are many other programmatic ways to store object instances.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by Cincy Steve Wednesday, May 28, 2014 3:36 AM
    Sunday, May 25, 2014 3:04 PM

All replies

  • If the object instance is serializable, you can use a binary serializer, and save in a image (byte array) column

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Friday, May 23, 2014 7:18 PM
  • Erik -

    I think my need may not be clear.  I don't need to put a copy of the object in the column (and subsequently persist it in the database).  Instead, I just need  to assign an object reference in the column and subsequently use that reference to interact with the object.  Since I'm using a database table to create a strongly-typed definition of the table, I need to know what SqlCe DataType might be used to create the strongly-typed DataTable that can handle the object reference. 

    See the code below that's designed to  illustrates what I need.  My questions are: (1) is there any way to do this; and (2) what SqlCe DataType does the ObjectPointer column have to be?

    Thanks for the help.

    Steve

    // Create myDataTable from the strongly typed database table generated by the VS designer

    myDataTable = new MyDataBaseDataSet.MyTableDataTable();

    // Assume I add some rows to it.

    // Get a reference to a DataRow in which to save an object reference myDataRow = myDataTable.Rows(I); // Save a reference to an instance of MyObject in the row myDataRow.ObjectPointer = myObject; // Elsewhere .... // Use the saved object reference to invoke SomeMethod ((MyObject)myDataRow.ObjectPointer)).SomeMethod();



    Friday, May 23, 2014 9:58 PM
  • I am unsure about what you are trying to achieve. Are you doing it right?

    Why not simply store a reference to the object during runtime? (In a list/dictionary)

    In order to store in a data related column, you must serialize and deserialize as suggested in my original reply


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Saturday, May 24, 2014 10:13 AM
  • Erik -

    I'll admit to trying to cut some corners, which I'd like to do if there's a way that doesn't cross some .Net, Visual Studio  or C# line.

    As you noted, I do want to store a reference to the object ONLY during runtime.  I can certainly use a List or a DataTable created for that purpose, which is really what I want to do.  I'd like to use a DataTable that I already have setup to store other variables, so that as I traverse it (e.g. using foreach DataRow), I can reference the object as well as the other variables using the foreach DataRow reference. 

    The trick I want to use is to define the DataTable using the VS database designer rather than manually creating the code that defines it.  The designer is visual, easy to use, self-documenting, and of special note makes the code that uses the DataTable easy to read because the resulting DataTable is strongly-typed.  I should have more clearly exhibited in the sample code how I use the strong-typing in the line that sets myDataRow.  It should read: "MyDatabaseDataSet.MyTableDataRow myDataRow = myDataTable.Rows[I];", where "MyDatabaseDataSet.MyTableDataRow" uses to the VS generated strongly-typed DataRow definition.  Note that the code does not store data in the database's DataTable itself.  It merely uses the database's DataTable DEFINITIONS to instantiate other tables.

    I have used this approach when the all data in the DataTable is of one or more native C# types (int, decimal, etc) for which there are corresponding SqlCe types.  However, I'd like to use it with a custom type (MyObject), if that's possible, and am looking for what SqlCe type might work for that purpose, if any.

    I admit that this may be an unusual way to create a DataTable definition.  I'm not sure where I can up with the idea.  I may have read it somewhere or just cooked it up myself.  But I really like what the strong-typing provides (without having to manually create it myself).

    Is there any way to include custom types using this idea?



    Sunday, May 25, 2014 2:36 PM
  • Yes, as stated in my first reply, you can serialize/deserialize into a byte array, and store this in a column of type "image", if that does not fulfill your requirements, then no, but there are many other programmatic ways to store object instances.


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    • Marked as answer by Cincy Steve Wednesday, May 28, 2014 3:36 AM
    Sunday, May 25, 2014 3:04 PM
  • Erik -

    Your observations indicate that I will not be able to do what I want in a straightforward way, which is key to my objective.  As you suggest, I'll revert to conventional ways to create and use the object collections I need.

    Thanks for your patience in trying to understand my need and for your time in responding.

    Steve

    Wednesday, May 28, 2014 3:36 AM