Hi,

Hopefully someone will be able to help me with this as I haven't found a satisfactory solution to this seemingly trivial problem yet.

We have a web application using strongly typed datasets with the code seperated into several layers.  The datasets are contained in their own assembly with the data access classes in another.  When creating the datasets VS.Net will automatically generate the settings.settings and app.config files in the dataset assembly, with the connection string stored in both.  We are storing the correct connection string in the web.config file, but cannot find an easy way to get the TableAdapters to use this connection string.

The best I've come up with is to delete the settings and app.config files in the dataset assembly and create a class with a static string that returns the correct connection string from the web.config file, this class needs to match the name of AppName.Settings.Default.ConnectionStringName.  This seems to work but is a bit kludgy, as adding any subsequent datasets will recreate the settings and app.config files.

Surely there's a better way.  Is there a way to create the typed datasets and TableAdapters without VS.Net creating those extra files?  Is there another way to instruct the TableAdapters to another connection string?

Regards,
Mark.

Another approach would be to explicitly assign a SqlConnection initialized with the connection string you need.

i.e.:

using(SqlConnection connection = new SqlConnection(..))
{
    adapter.Connection = connection;
   // .. do work
}

That is ideally what I'd like to do, but it looks like the TableAdapter that is autogenerated has the Connection property marked internal and I'm keeping my DataSets and data access code in seperate assemblies.

Reading another post on TableAdapter's led me to the ConnectionModifier property in the TableAdapter designer, so now my Connection is marked public and I can now assign my connections in the method bitmask described.

I just need to figure out a way to remove those extra app.config and settings.settings file in my DataSet assembly.  But at least it's working the way I want now.

Thanks Bradley, that's exactly what I was looking for.  I actually tried that method a while back but I must have got the naming of my connection string wrong because I could never get it to work, but it's working fine now.  The app.config file in the dataset assembly was always bothering me, but now I see it's only used by the designer in this scenario.

Thanks,
Mark.

Guys, thanks for all your helpful posts.  I am relatively new to asp.net, and I have a very similar problem to those described above...

I am writing a data access layer for a database which will be used in a web application.
I would like to use Nunit to test the data access layer, and have written a simple windows application that runs unit tests on a test database. 
I am using data sets and table adapters and would like for the table adapters to be able to use a connection string specified either in the web.config, or the app.config file, depending on whether the DAL library is being called from either the web application or the windows test application.
Currently I have focused on trying to get the library dataTable adapters to pick up a connection string defined in the web.config file.
I have tried Bradley’s suggestion, but with no luck so far.  I'm going crazy here, and would love it if someone with more experience could help me out...

I have some questions:
In the DAL library, there is the 'Settings.settings' and the 'app.config' file.  Am I correct to think that I should configure my table adapter to use a connection string from the Settings.settings file - lets call the connection string 'HappyConnectionString'.  then, at runtime, when the 'Settings' class in the DAL library is asked for the value of 'HappyConnectionString', it will somehow overwrite the value of happyConnectionString with the value it finds in the web.config file, as long as in the web.config file I have specified the fully qualified name of the connection string?
so in my project, I have the following...

A class library called HappyAppDAL. In this library, there is a folder called properties, and in the properties folder there is a file called Settings.settings.  I use the designer to make sure there is one entry in the Settings.settings file.  The entry has the following attributes:

Name - HappyConnectionString
Type - (Connection String)
Scope - Application
Value - Data Source=jim\sqlexpress;Initial Catalog=TestDatabase Security=True


Now I have a web project which uses HappyAppDAL.  in the web.config file I have the following section:

<configuration >
  <appSettings>
...
  </appSettings>
    <connectionStrings>
    <add name="HappyAppDAL.My.MySettings.HappyConnectionString" connectionString="Data Source=jim\sqlexpress;Initial Catalog=ProductionDatabase;Integrated Security=True"
          providerName="System.Data.SqlClient" />
  </connectionStrings>

When I build the web project and run it, I put a breakpoint just after I have created an instance of a HappyAppDAL table adapter, I look at the attributes of the table adapters connection, and am dismayed to see that it is using the connection string defined in the HappyAppDAL  Settings.settings file (Catalog=TestDatabase), and not the connection string from the web.config file (Catalog=ProductionDatabase).

In fact – I also put a breakpoint on the generated code in the file Settings.Designer.cs, which is the get method for the property ‘HappyConnectionString’ – I never seem to hit this breakpoint!!

Can anybody please tell me what i am doing wrong here?  is my connection string name not correct?  not fully qualified?  Am I supposed to delete the app.config and Settings.settings file in the HappyAppDAL project??

Any help on this would be greatly appreciated.  Sorry for writing a novel, but I wanted to try to explain myself clearly in the hope that someone might be able to help me.

Thanks in advance.

 

Glyn.

Thank goodness - its working for me now.  Along my travels, I learned the following, which I hope will help someone out...

I read somewhere that i should delete both the app.config and the Settings.settings file in the library, so i did this, and then i tried to add a setting (in the lib project) using the application setting designer.  I did this, but this (of course) did not create an entry in the app.config file (for the lib project).

I then used the connection string wizard - by clicking a datatable adapter in the datatable designer, and then when selecting a connection string, selected 'new connection...'  i then created a dummy connection - pointing to an arbitrary database (which did exist).  this process made the designer see that my Settings.settings file had a connection string in it which did not have a corresponding entry in the app.config file - so it put one in there for me.
The new entry in the app.config file now had the correct fully qualified name of the connection string, It looks like this...

        <add name="HappyAppDAL.Properties.Settings.HappyConnectionString"
             connectionString="Data Source=jim\sqlexpress;Initial Catalog=TestDatabase Integrated Security=True" providerName="System.Data.SqlClient" />


and in my web.config file, i changed the connection string there, so it now looks like this...

    <add name="HappyAppDAL.Properties.Settings.HappyConnectionString" connectionString="Data Source=jim\sqlexpress;Initial Catalog=ProductionDatabase;Integrated Security=True"
          providerName="System.Data.SqlClient" />

I now observe the table adapters in my HappyAppDAL library are all using the production database connection string specified in the web.config file.

Only took me about 8 hours to figure this out!

Take care out there,
Glyn.

We have the same issue. Have you figured out how to resolve this?

TIA.

PAH

Sorry...your second post did not show immediately. I will be putting your hard work (frustration and turmoil) to work for me :)

Thanks for posting back. --PAH

First, I would like to thank you BardMi. You solved my problem too.

Second, I have the following issue:

Auto generated SQL inserts uses ExecuteNonQuery which returns the number of rows effected; howevere, I need the ID of the inserted row.

The SQL inserts that are generated appends Select statement with Scope_Identity() but are not used due to the use of ExecuteNonQuery.

Question: Is there a way to resolve this problem?

The way I am doing it right now is via a global replace with a custom method I wrote.

Thanks

Ihab

 

mark182:

We have a web application using strongly typed datasets with the code seperated into several layers.  The datasets are contained in their own assembly with the data access classes in another.

Hello,

if you don't mind me asking, how did you separate these into separate assemblies. Do you use the Visual Studio DataSet Designer generated classes, because I thought they were generated into one file, so I can't separate the TableAdapters from the DataTables and DataRows?

Thanks!
 

I think he tells you that he has a layered solution where e.g.

 

UI - is one project

BLL is one class library ==> one assembly

DAL is another project ==> another assembly

Here is a solution for DataObject class using Strong Type Dataset.

tblDataTableAdapter mAdapter = new tblDataTableAdapter();

//set Adapter to use the custom connection string from project config file (ex: web.config, or app.config)

 string mConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AConnectionString"].ConnectionString;

 if (!string.IsNullOrEmpty(mConnectionString))

 mAdapter.Connection.ConnectionString = mConnectionString;

Wow! This post saved me.  I spent quite a while digging for this solution.

 

Thanks to All!