I have a website that will be used by multiple companies. Each company will have it's own database to work with.
Is there a way to create a dynamic connection string or web config file for each user/company that logs into the site?
I can use a url key or database lookup for the identity but I don't know how to create and use either a dynamic connection string or web config file if possible?
The plan is 1 website with multiple databases, each database is identical in structure but each having data for a specific company.
What is the best practice for this application. Using 1 database is not an option however I can have a common database that can be used to get the connection information.
since connections string is a pretty sensitive data, you should store it in db. You could also store it to Web.Config file, but i'd store it in the db since it will make your life much easier.
I would assume that writing to the web config would cause a problem because every user that logs in would modiify the web config and change the setting for all active web users. Is that true?
How could I access and use a connection string in the database? I know .net and sql very well. I'm lookin on how to implement.
Is there a way I can call the string it and store/use it globally for a specific user throughout the site without having to request the string from the database on every transaction?
Well, if every Comapany will have multiple users, you shouldn't let every user to change its connection string. There should be an admin that would handle stuff for all the users of specific Company.
If every user in a Single Company will have a unique connection string then i'd say you didn't think this trugh and you should redesign your idea. Something is really wrong.
The same way as you do with any other data. ConnectionStrings are just Strings :)
Well i don't see that as a problem since Connection String is so small and your DB with them will be stored in a local DB. But then again i don't even see a problem of retrieving it the first time and storing it into users Session or even better cache it
on Server side and you will have the access to it for all users from that Company.
Btw. may i ask why on earth would you want to have a remote DB for every Company??? Application will be much slower because of a remote DB and don't event let me start on security issues :)
Please "Mark As Answer" if the post helped you.
mitja.gti | www.mitjagti.com
1. Create a Groups for each company and assigned users to each group.
2. Have one web.config file and multiple connection string for each companies. Connection strings can be encrypted to address security concerns.
3. When a user login to the application, then check which groups or company the user belongs. Based on this the application can retrieve the corresponding connection strings.
DB is a good place to store the connection string, unless you meant to get the connection string information and storing in a application variable, else there may be significant amount of database hit. This is my 2 cent
Another suggestion is that you can get the connection string information and at runtime append the database name to the connection string and this information you can store it in a application variable,
Not sure where you got Remote DB from? I just said one database per company.
This is an application written for businesses to use for them selves and their customers. One database/Business can have thousands of customers and hundreds of users from the business. This database can also become quite large (many gigs 10 or 10's) per
database/business
I, on the other hand could have, hundreds of these businesses use this system.
If I was to store this all in 1 database it would require
1. A lot of rewrite (not that it couldn't be done) The app has been in use for many years for one business. Making the database multi-business would require re-writing hundreds of tables, thousands of sp's and hundreds of web pages and code behind.
2. Single point of failure
3. Potentially 1 HUGE Database
4. Testing - with one database it's all or nothing. with multiple, I can load new code on a small set of businesses to test before full rollout
5. Scalibility - I would think i could span this over multiple servers over time. More challenging with one database. (Not that I couldn't limit the number of businesses on one database but then I would say refer back to reason 1)
These are just a few reasons.
What I am trying to accomplish is one website to handle all of the databases instead of one website per business. If the business customer or the business users logs into the site I'm looking for the best way to connect them to the correct database from one
web site.
Not knowing the potential, this appears to be the safe bet in the short run. If all goes well we may consider option one above in the future. For now I'm just trying to keep the site to one site instead of one site per business. Make sense?
DB is a good place to store the connection string, unless you meant to get the connection string information and storing in a application variable, else there may be significant amount of database hit. This is my 2 cent
Another suggestion is that you can get the connection string information and at runtime append the database name to the connection string and this information you can store it in a application variable,
Thank You
With all due respect but you are wrong.
Every user has to login, right? Where are the credidential stored? Mhm, in the database. What happenes on logon? Connection is opened and db is queried. At the same time you can return a connection string if the login is successfull.
Even if you somehow look trough the fact i described in my previous paragraph there is a thing called server cache.
Please "Mark As Answer" if the post helped you.
mitja.gti | www.mitjagti.com
I apologize for my misunderstanding. Your design is fine.
What i would do is create a custom Membership provider and one database for user credidentials and connectionStrings. When users login i would pass with "OK flag" also a
connection string. This aproach is also good because of the Roles. You can have one role Executives, Sales, Production, HR ... for the application and users from role Sales in Company1 won't be able to access data from Company2 since their connectionString
is different. Also there's a chance to have cache and profiles stored to that same db. And i think that's what the asp.net team had in mind creating it...
Please don't take me wrong, i'd just store basic user info, credidentials and connectionStrings. Take a look at
default db design.
porterboy
Member
442 Points
185 Posts
Dynamic Connection String or Web Config
Dec 20, 2011 07:56 PM|LINK
I have a website that will be used by multiple companies. Each company will have it's own database to work with.
Is there a way to create a dynamic connection string or web config file for each user/company that logs into the site?
I can use a url key or database lookup for the identity but I don't know how to create and use either a dynamic connection string or web config file if possible?
The plan is 1 website with multiple databases, each database is identical in structure but each having data for a specific company.
What is the best practice for this application. Using 1 database is not an option however I can have a common database that can be used to get the connection information.
Any help would be appreciated.
mitja.GTI
Star
11157 Points
2094 Posts
Re: Dynamic Connection String or Web Config
Dec 20, 2011 09:17 PM|LINK
Hi,
since connections string is a pretty sensitive data, you should store it in db. You could also store it to Web.Config file, but i'd store it in the db since it will make your life much easier.
Here is one example of Read, Update, Insert, Delete.
Here is an example of storing Connection String to Web Config Programatically
<body> <form id="form1" runat="server" > <div> Connection name:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> Connection string:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> Provider name:<asp:TextBox ID="TextBox3" runat="server">System.Data.SqlClient</asp:TextBox><br /> <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click1" /> </div> </form> </body>protected void Button1_Click1(object sender, EventArgs e) { var configuration = WebConfigurationManager.OpenWebConfiguration("~"); var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings"); section.ConnectionStrings.Add( new ConnectionStringSettings(TextBox1.Text, TextBox2.Text, TextBox3.Text) ); configuration.Save(); }mitja.gti | www.mitjagti.com
porterboy
Member
442 Points
185 Posts
Re: Dynamic Connection String or Web Config
Dec 20, 2011 09:35 PM|LINK
I would assume that writing to the web config would cause a problem because every user that logs in would modiify the web config and change the setting for all active web users. Is that true?
How could I access and use a connection string in the database? I know .net and sql very well. I'm lookin on how to implement.
Is there a way I can call the string it and store/use it globally for a specific user throughout the site without having to request the string from the database on every transaction?
mitja.GTI
Star
11157 Points
2094 Posts
Re: Dynamic Connection String or Web Config
Dec 20, 2011 09:57 PM|LINK
Well, if every Comapany will have multiple users, you shouldn't let every user to change its connection string. There should be an admin that would handle stuff for all the users of specific Company.
If every user in a Single Company will have a unique connection string then i'd say you didn't think this trugh and you should redesign your idea. Something is really wrong.
The same way as you do with any other data. ConnectionStrings are just Strings :)
Well i don't see that as a problem since Connection String is so small and your DB with them will be stored in a local DB. But then again i don't even see a problem of retrieving it the first time and storing it into users Session or even better cache it on Server side and you will have the access to it for all users from that Company.
Btw. may i ask why on earth would you want to have a remote DB for every Company??? Application will be much slower because of a remote DB and don't event let me start on security issues :)
mitja.gti | www.mitjagti.com
GPankaj
Contributor
4588 Points
768 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 05:40 AM|LINK
Hi,
I would suggest the following.
1. Create a Groups for each company and assigned users to each group.
2. Have one web.config file and multiple connection string for each companies. Connection strings can be encrypted to address security concerns.
3. When a user login to the application, then check which groups or company the user belongs. Based on this the application can retrieve the corresponding connection strings.
I think this would be simple to implement
Thank You and Hope this helps
mitja.GTI
Star
11157 Points
2094 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 05:45 AM|LINK
I suggested using db because every time you change Web.Config ApplicationPool is recycled ;)
mitja.gti | www.mitjagti.com
GPankaj
Contributor
4588 Points
768 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 09:16 AM|LINK
DB is a good place to store the connection string, unless you meant to get the connection string information and storing in a application variable, else there may be significant amount of database hit. This is my 2 cent
Another suggestion is that you can get the connection string information and at runtime append the database name to the connection string and this information you can store it in a application variable,
Thank You
porterboy
Member
442 Points
185 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 10:15 PM|LINK
mitja.GTI
Not sure where you got Remote DB from? I just said one database per company.
This is an application written for businesses to use for them selves and their customers. One database/Business can have thousands of customers and hundreds of users from the business. This database can also become quite large (many gigs 10 or 10's) per database/business
I, on the other hand could have, hundreds of these businesses use this system.
If I was to store this all in 1 database it would require
1. A lot of rewrite (not that it couldn't be done) The app has been in use for many years for one business. Making the database multi-business would require re-writing hundreds of tables, thousands of sp's and hundreds of web pages and code behind.
2. Single point of failure
3. Potentially 1 HUGE Database
4. Testing - with one database it's all or nothing. with multiple, I can load new code on a small set of businesses to test before full rollout
5. Scalibility - I would think i could span this over multiple servers over time. More challenging with one database. (Not that I couldn't limit the number of businesses on one database but then I would say refer back to reason 1)
These are just a few reasons.
What I am trying to accomplish is one website to handle all of the databases instead of one website per business. If the business customer or the business users logs into the site I'm looking for the best way to connect them to the correct database from one web site.
Not knowing the potential, this appears to be the safe bet in the short run. If all goes well we may consider option one above in the future. For now I'm just trying to keep the site to one site instead of one site per business. Make sense?
mitja.GTI
Star
11157 Points
2094 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 10:38 PM|LINK
With all due respect but you are wrong.
Every user has to login, right? Where are the credidential stored? Mhm, in the database. What happenes on logon? Connection is opened and db is queried. At the same time you can return a connection string if the login is successfull.
Even if you somehow look trough the fact i described in my previous paragraph there is a thing called server cache.
mitja.gti | www.mitjagti.com
mitja.GTI
Star
11157 Points
2094 Posts
Re: Dynamic Connection String or Web Config
Dec 21, 2011 10:50 PM|LINK
I apologize for my misunderstanding. Your design is fine.
What i would do is create a custom Membership provider and one database for user credidentials and connectionStrings. When users login i would pass with "OK flag" also a connection string. This aproach is also good because of the Roles. You can have one role Executives, Sales, Production, HR ... for the application and users from role Sales in Company1 won't be able to access data from Company2 since their connectionString is different. Also there's a chance to have cache and profiles stored to that same db. And i think that's what the asp.net team had in mind creating it...
Please don't take me wrong, i'd just store basic user info, credidentials and connectionStrings. Take a look at default db design.
[How Do I:] Create a Custom Membership Provider?
mitja.gti | www.mitjagti.com