Click here to Skip to main content
15,943,626 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello friends..
I am doing a multi tenant project in C# .net framework 4. This is a web application. When new user Registers on the website, I am creating a new schema with the same name of the user. Then as per user's requirement I want to create table under that schema(The schema which I created with user's name).But it says that you don't have permission to use the schema. Following is my code where I create schema. Schema is created successfully. Here, txtCompanyName is my textbox. User enters company name in this textbox and I use this name to create Schema.
C#
SqlConnection con = new SqlConnection("Data Source=admin-pc;Initial Catalog=ReportSystem;Integrated Security=True;Pooling=False");
      SqlCommand cmd = new SqlCommand();
      DataTable dt = new DataTable();
  protected void imgNextTab1_Click(object sender, ImageClickEventArgs e)
      {

          objUser.CompanyName = txtCompnyName.Text;
          objUser.Password = txtpwd.Text;
          objUser.Email = txtEmail.Text;
          objUser.SubscriptionDate = DateTime.Now;
          objUser.Contact = txtContact.Text;
          objUser.AdminName = txtAdminName.Text;
          objUser.AdminDesi = ddlDesi.SelectedItem.Text;
          objUser.PlanType = 1;
          objUser.AdminContact = txtContactAdmin.Text;
          //objbl.InsertUser(objUser);


          // create new schema with the name of company


          cmd.Connection = con;
          cmd.CommandText = "Create schema " + txtCompnyName.Text + ";";
          cmd.CommandType = CommandType.Text;
          try
          {
              using (con)
              {
                  con.Open();
                  cmd.ExecuteNonQuery();
                  con.Close();
                  //Response.Write("successfully");
              }
          }
          catch (Exception ex)
          {
              //Response.Write(ex);
          }

          // select category from productmaster


          TabContainer1.ActiveTabIndex = TabContainer1.ActiveTabIndex + 1;
          dt = objbl.SelectMasterProducts();
          ddlbind();
          ViewState["ProductCategory"] = dt;
      }

This is the code where I create table under the same schema.
C#
protected void imgNextTab2_Click(object sender, ImageClickEventArgs e)
       {
           SqlConnection con1 = new SqlConnection("Data Source=admin-pc;Initial Catalog=ReportSystem;Integrated Security=True;Pooling=False");
           // Create new table in schema
           cmd.CommandType = CommandType.Text;
           cmd.Connection = con1;
           cmd.CommandText = "create table ['"+ txtCompnyName.Text +"'].[Products](Id int Primary key,"+
                               "Name nvarchar(30) not null,"
                               +"ProductDesc nvarchar(100),Price decimal (20),"+
                               "CategoryId int not null);";
           try
           {
               using (con1)
               {
                   con1.Open();
                   cmd.ExecuteNonQuery();
                   con1.Close();
                   //Response.Write("successfully");
               }
           }
           catch (Exception ex)
           {
               //Response.Write(ex);
           }


           TabContainer1.ActiveTabIndex = TabContainer1.ActiveTabIndex + 1;
       }

Tell me whether this approach is right or not. If it is right, tell me how to create a table in the schema.
I can create a table in the schema by executing the query we write by - right click on database -> add new query. But same thing is not happening when I execute above code.
Posted
Updated 16-Jun-15 6:35am
v3
Comments
Krunal Rohit 16-Jun-15 2:12am    
Why are you using Schema ? I mean, you can store each registered user details in one table only.

-KR
Dolly Nimavat 16-Jun-15 2:19am    
There is one table in which i will store details of each user. But under each user there are various tables as per user's requirements. Like for automobiles' business and for restaurant there will be different products and all other things. So, I want to diffrentiate tables under each schema. This is multitenant application.
Raul Iloc 16-Jun-15 2:55am    
Could you show the code that create the DB connection object (named "con") used in your imgNextTab1_Click() method?
Dolly Nimavat 16-Jun-15 3:19am    
Yes, I update the question

1 solution

First, I strongly urge you not to create a schema per user. This can cause a lot of issues and I'd be happy to talk alternatives with you.

With the disclaimer out of the way, we need to move onto your second issue. Your current approach is vulnerable to a SQL injection attack. To achieve what you are trying to do, I suggest creating a stored procedure that would handle creating your schema and tables you need. You will need to use dynamic SQL to achieve this. You procedure would like something like this:

SQL
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'BuildCustomerSchema' AND [schema_id] = SCHEMA_ID('dbo'))
	DROP PROCEDURE dbo.BuildCustomerSchema
GO

CREATE PROCEDURE dbo.BuildCustomerSchema
	@schemaName VARCHAR(100)
AS
BEGIN
	-- The added CHAR values are used purely for formating purposes and read ability
	DECLARE @sql NVARCHAR(MAX) = N'CREATE SCHEMA [' + @schemaName + '] AUTHORIZATION [dbo];'

	-- For debug purspose, remove for production
	PRINT @sql

	-- Executes the dynamic SQL
	EXEC sp_executesql @sql

	SET @sql = 'CREATE TABLE [' + @schemaName + '].[Products](' + CHAR(13) + CHAR(10) +
		CHAR(9) + 'Id INT PRIMARY KEY,' + CHAR(13) + CHAR(10) +
		CHAR(9) + 'Name NVARCHAR(30) NOT NULL,' + CHAR(13) + CHAR(10) +
		CHAR(9) + 'ProductDesc NVARCHAR(100),' + CHAR(13) + CHAR(10) +
		CHAR(9) + 'Price DECIMAL (20),' + CHAR(13) + CHAR(10) +
		CHAR(9) + 'CategoryId INT NOT NULL)'

	-- For debug purspose, remove for production
	PRINT @sql

	-- Executes the dynamic SQL
	EXEC sp_executesql @sql
END
GO


Then, you would simply need to call the stored procedure as follows:

C#
protected void imgNextTab2_Click(object sender, ImageClickEventArgs e)
{
    SqlConnection con1 = new SqlConnection("Data Source=admin-pc;Initial Catalog=ReportSystem;Integrated Security=True;Pooling=False");
    // Create new table in schema
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = con1;
    cmd.CommandText = "dbo.BuildCustomerSchema";
    cmd.Parameters.AddWithValue("@schemaName", txtCompnyName.Text);

    try
    {
        using (con1)
        {
            con1.Open();
            cmd.ExecuteNonQuery();
            con1.Close();
            //Response.Write("successfully");
        }
    }
    catch (Exception ex)
    {
        //Response.Write(ex);
    }

    TabContainer1.ActiveTabIndex = TabContainer1.ActiveTabIndex + 1;
}


A few other dangers I need to warn you about with this. You will need use an account that has the ability to create objects in the database. Your standard read/write accounts won't cut it for this. Management of your database will get crazy as the number of schemas increase. You will need to consider partitioning the database across multiple files based on schema (this will help with backup and restore). You will also want to consider this very careful as it can affect your ability to deploy and version control the database (a very lengthy discussion in itself).

I hope this helps. Happy coding!
 
Share this answer
 
Comments
Dolly Nimavat 16-Jun-15 23:19pm    
Thank you for your reply. You are right that database management will be difficult. But the dedicated database server we are going to use for this.
1. Here you have written - authorization [dbo] - does it mean that new schema will be created under the schema [dbo]?
2. IF EXISTS(SELECT * FROM sys.objects WHERE name = 'BuildCustomerSchema' AND [schema_id] = SCHEMA_ID('dbo')) in this line what does - [schema_id] = SCHEMA_ID('dbo') - it mean? I can't understand because I have used stored procedure only for tables.
Dolly Nimavat 17-Jun-15 0:01am    
I tried to create a procedure as per your suggestion. But in if statement there is error - This statement is not recognized in this context.
virusstorm 18-Jun-15 7:35am    
Take a look at this tutorial:
http://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure/

The "IF EXISTS" part is to make sure the stored procedure doesn't exist, other wise you would have to replace the "CREATE PROCEDURE" with "ALTER PROCEDURE".

The "AUTHORIZATION" keyword simply says who owns the schema. I usually do "dbo" so that the owners of that schema also own the new schema.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900