I'm trying to store a lightly filtered copy of a database for offline reference, using ADO.NET DataSets. There are some columns I need not to take with me. So far, it looks like my options are:
SELECT
rows for the DataSetI've deleted the columns' entries in the DataSet designer. WriteXMl
still outputs them, to my dismay. If there's a way to limit WriteXml
's output to typed rows, I'd love to hear it.
I tried to filter the columns out with careful SELECT
statements, but ended up with a ConstraintException
I couldn't solve. Replacing one table's query with SELECT *
did the trick. I suspect I could solve the exception given enough time. I also suspect it could come back again as we evolve the schema. I'd prefer not to hand such a maintenance problem to my successors.
All told, I think it'll be easiest to filter the XML output. I need to compress it, store it, and (later) load, decompress, and read it back into a DataSet later. Filtering the XML is only one more step — and, better yet, will only need to happen once a week or so.
Can I change DataSet
's behaviour? Should I filter the XML? Is there some fiendishly simple way I can query pretty much, but not quite, everything without running into ConstraintException
? Or is my approach entirely wrong? I'd much appreciate your suggestions.
UPDATE: It turns out I copped ConstraintException
for a simple reason: I'd forgotten to delete a strongly typed column from one DataTable. It wasn't allowed to be NULL
. When I selected all the columns except that column, the value was NULL
, and… and, yes, that's profoundly embarrassing, thank you so much for asking.
It's as easy as Table.Columns.Remove("UnwantedColumnName")
. I got the lead from
Mehrdad's wonderfully terse answer to another question. I was delighted when Table.Columns
turned out to be malleable.