|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Frustrated trying to use "pure" VS.NET to access database propertiesI 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?
Show quote
Hide quote
"Sam Malone" <the_sam_mal***@hotmail.com> wrote in message Yeah, GetSchema is useful but only goes so far. If you are using SQL news:uORtEmPeGHA.3632@TK2MSFTNGP02.phx.gbl... >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? > 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
Show quote
Hide quote
"Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message As mentioned in my original post, the database in question (for now) is a news:%23cWfttPeGHA.1880@TK2MSFTNGP02.phx.gbl... > > "Sam Malone" <the_sam_mal***@hotmail.com> wrote in message > news:uORtEmPeGHA.3632@TK2MSFTNGP02.phx.gbl... >>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? >> > > 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 > 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.
Show quote
Hide quote
"Sam Malone" <the_sam_mal***@hotmail.com> wrote in message Yeah, of course, sorry, I just have a habit of not re-reading the op before news:eDOMjxPeGHA.4912@TK2MSFTNGP05.phx.gbl... > > "Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message > news:%23cWfttPeGHA.1880@TK2MSFTNGP02.phx.gbl... >> >> "Sam Malone" <the_sam_mal***@hotmail.com> wrote in message >> news:uORtEmPeGHA.3632@TK2MSFTNGP02.phx.gbl... >>>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? >>> >> >> 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 >> > > 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. > > > > > 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. <za***@construction-imaging.com> wrote in message
news:1147793376.220222.273880@y43g2000cwc.googlegroups.com... Well, I'm not the only one to do that, w00t! :) Made me feel a little > 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. > better :) heh Mythran za***@construction-imaging.com wrote:
> Shoot, I didn't notice you were using MySQL. Please ignore my post RE: Actually the INFORMATION_SCHEMA views are an ANSI standard and should be > 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. 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 -- (O)enone
Show quote
Hide quote
"Oenone" <oen***@nowhere.com> wrote in message Thanks, I'll have a look and see what I find.news:XBnag.31$1Y6.14@newsfe1-gui.ntli.net... > za***@construction-imaging.com wrote: >> 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. > > 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 > > -- > > (O)enone 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 doesn't the Ansi standard reccomend some common objects like this
though? 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 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 Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:eTbAq3PeGHA.3364@TK2MSFTNGP05.phx.gbl... > 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 > 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 Show quoteHide quote "Sam Malone" <the_sam_mal***@hotmail.com> schreef in bericht news:uZBCr$PeGHA.4720@TK2MSFTNGP03.phx.gbl... > 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 > > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:eTbAq3PeGHA.3364@TK2MSFTNGP05.phx.gbl... >> 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 >> > >
Show quote
Hide quote
"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message Just like all multi-cultural communications, sometimes you have to learn the news:OQVLuQReGHA.3388@TK2MSFTNGP05.phx.gbl... > 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 > 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 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. Show quoteHide quote "Mythran" <kip_potter@hotmail.comREMOVETRAIL> wrote in message news:%23wCWqhReGHA.1324@TK2MSFTNGP04.phx.gbl... > > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:OQVLuQReGHA.3388@TK2MSFTNGP05.phx.gbl... >> 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 >> > > 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 > 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??? Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:OQVLuQReGHA.3388@TK2MSFTNGP05.phx.gbl... > 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 > > > "Sam Malone" <the_sam_mal***@hotmail.com> schreef in bericht > news:uZBCr$PeGHA.4720@TK2MSFTNGP03.phx.gbl... >> 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 >> >> >> "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message >> news:eTbAq3PeGHA.3364@TK2MSFTNGP05.phx.gbl... >>> 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 >>> >> >> > > Check into the Information_Schema views, in particular the
Information_Schema.Columns view. Sam,
Go to dev.mysql.com and download the mysql .net connector. You can use the Show Tables command to list the tables. Use the show columns command to list the columns. I have an example on the vb-tips site with show tables. http://www.vb-tips.com/default.aspx?ID=c6b62715-d07e-4e48-92da-e7603e957de2 http://dev.mysql.com/doc/refman/5.1/en/show-columns.html http://dev.mysql.com/downloads/connector/net/1.0.html Ken ------------------------------- Show quoteHide quote "Sam Malone" wrote: > 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? > > > |
|||||||||||||||||||||||
Other interesting topics