Frustrated trying to use "pure" VS.NET to access database properties

  • Thread starter Thread starter Sam Malone
  • Start date Start date
S

Sam Malone

I am trying to get details from a database. I really want to use only native
VS.NET managed code "stuff" (just cuz I want to) and avoid any interop
stuff. So, I'm trying to do this without using any ADODB or ADOX stuff.
What I'm trying to do is retrieve all the properties of all the components
of a database. The GetSchema method goes a long way but (so far) I'm missing
how to get the default value of a column. In the table that's returned using
the GetSchema ("Columns", res) method there "appears to be" a column that
someone intended to be populated with the default value (it's called
COLUMN_DEF) but I have never seen anything in it (and I KNOW the database
definition specifies one). The database in question (in this case) is a
MySQL database but I'm trying to get this program to be very generic so that
no matter what the source database is, I'd get the same (and correct)
results.

Any thoughts?
 
Sam Malone said:
I am trying to get details from a database. I really want to use only
native VS.NET managed code "stuff" (just cuz I want to) and avoid any
interop stuff. So, I'm trying to do this without using any ADODB or ADOX
stuff.
What I'm trying to do is retrieve all the properties of all the components
of a database. The GetSchema method goes a long way but (so far) I'm
missing how to get the default value of a column. In the table that's
returned using the GetSchema ("Columns", res) method there "appears to be"
a column that someone intended to be populated with the default value
(it's called COLUMN_DEF) but I have never seen anything in it (and I KNOW
the database definition specifies one). The database in question (in this
case) is a MySQL database but I'm trying to get this program to be very
generic so that no matter what the source database is, I'd get the same
(and correct) results.

Any thoughts?
Click to expand...

Yeah, GetSchema is useful but only goes so far. If you are using SQL
Server, then you can use the sp_columns stored procedure to list the column
schema, which is more indepth than GetSchema, but also can be cryptic if you
aren't used to it :)

If you have SQL Query Analyzer, you can use an index search in it's help
documentation to search for sp_columns to review what each field in the
results collection represents, and map from there :)

Cheers!

Mythran
 
Mythran said:
Yeah, GetSchema is useful but only goes so far. If you are using SQL
Server, then you can use the sp_columns stored procedure to list the
column schema, which is more indepth than GetSchema, but also can be
cryptic if you aren't used to it :)

If you have SQL Query Analyzer, you can use an index search in it's help
documentation to search for sp_columns to review what each field in the
results collection represents, and map from there :)

Cheers!

Mythran
Click to expand...

As mentioned in my original post, the database in question (for now) is a
MySQL database but I'm "trying" to write this so it's generic and the
backend database is not the deciding factor in what info I can get from it.
I know I could (and will if I "HAVE" to) revert to ADODB/ADOX but (as much
for the challenge as anything), I'm trying to do it using ONLY native VS.NET
2005 capapbilities and no interop stuff.
 
Check into the Information_Schema views, in particular the
Information_Schema.Columns view.
 
Sam Malone said:
As mentioned in my original post, the database in question (for now) is a
MySQL database but I'm "trying" to write this so it's generic and the
backend database is not the deciding factor in what info I can get from
it.
I know I could (and will if I "HAVE" to) revert to ADODB/ADOX but (as much
for the challenge as anything), I'm trying to do it using ONLY native
VS.NET 2005 capapbilities and no interop stuff.
Click to expand...

Yeah, of course, sorry, I just have a habit of not re-reading the op before
posting and always add something that shouldn't be there. Unfortunately,
ADO.Net doesn't provide all the information for a specific database.
Instead, it does what it can to provide some information for the a lot of
the dbms' out there and at that, it provides generic support (generic isn't
bad ... just not the *most* efficient for a particular dbms).

HTH,
Mythran
 
Shoot, I didn't notice you were using MySQL. Please ignore my post RE:
Information_Schema, that is just in Microsoft SQL. And it's really a
pretty from end to what is return by the sp_columns stored proc.
 
Sam,

In my idea gave Mythran you only the clever advice to get those using SQL
procedures. The results of that you can get forever get with
command.ExecuteScalar

Cor
 
Shoot, I didn't notice you were using MySQL. Please ignore my post RE:
Information_Schema, that is just in Microsoft SQL. And it's really a
pretty from end to what is return by the sp_columns stored proc.
Click to expand...

Well, I'm not the only one to do that, w00t! :) Made me feel a little
better :) heh

Mythran
 
My apologies, Cor, I do know you're trying to help and that English isn't
your first language but this time I don't understand what you've written.
Any chance you (or someone else) could try again???

Thanks
 
Shoot, I didn't notice you were using MySQL. Please ignore my post RE:
Information_Schema, that is just in Microsoft SQL. And it's really a
pretty from end to what is return by the sp_columns stored proc.
Click to expand...

Actually the INFORMATION_SCHEMA views are an ANSI standard and should be
supported in many DBMS. I know first hand that the latest version of MySql
implements them, and I'm pretty sure that the default value gets included
within the data within INFORMATION_SCHEMA.COLUMNS.

I recently created a MySql version of some SQL Server-based code that read
the structure of the database. Because I'd used the INFORMATION_SCHEMA views
in SQL Server, the code remained virtually unchanged. I'm a big fan of
these.

More info here:

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
 
Oenone said:
Actually the INFORMATION_SCHEMA views are an ANSI standard and should be
supported in many DBMS. I know first hand that the latest version of MySql
implements them, and I'm pretty sure that the default value gets included
within the data within INFORMATION_SCHEMA.COLUMNS.

I recently created a MySql version of some SQL Server-based code that read
the structure of the database. Because I'd used the INFORMATION_SCHEMA
views in SQL Server, the code remained virtually unchanged. I'm a big fan
of these.

More info here:

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
Click to expand...
Thanks, I'll have a look and see what I find.
 
Can you tell me what you don't understand in my message.

There is as typo two times wrongly written get, but that can in my idea not
be the reason that you don't understand what I wrote.

Cor
Sam,

In my idea gave Mythran you the clever advice to get what you need by using
SQL
procedures.

You can forever get the results of those with the AdoNet instruction
command.ExecuteScalar

Cor
 
Cor Ligthert said:
Can you tell me what you don't understand in my message.

There is as typo two times wrongly written get, but that can in my idea
not be the reason that you don't understand what I wrote.

Cor
Sam,

In my idea gave Mythran you the clever advice to get what you need by
using SQL
procedures.

You can forever get the results of those with the AdoNet instruction
command.ExecuteScalar

Cor
Click to expand...

Just like all multi-cultural communications, sometimes you have to learn the
subtle differences in words that others write in order for you to understand
them. Cor does a good job of communicating given a language barrier most
people try to avoid. But, if you don't understand what he wrote as
commented above, I'll rephrase what he did write :) Hope I do not offend
you, Cor ... just trying to defend ya ;)

From Cor, paraphrased:

__

Can you tell me what you don't understand in my message?

There is a typo two times written wrong as "get", but that can not, in my
opinion, be the reason that you don't understand what I wrote.

Cor
Sam,

In my opinion, Mythran (that's me!) gave you clever advice to get what you
need by using SQL procedures. You can "always" get the results of those
with the ADO.Net command, command.ExecuteScalar.

Cor

__

Once again, I'm not trying to offend...just going out on a limb here trying
to bridge a small mis-understanding gap between two parties ;)

HTH,
Mythran
 
oops i didn't see this thanks!

anywhere else to get a full list of these ansi-reccomended schema
tables?
im pretty sure that oracle and the main players follow these

i was just pretty darn perturbed today when i discovered that in the
sys.object table that they renamed the field 'xtype' to be type

id just love to have a list of these for if and when i need to discover
more of a schema on a new rdbms
 
OK - Let me try this...
Here's what I "think" you mean.

"In my opinion, Mythran gave you good advice. He advised that you can get
what you need by using SQL procedures"
"You can always get the results of those procedures using the ADO.NET
command.ExecuteScalar instruction"

Am I correct?? (and thanks again for your assistance)

However the backend database (in my case) is a MySQL database (but it could
be anything, even MS Access) so will this suggestion apply???
 
Thanks for your help too, Mythran and believe me I was not trying to be
critical of Cor either. He DOES do a fantastic job helping out here and I
recognize the language issue. I tried (sometimes I'm not too good at it) to
be tactful in requesting he try again and if I wasn't sucessful (in being
tactful), I do apologize.
 
Back
Top