none
SQL -- Use of Connection String RRS feed

  • Question

  • I am developing a small SQL database, less than 2,000 records, at the present.

    I'm using the Data Source Configuration Wizard in VisualStudio (my bad?) to create a Dataset which I then use in my application through the "Me." interface like this :

        Me.ProductTableAdapter.Fill(Me.CIMSQLDataSet.Product)

    By the time the code gets to this instruction, it's implicit that the Connection String associated with this DataSource has been consumed to fill the DataSet. My problem is that, I don't see where I can effectively *tell* the DataSet to use a different Connection String, at runtime, other than the one that was used at the time of going through the "DataSource Configuration Wizard"?

    For the purpose of testing and development, I'm always connecting with the same user/pwd but for deployment, when UserX launches my application on machine Y, after he signs in, don't I need to use his credentials to log in the database?

    I don't see how I can use users credentials to log into the database when the Connection String has been "coded" in SQLDataAdpters? (That's the best of my humble understanding) I'm not sure if I remember there is an option to supply the credentials later during DataSource Configuration? Will have to revisit its options... 

    Sorry if I seem to make this problem more complicated than it is.



    Thursday, April 23, 2020 9:06 PM

Answers

  • Core,

    I tried Karen's solution but it did not work for me. I could net get the SqlConnectionStringBuilder to work inside My.Application.Events for some reason.

    I came accross one solution here:

    http://thecodemonk.com/2008/02/18/tableadapter-connection-strings/

    I struggled with it for a while and went on to search for another solution which I found in modifying the TableAdapter ConnectionString. 

    First, I create the ConnectionString, using Karen's suggested

    Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder()
    builder.DataSource = "VAIO"
    builder.InitialCatalog = "Roger"
    builder.UserID = "Joe"
    builder.Password = "1234"
    builder.ConnectTimeout = 30

    After that, I assign the new ConnectionString to the Table Adapter. And then I let the TabaleAdapter.Fill method to do its magic with these two instructions:

    Me.Table1TableAdapter.Connection.ConnectionString = builder.ConnectionString
            Me.Table1TableAdapter.Fill(Me.RogerDataSet.Table1)

    It works like a charm, so far.

    • Marked as answer by roger.breton Thursday, May 14, 2020 1:57 AM
    Thursday, May 14, 2020 1:56 AM

All replies

  • Hello,

    You can change the connection string by clicking the following under project properties

    Select Startup

    Then the code below is where you change the connection by first getting the current connection string, using SqlConnectionBuilder to change the connection string then set it (note you need to set it this way as going thru the normal way to change a setting will not work as ConnectionStrings are read-only)

    Imports Microsoft.VisualBasic.ApplicationServices
    
    Namespace My
        ' The following events are available for MyApplication:
        ' Startup: Raised when the application starts, before the startup form is created.
        ' Shutdown: Raised after all application forms are closed.  This event is not raised if the application terminates abnormally.
        ' UnhandledException: Raised if the application encounters an unhandled exception.
        ' StartupNextInstance: Raised when launching a single-instance application and the application is already active. 
        ' NetworkAvailabilityChanged: Raised when the network connection is connected or disconnected.
        Partial Friend Class MyApplication
            Private Sub MyApplication_Startup(sender As Object, e As StartupEventArgs) Handles Me.Startup
                Dim current = My.Settings.ForumExampleConnectionString
                Dim builder As New SqlClient.SqlConnectionStringBuilder(current)
                builder.DataSource = "6X2V0G2\SQLEXPRESS"
                My.Settings.Item("ForumExampleConnectionString") = builder.ConnectionString
            End Sub
        End Class
    End Namespace
    

    Note: on the above, there are other wacky ways around this to make it dynamic but that is getting into a grey area.

    Note: There are other ways which I've written about in the following repository but I did that in C# and don't have time to write it in VB.NET, any ways the above works fine.




    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, April 23, 2020 10:47 PM
    Moderator
  • I managed to locate the VisualStudio area you refered to in your reply, Karen, and the corresponding StartUp event (this .NET environment is like a gruyere swiss cheese with lots of "holes" to get into).

    I get the idea:

    1) Capture the "Current" connection string
    2) Create a new one using the SqlConnectionStringBuilder
    3) Specify DataSource
    4) Write it back to AppConfig 

    The only way I could use this approach is, if, before executing all those instructions, display a small login dialogBox for the user to input his login/password. If that worked, then I could pass the login/password to the SqlConnectionStringBuilder in the builder.DataSource.

    Would that make sense?

    Friday, April 24, 2020 12:02 AM
  • I have yet another article/code sample which may give you ideas as written it does not show how to work this in with a data wizard style of working with a connection that is secure so keep that in mind.

    Microsoft TechNet SQL-Server database login for Windows Forms (VB.NET), repository.

    In closing, TableAdapters are not the best method to work with data unless you fully understand them. I quit writing about them over ten years ago.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, April 24, 2020 12:47 AM
    Moderator
  • Would that make sense?

    The user-id and password only allows the user to access your program based upon information kept in a Users table as your program authenticates the user's credentials to use the program, which is usually a valid email address and psw.

    https://www.donedone.com/building-the-optimal-user-database-model-for-your-application/

    The user should not be allowed to enter a user-id and password as credentials to access the database. The credentials of user-id and psw should be established for the database to login on the database allowing the program to use the database that is given on the connectionstring. It's a generic user-id and password known by the database that is given on the connectionstring.

    You can make your on app.config for the Windows form project and use the Configuration Manager to retrieve the connectionstring.

    https://docs.microsoft.com/en-us/dotnet/api/system.configuration.configurationmanager.connectionstrings?view=netframework-4.8

    https://webcheatsheet.com/asp/sqlserver_connection_strings.php

    Private Sub SurroundingSub()
        Dim sqlSelectCommand1 As SqlCommand = New SqlCommand()
        Dim sqlDataAdapter1 As SqlDataAdapter = New SqlDataAdapter()
        Dim sqlConnection1 As SqlConnection = New SqlConnection()
        Dim connSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("SampleDB")
    
        If connSettings IsNot Nothing AndAlso connSettings.ConnectionString IsNot Nothing Then
            sqlConnection1.ConnectionString = ConfigurationManager.ConnectionStrings("SampleDB").ConnectionString
        End If
    
        sqlSelectCommand1.CommandText = "SELECT TOP 50  AddressID, AddressLine1, City, PostalCode, ModifiedDate FROM Person.Address"
        sqlSelectCommand1.Connection = sqlConnection1
        sqlDataAdapter1.SelectCommand = sqlSelectCommand1
        Me.DataSource = sqlDataAdapter1
    End Sub

    You can add an app.config file yourself manually to the Project off of 'Add New Items'.

    https://docs.microsoft.com/en-us/visualstudio/ide/how-to-add-app-config-file?view=vs-2019

    It works the same way for a VB.net project.

    You should really learn how to use Visual Studio's Service Based database feature. All the more recent versions of VS have this feature.

    https://docs.microsoft.com/en-us/visualstudio/data-tools/create-a-sql-database-by-using-a-designer?view=vs-2019https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15

    https://www.codeproject.com/Articles/6538/Configuration-Settings-File-for-providing-applicat

    <copied>

    Note: When you compile your application, VS.NET will automatically create a file called <your application name>.exe.config in your bin\debug folder. The contents of the app.config will be automatically copied to this new config file when you compile the application. When you deliver the application to the end user, you have to deliver the exe and this new config file called <your application name>.exe.config and NOT the app.config. Users can modify the data in <your application name>.exe.config file and application will read the data from the config file, when restarted.

    <end>

    The exe.config file is being used even when you run the program in VS, becuase it's the runtime config file that .NET looks for to get config information from it.



    • Edited by DA924x Friday, April 24, 2020 2:08 AM
    Friday, April 24, 2020 1:56 AM
  • Yea this has been from the beginning a complaint against the DataAdapter wizard Solution.

    Bill Vaughn, the once well known expert on SQL server from Seatle, was always telling this to Microsoft. 

    But it is one of those things like the almost Microsoft killer Windows 8. At Microsoft they believe it is the best solution. Probably one of the reason the wizard became never a success while beside this, it is very easy to use.

    You have to do it using the by everybody from outside changeable settings.

    By the way "Me" is no interface, it tells that the members of this class are used, it is only needed if the same name can exist outside a class.

    Private Roger as string
    Private sub Whatever
    dim roger as string
    me.roger = Roger
    end sub
    end Class

    But most use it simply to use the intellisence to get a member list 

     

    Success
    Cor

    Friday, April 24, 2020 6:51 AM
  • Core,

    I tried Karen's solution but it did not work for me. I could net get the SqlConnectionStringBuilder to work inside My.Application.Events for some reason.

    I came accross one solution here:

    http://thecodemonk.com/2008/02/18/tableadapter-connection-strings/

    I struggled with it for a while and went on to search for another solution which I found in modifying the TableAdapter ConnectionString. 

    First, I create the ConnectionString, using Karen's suggested

    Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder()
    builder.DataSource = "VAIO"
    builder.InitialCatalog = "Roger"
    builder.UserID = "Joe"
    builder.Password = "1234"
    builder.ConnectTimeout = 30

    After that, I assign the new ConnectionString to the Table Adapter. And then I let the TabaleAdapter.Fill method to do its magic with these two instructions:

    Me.Table1TableAdapter.Connection.ConnectionString = builder.ConnectionString
            Me.Table1TableAdapter.Fill(Me.RogerDataSet.Table1)

    It works like a charm, so far.

    • Marked as answer by roger.breton Thursday, May 14, 2020 1:57 AM
    Thursday, May 14, 2020 1:56 AM