asp.net mvc multiple connection strings

问题:

I am creating asp.net MVC Application using MVC 3.0. I have 2 users but the DataBase is the same. So, Is it possible to setup two connection strings or even more in web.config? when user login, I redirect it to his database, so then he can use his DataBase.

So major issue here is to find out which user is logged in and use connection string for that user.

I am using default mvc account controller and for example when i want to display welcome message for user in my view i type: if (@User.Identity.Name == "UserName") then some message So where is the best place to find out which user is logged in and set his connection string in controller or in a view?

回答1:

I agree with Jakub's answer: there are better ways of handling multi-tenancy than having a different database per user.

However, to answer your specific question, there are two options that come to mind:

1. You can set the connection string to a session variable immediately after login.

2. Your data access layer can choose the connection string based on the logged in user when it's created. (I'd recommend this over the first option)

   1. To store the connection after login, if you're using the standard ASP.NET MVC Account Controller, look at the LogOn post action:

      [HttpPost]
      public ActionResult LogOn(LogOnModel model, string returnUrl)
      {
          if (ModelState.IsValid)
          {
              if (Membership.ValidateUser(model.UserName, model.Password))
              {
                  //EXAMPLE OF WHERE YOU COULD STORE THE CONNECTION STRING
                  Session["userConnectionString"] = SomeClass.GetConnectionStringForUser(model.UserName);
      
                  FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe);
                  if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/")
                  && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
                  {
                      return Redirect(returnUrl);
                  }
                  else
                  {
                      return RedirectToAction("Index", "Home");
                  }
              }
              else
              {
                  ModelState.AddModelError("", "The user name or password provided is incorrect.");
              }
          }
      
          // If we got this far, something failed, redisplay form
          return View(model);
      }
      

   2. If you wanted to select the connection string when doing data access, your repository or data access layer will probably have a system for handling that. For instance with Entity Framework Code First, the DbContext constructor allows you to pass in the name of a connection string when you're creating it:

   connectionString = SomeClass.GetConnectionStringForUser(model.UserName); DbContext context = new DbContext(connectionString);

But again, I'd look at other ways of handling multitenancy unless your business dictates that your users have physically separate databases.

回答2:

Yes, you can have as many connection strings in your web.config file as you want.

But, if you're designing a multi-tenant application than there are better ways of doing it than adding a connection string to web.config file every time a new user signs up.

Probably the best way for you is to have a single database where user-related tables have foreign keys to Users table.

You can learn more about multi-tenant architectures from this Microsoft article.

回答3:

you can have multiple connection strings in web.config. Now if you want to use different connection string for different users there must be some criteria for division of users

 <appSettings><add key="connectionString" value="Data Source=develope\sqlexpress;Initial  Catalog=validation_tdsl;Integrated Security=True;Max Pool Size=1000;Connect Timeout=60000;"></add>
 <add key="connectionString1" value="server=MARK\SQLEXPRESS;database=name;integrated security=true;Max Pool Size=1000;Connect Timeout=60000;"></add>
  <add key="connectionString2" value="server=name\SQLEXPRESS;database=FM;integrated security=true;Max Pool Size=1000;Connect Timeout=60000;"></add>

and later you can use them like following

  Dim con As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))

 Dim con1 As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString1"))

EDIT : In c# it would be:

  SqlConnection  con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"]);

  SqlConnection  con1 = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString1"])

Note: ConfigurationSettings is now obsolete.