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.
精彩评论