Can someone please tell me...

Guest · Mar 21, 2006

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...

Kevin Yu [MSFT] · Mar 21, 2006

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."

Cor Ligthert [MVP] · Mar 21, 2006

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#)

Miha Markic [MVP C#] · Mar 21, 2006

Hi David,

David Thielen said:
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...

Martin Aupperle · Mar 21, 2006

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

Guest · Mar 21, 2006

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.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Guest · Mar 21, 2006

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.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Miha Markic [MVP C#] · Mar 21, 2006

Martin Aupperle said:
Yes, but how to to it, e.g. for Access and Oracle? Code for SQL-server
was already given in another post.

Depends - for the Access you have to issue a separate sql statement ("SELECT
@@Identity" I think) and don't know for sure for Oracle.

Miha Markic [MVP C#] · Mar 21, 2006

David Thielen said:
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
?).

? sucks. Imagine a large statement full of parameters.

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.

Databases are too different IMO for handling this from ado.net.

Guest · Mar 21, 2006

? sucks. Imagine a large statement full of parameters.

Well then there's Hibernate - but I prefer the ? to Hibernate.

Miha Markic [MVP C#] · Mar 21, 2006

David Thielen said:
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.

Paul Clement · Mar 21, 2006

On Tue, 21 Mar 2006 11:26:09 GMT, (e-mail address removed) (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)

Martin Aupperle · Mar 21, 2006

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

Paul Clement · Mar 21, 2006

On Tue, 21 Mar 2006 17:18:45 GMT, (e-mail address removed) (Martin Aupperle) wrote:

¤ On Tue, 21 Mar 2006 10:07:08 -0600, Paul Clement
¤
¤ >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)

Guest · Mar 21, 2006

I'll agree with you on that. The best would be named parameters that worked
for every client - that would be really nice.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Martin Aupperle · Mar 22, 2006

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?

Martin

Paul Clement · Mar 22, 2006

On Wed, 22 Mar 2006 09:43:02 GMT, (e-mail address removed) (Martin Aupperle) wrote:

¤ On Tue, 21 Mar 2006 12:28:39 -0600, Paul Clement
¤
¤ >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)

Martin Aupperle · Mar 23, 2006

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.

Martin

Paul Clement · Mar 23, 2006

On Thu, 23 Mar 2006 08:52:09 GMT, (e-mail address removed) (Martin Aupperle) wrote:

¤ On Wed, 22 Mar 2006 08:56:00 -0600, Paul Clement
¤
¤ >
¤ >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)

Matt Noonan · Mar 24, 2006

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

Can someone please tell me...

Guest

Kevin Yu [MSFT]

Cor Ligthert [MVP]

Miha Markic [MVP C#]

Martin Aupperle

Guest

Guest

Miha Markic [MVP C#]

Miha Markic [MVP C#]

Guest

Miha Markic [MVP C#]

Paul Clement

Martin Aupperle

Paul Clement

Guest

Martin Aupperle

Paul Clement

Martin Aupperle

Paul Clement

Matt Noonan