Results 1 to 9 of 9

Thread: Dynamic Connection for typed Dataset

  1. #1
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default Dynamic Connection for typed Dataset

    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.

    ----

  2. #2
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    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
    Last edited by ToxXxic; Sep 19, 2009 at 04:58 PM.
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  3. #3
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    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

  4. #4
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    Quote Originally Posted by codecarnage View Post
    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
    Last edited by ToxXxic; Sep 21, 2009 at 09:22 AM.
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  5. #5
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    Quote Originally Posted by ToxXxic View Post
    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.
    I was speaking generally when I said modify the connection of the dataset, what I really meant was that I wanted to modify the connection being used/references by the DA objects found within the xsd file...'dataset'

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

    There are however many ways to get around the problem you are having.
    Quote Originally Posted by ToxXxic View Post
    1. use an untyped dataset to bind all your controls instead of a typed one
    The problem exists on multiple pages at this stage of the project I don't want to modify much of the code.

    Quote Originally Posted by ToxXxic View Post
    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()
    dataset1=untypedDataset.copy 'copy the over the data
    Interesting...but again may require more code than I'm willing to write

    Quote Originally Posted by ToxXxic View Post
    3. Manipuplate the Dataadapter as described here:
    http://www.jstawski.com/archive/2007...taadapter.aspx
    This resource seems to provide a variation to the solutions I've found to the general problem, however what seems to be my problem is that I'm working on a asp.net website and not a windows nor web application. The designer.vb file which would normally created is not available at design time when working with websites. The respective code is actually auto-generated ad run time by the compiler.

    In one of the responses to the linked post, someone indicated that the connectionModifier property of the DA should be set to public, however I noticed that even when doing so the method/property is not directly accessible as is pro ported. If the DA class was implemented as a shared class then it think I would have had better luck...and this is where I think I may over thinking, If I was to instantiate a new class of the type DANAME, would the dataset automatically use it, or would I now have to programmatically reassign the new DA?

    Quote Originally Posted by ToxXxic View Post
    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
    Not keen on this solution, thanks for posting not applicable to my project...but has educational value for the TJ community. I've done this is other projects, under more controlled environments.

    Let me offer some backgroud. In the solution's original state the connection string was stored in the web.config file and was used throughout the site perfectly fine. However I've recently been instructed to encrypt the connection string - which is to be stored instead of the of the clear text. I now have a method which retrieves the encrypted string, decrypt it and then return it to the requesting objects. The datasets I'm having trouble with dosen't seem to offer me that level of ease

    ---


    options 5 and 6 seem to be brilliant answers and that (Ans. 5) was the path I selected after some research, I was tired so I didn't finish the implementation...hopefully it works out well.

    Thanks Again...your participation on the post was much appreciated.

  6. #6
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    this is what normally works for me. I normally change the typed dataadpter connectionstring properties


    Code:
    Dim constr As String = "workstation id=ToxXxic;packet size=4096;integrated security=SSPI;data source=ToxXxic;persist security info=False;initial catalog=ToxXxic"
            Me.SqlDataAdapter1.SelectCommand.Connection.ConnectionString = constr
            Me.SqlDataAdapter1.DeleteCommand.Connection.ConnectionString = constr
            Me.SqlDataAdapter1.SelectCommand.Connection.ConnectionString = constr
            Me.SqlDataAdapter1.InsertCommand.Connection.ConnectionString = constr
    I usually put this in a class or global module and call it in all the page load events
    Last edited by ToxXxic; Sep 21, 2009 at 10:14 AM.
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  7. #7
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    Ok...I've got it. somehow I believe something is wrong with my VS installation but thats a whole other matter.

    What needs to be done:
    On the respective tableAdapter for the desired dataset(xsd), change the connectionModifier property to public.

    Then on the page with your ObjectDataSource declarations, add the following code:
    ----------------------------------------------------------------------------------------------

    Code:
    Protected Sub ObjectDataSource1_ObjectCreating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceEventArgs) Handles ObjectDataSource1.ObjectCreating
        
                  dim dAs as yourTableAdapter = New yourTableAdapter
                  dAs.Connection = New SqlConnection("runtime_conn_str_to_use")
                  e.ObjectInstance = dAs
    
    End Sub
    please remember to import the corresponding namespace for the data adapters being referenced.

    * It is not very different from Toxxic's suggestion, however this is the solution which actually solved the problem I had....

    respect

  8. #8
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    why didnt you just encrypt the configuration section... and let the framework worry about decrypting it at runtime?

    using DPAPI
    using RSA

    Programmatically
    Sorry i know im late, bu someone else might benefit
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  9. #9
    Join Date
    Jul 2004
    Posts
    153
    Rep Power
    0

    Default

    I came across this solution, however there are several other data and objects which are encrypted in the app. and I wanted them to all use the same encryption class/algorithm.

    Plus there was a partiality to using AES by a certain IT audit firm.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •