User-defined types in Strongly Typed DataSets

[Guest]

Hi

Is it possible to use user-defined types and not only system types when generating a typed DataSet?
Specifically, Is it possible to specify a user-defined type, capable of implicit conversions to/from the corresponding DB type in the XSD schema, so it will be there in the generated typed DataSet

Thank you.

 

[Miha Markic]

Nope.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,

Is it possible to use user-defined types and not only system types when generating a typed DataSet?
Specifically, Is it possible to specify a user-defined type, capable of

implicit conversions to/from the corresponding DB type in the XSD schema, so
it will be there in the generated typed DataSet?

 

[Wayne Bradney]

Hi David,

It's nice to find someone else that's thinking along these lines. Not
everyone has SQL-92 data, or binds directly to relational database
tables, after all.

Is it possible to use user-defined types and not only system types when generating a typed DataSet?

I've found that yes, this is possible - I'm doing it in my
application. It's not easy, but if you can get your DataSet to reflect
real types you'll get a big payoff further down the line where your
data-bound controls can start looking at the type information through
reflection and acting accordingly. It's actually a shame MS didn't go
the extra mile to cater for this by default, but maybe we'll see it in
a future framework release.

Specifically, Is it possible to specify a user-defined type, capable of implicit conversions to/from the corresponding DB type in the XSD schema, so it will be there in the generated typed DataSet?

The answer to this is no, not directly, if you intend to rely on the
standard .NET DataAdapters. But if you're willing to write your own...

If you're like me you started out by editing the dataset schema file
directly, and by trial-and-error you found out about the DataType
attribute of the msdata namespace
("urn:schemas-microsoft-com:xml-msdata"). For example:

<xs:element name="Key" msdata:Caption="ID" msdata:ReadOnly="true"
msdataataType="MyNamespace.MyDataType" />

I can't find any documentation on this attribute, but as it turns out
you can put virtually any type you like in here, and the
MSDataSetGenerator _should_ generate a strongly-typed dataset that
reflects that type. The generated DataSet, after all, just contains a
bunch of managed code property getters/setters that give access to
your data - why shouldn't they return real types instead of only
SQL-92-bindable types? However, if you try this, you'll probably find
that the MSDataSetGenerator chokes on any type that's not part of the
Framework, or indeed on any type that does not yet exist or is not
currently loadable.

I punted on this whole issue by simple replacing the
MSDataSetGenerator with one of my own that does almost exactly the
same thing as the original, but isn't so picky about the types it's
given. If you mis-type a name or pick a type that doesn't exist,
you'll get compile-time errors. Also don't expect any help from the
IDE to edit your schema using the drag-and-drop editor - you're going
to be maintaining these schemas in the text editor.

What this gets you is a generated class representing your
strongly-typed dataset that does everything the MSDataSetGenerator one
would do, except it's truly strongly-typed because you've got the
_real_ types in there. Your types.

Now you've got another problem, however. The SqlDataAdapter doesn't
know anything about your types, or how to construct them from the data
that's in the database, so it'll certainly choke if you try to Fill
your DataSet with one. So - you guessed it - you write your own
DataAdapter:

public class MyDataAdapter : DataAdapter
{
....
}

This can be hard or easy, depending on where your real data is. In my
case I _have_ to write my own adapter anyway because my data comes
from a middle-tier of legacy COM objects, not directly from a SQL
database. In my adapter I'm just reading lists of objects from the
server and building up the tables of the DataSet but in this case I
have control over how that happens and I can perform whatever
conversions are necessary from the data objects to the DataSet. This
also gives me a layer in which I can re-shape or re-map data however I
like if the database guys or the server guys haven't done a
particularly good job of object design (compound keys can be
simplified in this way). If you're accessing a DB directly, you need
to get into DataReaders, Connections and Commands.

So far so good, but if you rely on DataSet.WriteXml and
DataSet.ReadXml for serialization, you'll find that the serialization
algorithm will serialize your types by just calling ToString() on your
object, so you'll need to address that if necessary. I haven't found a
need for this yet, so you're on your own there. Again, it's a shame
that the serialization algorithm doesn't look for some interface (eg.
"XmlSerializable") and call ToXml(), FromXml() to service custom
types.

That seems like a lot of work to get real types in a DataSet, but the
payoff comes when you bind to controls. Everything will work when you
bind to a standard control like the DataGrid, and your custom types
will be rendered as the ToString() of the object, which is probably
exactly what you want. The real payoff comes when you derive your own
control from the standard one, and add some intelligence based on your
custom column types. In my application I have a control derived from a
third-party data-bound grid control that looks at the schema of the
bound DataSet and configures the columns accordingly, eg:

- Correct column headings (from msdata:Caption instead of the element
name)
- Editability of columns (based on msdata:ReadOnly)
- For date-type columns my grid can pop-up a date picker to edit the
cell and the grid can validate accordingly
- I have a Type called User which represents a user in my system -
those columns can present a drop-down of the user list
- I have many columns that represent enumerations - the correct
dropdown can be configured for each column
- I can distinguish between Money types and regular Floats and
validate them accordingly, or present a picker that allows the user to
pick the currency at the same time as the amount

All of this is centralised in the Control - my application just
performs the filling and binding - the grid is smart enough to infer
what it needs from the types in the Schema. This is crucial for an
application that has hundreds of grids across dozens of screens - you
don't want to be writing this code over and over again.

 

[Guest]

Hi Wayne

Thank you very much for such a detailed answer. I have started a thread in the Whidbey ADO.NET newsgroup and added a post that points to our correspondence here, along with some conclusions. For sure you have something to add, so here's the link
http://communities.microsoft.com/ne...rosoft.private.whidbey.adodotnet&iPageNumber=

Unfortunately, I did not intend to re-write the data adapters, but use the existing SqlDataAdapter
But anyway, is there a simple way to do it

Thanks a lot.

 

[Wayne Bradney]

David,

Implementing your own DataAdapter is not as hard as you'd think - it's
actually little more than implementing the public DataAdapter methods.
Here's an example of the ones I have - your mileage may vary if you're
accessing a real database directly. Some of the names have been
changed to protect the innocent...

using System;
using System.Data;
using System.Data.Common;

namespace MyNS {
/// <summary>
/// Summary description for MyDataAdapter.
/// </summary>
public class MyDataAdapter : DataAdapter
{
public MyDataAdapter() {
}

#region <DataAdapter implementation>
public override int Fill(DataSet dataSet) {
int rows = 0;

MyDataSet mds = (MyDataSet) dataSet;
mds.Clear();

/* ... Instantiate a interop-wrapped COM
object
and call a method that retrieves a
list of objects ... */

for (int i = 0; i < list.Count; i++) {
MyDataSet.MyRow mr = mds.MyTable.AddMyRow(
MyColumn1,
MyColumn2,
...
);
rows++;
if (this.AcceptChangesDuringFill) mr.AcceptChanges();
}

return rows;
}

public override DataTable[] FillSchema(DataSet dataSet, SchemaType
schemaType) {
/* Strongly typed DataSet - no need to implement this */
return dataSet.Tables;
}

public override IDataParameter[] GetFillParameters() {
/* Implement this if you want to filter using
the actual database query - N/A to me */
return null;
}

public override int Update(DataSet dataSet) {
int rows = 0;
MyDataSet mds = (MyDataSet) dataSet;
MyDataSet.MyRow dsMy;
DataTable dt = mds.MyTable;

DataTable dtModified = dt.GetChanges(DataRowState.Modified);
if (dtModified != null) {
foreach (DataRow dr in dt.Rows) {
dsMy = (MyDataSet.MyRow) dr;
/* ... UPDATE your datasource
with the new information from this row ... */
rows++;
}
}

DataTable dtDeleted = dt.GetChanges(DataRowState.Deleted);
if (dtDeleted != null) {
foreach (DataRow dr in dt.Rows) {
dsMy = (MyDataSet.MyRow) dr;
/* ... DELETE this row from
your datasource ... */
rows++;
}
}

DataTable dtAdded = dt.GetChanges(DataRowState.Added);
if (dtAdded != null) {
foreach (DataRow dr in dt.Rows) {
dsMy = (MyDataSet.MyRow) dr;
/* ... INSERT this row into
your datasource ... */
rows++;
}
}

return rows;
}

#endregion
}
}

If you're accessing a real database, you'll probably have to take care
of errors when updating, deleting, etc, and there may be some
connection-pooling issues you'll have to deal with, depending on your
application. Like I said, I don't access the DB directly so my
DataAdapters are just extra wrappers for the COM objects I already
have, and are therefore quite simple.

The custom DataSetGenerator plug-in is not such an innovation, either
- in fact I cheat by first shelling out to the original
MSDataSetGenerator (xsd.exe), and then running a set of substitutions
on it's output to refine the output types. The sequence of event is
roughly:

1. Pre-process the .xsd file, tracking all of the msdataataType
declarations and build up a cache of substitution pairs.
2. Replace all these msdataataType declarations with xs:string, so
that xsd.exe doesn't choke.
3. Pass the resulting schema to xsd.exe
4. Load the resulting .cs file and use the cache to perform the right
substitutions.
5. Write the file back out to the final .cs file.

If you're at all interested I could send you the code for this tool,
but I'll warn you first that it's functional but not pretty!

Regards,
WMB