FillSchema

[Alex K.]

Hi all

I noticed that if Select stmt returns dataset containing two tables,
executing FillShema only populates schema info for first table only.

Simple test: (.NET 2.0)

....
string strSQL = "Select * from tableA select * from tableB"
OleDbConnection cn = new OleDbConnection(strConnection);
OleDbCommand cmd = new OleDbCommand(strSQL);
cmd.Connection = cn;
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
int ret = adapter.Fill(ds);
adapter.FillSchema(ds, SchemaType.Mapped, "Table");
adapter.FillSchema(ds, SchemaType.Mapped, "Table1");

Everything is working fine, except that for second table, shema info is not
set. In particular, MaxLength attribute of all string fields in second table
is always -1. In the first table, MaxLength shows real max length of a field.

Is there any way to fill schema for second table (and other tables if any)?

Thank you
Alex

 

[Alex K.]

(just to revive this thread
Hey, C# experts, where are you?...

 

[Liz]

string strSQL = "Select * from tableA select * from tableB"

where's the semi-colon?

string strSQL = "SELECT * FROM tableA; SELECT * FROM tableB"

I would have thought you'd get a SQL Exception ....

 

[Peter Duniho]

(just to revive this thread
Hey, C# experts, where are you?...

For what it's worth, I didn't see anything in your question that had
anything specific to do with C#.

It appears to be primarily a database question. Granted, there are
several C# experts following this newsgroup who _also_ are database
experts, and they do in fact sometimes answer database questions here.

But if you've got a database question and you really really need an
answer, you're probably better off posting the question to a database
newsgroup (even one that still is specific to .NET, if appropriate).

Pete

 

[Alex K.]

Peter,

the question is about how C# function FillSchema works, not about how
database works. On database side, I have no problems at all - of course, both
db tables have all their properties in place.

When you create a dataset using Select stmt, and then look into datatables
that are contained in that dataset, you will see that MaxLength property is
not populated unless you execute FillSchema on that dataset.

My question is: why FillSchema call only populates schema properties (e.g.
MaxLength) only for the first datatable in dataset?

Thank you for your response.

 

[Peter Duniho]

the question is about how C# function FillSchema works, not about how
database works.

But there's no such thing as the "C# function FillSchema". Asking "how
the C# function FillSchema works" doesn't make any sense. You might as
well ask "how do I ride a unicorn?"

The .NET class DataAdapter is what has the FillSchema method. It's very
specific to using databases in .NET. Here's the description of the
DataAdapter class from the .NET documentation:

Represents a set of SQL commands and a database
connection that are used to fill the DataSet and
update the data source.

As I said, it's true that there are people participating in this newsgroup
that are experts in databases. But they don't make up the majority of the
people in the newsgroup and you have no reason at all to expect, as your
follow-up implies, that someone who is a "C# expert" would necessarily
have any idea about the answer to your question.

I'm sorry if you don't find that to be useful information. But the fact
is, if you want to get a good answer to your question, you will do better
asking people who typically would have a good answer for it. And that's
the people who are specifically involved in databases, especially those
focused on .NET. The number of those people who would read your question
in this newsgroup is far fewer than in a .NET newsgroup that is actually
specifically about databases.

There's not really anything wrong with posting your question here in the
first place, especially since even though this is ostensibly a C#
newsgroup, we answer all sorts of more general .NET questions here. But
you should not be surprised when a question that's not C#-specific doesn't
elicit a reply, and you should especially not think that a person being a
C# expert in any way suggests, as your follow-up implied, that they should
be expected to know the answer to a non-C# question.

And it's especially silly to argue that your question has nothing to do
with databases. Clearly it is very much about how to use databases from
within .NET. There's a lot more to database usage than just creating a
database.

And no, I don't know the answer to your question. If I did, I'd have
answered it. I'm just trying to help you understand better why you might
not have gotten a reply, and why you may in fact never get a suitable
reply in this newsgroup.

Pete

 

[Liz]

Alex, here's your answer, direct from the docs:

Note
If the FillSchema method of the OleDbDataAdapter object is called for a
command that returns multiple result sets, only the schema information from
the first result set is returned. When returning schema information for
multiple result sets using the OleDbDataAdapter, it is recommended that you
specify a MissingSchemaAction of AddWithKey and obtain the schema
information when calling the Fill method.

Look for the topic:

"Adding Existing Constraints to a DataSet"

Liz

 

[Alex K.]

Thank you, Peter

When we talk about databases, we mean different things. What I call
database, is a SQL Server database, Oracle database, MS Jet database etc.
What you call database is just .NET database layer, database-oriented part of
..NET function set.
Yes, you are right when you are saying that FillSchema question is more
about .NET database layer. But as I told you, it has nothing to do with
databases themselves because not SQL Server, nor Oracle, nor Jet have
function called "FillSChema".

I didn't find any newsgroup specific to .NET database-oriented functions,
except odbcnet which as of now have as many as 10 (ten) postings since June
2004. Not really perfect place to post a question - don't you think? Besides,
I am not using ODBC, those days are long gone.

To follow your advice to post only C# specific questions to this newsgroup
effectively means that 95% of postings here must be posted somewhere else,
because C# itself is nothing more than syntax shell around .NET functions.
(Same as VB.NET).

When readers are posting questions about sockets, graphics or XML, (examples
from current page), they are not asking about C# using your meaning of the
word.
Even when user asks about label alignment -- this is not strictly C#
question as it is more about Windows.Forms. So, your comments about the
location of the FillSchema posting sound completely unfounded. With your
permission or not, I will continue to post here all questions that I may have
about using C# in real world tasks, such as db programming, graphics
programming, internet programming and so on. May be, someday there will be
multiple C# groups in MSDN, but for now, there is only one that is worth
posting in.

Thanks again for you reply.
Alex

 

[Alex K.]

Thank you, Liz

I have read this topic in the HELP. It didn't help much: I tried. When I do
this, it is trying to add UniqueConstraint which causes another problems with
my query (real query, not the example I posted). Error handling routine grows
crazy.

I still hope to find simple and elegant solution to populate schema info in
multi-table dataset ... :-|

Thanks again.
Alex

 

[Liz]

have you queried microsoft.public.dotnet.framework.adonet ? I don't know
anything about the NG but it has the right name ....

 

[Peter Duniho]

Thank you, Peter

When we talk about databases, we mean different things. What I call
database, is a SQL Server database, Oracle database, MS Jet database etc.
What you call database is just .NET database layer, database-oriented
part of .NET function set.

No, it's not "just" that. But yes, I do include the .NET database layer.

Yes, you are right when you are saying that FillSchema question is more
about .NET database layer. But as I told you, it has nothing to do with
databases themselves because not SQL Server, nor Oracle, nor Jet have
function called "FillSChema".

So are you saying that any "C# expert" should be able to answer your
question? If not, I don't see why you are debating this at all. If so,
why does being an expert in C# imply knowledge of database-related topics?

[...]
When readers are posting questions about sockets, graphics or XML,
(examples from current page), they are not asking about C# using
your meaning of the word.

I readily acknowledge the large amount of traffic in this newsgroup that
is theoretically off-topic. My point is not to argue that you shouldn't
have asked your question here, and had you read what I'd written you'd
understand that.

My point _is_ that your impatience with the people in this newsgroup is
ill-placed. While you're free to ask the question here, you have no
reason to expect it would be answered. That's true for any question, of
course, but even more so for something as esoteric as the issue you're
running into.

[...] With your
permission or not, I will continue to post here all questions that I may
have about using C# in real world tasks

Again, you clearly have not bothered to read what I wrote. This isn't
about what you _may_ post here. It's about unreasonable expectations of
what sort of response you might get. Posting your original question
wasn't a problem at all. Acting like a "C# expert" ought to know the
answer to your question is a problem.

Furthermore, it's only a problem inasmuch as it sets you up for
disappointment. I'm not trying to take you task for doing something
wrong, as it seems like you think I am doing. Where does "with your
permission or not" come from? I'm not telling you what you may or may not
do. I'm just trying to be helpful.

If you don't appreciate the help, fine. But I don't see why you feel the
need to argue about what a "database question" is, and there's certainly
no need for the offense you appear to have taken regarding my motives.

Pete