2

We are about to implement an application using winforms. I would like to have a 3 layer architecture (GUI, Business Logic and Data Access Layer.

We have one database per customer, so we must be able to access different databases (possible on different servers as well) using the application. E.g. Customer A is on server A and Customer B is on server B.

EDIT: Deployment scenario: This application may be installed on ServerA, but the databases might be on ServerA, ServerB, ServerC, ServerX (I think you get the picture).

Reading the db connection from a database is then somewhat complicated since I don't know which db the user wants to connect to. And to top it off the user ids are only unique in the same db, so a user with the username e.g "admin" can exists in multiple databases:)

We would like to be able to log on the application providing a user name, password and connection string information. Now, how do you send the connection string information to the DAL, so that the GUI nor the Business Layer doesn't have to have a knowledge of the database connection string? I would not like to store the connection string in the GUI project and pass that as a parameter to the Business Layer that in turns passes the connection string to the DAL every time I need some data in the database.

EDIT: The connection string information only need to be available while the user is logged in. As soon as he logs out this information should be deleted)

I have implemented a class in an new project that inherits from ApplicationSettingsBase (Both the UI project and the DAL project have a reference to the new project). So I'm now able to persist the connection information (to a user.config file by default). So I can instantiate that class from the User interface and store the connection information by calling base.Save on my class and then in the DAL I can instantiate the same class and read the connection information there. Not sure if I like that solution because the user.config file is tied to the windows user (by storing the file in the C:\Users...\AppData\ hierarchy and I'm not sure about the performance by doing it this way. Overkill maybe?

EDIT: I have not been able to find a satisfactory solution yet, so I appreciate more answers from the community:)

EDIT:

I found a way to solve this. I have only testet the solution in a small test project, but here goes:

The user logs in and the UI methods responsible for retrieving the login information runs this method: 

public void SetTempSetting()
{
       // Get the configuration file.
       Configuration config  = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);           

       // Add the connection string.
       ConnectionStringsSection csSection = config.ConnectionStrings;
                csSection.ConnectionStrings.Add(new ConnectionStringSettings("ConnectionStringName", GetConnectionString()));

      // Save the configuration file.
      config.Save(ConfigurationSaveMode.Modified);

}

The SetTempingSetting() method will write the connectionstring to the ProjectName.dll.config

And in the DAL project I can get the connectionstring from the ConfiguraionManager like this: 

var connectionstring = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

And when the user logs out of the application the logout method can execute this method to delete the connectionstring from the Project.dll.config

public void RemoveTempSetting()
        {
            // Get the configuration file.
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);            
            // Add the connection string.
            ConnectionStringsSection csSection = config.ConnectionStrings;
            csSection.ConnectionStrings.Remove("ConnectionStringName");
            // Save the configuration file.
            config.Save(ConfigurationSaveMode.Modified);            
        }

Any thoughts on this solution? Pros? Cons? Overengineered? Bad design?

OKB
  • 715
  • 3
  • 14
  • 30
  • Do these seperate customer DB's have the same schema? Is there some contractual or regulatory reason you keep them seperate? We had something like this and ended up combining DB's into one. Then adding a new customer was simply a matter of adding a row to a 'customer' table. Scales better, new clients spun up faster, same connection string all the time, etc. etc. – n8wrl May 10 '11 at 18:41
  • It's only historical reasons why we have separate customer db's. (Please don't ask me why:) ).The schemas are identical. So it's a matter of how to use multiple connection strings. – OKB May 11 '11 at 09:28
  • Could it be an idea to have a static class that contain the connection information in a separate project and then reference that project in the GUI project and in the DAL project? – OKB May 11 '11 at 09:29
  • OKB, if you solved your problem you should add it as an answer, not add it to the question. That way your answer can be found better, and may get you some upvotes. – Dour High Arch Aug 31 '11 at 19:34

4 Answers4

2

Your DAL needs some sort of configuration database. It needs to know how to connect to the configuration database, ask for connection information for a specific customer and then use that to resolve the query. Presumably, you're passing customer ID to the DAL.

I'd argue that it properly belongs in the DAL, rather than in your business objects or the presentation layer, as connection information, by its very nature is implementation-specific.

Whether that 'configuration database' is a config file, or something else is a design/implementation choice. Nothing to stop you from storing connection strings as user attributes in LDAP (1 LDAP user per customer), for instance.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • Thank you for answerering, but it don't help me much since the application don't know anything about the database it need to connect to before the user logs in (and provides db connection info) – OKB Jun 02 '11 at 15:09
  • @OKB: try reading up on the various standards for Service Discovery (http://en.wikipedia.org/wiki/Service_discovery). That may show you a path. – Nicholas Carey Jun 02 '11 at 16:37
1

Its always a challenge to support multiple customers. My solution to this problem was to create a DAL for each database type, which all used the same defined interface. At program startup I instaniated an instance of the appropriate DAL based on the customer configuration. I passed a token to the DAL that was a reference to the appropriate connection string in the config file of the DAL (the DAL was a part of a web service middle tier). I am going from memory here so I hope I am getting all the details right, however the main concept is to use dependency injection to instaniate the appropriate DAL, and then set a property of the instantiated DAL that allows it to retrieve the appropriate connection string, thus eliminating the need for storing the connection string on the client. So, at logon, the user can be presented with a list of tokens (representing connection strings) for which they have the appropiate permissions (done through user configuration), or there can be a default token for each user, which gets them database access with a minimum of credential entries.

Mike Hillman
  • 11
  • 1
0

As per my opinion, connection string should be stored in Data Access Layer.

FIre Panda
  • 6,537
  • 2
  • 25
  • 38
0

Here is how I did it. I save the connection string in GUI, but won't pass it around.

  1. Create a DataLayer, connect to database, this case I use Entity Framework. It'd create the app.config and connection string automatically

< add name="ECOMEntities" connectionString="metadata=res:///Model1.csdl|res:///Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=DEV;Initial Catalog=ECOM;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"" providerName="System.Data.EntityClient" />'

  1. I copy and past the connection string above to the GUI's App.config file

  2. I then delete the datalayer's app.configure file.

vnRock
  • 137
  • 3