how can i dynamically change the connection string

I am using vb.net and SQL server 2005

I have a project with database classes which are generated from database objects. That is I add a dataset to the project and drag and drop a database object onto it. The prob开发者_如何学Golem with this is that I have to give a connectionstring which is stored in the code. This is ok while developing the application. But runtime I want to use another connectionstring, both for security reasons and because each user of the application will correspond to a database user (SQL) and shall have a corresponding connectionstring. Is there an easy way to do this without having to restore to plain all datasets and do everything myself?

Store your connection string in the app.settings

http://msdn.microsoft.com/en-us/library/a65txexh(VS.80).aspx

You can add a file to your application call app.settings. In here you can store you connection strings (they can be encrypted). To access them you just call the following

Dim con As String = ConfigurationManager.ConnectionStrings("myDb").ConnectionString

If you have to log in explicitly with different user names and passwordsjust capture the username and password at application login. And build the string up programatically.

An example of this may be

  <connectionStrings>
    <add name="myDb" connectionString="MYSERVER;Initial Catalog=MYDB;Persist Security  
Info=True;User ID={username};Password={pwd}"/>
  </connectionStrings>

Dim con As String = ConfigurationManager.ConnectionStrings("myDb").ConnectionString
con = con.Replace("{username}",txtUserName.Text)
con = con.Replace("{pwd}",txtPassword.Text)

'now you can do something with con like hook it up to a dataset

You could try building your own connection string in code.

 Dim sqlConn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=" & my.Settings.Server & ";uid=" & txtUsername.Text & ";pwd=" & txtPassword.Text & ";database=" &  txtDatabaseName.Text & ";Connect Timeout=150;MultipleActiveResultSets=True")

So for example if you had a function that returned a value and you needed it run using your current values in the connection string you could do do something like the function below. (The code is pointless but more to show you what I am talking about)

Private Function GetFirstName(ByVal p_CustomerID As String) As String
  Dim cmd As New SqlClient.SqlCommand
  Dim sqlConn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=" & my.Settings.Server & ";uid=" & txtUsername.Text & ";pwd=" & txtPassword.Text & ";database=" &  txtDatabaseName.Text & ";Connect Timeout=150;MultipleActiveResultSets=True")
  Dim sReturnResult As String = String.Empty

  With cmd
    .Connection = sqlConn
    .CommandText = "SELECT [FIRST_NAME] " _
          & " FROM CUSTOMER " _
          & "  WHERE ID = @ID"
    .Parameters.Add("@ID", SqlDbType.VarChar, 30)
    .Parameters("@ID").Value = p_CustomerID
  End With

  Dim SQLAdapter As New SqlClient.SqlDataAdapter(cmd)
  Dim ds As New DataSet

  Try
    SQLAdapter.Fill(ds, "CUSTOMER")

    If ds.Tables(0).Rows.Count > 0 Then
        sReturnResult = ds.Tables("CUSTOMER").Rows(0).Item("FIRST_NAME").ToString()
    End If
  Catch ex As Exception
    MsgBox("GetFirstName: " & ex.Message)
  Finally
    ds.Dispose()
    sqlConn.Dispose()
    cmd.Dispose()
    SQLAdapter.Dispose()
  End Try

  Return sReturnResult


End Function

If you had a strongly typed DataSet that you wanted to change the table adapter on you could also do it in a similar fashion

Dim sqlConn As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=" & my.Settings.Server & ";uid=" & txtUsername.Text & ";pwd=" & txtPassword.Text & ";database=" &  txtDatabaseName.Text & ";Connect Timeout=150;MultipleActiveResultSets=True")

CUSTOMERTableAdapter.Connection = sqlConn

One final note is that I personally never check the box to store the connection string inside the DataSet. I have found if I did this by mistake the easiest way to resolve this was to create another project make an identical DataSet and not select the save connection string information and then I would copy and paste the 3 or 4 files over top of the existing project DataSet (make a backup first) and then reopen the project do a clean and rebuild.