Connection string / design question

[Jens Weiermann]

Hi,

I have a question regarding the handling of connection strings.

I have (finally) written a data access assembly that does all the reading /
writing from/to the database. Basically, this is a Dataset.xsd type thingie
with all the table adapters and stuff.

Now I want to use this data access assembly in all other parts of my
application - windows forms apps, web apps, web services and windows
services. Depending on where these apps are run, the connection string has
to be different (some machines are in the domain so I can use trusted
connections, some are outside so I must use sql authentication), but all of
them connect to the same database. Plus I must be able to change between
development / production databases without changing the source code.

How would I best go about managing these connection strings?

Thanks in advance!
Jens

 

[William \(Bill\) Vaughn]

This is an issue that we've seen addressed in the new version of Visual
Studio (Orcas) due out this year. While it can be done in the current
versions, it's tough to manage and nearly impossible to explain without
hand-puppets and special effects.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Jens Weiermann]

This is an issue that we've seen addressed in the new version of Visual
Studio (Orcas) due out this year. While it can be done in the current
versions, it's tough to manage and nearly impossible to explain without
hand-puppets and special effects.

Thanks for your answer - not quite what I wanted to hear, though ;-)

Got a short hint to get me started?

Jens

 

[Rory Becker]

This is an issue that we've seen addressed in the new version of

Visual Studio (Orcas) due out this year. While it can be done in the
current versions, it's tough to manage and nearly impossible to
explain without hand-puppets and special effects.

Eem.... Whilst I would truly love to see the puppet show, what's wrong with
loading the connection string from the app.config file?


---Optional posibly too complicated/involved information---
Actually we use a more complicated version where our DA dll user a provider
to locate the connection string. this provider is set differently on a per
application basis.

One provider uses the App.Config

Another hard codes it.

Another looks in the string.

Another (Web oriented) is clever enough to look in the bin director at a
custom class.

You could easily make one to load it from the registry or via a webservice
if that's what floats your boat.
-------------------------------------------------------------

All of this aside, simply reading from the App.Config (MyApp.exe.Config)
file should work

Shouldn't it?

Have I made some giant mistake?

 

[Rory Becker]

This is an issue that we've seen addressed in the new version of
I nearly forgot to ask....

What neat trick has Orcas come up with to deal in this area?

I have the VPC locally and would like to play araound in this are.

Thanks

 

[William \(Bill\) Vaughn]

The demo that I saw with Whidbey took about a dozen steps to accomplish and
no, I don't recommend it. It's just too fragile.
The Orcas demos we saw last month at the Summit made it look a lot easier.
Could I repro this at this point? Ah, no. I expect we'll see it demoed at
TechEd.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Jens Weiermann]

Actually we use a more complicated version where our DA dll user a provider
to locate the connection string. this provider is set differently on a per
application basis.

Hi Rory, thanks for your reply.

How are you actually doing that? I mean, I guess I could do this quite
easily when hand-coding all the stuff, but I would prefer to use the visual
dataset designers of VS. Problem here is that you cannot assign a
connection *string* to the table adapters, only a connection *object*. Only
items available to choose from are the connections defined in the settings
file...

Thanks!
Jens

 

[Rory Becker]

Actually we use a more complicated version where our DA dll user a

How are you actually doing that? I mean, I guess I could do this quite
easily when hand-coding all the stuff, but I would prefer to use the
visual dataset designers of VS. Problem here is that you cannot assign
a connection *string* to the table adapters, only a connection
*object*. Only items available to choose from are the connections
defined in the settings file...

Sorry Jens... all done manually.. no designers here.

Although we do generate Strongly typed classes on a per table basis for our
dataaccess layer.

Each of these knows (from our template) where to find the global ProviderSocket
which should hold a connectionstring provider.

I hope this helps

 

[Rory Becker]

Problem here is that you cannot assign

a connection *string* to the table adapters, only a connection
*object*. Only items available to choose from are the connections
defined in the settings file...

Ok I'm "totally" not familiar with the visual designers

(So where are they? and how come you can't mess with what they create?)

but....

Don't they ultimately generate you some code which does the assignment of
a connection Object or a connection string somehow?

Couldn't you intercept/replace this code with your own logic?

(I might creating a derivative from SQLConnection (I'm still thinking in
VS2003 terms... Sorry ) and having the constructor grab the ConnectionString
from some known location.

I guess I've spent so much time not trusting designer generated code (Yeah
funny now I'm generating classes of my own ), that I have just gotten used
to doing most of the hard graft myself.

[The use of "MyGeneration" (www.MyGenerationSoftware.com) has made this much
easier although I gather that CodeSmith can do this sort of stuff too]

I am however interested in trying some of this stuff out again.... Any pointers
as to where I should look and what I should mess with?.....

Anyone...?

(BTW Jens..... What version of Studio are you using?.... I have heard that
the databinding in VS2005 is much better but I'm not sure where to start)