Setting DataSource connectionstring at runtime

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

Answer 1

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...

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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

 

 

 

Answer 8

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

Answer 9

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.