Re: Alternative to using DSN to connect to database

If you (or your system administrator) want to use ODBC (DSN) to manage data
access, then you are forced to give up native .NET SQL Server provider, and
you have to use .NET ODBC provider. In this case, your data access to SQL
Server is NOT optimized.

As Paul pointed out, ODBC is old technology and should be avoided if
possible. Also as Paul suggested, you can use *.config file to store
connectionstring. With a bit more imagination, you can even make a
centralized config file to be read by all data access apps in your office.
Just because of the Admiistrator thinking DSN is easier for him to control
data access, then you have to give up optimal data access technology you are
supposed to use, IMO, this is not good situation I'd like to be. (again,
unless there is reason that makes DSN is only option. What it could be?).

"MDFS" <MDFS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E0E29104-FDBB-42DE-A1B4-119F37FD92A1@xxxxxxxxxxxxxxxx
> 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.
>>
>


.

Relevant Pages

  • Re: Silly ODBC Connection problem
    ... it seems to me this must be done interactively on each client--either that or use the magical "DSN-less" connection. ... It's been 11 or years or so since I wrote about ODBC so it's getting kinda fuzzy. ... 556-9205 (Pacific time) ... > created a System DSN named "MyDSN" in the ODBC Administrator tool. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Alternative to using DSN to connect to database
    ... use a native driver where possible. ... Basically my problem is that the ODBC Connection Manager in Control Panel ... At present (since nearly all use the ODBC DSN Connection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Linking to back end on a web server ?
    ... When I do the File--Get External Data and select the DSN, ... "You cannot use ODBC to import from, export to, or link an external ... Microsoft Jet or ISAM database table to your database. ... that DSN-based connection to a DSN-less connection. ...
    (microsoft.public.access.externaldata)
  • Re: Error Message when using ODBC: " Record had too few data f
    ... If so, it's OLEDB. ... look at the connection string. ... If my guess is wrong and you are using ODBC, ... System DSN, but it can be useful to create a File DSN because you can open ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQL Desktop server got somehow disconnected
    ... I can indeed make an ODBC connection. ... This is the output I get from the SQL server setup window after testing the ...
    (microsoft.public.sqlserver.connect)