DataSet AutoGenerated Code

[JaedenRuiner]

Is it wise to alter any of the dataset autogenerated code, like you create the dataset, and it generates the datatable classes and the tableadapterclasses, but there is generated one SqlConnection() class per table adapter, even when they are managed by a single TableAdapterManager. Is it wise at all to go into the code and add a property or alter it in some way so that you can have say:

class TableAdapterManager

property Connection() as SqlConnection
set
  m_connection = value
  Table1Adapter.Connection = m_connection
  Table2Adapter.Connection = m_connection
end set
end property

Yes, that is crappy pseudo code, but you get the idea. Can I do that at all, or should i never manipulate the "MyDataSet.designer.vb" file.
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

ps the other reason for this is that option that says when you create an MDF connection string for the dataset, you can decide to

"No, don't save sensitve data in connection string, i will add that programmatically"

When doing that how/where can you add that programmatically so that the password (and/or userid) is asked for separately and transmitted directly to the database through the connection without being in the connection string?

 

[JohnH]

You can't edit generated code, but you can create a partial class that extends it. Most VS generated classes are prepared for this by being marked with the Partial keyword.


Protecting Connection Information (ADO.NET) help article has some info about security. A connection can be set for example in form Load by using the TA.Connection property.

The other reason is perhaps worry about multiple connections to db? Pooling is used, so multiple connection instances in your code won't create more actual db connections.

 

[JaedenRuiner]

You can't edit generated code, but you can create a partial class that extends it. Most VS generated classes are prepared for this by being marked with the Partial keyword.

Well, i found the SqlConnectionStingBuilder which helped me with the whole security login aspect I was concerned with, but I guess my issue was more about the "setting" of the connection string.

See, in the TableAdapterManager class, generated by the XSD designer for a Typed DataSet, there are multiple connections, one for each TA, and an IDBConnection interface property for the TA Manager. However, of the many ways that one can set the connection and open it for the individual TA's for each DataTable to be populated for the DataState, it basically means storing the "connection string" in at least 1 if not more places. I rather did not like that concept of creating the connection string with User Id and Password to set to each table adapter's connection property. The initial connection string of all the TA's is imcomplete because the Password and UserId are not stored in my connection string I add them at Run time through a Logon Dialog.

Yet, after an initial connection is made with the appropriate User Id and Logon, when I try to access database with one of the TA's (not associated with my active connection) their connection fails because of an incomplete connection string, so i wanted to make the "1 time" connection and then set the Connection for all the TAs. The TA's Connection Property Set() method basically will take the Connnection value and store it internally as well as setting all the contained SqlCommand classes to the same connection. My issue was why the TableAdapterManager class did not initially do the same.
From my DataSet.Designer.xsd File

      <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
       Global.System.ComponentModel.Browsable(False)> _
     Public Property Connection() As Global.System.Data.IDbConnection
         Get
           ..snip..
         End Get
         Set(ByVal value As System.Data.IDbConnection)
            Me._connection = value
            'Added Lines here
            Me._ta_tblBOL.Connection = value
            Me._ta_tblEmail.Connection = value
            Me._ta_tblImport.Connection = value
            Me._ta_tblLocation.Connection = value
            Me._ta_tblMaterial.Connection = value
         End Set
      End Property

I figured this was probably not the wisest thing to do, but it does work within the construct of my application. My idea was to get the logon, build the string, connect, and then with "persist security info" off, the password string would be lost in the ether, but I'd have the valid connection to the DB, but I didn't want to have to set the connection property for ALL my typed DataTables manually. (i'm lazy I guess).

Can i instead put that Property in the Partial class, or override it somehow? Or would it be better to just create my own property in the partial class that sets these connection properties instead of updating the Designer.xsd?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[cjard]

Click anywhere in the dataset designer surface and you will see properties for the dataset itself. The ConnectionModifier property will allow you to change to "Public" which means all tableadapters within this dataset will expose their .Connection as a public property

There are also ways to avoid having the connection string stored in the settings file. search the forum for ApplicationScope, and look for a long thread of which I'm a participant