Dynamic Connection for typed Dataset

**codecarnage** · Sep 19, 2009, 09:44 AM

Guys, I have a slight problem, which I need help fixing ASAP. I have a some pages in a project uses a few typed datasets as the datasource. Originally the connection string was stored in the web.config, now things have changed.

The connection can longer be used directly from that location, as a result I need to be able to override the connection (string) at runtime, I can't seem to find a solution which does this. I tried to reference the datatable

I'm not avid user of type dataset, I code everything to have greater control, but these pages weren't mine.

----

**ToxXxic** · Sep 19, 2009, 04:44 PM

In the page load event or any other event you desire you could add a code such as

Code:

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim selectCMD As SqlCommand = New SqlCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30

Dim custDA As SqlDataAdapter = New SqlDataAdapter
custDA.SelectCommand = selectCMD

Try
   nwindConn.Open()

   'where DataSet1 is the name of the typed dataset
   DataSet1.Clear()
   DataSet1.AcceptChanges()
   custDA.Fill(DataSet1 , "Customers")

   'Rebind your controls

   nwindConn.Close()

Catch ex As Exception
     'Throw
End Try

You actually change the connection string of the connection object because the dataset does not have a connectionstring property

**codecarnage** · Sep 20, 2009, 06:13 AM

Toxxxic thanks for at least attempting to answer my question, it would appear that there aren't many .net developers on this site or is it that the question was vague.

Based on the your code sample posted above, it would expect it work but for some reason it does not. Additionally I'm not particularly interested in overriding the various command objects that fills the Dataset. All I really want is to the change the connection while leaving everything else in tact.

My next observation is that there are not many properties or methods exposed by the typed dataset. Is that normal, I cannot see how the DS which is considered to be strongly typed does not expose its connection prop. etc.

Thanks again for the response, however my problem persists

**ToxXxic** · Sep 21, 2009, 08:20 AM

Originally Posted by codecarnage

Toxxxic
Additionally I'm not particularly interested in overriding the various command objects that fills the Dataset. All I really want is to the change the connection while leaving everything else in tact.

My next observation is that there are not many properties or methods exposed by the typed dataset. Is that normal, I cannot see how the DS which is considered to be strongly typed does not expose its connection prop. etc.

Datasets do not have connection properties. You can only manipulate a connection object , a dataAdapter.connection object or a command.connection ojbect that interacts with a dataset.

A typed dataset usually has a typed dataadapter. Ensure that you change the connectionstring property of the dataadaper.

There are however many ways to get around the problem you are having.
1. use an untyped dataset to bind all your controls instead of a typed one

2. Fill a untyped dataset with the data you desire then copy the data into the typed dataset.
example:

Code:

 
dataset1.Clear() 'typed dataset
dataset1.Acceptchanges()
'where untypedDataset is a dataset the you created through code. I assume you know how to fill an untyped dataset
dataset1=untypedDataset.copy 'copy the over the data

3. Manipuplate the Dataadapter as described here:
http://www.jstawski.com/archive/2007...taadapter.aspx
You can use an online c# to vb converter if do dont know c#

4. Programmatically change the connectionstring in the web.config file
example:

Code:

Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration("~") 
Dim section As ConnectionStringsSection = TryCast(config.GetSection("connectionStrings"), ConnectionStringsSection) 
If section IsNot Nothing Then 
    section.ConnectionStrings("MyConnectionString").ConnectionString = connectionString 
    config.Save() 
End If

5. create a new class for the dataset's tableadapter
Example;

Code:

'Create a class in your AppCode folder called <name of your table adapter>.vb and copy the following code into it 
'(making changes where necessary):

Imports Microsoft.VisualBasic

Partial Public Class YourTableAdapter
    Inherits YourDataSetTableAdapters.YourTableAdapter

    Public Property cConnectionString() As String
        Get
            Return Connection.ConnectionString
        End Get
        Set(ByVal value As String)
            Connection.ConnectionString = value
        End Set
    End Property
End Class

'Then on the page with your  ObjectDataSource declarations, add the following code:Protected Sub ObjectDataSource1_ObjectCreating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceEventArgs) Handles ObjectDataSource1.ObjectCreating
    e.ObjectInstance = New YourTableAdapter
    e.ObjectInstance.cConnectionString = ConfigurationManager.AppSettings("YourConnectionString")
End Sub

6. overide the connectionstring property of your project as shown here:
http://kmccaa.blogspot.com/2007/07/h...ts-own_11.html

**codecarnage** · Sep 21, 2009, 09:54 AM