none
Use custom type in DataTable column, with SqlParameter mapping RRS feed

  • Question

  • I need to store objects of a custom class in DataTable columns and have them converted to a SqlDbType.DateTime when the TableAdapter updates the database.  The DataSet designer does allow System.Object as the datatype of a DataTable column, so it looks like I can store objects of my class type in the DataRow.  My question is this:  If I define an implicit type conversion for my custom class to DateTime, will the parameter mapping use it when populating the SqlParameters set up in the TableAdapter insert/update commands.  Example:

     

    Code Block

    // set up UpdateCommand parameter

    cmd.Parameters.Add("@pDateParam", SqlDbType.DateTime, 8, "MyCustomColumn");

    myTableAdapter.UpdateCommand = cmd;

    ...

     

    // populate DataTable

    myDataTable[0].MyCustomColumn = new MyClass(etc...);

    ...

     

    // save the DataTable

    myTableAdapter.Update(myDataTable);

    ...

     

    public class MyClass

    {

    // define type conversion

    public static implicit operator DateTime (MyClass myobj)

    {

    return (DateTime)(result);

    }

    //...etc

    }

     

    Has anyone tried this?  Can you confirm this will work?  I haven't set up an experiment yet -- I thought I would ask here first.

     

    Update:  I set up a quick test of this.  The Update throws an exception "Failed to convert parameter value from a MyClass to a DateTime."  I tried both an implicit and explicit conversion operators.  No dice.  The conversion operator does in fact work, as proved by

     

    Code Block
    MessageBox.Show(string.Format("Date {0:d}.", (DateTime)(myRow.MyColumn as MyClass)));

     

     

     

    Why doesn't the conversion operator work within the Update?  Is there any way to make this work?

    Wednesday, October 31, 2007 12:16 AM

Answers

  • The best solution I've found is to add a RowUpdating event delegate to the SqlDataAdapter.  I omit the automatic parameter mapping in the adapter setup, and handle the custom datatyped columns myself.  Example:

     

    Code Block

    private static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)

    {

        MyDataRow row = (MyDataRow) e.Row;

        switch ( e.StatementType )

        {

            case StatementType.Insert:

            case StatementType.Update:

                e.Command.Parameters["@pMyDateTime"].Value =

                    row.MyDateTimeCol.ToDateTime();  // custom type conversion

                break;

        }

    }

     

     

    Thursday, January 17, 2008 12:54 AM

All replies

  • There's no direct way to store a custom type in a column of a table.  The closest thing to it is to serialize the object then store the bytes in an IMAGE column.  This is usually a bad idea because you lose any ability to query the data via SQL.  It can also be a pain when it comes to dealing with versions, debug vs. release mode, etc...

     

    I recommend creating a relational model for your class and storing its data in the model.  In other words, create tables that store the data for the class (its fields) then use inserts/updates to manipulate the model and selects to read it.

     

    For simple objects this is straightforward - every field can be mapped to a table column of corresponding type; varchar for strings, int for ints, float for doubles, etc...  For more complicated objects you'll have to translate from Object Oriented design to Relational design; 1 to 1 and 1 to many relationships via foreign keys, many to many relationships via join tables (a table connecting one key field to another key field).

     

     

    Thursday, November 1, 2007 9:23 PM
  • The issue is not one of storing the custom type in the database.  It is only used internal to the program, and, as I said, is to be converted to a DateTime value when saved to the database. The problem is that the SqlDataAdapter or TableAdapter will not use the class's DateTime conversion operator when populating the corresponding DateTime SqlParameter during the Update operation.  Unless someone knows how to make that work, I will preprocess the DataTable prior to the Update and replace my custom objects with their DateTime result so the Update will work.

     

    Friday, November 2, 2007 12:33 AM
  • The best solution I've found is to add a RowUpdating event delegate to the SqlDataAdapter.  I omit the automatic parameter mapping in the adapter setup, and handle the custom datatyped columns myself.  Example:

     

    Code Block

    private static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)

    {

        MyDataRow row = (MyDataRow) e.Row;

        switch ( e.StatementType )

        {

            case StatementType.Insert:

            case StatementType.Update:

                e.Command.Parameters["@pMyDateTime"].Value =

                    row.MyDateTimeCol.ToDateTime();  // custom type conversion

                break;

        }

    }

     

     

    Thursday, January 17, 2008 12:54 AM