how can i dynamically change the connection string

Question

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 problem 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?

Answer 1

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