|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can someone please tell me...I am finding the following very frustrating and if someone could just tell me why - I think it would be less frustrating: 1) Why is setting parameters in ADO.NET vendor specific? JDBC has had this vendor neutral since version 1.0. 2) Why no simple vendor independent way to get the value of an auto-generated primary key? This has been a need forever and instead everyone has to implement this on their own. 3) Why no vendor independent way to get all tables, views, & stored procedures in a database and to get all columns in a table/view? OleDb has this but not ADO.NET. 4) Why is DbProviderFactory.CreateDataSourceEnumerator() only provided for SqlServer? The OleDb info is in the registry and the Oracle info is (partially) in the root Oracle config file. 5) Why only 2 clients? Back in the ODBC and OleDb days MS was doing everything it could to have tons of clients shipped with Windows. Now it's SqlServer, Oracle, and the two bridge clients (OleDb & ODBC)? If someone at MS could just explain why... Hi Dave,
I can only answer the last question is that ODBC and OleDb provider still exists in .NET framework 2.0. You might not find it. For other issues, they are design issues. As a support engineer. I don't know how it is designed. Kevin Yu ======= "This posting is provided "AS IS" with no warranties, and confers no rights." David,
I thought that Frans Bouma's product should overcome your problems. At least I would try to contact him in your situation if he does not react in my change of the subject. LLBLGen Pro website: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ I hope this helps, Cor Show quote "David Thielen" <thielen@nospam.nospam> schreef in bericht news:8972ED45-CA94-43C4-B80B-09235E231319@microsoft.com... > Hi; > > I am finding the following very frustrating and if someone could just tell > me why - I think it would be less frustrating: > > 1) Why is setting parameters in ADO.NET vendor specific? JDBC has had this > vendor neutral since version 1.0. > > 2) Why no simple vendor independent way to get the value of an > auto-generated primary key? This has been a need forever and instead > everyone > has to implement this on their own. > > 3) Why no vendor independent way to get all tables, views, & stored > procedures in a database and to get all columns in a table/view? OleDb has > this but not ADO.NET. > > 4) Why is DbProviderFactory.CreateDataSourceEnumerator() only provided for > SqlServer? The OleDb info is in the registry and the Oracle info is > (partially) in the root Oracle config file. > > 5) Why only 2 clients? Back in the ODBC and OleDb days MS was doing > everything it could to have tons of clients shipped with Windows. Now it's > SqlServer, Oracle, and the two bridge clients (OleDb & ODBC)? > > If someone at MS could just explain why... > > -- > thanks - dave > david_at_windward_dot_net > http://www.windwardreports.com > LBLLGen looks very interesting - any comments on how well it works in
practice? I ask because in the java world Hibernate looks great - but the more I used it the less I liked it. Show quote "Cor Ligthert [MVP]" wrote: > David, > > I thought that Frans Bouma's product should overcome your problems. > At least I would try to contact him in your situation if he does not react > in my change of the subject. > > LLBLGen Pro website: http://www.llblgen.com > My .NET blog: http://weblogs.asp.net/fbouma > Microsoft MVP (C#) > ------------------------------------------------------------------------ > > I hope this helps, > > Cor > > > > "David Thielen" <thielen@nospam.nospam> schreef in bericht > news:8972ED45-CA94-43C4-B80B-09235E231319@microsoft.com... > > Hi; > > > > I am finding the following very frustrating and if someone could just tell > > me why - I think it would be less frustrating: > > > > 1) Why is setting parameters in ADO.NET vendor specific? JDBC has had this > > vendor neutral since version 1.0. > > > > 2) Why no simple vendor independent way to get the value of an > > auto-generated primary key? This has been a need forever and instead > > everyone > > has to implement this on their own. > > > > 3) Why no vendor independent way to get all tables, views, & stored > > procedures in a database and to get all columns in a table/view? OleDb has > > this but not ADO.NET. > > > > 4) Why is DbProviderFactory.CreateDataSourceEnumerator() only provided for > > SqlServer? The OleDb info is in the registry and the Oracle info is > > (partially) in the root Oracle config file. > > > > 5) Why only 2 clients? Back in the ODBC and OleDb days MS was doing > > everything it could to have tons of clients shipped with Windows. Now it's > > SqlServer, Oracle, and the two bridge clients (OleDb & ODBC)? > > > > If someone at MS could just explain why... > > > > -- > > thanks - dave > > david_at_windward_dot_net > > http://www.windwardreports.com > > > > > Hi David,
"David Thielen" <thielen@nospam.nospam> wrote in message Because each database is different from others. You could use factory news:8972ED45-CA94-43C4-B80B-09235E231319@microsoft.com... > Hi; > > I am finding the following very frustrating and if someone could just tell > me why - I think it would be less frustrating: > > 1) Why is setting parameters in ADO.NET vendor specific? JDBC has had this > vendor neutral since version 1.0. pattern though (nicely supported in ado.net 2). > Because it doesn't make sense. Again, this should be a part of your logic.> 2) Why no simple vendor independent way to get the value of an > auto-generated primary key? This has been a need forever and instead > everyone > has to implement this on their own. > It has in ado.net 2 (DbConnection.GetSchema method)> 3) Why no vendor independent way to get all tables, views, & stored > procedures in a database and to get all columns in a table/view? OleDb has > this but not ADO.NET. > Don't know but you could use oracle's own managed provider or any other > 4) Why is DbProviderFactory.CreateDataSourceEnumerator() only provided for > SqlServer? The OleDb info is in the registry and the Oracle info is > (partially) in the root Oracle config file. (that supports it). > Should MS cover any database on the planet? You can easily use 3rd party > 5) Why only 2 clients? Back in the ODBC and OleDb days MS was doing > everything it could to have tons of clients shipped with Windows. Now it's > SqlServer, Oracle, and the two bridge clients (OleDb & ODBC)? managed provider. > I am not exactly from MS but I hope my answers will shed some light...> If someone at MS could just explain why... -- Miha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > Yes, but how to to it, e.g. for Access and Oracle? Code for SQL-server>> >> 2) Why no simple vendor independent way to get the value of an >> auto-generated primary key? This has been a need forever and instead >> everyone >> has to implement this on their own. > >Because it doesn't make sense. Again, this should be a part of your logic. > was already given in another post. Tnx - Martin "Martin Aupperle" <newsgro***@PrimaProgramm.de> wrote in message Depends - for the Access you have to issue a separate sql statement ("SELECT news:441fe277.13809687@news.btx.dtag.de... > > >>> >>> 2) Why no simple vendor independent way to get the value of an >>> auto-generated primary key? This has been a need forever and instead >>> everyone >>> has to implement this on their own. >> >>Because it doesn't make sense. Again, this should be a part of your logic. >> > Yes, but how to to it, e.g. for Access and Oracle? Code for SQL-server > was already given in another post. @@Identity" I think) and don't know for sure for Oracle. -- Miha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ On Tue, 21 Mar 2006 11:26:09 GMT, newsgro***@PrimaProgramm.de (Martin Aupperle) wrote:
¤ > ¤ >> ¤ >> 2) Why no simple vendor independent way to get the value of an ¤ >> auto-generated primary key? This has been a need forever and instead ¤ >> everyone ¤ >> has to implement this on their own. ¤ > ¤ >Because it doesn't make sense. Again, this should be a part of your logic. ¤ > ¤ Yes, but how to to it, e.g. for Access and Oracle? Code for SQL-server ¤ was already given in another post. ¤ ¤ Tnx - Martin Oracle doesn't implement identity columns, but a sequence number. You create a sequence for a unique (primary key) column in a Table. Once the sequence has been created you can query for the next sequence number before inserting a new row or include the call to fetch the next sequence number in your SQL statement: CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20; INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_seq.nextval, 'Kraft Foods'); Paul ~~~~ Microsoft MVP (Visual Basic) On Tue, 21 Mar 2006 10:07:08 -0600, Paul Clement
<UseAdddressAtEndofMess***@swspectrum.com> wrote: Show quote >Oracle doesn't implement identity columns, but a sequence number. You create a sequence for a unique Thanks. But how do I get the seqeuence number for a newly inserted>(primary key) column in a Table. Once the sequence has been created you can query for the next >sequence number before inserting a new row or include the call to fetch the next sequence number in >your SQL statement: > >CREATE SEQUENCE supplier_seq > MINVALUE 1 > START WITH 1 > INCREMENT BY 1 > CACHE 20; > >INSERT INTO suppliers >(supplier_id, supplier_name) >VALUES >(supplier_seq.nextval, 'Kraft Foods'); > record back into my program? Its the primary key! Tnx - Martin On Tue, 21 Mar 2006 17:18:45 GMT, newsgro***@PrimaProgramm.de (Martin Aupperle) wrote:
¤ On Tue, 21 Mar 2006 10:07:08 -0600, Paul Clement ¤ <UseAdddressAtEndofMess***@swspectrum.com> wrote: ¤ ¤ >Oracle doesn't implement identity columns, but a sequence number. You create a sequence for a unique ¤ >(primary key) column in a Table. Once the sequence has been created you can query for the next ¤ >sequence number before inserting a new row or include the call to fetch the next sequence number in ¤ >your SQL statement: ¤ > ¤ >CREATE SEQUENCE supplier_seq ¤ > MINVALUE 1 ¤ > START WITH 1 ¤ > INCREMENT BY 1 ¤ > CACHE 20; ¤ > ¤ >INSERT INTO suppliers ¤ >(supplier_id, supplier_name) ¤ >VALUES ¤ >(supplier_seq.nextval, 'Kraft Foods'); ¤ > ¤ Thanks. But how do I get the seqeuence number for a newly inserted ¤ record back into my program? Its the primary key! ¤ ¤ Tnx - Martin ¤ You will have to query for the next sequence number first. Since only a single value is returned use ExecuteOracleScalar (for the Oracle provider) or ExecuteScalar (for OleDb). SELECT supplier_seq.NEXTVAL FROM dual; Then specify the returned value in your INSERT "INSERT INTO suppliers (supplier_id, supplier_name) VALUES (" & supplier_seq_number & ", 'Kraft Foods')" Paul ~~~~ Microsoft MVP (Visual Basic) On Tue, 21 Mar 2006 12:28:39 -0600, Paul Clement
<UseAdddressAtEndofMess***@swspectrum.com> wrote: >You will have to query for the next sequence number first. Since only a single value is returned use OK, I understand now, thanks. >ExecuteOracleScalar (for the Oracle provider) or ExecuteScalar (for OleDb). > >SELECT supplier_seq.NEXTVAL FROM dual; > >Then specify the returned value in your INSERT > >"INSERT INTO suppliers >(supplier_id, supplier_name) >VALUES >(" & supplier_seq_number & ", 'Kraft Foods')" > > AM I right to say that this needs two trips to the database? Right now we are doing it with a SELECT MAX(...) FROM ... and then increment the result. This also needs two trips. Whats the difference then? Martin On Wed, 22 Mar 2006 09:43:02 GMT, newsgro***@PrimaProgramm.de (Martin Aupperle) wrote:
¤ On Tue, 21 Mar 2006 12:28:39 -0600, Paul Clement ¤ <UseAdddressAtEndofMess***@swspectrum.com> wrote: ¤ ¤ >You will have to query for the next sequence number first. Since only a single value is returned use ¤ >ExecuteOracleScalar (for the Oracle provider) or ExecuteScalar (for OleDb). ¤ > ¤ >SELECT supplier_seq.NEXTVAL FROM dual; ¤ > ¤ >Then specify the returned value in your INSERT ¤ > ¤ >"INSERT INTO suppliers ¤ >(supplier_id, supplier_name) ¤ >VALUES ¤ >(" & supplier_seq_number & ", 'Kraft Foods')" ¤ > ¤ > ¤ OK, I understand now, thanks. ¤ ¤ AM I right to say that this needs two trips to the database? Right now ¤ we are doing it with a SELECT MAX(...) FROM ... ¤ and then increment the result. This also needs two trips. Whats the ¤ difference then? Yes, two trips are required if your code requires the value of the sequence number for a subsequent operation. Don't know whether there is any real difference between the methods performance-wise. Paul ~~~~ Microsoft MVP (Visual Basic) On Wed, 22 Mar 2006 08:56:00 -0600, Paul Clement
<UseAdddressAtEndofMess***@swspectrum.com> wrote: > I never worked with "stored procedures" - might they be used to avoid>Yes, two trips are required if your code requires the value of the sequence number for a subsequent >operation. > >Don't know whether there is any real difference between the methods performance-wise. > the two trips? I remember another thread in this NG about this, but I can't find it. Martin On Thu, 23 Mar 2006 08:52:09 GMT, newsgro***@PrimaProgramm.de (Martin Aupperle) wrote:
¤ On Wed, 22 Mar 2006 08:56:00 -0600, Paul Clement ¤ <UseAdddressAtEndofMess***@swspectrum.com> wrote: ¤ ¤ > ¤ >Yes, two trips are required if your code requires the value of the sequence number for a subsequent ¤ >operation. ¤ > ¤ >Don't know whether there is any real difference between the methods performance-wise. ¤ > ¤ ¤ I never worked with "stored procedures" - might they be used to avoid ¤ the two trips? I remember another thread in this NG about this, but I ¤ can't find it. Yes, you can use a stored procedure, although I don't think I've ever attempted to return a value (such as a sequence number) from one that is performing an INSERT. Paul ~~~~ Microsoft MVP (Visual Basic) Really? I do it all the time. :)
Northwind Employees table, EmployeeID is an IDENTITY column: CREATE PROCEDURE [dbo].[daab_AddEmployees] ( @EmployeeID int = NULL OUTPUT, @LastName nvarchar(20), @FirstName nvarchar(10), @Title nvarchar(30) = NULL, @TitleOfCourtesy nvarchar(25) = NULL, @BirthDate datetime = NULL, @HireDate datetime = NULL, @Address nvarchar(60) = NULL, @City nvarchar(15) = NULL, @Region nvarchar(15) = NULL, @PostalCode nvarchar(10) = NULL, @Country nvarchar(15) = NULL, @HomePhone nvarchar(24) = NULL, @Extension nvarchar(4) = NULL, @Photo image = NULL, @Notes ntext = NULL, @ReportsTo int = NULL, @PhotoPath nvarchar(255) = NULL ) AS BEGIN SET NOCOUNT ON DECLARE @Err int INSERT INTO [Employees] ( [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Photo], [Notes], [ReportsTo], [PhotoPath] ) VALUES ( @LastName, @FirstName, @Title, @TitleOfCourtesy, @BirthDate, @HireDate, @Address, @City, @Region, @PostalCode, @Country, @HomePhone, @Extension, @Photo, @Notes, @ReportsTo, @PhotoPath ) SET @Err = @@Error SELECT @EmployeeID = SCOPE_IDENTITY() RETURN @Err END Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:nfd522djm3bt4t1e8ribotlc95ll6j9gpc@4ax.com... > On Thu, 23 Mar 2006 08:52:09 GMT, newsgro***@PrimaProgramm.de (Martin > Aupperle) wrote: > > Yes, you can use a stored procedure, although I don't think I've ever > attempted to return a value > (such as a sequence number) from one that is performing an INSERT. > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) On Thu, 23 Mar 2006 22:27:27 -0500, "Matt Noonan" <nore***@noreply.com> wrote: ¤ Really? I do it all the time. :)¤ Yes, I've never had to do it, but I didn't say you couldn't. ;-) He was asking about Oracle though so you're going to have to change your code a bit. ;-) Paul ~~~~ Microsoft MVP (Visual Basic) Personally I try to avoid stored procedures so our app can be easily moved
between database vendors. Show quote "Paul Clement" wrote: > On Thu, 23 Mar 2006 22:27:27 -0500, "Matt Noonan" <nore***@noreply.com> wrote: > > ¤ Really? I do it all the time. :) > ¤ > > Yes, I've never had to do it, but I didn't say you couldn't. ;-) > > He was asking about Oracle though so you're going to have to change your code a bit. ;-) > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) > Which is a difficult challenge, even when you're talking about Access and
SQL Server. Getting schemas to match between vendors is the primary obstacle, and it makes writing portable parameterized queries difficult. Even with the Enterprise Library... :-) Show quote "David Thielen" <thielen@nospam.nospam> wrote in message news:B8B75FF2-98B2-4B2B-95E9-B3B15F6FF4A7@microsoft.com... > Personally I try to avoid stored procedures so our app can be easily moved > between database vendors. > > -- > thanks - dave > david_at_windward_dot_net > http://www.windwardreports.com Not a problem! ;-)
BEGIN SELECT EMPSEQ.NEXTVAL INTO :EMPLOYEE_ID FROM DUAL; INSERT INTO EMPLOYEES (EMPLOYEE_ID, etc.) VALUES (:EMPLOYEE_ID, etc.); END Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:2mv722d97ku14gljp3illbkkcb6j2oukma@4ax.com... > On Thu, 23 Mar 2006 22:27:27 -0500, "Matt Noonan" <nore***@noreply.com> > wrote: > > ¤ Really? I do it all the time. :) > ¤ > > Yes, I've never had to do it, but I didn't say you couldn't. ;-) > > He was asking about Oracle though so you're going to have to change your > code a bit. ;-) > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) Hi;
First off thanks for your replies. A couple of comments. 1) JDBC does manage this - it does not need to be vendor specific. JDBC does this by always using a ? for vars and all vars are set using an index (which ?). 2) I still think if you and I and everyone else are implementing this in our logic then by definition it belongs in the library. 3) thank you - will check this out. 4) I know - but it sort of kills the idea behind DbProviderFactory.CreateDataSourceEnumerator() that it only works for some (SqlServer only) providers. 5) true. Show quote "Miha Markic [MVP C#]" wrote: > Hi David, > > "David Thielen" <thielen@nospam.nospam> wrote in message > news:8972ED45-CA94-43C4-B80B-09235E231319@microsoft.com... > > Hi; > > > > I am finding the following very frustrating and if someone could just tell > > me why - I think it would be less frustrating: > > > > 1) Why is setting parameters in ADO.NET vendor specific? JDBC has had this > > vendor neutral since version 1.0. > > Because each database is different from others. You could use factory > pattern though (nicely supported in ado.net 2). > > > > > 2) Why no simple vendor independent way to get the value of an > > auto-generated primary key? This has been a need forever and instead > > everyone > > has to implement this on their own. > > Because it doesn't make sense. Again, this should be a part of your logic. > > > > > 3) Why no vendor independent way to get all tables, views, & stored > > procedures in a database and to get all columns in a table/view? OleDb has > > this but not ADO.NET. > > It has in ado.net 2 (DbConnection.GetSchema method) > > > > > 4) Why is DbProviderFactory.CreateDataSourceEnumerator() only provided for > > SqlServer? The OleDb info is in the registry and the Oracle info is > > (partially) in the root Oracle config file. > > Don't know but you could use oracle's own managed provider or any other > (that supports it). > > > > > 5) Why only 2 clients? Back in the ODBC and OleDb days MS was doing > > everything it could to have tons of clients shipped with Windows. Now it's > > SqlServer, Oracle, and the two bridge clients (OleDb & ODBC)? > > Should MS cover any database on the planet? You can easily use 3rd party > managed provider. > > > > > > If someone at MS could just explain why... > > I am not exactly from MS but I hope my answers will shed some light... > > -- > Miha Markic [MVP C#] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > > "David Thielen" <thielen@nospam.nospam> wrote in message ? sucks. Imagine a large statement full of parameters.news:7DAE9CD5-440B-4210-8041-578CF4DA3957@microsoft.com... > Hi; > > First off thanks for your replies. A couple of comments. > > 1) JDBC does manage this - it does not need to be vendor specific. JDBC > does > this by always using a ? for vars and all vars are set using an index > (which > ?). > Databases are too different IMO for handling this from ado.net.> 2) I still think if you and I and everyone else are implementing this in > our > logic then by definition it belongs in the library. -- Miha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > ? sucks. Imagine a large statement full of parameters. Well then there's Hibernate - but I prefer the ? to Hibernate."David Thielen" <thielen@nospam.nospam> wrote in message Actually there is plenty of alternatives. :-)news:05091DAB-8ECA-4613-9702-6BF33D0C9A95@microsoft.com... >> ? sucks. Imagine a large statement full of parameters. > > Well then there's Hibernate - but I prefer the ? to Hibernate. Just these days I am fixing code I didn't write. Among the code there is a huuuuuge insert with more than 30 fields (using oledb and nice questionmarks) which has something mismatched. Now, figuring out which parameter is which is a pain. -- Miha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ I'll agree with you on that. The best would be named parameters that worked
for every client - that would be really nice. Show quote "Miha Markic [MVP C#]" wrote: > "David Thielen" <thielen@nospam.nospam> wrote in message > news:05091DAB-8ECA-4613-9702-6BF33D0C9A95@microsoft.com... > >> ? sucks. Imagine a large statement full of parameters. > > > > Well then there's Hibernate > > Actually there is plenty of alternatives. :-) > > - but I prefer the ? to Hibernate. > > Just these days I am fixing code I didn't write. Among the code there is a > huuuuuge insert with more than 30 fields (using oledb and nice > questionmarks) which has something mismatched. Now, figuring out which > parameter is which is a pain. > > -- > Miha Markic [MVP C#] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > >  |
|||||||||||||||||||||||
Other interesting topics