I'm having a bit of a problem that I can't solve.

I've already built an ASP.NET webforms application in C# which is going to be used by different companies, each company will have their own database.

The database creation process starts when the user successfully registers on the website, each company probably will have several users.

The application contains a main database where I store the subscribers info:

|ID| |SubscriberName| |SubscriberEmail| |SubscribedDate|

and then a database will be created for this specific user using this code :

cmd.CommandText = "select * form Subscriber";

DataTable customerscount = new DataTable();
sda.Fill(customerscount);

var createcmd = con.CreateCommand();

string dbname;

if (customerscount.Rows.Count > 0)
{
    int subcount = customerscount.Rows.Count;
    dbname = "User"   subcount.ToString();
}
else
{
    dbname = "User1";
}

createcmd.CommandText = "CREATE DATABASE "   dbname   " COLLATE ARABIC_CI_AI_KS";
createcmd.ExecuteNonQuery();

SqlConnection newcon = new SqlConnection();
string oldconnetion = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder(oldconnetion);
scb.InitialCatalog = dbname;

newcon.ConnectionString = scb.ConnectionString;

SqlCommand newcmd = new SqlCommand();
cmd.Connection = newcon;

newcon.Open();

newcmd.CommandText = "CREATE TABLE [Users] ([ID][int] IDENTITY(1, 1) NOT NULL,"  
            "[Fullname] [nvarchar] (max) NULL,"  
            "[Username] [nvarchar] (max) NULL,"  
            "[Password] [nvarchar] (max) NULL,"  
            "[Type] [nvarchar] (max) NULL,"  
            "CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
newcmd.ExecuteNonQuery();

with other tables I didn't include.

My problem is how to figure out the user's database when logging in?

Users tables will be inside the newly created table:

|ID| |Fullname| |Username| |Password|

I'm sorry for long text

CodePudding user response：

Question quite much answers this problem. You really, BUT REALLY REALLY don't want to create a new database for each company. What you do is create company table, and then each user on the system can belong to company (in the table of users). Even then, you have to be careful, since what happens if two users in different companies have the same user and logon name? (hint: you can't allow that). Trying to run the web site, and with all kinds of different web pages, and each page having to pick, to know, to choose what database to operate on is HUGE mess waiting to happen, and ALL database code will be difficult.

Even a simple query to get their projects? You now can't use a standard data routine, and which database will it pull from? Tring to run a web site with 50 different databases? Nope, not going to happen - it just not.

You need to normalize your data. So, for example, we don't create 12 tables for 12 months of invoices, but we create ONE table, and add column called invoice date. Now, we can have as many months as you want.

If you need to seperate out uses by a company - say the company they belong to?

Then you need a table of companies. The next issue, is who will issue logons, or can they self sign up? And if they can self sign up, can they sign up to ANY company? Who's going to manage this?

Regardless, if you have many companies, and users belong to a given commpany, the you need a table called companies, and then users are in a user table that is related to table companies.

I mean, what happens tomorrow if you need to add a new feature to the web site, and this requires you to add say ONE small new column to the database - how about say the users summer residence and phone number?

Now, with 50 databases, you have to go and TRY to update, modify, and maintains 50 databases? How you going to add one simple new column, or make one simple design change to your software?

This is a NON starter, and a VERY bad design - it just is. Such a web site is not manageable at all. It just not.