How to save/update DataTable when its structure is unknown?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I would like to have a functionality that saves/updates a given DataTable
into the DB while its structure is unknown.
I think of 2 possible solutions:
1) to use SqlCommandBuilder - which is very expensive in the runtime
(performance issues)
2) constructing my own commands - iterating over the DataTable and construct
update, delete and insert commands. In this approach I have a small problem
as I don't know the original sql types of the columns and it seems to be an
equivalent to the first solution either.

Does anybody think/may suggests another solution, approach ?

Thanks in advance,
Uri
 
Depending on what you mean by unknown - it may be easy it may be hard, can
you tell me a little more.
 
Here's a bit of VB.NET code "borrowed" from a sample program called DataEasy on Planet Source Code that should give you some
idea on how to find the Database's Schema (structure).

' Attempt to fill the dataset through the accessDataAdapter

accessDataAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", mytbl)})

'populate the DataSet with existing constraints information from a data source

accessDataAdapter.FillSchema(accessDataSet, SchemaType.Source, mytbl)

' Fill the dataset

accessDataAdapter.Fill(accessDataSet)

'create an instance for a datatable

accessDataTable = accessDataSet.Tables(mytbl)



Of course there is more to it than this bit of code. But, at least you get the idea that it can be done. From here you can build
your SQL commands.

The program DataEasy is written to work with Access databases and is a very nice, "generic" program that really shows how to do
several things.

I have learned quite a bit from it.

james
 
I think of 2 possible solutions:
1) to use SqlCommandBuilder - which is very expensive in the runtime
(performance issues)

2) constructing my own commands - iterating over the DataTable and
construct
update, delete and insert commands. In this approach I have a small
problem
as I don't know the original sql types of the columns and it seems to be
an
equivalent to the first solution either.
Click to expand...

Why do you think that the second option is less expensive than the first?

Or better, what do you think that the commandbuilder does?

I think by the way that when you do a fillschema and than a commandbuilder
one time, that you have a very inexpensive situation.

(I never tried that by the way, however I would not know why not)

I hope this helps?

Cor
 
Hi,
I am receiving the DataTable from other module (I didn't create it).
I know only the Database, DataTable's name and of course I have the
DataTable's data.
I can't assume that I know the DataTable's structure - I can infer it though
during the run time

Thanks,
Uri
 
Why do you think that the second option is less expensive than the first?
Or better, what do you think that the commandbuilder does?
I think by the way that when you do a fillschema and than a commandbuilder
one time, that you have a very inexpensive situation.
(I never tried that by the way, however I would not know why not)
I hope this helps?
Cor
Click to expand...

Hi,
I actually stated that in my opinion the second approach will be equivalent
to the first.
The problem is that I am trying to avoid to fill the schema. I am receiving
the DataTable that was already constructed (maybe even using the fill - I
don't know that) so I am trying to infer its structure in runtime with as
less as possible DB accesses.
Maybe it can't be done without actually accessing at least once to the DB,
that's why I am consulting you guys here :)
Thanks,
Uri
 
Hi,
I actually stated that in my opinion the second approach will be
equivalent
to the first.
Click to expand...

I had seen that however to help you thinking it over, I did not wanted to
write direct, "why are you thinking that you can do it better?

:-)

However you never can do what you want when you don't have the schema
information.

Cor
 
Uri said:
Hi,
I actually stated that in my opinion the second approach will be equivalent
to the first.
The problem is that I am trying to avoid to fill the schema. I am receiving
the DataTable that was already constructed (maybe even using the fill - I
don't know that) so I am trying to infer its structure in runtime with as
less as possible DB accesses.
Maybe it can't be done without actually accessing at least once to the DB,
that's why I am consulting you guys here :)
Thanks,
Uri
Click to expand...

Let me see if I can throw some light. Not to provide a solution but to
explain why there isn't one, and to rant a bit. ;-) You'll never be able to
get _database_ column type info from a DataSet/DataTable because they're
database-independent by definition. OTOH, by definition the Command object
retrieves this info during SELECT query execution. Unfortunately it's not
exported so client code has no way to access it other than indirectly via the
often and justly vilified CommandBuilder, a model of poor OO design. In ADO
of course type info is exported from the RecordSet object, which apparently
suffered from being too useful and went away in ADO.NET. From the traffic on
this newsgroup it's pretty clear that the design team didn't think this one
all the way through. This is mystifying as I can't think of a single real
world database application I've been involved with that doesn't interact with
the schema at runtime.

With regard to your problem, the efficient solution should have been to
retrieve the info from the command object (in lieu of a recordset), which
again has to have the information and has already incurred the hit. Contrary
to the .NET koolaid, it's not inherently inefficient to retrieve this schema,
since you have to do it anyway, but it certainly is if you have to do it
twice. This is all a tail-wagging-dog exercise to serve the toy use case of
compile-time knowledge of the schema. Again, not a scenario that comes up
much in the real applications, certainly not worth bending the design around
in such a tortured manner. Look for reality to intrude somewhere around
ADO.NET 3.x. ;-)

HTH
 
Hi,

Thank you for your response,
After searching a lot in the internet and msdn I have come to the same
conclusion :)
Thank you again for your well explained response it sure helped me,
Uri
 
If the module that gave you the datatable also gave you the CommandText
of the SelectCommand, you would be set.
What are the chances of that?
 
Back
Top