VS 2003 question

I know that this question has been asked alot but I have seen no direct
answer to it as yet.

From control panel it is possible to specify the connection parameters for a
database. Then from code simply request the DSN and the connection manager
would resolve the connection to the database.

This was great if the database server location moved it was a simple process
for any user (from small to large enterprises) to change the setting using
the control panel manager.

Am I right in saying that there is no corrisponding facility for doing this
when using an SQL Client Connection or OLEDB Connection object in .NET?


Regards,
Myles

oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
"MDFS" <(E-Mail Removed)> wrote in message
news:4D7CBCEC-45CC-4AEC-B6DE-(E-Mail Removed)...
> VS 2003 question
>
> I know that this question has been asked alot but I have seen no direct
> answer to it as yet.
>
> From control panel it is possible to specify the connection parameters for
> a
> database. Then from code simply request the DSN and the connection manager
> would resolve the connection to the database.
>
> This was great if the database server location moved it was a simple
> process
> for any user (from small to large enterprises) to change the setting using
> the control panel manager.
>
> Am I right in saying that there is no corrisponding facility for doing
> this
> when using an SQL Client Connection or OLEDB Connection object in .NET?
>
>
> Regards,
> Myles

Of course you can.

The ODBC .net provider is supposed to work on top of any ODBC native driver.
So you have the same facilities in configuration as using a native ODBC
driver.



The "right" way is to use a managed .NET provider targeted for a database;
if you want to change the connection string dynamically you can put the
connection string in a configuration file (eventually encrypt the connection
string if needed).



Dumitru



"MDFS" <(E-Mail Removed)> wrote in message
news:4D7CBCEC-45CC-4AEC-B6DE-(E-Mail Removed)...
> VS 2003 question
>
> I know that this question has been asked alot but I have seen no direct
> answer to it as yet.
>
> From control panel it is possible to specify the connection parameters for
> a
> database. Then from code simply request the DSN and the connection manager
> would resolve the connection to the database.
>
> This was great if the database server location moved it was a simple
> process
> for any user (from small to large enterprises) to change the setting using
> the control panel manager.
>
> Am I right in saying that there is no corrisponding facility for doing
> this
> when using an SQL Client Connection or OLEDB Connection object in .NET?
>
>
> Regards,
> Myles

Scott nice try but no joy here's an example
Dim cnn As New SqlClient.SqlConnection
Dim cnn2 As New OleDb.OleDbConnection
' This will fail because DSN is NOT supported
cnn.ConnectionString = "DSN=MyDSN;UID=sa"
cnn.Open()
' This will work UNTIL the database is moved to another machine on the
' network... Basically the command ignores DSN and defaults to local!
cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
cnn2.Open()

Dumitru,
Thanks for that. I understand that. The question is what about when you
are using OLEDB Connections and SQLClient Connections by way of an example:

Dim cnn As New SqlClient.SqlConnection
Dim cnn2 As New OleDb.OleDbConnection
' This will fail because DSN is NOT supported... OK so we try OLEDB
cnn.ConnectionString = "DSN=MyDSN;UID=sa"
cnn.Open()
' This will work UNTIL the database is moved to another machine on the
' network... Basically the command ignores DSN and defaults to local!
cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
cnn2.Open()
Myles


"Dumitru Sbenghe" wrote:

> Of course you can.
>
> The ODBC .net provider is supposed to work on top of any ODBC native driver.
> So you have the same facilities in configuration as using a native ODBC
> driver.
>
>
>
> The "right" way is to use a managed .NET provider targeted for a database;
> if you want to change the connection string dynamically you can put the
> connection string in a configuration file (eventually encrypt the connection
> string if needed).
>
>
>
> Dumitru
>

I am curious one thing: is your system REQUIRES you to use DSN, not managed
..NET provider, for particular technical reason?
As for SQL Server could be moved to other computer than the original
location when the system is designed, you could simply change the server
name. Note, even you use DSN, you have to change server name in the DSN
anyway.

You should always use SQL Server's native .NET provider, instead of ODBC
(DSN), unless you has special reason. Since your original question is about
alternative to using DSN, then, simply look at ConnectionString in almost
all ADO.NET sample: you only need to give server name, database name and
user/password (if you use SQL security).

So, I really confused on what your problem is.

"MDFS" <(E-Mail Removed)> wrote in message
news:1113FCDC-81FF-4AE9-9320-(E-Mail Removed)...
> Dumitru,
> Thanks for that. I understand that. The question is what about when you
> are using OLEDB Connections and SQLClient Connections by way of an
> example:
>
> Dim cnn As New SqlClient.SqlConnection
> Dim cnn2 As New OleDb.OleDbConnection
> ' This will fail because DSN is NOT supported... OK so we try OLEDB
> cnn.ConnectionString = "DSN=MyDSN;UID=sa"
> cnn.Open()
> ' This will work UNTIL the database is moved to another machine on
> the
> ' network... Basically the command ignores DSN and defaults to
> local!
> cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
> cnn2.Open()
> Myles
>
>
> "Dumitru Sbenghe" wrote:
>
>> Of course you can.
>>
>> The ODBC .net provider is supposed to work on top of any ODBC native
>> driver.
>> So you have the same facilities in configuration as using a native ODBC
>> driver.
>>
>>
>>
>> The "right" way is to use a managed .NET provider targeted for a
>> database;
>> if you want to change the connection string dynamically you can put the
>> connection string in a configuration file (eventually encrypt the
>> connection
>> string if needed).
>>
>>
>>
>> Dumitru
>>
>

On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" <(E-Mail Removed)> wrote:

¤ VS 2003 question
¤
¤ I know that this question has been asked alot but I have seen no direct
¤ answer to it as yet.
¤
¤ From control panel it is possible to specify the connection parameters for a
¤ database. Then from code simply request the DSN and the connection manager
¤ would resolve the connection to the database.
¤
¤ This was great if the database server location moved it was a simple process
¤ for any user (from small to large enterprises) to change the setting using
¤ the control panel manager.
¤
¤ Am I right in saying that there is no corrisponding facility for doing this
¤ when using an SQL Client Connection or OLEDB Connection object in .NET?

Yes, you are correct.

Legacy DSN type connections have been essentially replaced, although they are still supported via
the native .NET ODBC provider.

Keep in mind that you don't have to hard code connection strings. These can be place in a .config
file where that can just as easily be modified or pushed out to the client.


Paul
~~~~
Microsoft MVP (Visual Basic)

Hi Norman,
First of all I do not have a requirement to use ODBC I would prefer and will
use a native driver where possible.

Basically my problem is that the ODBC Connection Manager in Control Panel
acts as a central repository for connection information to named systems.
This allows for the system administrator to manage and move the database
without impacting the application or modify the application settings directly.

The situation that we are generating for ourselves with the .net way is that
there appears to be a seperate and custom connection repository for each
application. For example I have a customer that has 4 database systems and
numerous bespoke and customised applications accessing the database (lets say
4 for this example). At present (since nearly all use the ODBC DSN Connection
information) if the database(s) are moved all that needs to happen is the 4
ODBC Settings on the workstations are updated on the next logon by the
workstation and everything is up and running.

With the .Net native drivers it appears that each application holds it's own
connection information since the DSN facility is not available. Now we have a
situation that when the database server moves the 4*4=16 settings need to be
altered. Depending on how the developer has designed their system this may
require, Registry Updates, File Update OR each application being accessed
manually on each workstation and the settings changed (not good if its a
large organisation).

And remember each one of these systems could have been designed by a
different software vendor. I hope that you can begin to see the nightmare
ahead of us and I deal with regulated industries which I have no doubt when
they realise the potential threat to systems integrity will go nuts. Imagine
a production plant where on one workstation the production management system
is pointing at the live database but the extract and order management system
is pointing at the test server because they both have their own connection
setting store!

From my point of view (development) I want to know if there is an equivilant
connection information repository that can be used when using the native
drivers?

I hope this clarifys the question and raises the importance of having a
central connection setting store that is independant of the application being
run

Regards,
Myles


"Norman Yuan" wrote:

> I am curious one thing: is your system REQUIRES you to use DSN, not managed
> ..NET provider, for particular technical reason?
> As for SQL Server could be moved to other computer than the original
> location when the system is designed, you could simply change the server
> name. Note, even you use DSN, you have to change server name in the DSN
> anyway.
>
> You should always use SQL Server's native .NET provider, instead of ODBC
> (DSN), unless you has special reason. Since your original question is about
> alternative to using DSN, then, simply look at ConnectionString in almost
> all ADO.NET sample: you only need to give server name, database name and
> user/password (if you use SQL security).
>
> So, I really confused on what your problem is.
>

What Paul said. You can store your connection string in a config file, so
you needn't worry about server name changes. If you need security on top of
that (as it's in plain text) you can store it in encrypted format. There
are lots of web pages that illustrate this method.

-Altaf

--------------------------------------------------------------------------------
All that glitters has a high refractive index.
www.mendhak.com


"Paul Clement" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS"
> <(E-Mail Removed)> wrote:
>
> ¤ VS 2003 question
> ¤
> ¤ I know that this question has been asked alot but I have seen no direct
> ¤ answer to it as yet.
> ¤
> ¤ From control panel it is possible to specify the connection parameters
> for a
> ¤ database. Then from code simply request the DSN and the connection
> manager
> ¤ would resolve the connection to the database.
> ¤
> ¤ This was great if the database server location moved it was a simple
> process
> ¤ for any user (from small to large enterprises) to change the setting
> using
> ¤ the control panel manager.
> ¤
> ¤ Am I right in saying that there is no corrisponding facility for doing
> this
> ¤ when using an SQL Client Connection or OLEDB Connection object in .NET?
>
> Yes, you are correct.
>
> Legacy DSN type connections have been essentially replaced, although they
> are still supported via
> the native .NET ODBC provider.
>
> Keep in mind that you don't have to hard code connection strings. These
> can be place in a .config
> file where that can just as easily be modified or pushed out to the
> client.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

Hi Pay that answers the question although I don't particularly like the
answer.

If you look at the mail in response to Norman I outlined the benifits of a
central connection store. I supose that the real problem I have with it is
that we have lost a facility from the operating environment that was very
useful from everyones perspective.

Unfortunatly the only people who could really provide a replacement facility
is microsoft as they are the only one that everyone will accept...

Thanks for the response

Myles.

"Paul Clement" wrote:

> On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" <(E-Mail Removed)> wrote:
>
> ¤ VS 2003 question
> ¤
> ¤ I know that this question has been asked alot but I have seen no direct
> ¤ answer to it as yet.
> ¤
> ¤ From control panel it is possible to specify the connection parameters for a
> ¤ database. Then from code simply request the DSN and the connection manager
> ¤ would resolve the connection to the database.
> ¤
> ¤ This was great if the database server location moved it was a simple process
> ¤ for any user (from small to large enterprises) to change the setting using
> ¤ the control panel manager.
> ¤
> ¤ Am I right in saying that there is no corrisponding facility for doing this
> ¤ when using an SQL Client Connection or OLEDB Connection object in .NET?
>
> Yes, you are correct.
>
> Legacy DSN type connections have been essentially replaced, although they are still supported via
> the native .NET ODBC provider.
>
> Keep in mind that you don't have to hard code connection strings. These can be place in a .config
> file where that can just as easily be modified or pushed out to the client.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>