none
Using SQLCleint with an ODBC DSN RRS feed

  • Question

  • Hello,

    I just want to clarify that I'm not actually trying to use "DSN=xxx" in my connection string. I understand that this is an ODBC specific object and is not compatible. I am part of a development team that uses a mixture of VB6 with ODBC to support our existing legacy code whilst we are converting it to .NET

    Our current .NET code also uses the ODBC interface and uses a User DSN that our support team creates on installation of the product. I am looking to introduce the SQLClient objects for better access to our SQL Server database. 

    I had this idea that I could look in the registry at HKCU\Software\ODBC\ODBC.INI\<DSN Name> and pull out the values of 'Server' and 'Database' into my connection string to create a direct one. However this seems a bit 'hacky', having to go to a hard coded registry name. Granted I can check that the variables exist and I can prevent code from using the SQLClient if I get an error, but I was just wondering if there is a better way to do this. 

    I just wanted to clarify, I do not know what the Server / Database is, this already existing in the DSN. I need to create a SQLClient Connection String from the DSN values.

    Thanks,

    Adam

    Monday, October 8, 2012 10:16 AM

Answers

  • One method is to open the ODBC DSN at startup to optain the server and database name needed for the connection string.  C# example:

            static string getSqlClientConnectionStringFromDsn(string dsn)
            {
                string dataSource;
                string database;
                var odbcConnection = new OdbcConnection(String.Format("DSN={0}", dsn));
                odbcConnection.Open();
                dataSource = odbcConnection.DataSource;
                database = odbcConnection.Database;
                odbcConnection.Close();
    
                return String.Format(
                    "Data Source={0};Initial Catalog={1};Integrated Security=SSPI"
                    , dataSource
                    , database);
            }


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Adam Lonsdale Tuesday, October 9, 2012 8:02 AM
    Tuesday, October 9, 2012 1:18 AM

All replies

  • Depending upon your environment you may be able to use SMO:

    http://www.sqldbatips.com/showarticle.asp?ID=45

    There are a number of methods you can use to retrieve the databases for a given SQL Server:

    http://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-server


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, October 8, 2012 2:49 PM
  • Hi Paul, 

    Thanks for the reply. I apologise in advance, the issue wasn't with populating a list of databases or servers, it was the idea of creating a connection string from an existing ODBC DSN. I placed some code in there to use the server and database already specified in the DSN and combined it with the details the user entered at our login screen and it all seems to be working pretty well. I've noticed a major performance difference in using LINQ (upto 300% slower) but that's a seperate issue. 

    Regards,

    Adam Lonsdale

    Monday, October 8, 2012 11:18 PM
  • One method is to open the ODBC DSN at startup to optain the server and database name needed for the connection string.  C# example:

            static string getSqlClientConnectionStringFromDsn(string dsn)
            {
                string dataSource;
                string database;
                var odbcConnection = new OdbcConnection(String.Format("DSN={0}", dsn));
                odbcConnection.Open();
                dataSource = odbcConnection.DataSource;
                database = odbcConnection.Database;
                odbcConnection.Close();
    
                return String.Format(
                    "Data Source={0};Initial Catalog={1};Integrated Security=SSPI"
                    , dataSource
                    , database);
            }


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked as answer by Adam Lonsdale Tuesday, October 9, 2012 8:02 AM
    Tuesday, October 9, 2012 1:18 AM
  • Fantastic, exactly what I was looking for, can't believe I didn't think of this method! Thanks
    Tuesday, October 9, 2012 8:02 AM
  • Just for the record, this is the code I had working to get a connection string from the registry,

    	Public Function GetConnectionString(ByVal UID As String, ByVal PWD As String, ByVal DSN As String) As String
    		Try
    			Dim strConnectionString As String = String.Empty
    			Dim strServer As String = String.Empty
    			Dim strDatabase As String = String.Empty
    			Dim regKey As RegistryKey = Registry.CurrentUser.OpenSubKey("Software\ODBC\ODBC.INI\" & DSN)
    
    			If Not IsNothing(regKey) Then
    				strServer = regKey.GetValue("Server", "").ToString
    				strDatabase = regKey.GetValue("Database", "").ToString
    			End If
    
    			If strServer.Length > 0 AndAlso strDatabase.Length > 0 AndAlso UID.Length > 0 AndAlso PWD.Length > 0 Then
    				strConnectionString = "Data Source=" & strServer & ";Initial Catalog=" & strDatabase & ";User Id=" & UID & ";Password=" & PWD & ";"
    			End If
    
    			Return strConnectionString
    
    		Catch ex As System.Exception
    			'Error handling removed, handle errors here
    		End Try
    
    	End Function
    Tuesday, October 9, 2012 8:10 AM