none
Populate a custom column in a DataSet Table Options RRS feed

  • Question

  • How does one go about populating a custom column in a DataSet Table?

    One field in the target table is a blob.  This blob contains a serialization of a .Net class.  The frontend would like to access this as a .Net class, not as a blob.  The goal is to add two new columns to the DataSet's Table class.

    1: Of type object, to unserialize the blob
    2: Of type string or int as to setup a parent/child relationship with another DataSet Table for DataBinding

    What is the best way to populate the two custom columns when the Adapter loads the data from the database?

    (The database is a old legacy non Microsoft DB, we are using .Net 3.5 but not the Entity Framework or Linq)

    Wednesday, May 27, 2009 5:47 PM

Answers

  • Hey Sam,

    Now I understand the schema. So in the database you have a table that has these fields right?

    int CategoryId
    string Name
    string Question
    varient Response

    Here is a link to Sql Server types and their CLR equvilant
    http://msdn.microsoft.com/en-us/library/ms131092.aspx

    You will notice that object maps to sql_varient. So what I would do is simply have a the Response a sql_varient. Then in DataSet the column when its brought through ddex should be mapped to object.

    Then I don't think you need to do anything complex. You should be able to drag and drop the table over and the adapters that are created should do the work of the conversion.

    So try this, create a table in a database as a sql_varient. Then create a data connection in VS, drag and drop this to the DataSet designer. When you look at the Response datacolumn you will see that its DataType is object. Everything should just work.

    Sorry it took so long to figure this out, I was confused where the data was coming from. Now if the database is not sql server, then what I would do is review the types for that database and see what they map too for CLR types. Then you can find the type that you need to specify for the column.

    This should help, let me know.

    Thanks
    Chris

    Thanks
    Chris



    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 9:53 PM

All replies

  • Do you have a way to materialize a new .net class from the value that is retrieved from the database? Will these values always materialize to a particular .net Type? Or could it materialize to multiple types? What data is the blob storing?

    Typically you have an adapter that would fill and update a DataSet
    http://msdn.microsoft.com/en-us/library/tkctt675.aspx

    Why do you need to have a column to hold a value to connect with another table for Databinding? I'm not sure I understand the design. When you pull back data from the database what columns does it have?

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 27, 2009 9:46 PM
  • First off, I am very open to implementing this differently.

    The table is a "question item" and the blob is the response properties.   Depending on the type of response the set of properties are totally different.  In the .Net code, all the different response property classes do derive from the IResponseProp interface, but the interface has no properties or methods as of yet.

    Examples of response types are: text, float, int, date, time, date time, etc.

    I than have an another type called ClassDetails which is a collection of all the response types that has a display name, tooltips, description, and the full class name of the response type.  The idea is to place this into a drop down list so the user can change the response type, when they do that, databinding will assign the selected ClassDetails to the question item so that the question item can use the type in the ClassDetails to create the response properties.

    My plan is to create a class called QuestionItemDataset that will contain the one QuestionItem and the List of ClassDetails.  The bindinglist's datasoure will be the QuestionItemDataset and controls like the text box for the question text will bind to the QuetionItem.Text property, but the drop down for the response will bind to the ClassDetail list and have the selected item bind to the Questionitem.ClassDetail property, which when set will create the new ResponseProperty.

    As a side note, the QuestionItem does have a categoryId which is a FK to the category table.  There needs to be a relationship setup there so that the user can pick a different category from a drop down, too.

     

    Thursday, May 28, 2009 12:09 AM
  • simple datatable.column.add and then add relationship constraints including ID relationship and FK.
    I do not think more than this is required in your scenario.
    // Adding a column using the constructor
    DataColumn myColumn = new DataColumn("ID", typeof(System.Int32));
    // Adding a column using a DataTable
    productsTable.Columns.Add("ID", Type.GetType("System.Int32")) ;

    Constraints is an instance of the System.Data.ConstraintCollection class,
    and is a container for zero or more System.Data.ForeignKeyConstraint
    and/or System.Data.UniqueConstraint objects. The former define the action
    to be taken on a column in a primary key–foreign key relationship when a row
    is updated or deleted, while the latter are used to enforce the rule that all values
    in a given column should be unique.
    //Create a relation between Customers and Orders.
    myDataSet.Relations.Add("CustomersToOrders",
    myDataSet.Tables["Customers"].Columns("CustomerID"],
    myDataSet.Tables["Orders"].Columns["CustomerID"]) ;

    OR
    // Create two DataColumns
    DataColumn parentColumn ;
    DataColumn childColumn ;
    // Set the two columns to instances of the parent and child columns
    parentColumn = myDataSet.Tables["Customers"].Columns["CustomerID"] ;
    childColumn = myDataSet.Tables["Orders"].Columns["CustomerID"] ;
    // Create a new DataRelation object
    DataRelation customersToOrders = New DataRelation("CustomersToOrders",
    parentColumn, childColumn) ;
    // Add the DataRelation to the DataSet.Relations collection
    myDataSet.Relations.Add(customersToOrders) ;

    AND GET the rows
    foreach (DataRow xRow in BookDataSet.Tables["Books"].Rows)
    {
    Console.WriteLine(xRow["Title"]);
    // Obtain child rows using the KeyTitleRef relation
    foreach (DataRow zRow in xRow.GetChildRows("KeyTitleRef"))
    {
    Console.WriteLine(" {0}", zRow["Rating"]);
    }
    }

    PK
    Thursday, May 28, 2009 7:22 AM
  • Thank you for the reply, but creating columns is not the concern, it is populating them.  I would prefer *NOT* to have to iterate through the whole list after calling Adapter.Fill(), but it looks like that is the only real solution.
    Thursday, May 28, 2009 1:49 PM
  • Are the IResponseProps that you get back always primitive types like string, int, etc, or are they every more complex? What is the source of this data?

    I think what you could do is have one column that is an object. But you will have to deserialize the instance yourself since I think you are the only one who knows how this works. You'll have to write the adapters to conform to this. I'm not sure I have a better solution than as you suggested.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 5:16 PM
  • IResponseProps will derive from either ISerializable  or IXmlSerializable, so it will be easy to save it as a blob and restore it as a blob.  Of course all thing things in it will also need to be serializable, too.

    Please remember, all I am looking for is how do I tap into the Adapter and/or Table to serialize and deserialize the object as it is saved to and read from the database?  I know how to do the serialization, I just don't know how to hook into the Microsoft code to do the serialization at the right time.  As it stands now, I will simply interate through the whole collection before the update to serialize it all out and right after Fill to deserialize the blob.  I am hoping I can hook and event, overload a function, or something so that I can do it during the Update and Fill.

    Sam
    Friday, May 29, 2009 5:36 PM
  • Hey Sam,

    So I was reviewing DataAdapter documentation more and its really all about hooking up to Relational Data sources, whether they are Sql, Oledb, Odbc, etc. For your situation in that you have things serialized in this way I'm not sure going through an adapter makes sense or optimizes anything really. What is producing these serialized pieces anyway? SInce its a customized serialization it will have to be customized hookup as well.

    Thanks
    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 6:05 PM
  • Chris,

    I am the first to admit that through the last 14 years of being a professional Windows developer (C++ and now C#.Net)  I have not been working with RDBMS very much at all.  I have worked a lot with XML, but not the traditional SQL based databases.

    I guess I am simply looking for the same functionality in ADO.Net that one has in serialization objects.  When serializing an object, one has the ability to simply add the attribute and let the runtime do the serialization, or you can implement ISerialization and implement the serialization yourself so that if you need to tweak the data before and/or after the serialization process, you can, on an object by object bases. 

    I am looking for the same concept in ADO.Net.  An equivalent might be a method in the DataTable that can be overloaded, like the BindingList.AddNewCore method, which would allow each row to be customized on entry to the table from the data source.  In my case, this is where the actual serialization and deserialization of the blob would happen.

    Sam

    Friday, May 29, 2009 6:33 PM
  • Hey Sam,

    I understand what you are talking about for serialization. DataSet itself implements ISerializable and IXmlSerializable. I'm unsure of the source of your data before you serialize it, but perhaps you can just put it in a DataSet and serialize and pass the DataSet, instead of passing the objects themselves. Is this beign passed over remoting?

    For the IResponseObject you'll have to just write a converter and fill the rows by hand. Then what I would do to pass the information back after people have edited a grid or made change. I would just call DataSet.GetChanges() and then you can package up the changes into you IResponseObject form and send it back.

    Not good solutions here.

    Chris
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 7:55 PM
  • Chris,

    I really don't see what relevence there is in knowing the source of the blob before serialization.  I think the basic concept is simple:

    In C# code the IResponseObject is simply metadata that OTHER things know about and will use, it pertains to a particular Question Item, but the Question Item is just data, so it in and of itself doesn't need to know any details about this mysterious IResponseObject.

    class QuestionItem
    {
        public int CategoryId { get; set; }
        public string Name { get; set;}
        public string Question { get; set; }
        public IResponseObject { get; set; }
    }
    

    There is a collection of these Question Items:

    BindingList<QuestionItem> questionItems;

    I would like to store all these questionItems in a table in the database.  How does one go about handling the fact that IResponseObject can be one of many different types?  I know it is possible to create OO style database tables by having a base table equivalent to the base class.  Than each derived table would have a second ID field back to base table row.  In the end, it is ideal for when you need reporting and/or setup relationships, but it is a lot of work. 

    All the IResponseObject has to do with is HOW the response is displayed to the user and the constraints on which they can enter information.  Example:  one might be an integer with a min value of 0 and max of 5.  Another one might be a text that must meet a regular expression.  Another one might be a date where only past dates can be entered.

    None of this is ever relevant to reporting, nor are there any relationships that need to be kept, it is real basic raw metadata that is simply a blob to the QuestionItem and the database, but VERY important as a C# object to the code using the QuestionItem.

    So what I have done is in the database defined the IResponseObject as a simple BLOB.  So what I am thinking is this:

    1: Create a typed DataSet via the DB providers DDEX for the QuestionItem.  This of course will have a string for the blob.
    2: Somehow add a new field to the QuestionItemTable in the typed dataset that would be of type IResponseObject.  I cannot seem to do that in the GUI, so I guess it needs to happen via code?
    3: Somehow add some code somewhere that right after each QuestionItemTableRow is created during the Fill(), take the contents of the blob string and create the object that will be stored in the new IResponseObject field.  Then do the reverse when Updating/Inserting to the database.

    The sole reason I mentioned Serialization in my last post is because when using serialize, which I am **NOT**, functionality as I described in step #3 is possible on an object by object bases by implementing the correct functions.  From all that I have gathered, this is not possible with a ADO.Net.  My ONLY option is the following:

    Once the Adapter is done iterating through the whole result set and returned from the Fill() method, I will have to iterate through it a second time creating each IResponseObject.  Same thing when calling update, before doing so, I will have to iterate through it first serializing things out to the blob.

    For the record, what exactly is a IResponseObject is not defined yet and I do expect, with time, it to grow to be more than just one class, but a nesting of multiple classes.  Again, to the QuestionItem it is simply metadata.

    I guess what I am really shocked about is that the concept of storing an object as a blob in the database field like this is such a foreign idea to ADO.Net.  I think in the end I am simply designing thing wrong and there is a much better way, fore I know the core folks at Microsoft are far smarter than I am, I just don’t seem to explain myself correctly, hence this long post :)

    Sam

    Friday, May 29, 2009 8:37 PM
  • Hey Sam,

    Now I understand the schema. So in the database you have a table that has these fields right?

    int CategoryId
    string Name
    string Question
    varient Response

    Here is a link to Sql Server types and their CLR equvilant
    http://msdn.microsoft.com/en-us/library/ms131092.aspx

    You will notice that object maps to sql_varient. So what I would do is simply have a the Response a sql_varient. Then in DataSet the column when its brought through ddex should be mapped to object.

    Then I don't think you need to do anything complex. You should be able to drag and drop the table over and the adapters that are created should do the work of the conversion.

    So try this, create a table in a database as a sql_varient. Then create a data connection in VS, drag and drop this to the DataSet designer. When you look at the Response datacolumn you will see that its DataType is object. Everything should just work.

    Sorry it took so long to figure this out, I was confused where the data was coming from. Now if the database is not sql server, then what I would do is review the types for that database and see what they map too for CLR types. Then you can find the type that you need to specify for the column.

    This should help, let me know.

    Thanks
    Chris

    Thanks
    Chris



    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, May 29, 2009 9:53 PM