locked
Setting DataSource connectionstring at runtime RRS feed

  • Question

  • Hello, all.  I am kind of an old 'newbee' at this, so bear with me for a moment. 

    I am developing a windows forms application in Visual Studio 2010 Professional that connects to a remote database (MS sqlServer 2008 R2).  To speed up development, I configured a DataSource and used the data objects associated with it.  In VB6, the data source could be assigned to the data objects during runtime using a variety of different runtime database connections, ODBC, ADO, ...

    I have been unable to find a similar method with the VS2010 Visual Basic DataSource and underlying TableAdapter, BindingSource, BindingNavigator, down to the individual ComboBox and assorted other bound controls.  It's convenient to have the integration, but I've got different users with different permissions, ... 

    The long and short is that I'd like to be able to assign the data source during runtime operation, at least to the connection string level.  Any help would be appreciated.

    Dan Kirk

    Thursday, December 22, 2011 1:02 AM

Answers

  • Hi,
    Welcome to MSDN forums

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyMDB.mdb"
            Dim SQLString As String = "SELECT * FROM TestDB"
            Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
            Dim DataSet1 As New DataSet()
            Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
            OleDBConn1.Open()
            OleDbDataAdapter1.Fill(DataSet1, "TestDB")
            DataGridView1.DataSource = DataSet1.Tables("TestDB")
        End Sub
    End Class
    
    
    

    By
    A Pathfinder..
    JoSWa


    If you find an answer helpful, click the helpful button. If you find an answer to your question, mark it as the answer.
    • Marked as answer by Mike Feng Friday, December 23, 2011 10:27 AM
    Thursday, December 22, 2011 5:08 AM
  • Dan,

    Those responsible for this at Microsoft make it every time easier, but because that other departments want more security direct more difficult.

    Currently you have AFAIK four ways, 

    You can change the application.config by hand (if you use an application for less than 10 endusers the far most easy way)

    You can change the application.config in a program (always a clumsy way to handle with change on many errors)

    If you don't use a generic datasource, but do it in the way like in VB6 you can simple set the connectionstring like Guile shows.

    You can add a partial class to the generated XSD code.

    I only use the first and third method I've written, I had until now no reason to take the other two.

     


    Success
    Cor
    • Marked as answer by Mike Feng Friday, December 23, 2011 10:27 AM
    Thursday, December 22, 2011 7:48 AM
  • It astounds me that Microsoft doesn't provide a means of setting the DataSource connection string at runtime. 

    Dan Kirk

    Hi

    That exist in more than one way

    I will tell you one of them that I use to change my typed-dataset connection at runtime

    In the project setting, you have a variable name that hold your connection of type "Connection String" of scope "Application" and value of your connection string. Let say the variable name is "myAPPConnString"

    So, in anywhere in your project(I usually do it in the Form_Load()), when you want to change your connection you can code

    My.Settings("myAPPConnString") = new_connection_string
    

     

     

     


    • Edited by Samir Ibrahim Friday, December 23, 2011 8:59 AM
    • Marked as answer by Dan_Kirk Friday, December 23, 2011 2:39 PM
    Friday, December 23, 2011 8:57 AM

All replies

  • HI,

          The sample oledb method will help you

    public void InsertRow(string connectionString, string insertSQL)
    {
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            // The insertSQL string contains a SQL statement that
            // inserts a new row in the source table.
            OleDbCommand command = new OleDbCommand(insertSQL);
    
            // Set the Connection to the new OleDbConnection.
            command.Connection = connection;
    
            // Open the connection and execute the insert command.
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            // The connection is automatically closed when the
            // code exits the using block.
        }
    }
    

    Pls ask if any doubt to be cleared


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful". Happy Coding...
    • Edited by Ragu.R Thursday, December 22, 2011 3:06 AM
    Thursday, December 22, 2011 3:03 AM
  • Hi,
    Welcome to MSDN forums

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyMDB.mdb"
            Dim SQLString As String = "SELECT * FROM TestDB"
            Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
            Dim DataSet1 As New DataSet()
            Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
            OleDBConn1.Open()
            OleDbDataAdapter1.Fill(DataSet1, "TestDB")
            DataGridView1.DataSource = DataSet1.Tables("TestDB")
        End Sub
    End Class
    
    
    

    By
    A Pathfinder..
    JoSWa


    If you find an answer helpful, click the helpful button. If you find an answer to your question, mark it as the answer.
    • Marked as answer by Mike Feng Friday, December 23, 2011 10:27 AM
    Thursday, December 22, 2011 5:08 AM
  • Dan,

    Those responsible for this at Microsoft make it every time easier, but because that other departments want more security direct more difficult.

    Currently you have AFAIK four ways, 

    You can change the application.config by hand (if you use an application for less than 10 endusers the far most easy way)

    You can change the application.config in a program (always a clumsy way to handle with change on many errors)

    If you don't use a generic datasource, but do it in the way like in VB6 you can simple set the connectionstring like Guile shows.

    You can add a partial class to the generated XSD code.

    I only use the first and third method I've written, I had until now no reason to take the other two.

     


    Success
    Cor
    • Marked as answer by Mike Feng Friday, December 23, 2011 10:27 AM
    Thursday, December 22, 2011 7:48 AM
  • Security is an interesting thing, Cor. 

    Recently, a co-worker asked me to make a backup of a project db and install it on a development server.  I was given a Windows login, and the MS SQLServer  was configured for the login to have permissions.  I logged in, and the permissions granted under Windows gave me permission to read, write, delete, and alter the db table structure but wouldn't allow the creation of the backup.

    Poked around a while, then finally looked in the app.config for the application running against the db.  Of course, in plain view was the dbadmin (actually the sa) login.  I created the backup and restored it to the development server.

    Now, I grant that the latest data objects are less likely to allow the underlying OS to be hacked, but, in my opinion, opens much room for compromising data.  Example:

    A filter has something like (SELECT * INTO dbo.EmployeesInfoHackerWants FROM OPENDATASOURCE('SQLNCLI', 'Data Source=server_name;Integrated Security=SSPI') .AdventureWorks2008R2.HumanResources.Employees) in the filter's source control text.

    Security requires an integrated approach, and making the data objects more rigid doesn't necessarily equate to more security.  It's an attempt to hide the underlying data application's functionality, and the programmer is attempting to use the underlying data application's functionality.

    Don't get me wrong, the data objects native to VS Visual Basic give the advantage of being 'native', quicker to program and faster in operation.  I would hate to try using the oledb objects and expect performance to meet the demands of heavy data usage. 

    If the app.config file is encrypted, that pretty much rules out hand editing on install. 

    Most data compromise is the result of an 'insider', roughly 90%, but not necessarily the operator whose job is using the developed program.  If compromising data is a click away, the temptation will always be there for employees to steal data to sell, or worse yet, be recruited and trained on how to steal data.

    Thanks for your response, I do appreciate you taking your time in helping me with my problem.

    Dan Kirk

    Thursday, December 22, 2011 12:34 PM
  • Hi

    I’m glad to hear that you got it working. Thank you for your feedback. It will be very beneficial for other community members having the similar questions.

    Also click Mark as answer for those who have solved your problems.

    By
    A Pathfinder..
    JoSwa

     


    If you find an answer helpful, click the helpful button. If you find an answer to your question, mark it as the answer.
    Thursday, December 22, 2011 12:51 PM
  • Hello, JoSwa.

    I do appreciate the time you've given to helping me with my problem.   Your suggested solution is to build an N-Tier app, pretty much.

    It's helpful and one solution to assigning the data source during runtime.  It also takes away all the native, inherit SQLServer data objects that ship with VS Visual Basic 2010 Professional except the bottom layer interface.  I found it helpful, and it provides the flexibility to use many different database types.

    If you are developing in oleDB, you might want to check out this link:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/e696d0ac-f8e2-4b19-8a08-7a357d3d780f

    SQLServer oleDB support will end with the next version (current version 2012 continues the support through lifecycle, but oleDB applications will not port to later versions).

    ODBC is the selected MS remote data connection, and is actively being developed as the 'native' connection in SQLServer. 

    I am migrating an application to SQLServer, however, and hoped to use the data objects that shipped with VS 2010.

    It astounds me that Microsoft doesn't provide a means of setting the DataSource connection string at runtime.  More astounding, MS provides only an editable configuration file to configure a program during the installation process.  It would appear that the latest VS Visual Basic is intended to develop only applications that run against a local *.mdf file, not against SQLServer.

    Perhaps the solution is to develop the application user interface with the DataSource shipped with VS, then replace it with late bindings using ODBC before compiling.

    Dan Kirk


    • Edited by Dan_Kirk Thursday, December 22, 2011 2:47 PM copy/paste - forgot to delete copied
    Thursday, December 22, 2011 2:37 PM
  • It astounds me that Microsoft doesn't provide a means of setting the DataSource connection string at runtime. 

    Dan Kirk

    Hi

    That exist in more than one way

    I will tell you one of them that I use to change my typed-dataset connection at runtime

    In the project setting, you have a variable name that hold your connection of type "Connection String" of scope "Application" and value of your connection string. Let say the variable name is "myAPPConnString"

    So, in anywhere in your project(I usually do it in the Form_Load()), when you want to change your connection you can code

    My.Settings("myAPPConnString") = new_connection_string
    

     

     

     


    • Edited by Samir Ibrahim Friday, December 23, 2011 8:59 AM
    • Marked as answer by Dan_Kirk Friday, December 23, 2011 2:39 PM
    Friday, December 23, 2011 8:57 AM
  • Samir, you have answered my question, [quote]The long and short is that I'd like to be able to assign the data source during runtime operation, at least to the connection string level.[/quote], precisely.  I sincerely thank you.

    Here's the code that should configure the connection string at login:

    '#  DATABASE CONFIGURATION FORM CODE

    Public Class fmDBConfig
        Private bolState As Boolean = False

        Private Sub btnTestConnection_Click(sender As System.Object, e As System.EventArgs) Handles btnTestConnection.Click
            Dim strDBLocation As String
            Dim strInstanceName As String
            Dim strConnect As String
            strDBLocation = Trim(txtDBLocation.Text)
            strInstanceName = Trim(txtInstanceName.Text)
            If strDBLocation = "" Or strInstanceName = "" Then
                txtStatus.Text = "Please Enter Server Values"
                Exit Sub
            End If
            Dim sqlTest As New SqlClient.SqlConnection()
            If bolState = False Then
                If Not IsNothing(sqlTest) Then
                    strDBLocation = txtDBLocation.Text
                    strInstanceName = txtInstanceName.Text
                    '##  OPEN A CONNECTION TO DATABASE WITH 'ONLY LOGIN' GUEST PERMISSIONS  ##
                    strConnect = "Data Source=" & strDBLocation & "\" & strInstanceName & _
                            ";Initial Catalog=MyApp_Database;Persist Security Info=True;" & _
                            "User ID=setup_admin;Password=my_password"
                    sqlTest.ConnectionString = strConnect
                    On Error GoTo Err_Trap
                    sqlTest.Open()
                    If sqlTest.State = ConnectionState.Open Then
                        txtStatus.Text = "Connection Successful."
                        sqlTest.Close()
                        btnTestConnection.Text = "&Save Connection"
                        txtDBLocation.ReadOnly = True
                        txtInstanceName.ReadOnly = True
                        bolState = True
                    Else
                        txtStatus.Text = "Connection Failed.  Please check entries."
                    End If
                    sqlTest = Nothing
                End If
            ElseIf bolState = True Then
                '##  SAVE DATA USING UTILITIES MODULE CODE BELOW
                Register_Server(strDBLocation, strInstanceName)
                btnTestConnection.Text = "&Test Connection"
                txtDBLocation.ReadOnly = False
                txtInstanceName.ReadOnly = False
            End If
            Exit Sub
    Err_Trap:
            sqlTest = Nothing
            txtStatus.Text = "Connection Failed.  Please check entries."
            btnTestConnection.Text = "&Test Connection"
            bolState = False
        End Sub

        Private Sub btnCancel_Click(sender As System.Object, e As System.EventArgs) Handles btnCancel.Click
            txtDBLocation.ReadOnly = False
            txtInstanceName.ReadOnly = False
            btnTestConnection.Text = "&Test Connection"
            bolState = False
        End Sub

        Private Sub fmDBConfig_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
            Me.Dispose()
        End Sub

    End Class

    '##  UTILITIES MODULE CODE

    #Region "Registry Functions"

        Public Sub Register_Server(ByVal strServerLoc As String, ByVal strServerName As String)
            Dim strAppVersion As String
            strAppVersion = My.Application.Info.Version.Major & _
                    "." & My.Application.Info.Version.Minor & "." & _
                    My.Application.Info.Version.Build & "." & My.Application.Info.Version.Revision

            With My.Computer.Registry
                .SetValue("HKEY_CURRENT_USER\Software\MyApp_Name", "Version", strAppVersion)
                .SetValue("HKEY_CURRENT_USER\Software\MyApp_Name\db_connection", "dbLocation", strServerLoc)
                .SetValue("HKEY_CURRENT_USER\Software\MyApp_Name\db_connection", "dbInstance", strServerName)
            End With
        End Sub

        Public Function GetSetting(ByVal strEntry As String) As String
            With My.Computer.Registry
                GetSetting = .GetValue("HKEY_CURRENT_USER\Software\MyApp_Name\db_connection", strEntry, "NotFound")
            End With
        End Function

    #End Region

    '##  LOGIN FORM CODE

    '##  SET CONNECTION STRING PROPERTIES AT LOGIN Where strUser_Name, strUser_Password are user input

        Dim strConnection As String
        strConnection = "Data Source=" & GetSetting("dbServer") & "\" & GetSetting("dbInstance") & _
                ";Initial Catalog=MyApp_Database;Persist Security Info=True;User ID=" & strUser_Name" & _
                ";Password=" & strUser_Password
        My.Settings("MyApp_DatabaseConnectionString") = strConnection

    Again, I would like to thank all that helped in finding the solution to my problem.

    Dan Kirk


    • Edited by Dan_Kirk Friday, December 23, 2011 3:18 PM forgot the 'db_connection' in the HKEY,
    Friday, December 23, 2011 2:58 PM
  • You are welcome Dan :)

    I have similar flow of your code, except I have a form to setup the connection to the server which has 4 input (server name, database name, username, password). Once the connection is validated, I store it in encrypted text file. Then I read the encrypted textfile, decrypt it, and then I use my.setting("conn") to change the connection of the datasource.

    Have a good day.

     

     

    Friday, December 23, 2011 7:33 PM