Monday, January 7, 2008

ASP.NET 2.0 Dataset ConnectionString Problems

ASP.NET 2.0 Dataset ConnectionStrings -
Development Vs. Production


I recently ran across an issue while working with the ASP.NET 2.0 Dataset and TableAdapters where my ConnectionString properties between my development environment and my production live environment got messed up. My local development environment worked great, however when I uploaded to production some of my data access commands (selects, inserts, updates, deletes) were throwing errors.

After a good deal of research with no luck I finally resorted to reading through the XML for the dataset to try to determine the problem, which with a little luck I was able to do. As it turns out I had added some new tables to the dataset, which in turn created TableAdapters, which is great. However, the new TableAdapters were set to use the local ConnectionString in the dataset and not the ConnectionString from my web.config. Ah ha... there is the problems, and now for the solution:

Setup:
ConnectionString in web.config that is different in local development version verses production environment.
Using ASP.NET 2.0 Datasets and TableAdapters for Data Access Layer

Resolution:

After scanning through my Dataset XML source code I found the following differences between some of the TableAdapter definitions:

<DbSource ConnectionRef="databaseConnectionString" DbObjectName="" DbObjectType="Unknown" GenerateShortCommands="True" GeneratorSourceName="UpdateQuery" Modifier="Public" Name="UpdateQuery" QueryType="NoData" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="True" UserGetMethodName="GetDataBy1" UserSourceName="UpdateQuery"/>


<DbSource ConnectionRef="databaseConnectionString (Web.config)" DbObjectName="" DbObjectType="Unknown" GenerateShortCommands="True" GeneratorSourceName="UpdateQuery" Modifier="Public" Name="UpdateQuery" QueryType="NoData" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="True" UserGetMethodName="GetDataBy1" UserSourceName="UpdateQuery"/>


Notice the second ConnectionRef property, it specifies that the ConnectionString should be pulled from the Web.config file, and not from the local property. That is the key, I updated all of the ConnectionRef instances to include the (Web.config) string and like magic my application worked great in the production environment.

-Aaron

3 comments:

Chris said...

I have been searching and searching for a way to set the connection once in the web.config. Thank you so much. Now if I make sure all of the datasets and table adapters have the generic database connection string name and just change the connection properties in the web.config I am set! Thanks again.

Derek said...

Thank you for this information. In my DAL project I found that the xsd contained: "databaseConnectionString (Settings)" and I replaced every occurrence with "databaseConnectionString (Web.config)". That did the trick!
Other people on the team were unable to run the code because of the reference to my local SQL server in the connection string.
The VS2008 solution has a separate project for the DAL.
The 'developer' connection string seems to be embedded all over the place:
Settings.settings
Settings.Designer.cs
app.config
DataAccessLayer.dll
-- and all the temp TableAdapter dlls

This seems like a bad idea to copy connections strings all over the place and embed them into DLLs.

Does anybody know how to avoid this mess?

Thanks

Derek

Anonymous said...

Thank you very much for the information!