Data Access and ObjectDataSource Control

Re: Strongly typed DataSet connection string

mysun — Sat, 20 Sep 2008 07:11:55 GMT

I have the same problem, can explain in some detail

Re: Strongly typed DataSet connection string

tonydelabone — Thu, 08 Nov 2007 08:18:27 GMT

Great help! This helped me a lot!

Re: Strongly typed DataSet connection string

Tick — Wed, 10 Oct 2007 18:22:39 GMT

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

Thanks to All!

Re: Strongly typed DataSet connection string

trongkha — Wed, 13 Jun 2007 17:49:07 GMT

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;

Re: Strongly typed DataSet connection string

salgo60 — Tue, 27 Mar 2007 08:28:26 GMT

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

Re: Strongly typed DataSet connection string

michiel1978 — Wed, 06 Dec 2006 09:42:27 GMT

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!

Re: Strongly typed DataSet connection string

hedroj — Fri, 09 Jun 2006 07:58:43 GMT

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

Re: Strongly typed DataSet connection string

pheld — Wed, 03 May 2006 16:38:53 GMT

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

Re: Strongly typed DataSet connection string

pheld — Wed, 03 May 2006 16:35:09 GMT

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

TIA.

PAH

Re: Strongly typed DataSet connection string

indirection — Tue, 11 Apr 2006 17:11:51 GMT

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.

Re: Strongly typed DataSet connection string

indirection — Tue, 11 Apr 2006 15:29:46 GMT

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.

Re: Strongly typed DataSet connection string

mark182 — Tue, 28 Mar 2006 06:55:55 GMT

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.

Re: Strongly typed DataSet connection string

BradMi — Mon, 27 Mar 2006 23:04:32 GMT

When the DataSet is created as part of a Web project, the TableAdapter reads connection string values directly from Web.config using the config APIs.

When the DataSet is created as a separate class libary project, the TableAdapter reads connection string values through an associated settings class (compiled from a .settings file at build time). The settings class will read the value from Web.config. As long as you have the correctly-named connection string name in Web.config it should work, for example:

Hope this helps,

Bradley

Re: Strongly typed DataSet connection string

mark182 — Wed, 22 Mar 2006 06:51:41 GMT

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.

Re: Strongly typed DataSet connection string

mark182 — Tue, 21 Mar 2006 14:54:35 GMT

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.