How to solve these data access issues in VS2005?

[Guest]

1. Connection String needs to be customizable by users during runtime.
2. Source of Connection String can be changed programmatically.
3. Tables can be modified by users.
4. Use table adapters or use data adapters
5. How to update all data tables for all datasets with changes in a
Windows Form without specifying dataset?

 

[Cowboy \(Gregory A. Beamer\)]

Not completely data "access" issues.

1. Connection String needs to be customizable by users during runtime.

This is not a major issue, as you can use a singleton for connection and
change at the user's whim. If you want to save back the connection string,
it is a bit more of an issue if you are using web.config alone, but it is
not insurmountable, as you can add auxiliary files with your own config
information.

2. Source of Connection String can be changed programmatically.

Source of connection string? You mean sometimes it comes from config,
sometimes from metadata tables, sometimes from XML files, sometimes from
user input? Or, do you mean you need to be able to change the connection
string using code?

If the former, set up a factory pattern with different options that are
available and you can then use the correct factory based either on config or
user input.

If the later, shy away from TableAdapters in 2.0, unless you want to edit
the code created to allow you access. In 3.5 (VS 2008, formerly Orcas) the
field opens up.

3. Tables can be modified by users.

To alter actual data tables, use SMO.

4. Use table adapters or use data adapters

Table Adapters are cool, but a pain for the flexibility you require during
run time.

5. How to update all data tables for all datasets with changes in a
Windows Form without specifying dataset?

I am not sure I understand this. Any time you open a table, you will have a
dataset. As long as you know which database you are currently using
(singleton config information?) and have the dataset, you can fire off an
update. Am I missing something?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************

 

[Guest]

Hi Gregory,

Thanks for your post.

For my questions #1 and #2:

I need to able to change the connection string using code and the changes
need to be saved. For example, I need the connection point to a production
MS SQL Server instead of development MS SQL Server. Another example, I need
to connect to a different database.

My understanding is that the connection string information is stored in
app.config by default. So, the question is how to read/write that
information. It seems that I may able to use ConfigurationManager class to
do that.

Peter