Tech Off Thread

34 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Connection String for Typed Datasets

Back to Forum: Tech Off
  • User profile image
    SLTDon

    Storing the connectionstring for a typed dataset in a readonly setting does not make a lot of sense.  What is the likelyhood that any of my 40 customers is going to have the same servername, userid and password as my development machine.

    How can I build a connectionstring and pass it to a typed dataset so that it won't try to use the development time connectionstring?

    Somebody obviously didn't think this through, or they expected us to just develop software and never deploy it.

    This isn't the only case where I have found functionality highly touted by Microsoft that lacks necessary functionality for deployment to multiple customers.

  • User profile image
    malathyr

    This is how I solved the issue (ateast in Visual studio 2008)

    Select (click on .xsd) file
    press F7

    Now you will see a partial class for the TableAdapter and Class

    In the Tableadapter, copy the following

    public string ConnectionString

    {

    get {return this._connection.ConnectionString; }

    set

    {

    if (this._connection == null)

    this._connection = new System.Data.SqlClient.SqlConnection();

    this._connection.ConnectionString = value; }

    }

    and when want to set the connectionstring, do the following

    Purchase.ContractsTableAdapters.ContractTableAdapter contractTA = new ContractTableAdapter();

    contractTA.ConnectionString = "Data Source=localhost;Initial Catalog=Contract;Integrated Security=True";

    Contracts.ContractDataTable contractsDT = contractTA.GetContracts();

    I hope this helps someone

  • User profile image
    jmbledsoe

    SLTDon wrote:
    Storing the connectionstring for a typed dataset in a readonly setting does not make a lot of sense.  What is the likelyhood that any of my 40 customers is going to have the same servername, userid and password as my development machine.

    How can I build a connectionstring and pass it to a typed dataset so that it won't try to use the development time connectionstring?

    Somebody obviously didn't think this through, or they expected us to just develop software and never deploy it.

    This isn't the only case where I have found functionality highly touted by Microsoft that lacks necessary functionality for deployment to multiple customers.


    Am I right that you're using the generated TableAdapters to do your data access?  If so, you can programmatically access the Connection property of any adapter instance that you create, and set it to any value you desire.  You will probably want to create a factory class that instantiates, configures and passes out TableAdapter instances.

    With all that said, though, I'm not much of a fan of the TableAdapters.  They won't help you fill related tables very easily.  You should try the MultiTableDataAdapter in the DataSet Toolkit instead!  Wink

  • User profile image
    SLTDon

    That property is readOnly.Sad

  • User profile image
    jmbledsoe

    SLTDon wrote:
    That property is readOnly.


    Expressionless . . . perhaps it depends on your RDBMS.  I whipped up a TableAdapter pointed at a SQL Server database, and the Connection property is settable. 

    Could you set the ConnectionString property of the Connection object? Assuming that all your deployments access the same RDBMS, you should be fine.  Big Smile

  • User profile image
    SLTDon

    Using SQL Server 2005.  The connectionstring property in the tableadapter of a typed dataset is readonly.

  • User profile image
    bitmask

    Are you using Visual Studio 2005? The table adapters can read the connection string from the app.config / web.config file so you can change the string without recompiling. There is no ConnectionString property in a strongly typed TableAdapter (but you could add one as a public property in a partial class).

  • User profile image
    SLTDon

    Yes but I need to be able to change it at run time at the customer site and it is readOnly.

  • User profile image
    bitmask

    You mean the configuration file is read-only? That's something you (or an admin) can undo.

  • User profile image
    SLTDon

    No I mean the connection setting in app.config.  Come on Can't anyone tell me how to fix this or is VS2005 just a pile of crap.  This is really pissing me off.   I am so disappointed in Microsoft, and I had low expectations.Perplexed

  • User profile image
    SLTDon

    Two questions.  There is a private sub called InitConnection() in the typed dataset. 

    1.  Can I write a sub to override that private sub and,

    2.  How do I do that?

    Thanks

  • User profile image
    jmbledsoe

    SLTDon wrote:
    Two questions.  There is a private sub called InitConnection() in the typed dataset. 

    1.  Can I write a sub to override that private sub and,

    2.  How do I do that?

    Thanks

    I think we're crossing signals here, or something else is going on in your environment.  First of all, I'll assume you found the InitConnection method on a generated TableAdapter class, not on your typed DataSet.  If that method really exists on your DataSet, then we're talking about two different things.

    With that said, I was able to create an instance of the MyDataSetTableAdapters.MyTableTableAdapter class, and it had a Connection property that is settable.  (MyDataSet is a DataSet that I created, and MyTable is a DataTable in that DataSet).  I too am working with a SQL Server 2005 database.

    Maybe you can pare down your code to a simple case where you still get the problem, and post it here for us to review.  Cool

  • User profile image
    SLTDon

    OK, I am looking in the dataset of the .xsd file with the Object Browser and I see no Connection Property.

  • User profile image
    graphicsxp

    Hello,
    I am experiencing exactely the same problem  as you ! I have to be able to change the xsd connection string at runtime and it's giving me a very hard time. Have you managed to solve this problem ?

    Thanks

  • User profile image
    jmbledsoe

    SLTDon wrote:
    OK, I am looking in the dataset of the .xsd file with the Object Browser and I see no Connection Property.

    Right, and you shouldn't find it there.  As far as I can tell, strongly-typed datasets will never have a Connection property.

    Let's say your typed DataSet is called AdventureWorks.  To access the table adapters for that DataSet, you need to look in the namespace AdventureWorksTableAdapters.  This namespace will have a single class for each table in your DataSet, called <Table Name>TableAdapter.  For instance, one of the tables in my DataSet is named Department, so I have a class in the AdventureWorksTableAdapters namespace called DepartmentTableAdapter.  It's *this* class that has the Connection property that I've been referring to.

    Now, this property is declared as internal, so you'll need to either:
    1. Create a partial class for each table adapter that has a public settable property for the ConnectionString, as bitmask suggested above, or:
    2. Create a class in the same assembly as your table adapters that sets their Connection properties.  This class should probably be a factory that creates your table adapters.
    I don't know what else to say really, so I hope this helps!  Smiley

  • User profile image
    sdoc

    Let me explain my problem in detail.I have a combobox (cbxCustomers) which needs to be linked to a column(CustomerName)  of table (Customers) in a database .

    So I dragged and dropped a BindingDatasource .created a new DataSource for my database (this brought up a wizard,in which I selected the table i needed ,it also gave me a choice to save  connection string as a variable which i named as dbConnectionString) At the end of this a DataSet and TabelAdapter appeared on my form.

    now the value of the ConnectionString is saved in the app.config file under applications settings section.But this section is read only

    I also noticed that the Dataset.xsd had a connection string section,and this is what tells it which connection string to use.
     <Connections>
              <Connection AppSettingsObjectName="MySettings" AppSettingsPropertyName="dbConnectionString" ConnectionStringObject="" IsAppSettingsProperty="True" Modifier="Assembly" Name="dbAccessConnectionString (MySettings)" PropertyReference="ApplicationSettings.MyApp.My.MySettings.GlobalReference.Default.dbConnectionString" Provider="System.Data.OleDb">
              </Connection>
            </Connections>


    now my problem is I needs to be able to change the value of the Connection String at deploy tim.What do i do?

  • User profile image
    sdoc

    see next post...

  • User profile image
    jmbledsoe

    sdoc wrote:
    now my problem is I needs to be able to change the value of the Connection String at deploy tim.What do i do?


    So, just to clarify, it's at deployment time and not runtime that you want to change the connection string, right?

    sdoc wrote:
    . . .now the value of the ConnectionString is saved in the app.config file under applications settings section.But this section is read only. . .


    When your application is compiled, it copies the app.config file to the same location as the executable, and renames it to <executable name>.config.  I may be stating the obvious, but that file is just an XML configuration file.  Make sure it isn't read-only (at the file system level), open it up in a text editor and plug in the connection string that you want to use.  Then deploy your application, including your modified config file.

    I haven't tested this, but from what I know about .NET, and from what you described you did, then this should work.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.