none
Use a System-ODBC datasource in VB.NET RRS feed

  • Question

  • OK.. I'm not sure how to word it but I know what I want...

    I would like to program my DB application to access a SYSTEM DSN, let's call it 'MyDatabase'.  That way, my program can be system agnostic as long as I set / create a SYSTEM DSN in ODBC to the name of 'MyDatabase'.  It will not matter to my program what the server, or the database is actually called, as long as it sees that SYSTEM DSN.

    How can I create a system connection in my application to point to that, instead of creating a 'raw' connectionstring to the actual server, and database?

    Any help appreciated!

    ADD:  Using VB 2008, connecting to a SQL 2005 Datasource, using SQLConnection object.
    • Edited by ColtsFanMN Tuesday, October 21, 2014 8:43 PM
    • Moved by Carl Cai Wednesday, October 22, 2014 3:04 AM more related
    • Moved by Carl Cai Wednesday, October 22, 2014 5:32 AM better place
    Tuesday, October 21, 2014 8:40 PM

Answers

  • ADD:  Using VB 2008, connecting to a SQL 2005 Datasource, using SQLConnection object.

    A SqlConnection object (System.Data.SqlClient namespace) connects to SQL Server using the ".Net Managed Provider for SQL Server", not ODBC.  If you want to connect to connect using ODBC, use System.Data.Odbc.OdbcConnection (and related ADO.NET objects in the System.Data.Odbc namespace) instead.  Your hard-coded application connection string need specify only "DSN=YourOdbcDataSourceName" (assuming Windows authentication).

    I suggest you use ODBC from VB.NET only if you must support other DBMS products that don't have a managed .NET provider.  This will provide the best performance. 

    If you only need to support SQL Server and simply want to make the database server configurable externally to the application, instead store the connection string in an app.config file.  Add a reference to System.Configuration.dll so code can retrieve the configured value at run time with:

    Dim connectionString As String = ConfigurationManager.ConnectionStrings("YourDatabaseConnection").ConnectionString
    Dim conn As New SqlConnection(connectionString)
    conn.Open()

    Below is a sample app.conf file that specifies a connection string named as above. You can specify any name you like as long as this name matches the hard-coded name above ("YourDatabaseConnection" in this example).

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <connectionStrings>
        <add name="YourDatabaseConnection" connectionString="Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
      </connectionStrings>
    </configuration>


    • Edited by Dan GuzmanMVP Wednesday, October 22, 2014 11:08 PM clarified code
    • Marked as answer by ColtsFanMN Thursday, October 23, 2014 2:17 PM
    Wednesday, October 22, 2014 11:49 AM
  • 1. In your referenced connectionstring, does "YourDatabaseConnection" reference back to the SQLConnection object in my form, so the name(s) match?  Can I then forget about the DIM statement, or how would I do this?

    2. I have more than one (1) SQLConnection object in my project.  Can/should I list them each in the '<connectionStrings>' section, or how should I do this?

    I edited my sample code and supplied a complete app.conf file you can test with.  The name "YourDatabaseConnection" can be any string you choose, typically a mnemonic name that is the database name or similar.  The only requirement is that the string in the application code match the name of the connection string in the app config file.

    If the connection strings for your 2 application connection objects are identical, specify only one entry in the app.config.  You will need 2 entries only if the connection strings are different, each with different logical names so they can be retrieved independently.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Fred BaoModerator Thursday, October 23, 2014 8:41 AM
    • Marked as answer by ColtsFanMN Thursday, October 23, 2014 2:17 PM
    Wednesday, October 22, 2014 11:18 PM

All replies

  • Hello,

    I moved this thread to Data Platform Development>ADO.NET Managed Providers

    forum to get supports for ODBC issues.

    Regards.

    Carl


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 22, 2014 5:34 AM
  • Hello ColtsFanMN,

    >> How can I create a system connection in my application to point to that, instead of creating a 'raw' connectionstring to the actual server, and database?

    According to your description, you want to create a system DSN connection for your SQL Server database using ODBC approach and in your application, you want to use this DSN connection instead of an exact one.

    If it is, then I think you firstly need to configure a DNS connection, you could follow below steps:

    1.Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
    
    2.Click the System DSN tab, and then click Add.
    
    3.Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
    
    4.Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later.
    
    5.Click Select.
    
    6.Click the correct database, and then click OK.
    
    7.Click OK, and then click OK.

    After configuring the system DSN, in your application, you could write code like below to use the DSN connection:

    C# code:

    OdbcConnection conn = new OdbcConnection("DSN=DFDB");
    
                try
    
                {
    
                    conn.Open();
    
                    OdbcCommand cmd = new OdbcCommand("select * from [Order]", conn);
    
                    OdbcDataAdapter da = new OdbcDataAdapter(cmd);
    
                    System.Data.DataSet ds = new System.Data.DataSet();
    
                    da.Fill(ds);
    
                }
    
                catch (Exception)
    
                {
    
    
                    throw;
    
                }
    
                finally
    
                {
    
                    conn.Close();
    
                }

    VB code:

    Dim conn As New Odbc.OdbcConnection
            Try
                conn.ConnectionString = "DSN=DFDB"
                conn.Open()
                Dim cmd As New Odbc.OdbcCommand
                cmd.Connection = conn
                cmd.CommandText = "select * from [Order]"
                Dim da As New Odbc.OdbcDataAdapter(cmd)
                Dim ds As New DataSet
                da.Fill(ds)
            Catch ex As Exception
            Finally
                conn.Close()
            End Try

    If this does not work for you, please feel free let me know.

    Best Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, October 22, 2014 6:03 AM
    Moderator
  • Fred,

    I already have the System DSN setup, I didn't ask how to do that.

    Your code appears to be in 'C' or some version of it.. as stated in my post, I am using VB2008.

    Thanks anyway.

    Wednesday, October 22, 2014 11:27 AM
  • ADD:  Using VB 2008, connecting to a SQL 2005 Datasource, using SQLConnection object.

    A SqlConnection object (System.Data.SqlClient namespace) connects to SQL Server using the ".Net Managed Provider for SQL Server", not ODBC.  If you want to connect to connect using ODBC, use System.Data.Odbc.OdbcConnection (and related ADO.NET objects in the System.Data.Odbc namespace) instead.  Your hard-coded application connection string need specify only "DSN=YourOdbcDataSourceName" (assuming Windows authentication).

    I suggest you use ODBC from VB.NET only if you must support other DBMS products that don't have a managed .NET provider.  This will provide the best performance. 

    If you only need to support SQL Server and simply want to make the database server configurable externally to the application, instead store the connection string in an app.config file.  Add a reference to System.Configuration.dll so code can retrieve the configured value at run time with:

    Dim connectionString As String = ConfigurationManager.ConnectionStrings("YourDatabaseConnection").ConnectionString
    Dim conn As New SqlConnection(connectionString)
    conn.Open()

    Below is a sample app.conf file that specifies a connection string named as above. You can specify any name you like as long as this name matches the hard-coded name above ("YourDatabaseConnection" in this example).

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <connectionStrings>
        <add name="YourDatabaseConnection" connectionString="Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
      </connectionStrings>
    </configuration>


    • Edited by Dan GuzmanMVP Wednesday, October 22, 2014 11:08 PM clarified code
    • Marked as answer by ColtsFanMN Thursday, October 23, 2014 2:17 PM
    Wednesday, October 22, 2014 11:49 AM
  • Thanks Dan.. good input, I appreciate it.

    I will try it out and let you know.. thanks!

    ColtsFanMN

    Wednesday, October 22, 2014 1:28 PM
  • Dan,

    OK.. your statement of:

    "If you want to connect to connect using ODBC, use System.Data.Odbc.OdbcConnection (and related ADO.NET objects in the System.Data.Odbc namespace) instead.  Your hard-coded application connection string need specify only "DSN=YourOdbcDataSourceName" (assuming Windows authentication).

    I was thinking that this is exactly what I wanted to do.. but think instead what I am really trying to accomplish is what you point out here:

    "If you only need to support SQL Server and simply want to make the database server configurable externally to the application, instead store the connection string in an app.config file."

    So, I think I want to do that instead, but I coded the SQLConnection in the form, not as a 'connectionstring' as your DIM statement shows.  I'm just so-so on XML, and never have used references to System.Configuration.dll or an app.config file.  So, I have a couple of questions..

    1. In your referenced connectionstring, does "YourDatabaseConnection" reference back to the SQLConnection object in my form, so the name(s) match?  Can I then forget about the DIM statement, or how would I do this?

    2. I have more than one (1) SQLConnection object in my project.  Can/should I list them each in the '<connectionStrings>' section, or how should I do this?

    I appreciate the help..

    ColtsFanMN

    Wednesday, October 22, 2014 3:31 PM
  • 1. In your referenced connectionstring, does "YourDatabaseConnection" reference back to the SQLConnection object in my form, so the name(s) match?  Can I then forget about the DIM statement, or how would I do this?

    2. I have more than one (1) SQLConnection object in my project.  Can/should I list them each in the '<connectionStrings>' section, or how should I do this?

    I edited my sample code and supplied a complete app.conf file you can test with.  The name "YourDatabaseConnection" can be any string you choose, typically a mnemonic name that is the database name or similar.  The only requirement is that the string in the application code match the name of the connection string in the app config file.

    If the connection strings for your 2 application connection objects are identical, specify only one entry in the app.config.  You will need 2 entries only if the connection strings are different, each with different logical names so they can be retrieved independently.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Fred BaoModerator Thursday, October 23, 2014 8:41 AM
    • Marked as answer by ColtsFanMN Thursday, October 23, 2014 2:17 PM
    Wednesday, October 22, 2014 11:18 PM
  • I would like to think I am not stupid.. but I am just not getting this..

    I am using SQL Data Adapters (NOT Table Adapters) and each specifies/requires a SQL Connection Object that is now embedded in all my forms..  so I don't know how to migrate away from that.. I can't use a coded (the DIM statement)connection at design time, to update/create the Data Adapter, and if I remove the connection object from the existing Data Adapter, it breaks..

    Thanks Dan for your help.. I am sure the solution you provided is correct.  Just too damn dumb to use it.

    Thursday, October 23, 2014 2:17 PM
  • I'm not sure I understand what you mean by the SQlConnection object being embedded on all the forms.  But at the end of the day, you must specify the connection string somewhere in your application.  Wherever that is in the application now, all you need to do use a configurable value instead of hard-coding it. 

    The fact that you use SqlDataAdapters doesn't really matter since it all boils down to SqlConnection, SqlCommand, and SqlDataReader objects in the end. 

    Feel free to post a snippet of your code if you need more help.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, October 24, 2014 2:21 AM
  • By 'embedded', I mean that I dropped a SQLDataAdapter on my form and in the Wizard that followed, I created a connection object to handle the SQL Connection.  So there is nowhere in 'my' code that I reference the connection object.  I just do the 'Fill' method of the SQLDataAdapter, to fill my Dataset.  I also created the Stored Procedures that handle the Update, Delete, and Add chores.  The whole application uses bound controls.

    I don't use TableAdapter(s) since I don't need the extra 'features', or generated code associated with them.  I just handle everything through the SQLDataAdapter.

    So all I really wanted to do was to move the connection from 'hard coded' to an 'external configured' item.. but everything I have tried blows up.

    The application actually works great as is.. I just wanted the flexibility of having it not hard coded.  I'm just over-thinking it I am sure.

    Friday, October 24, 2014 12:49 PM
  • I understand your situation better now. The designer initializes the SqlConnection ConnectionString property with the design-time value in the designer-generated code.  You can override that value at run time in your form(s) Load event handler.  That will allow you to configure the connection string externally without resorting to ODBC. 

    For example, assuming your connection object is named SqlConnection1:

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        SqlConnection1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("YourDatabaseConnection").ConnectionString
    
    End Sub

     

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, October 25, 2014 2:31 AM