How to create a table in different schema

Question

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 15-Jun-15 19:58pm

Dolly Nimavat

Updated 16-Jun-15 6:35am

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

virusstorm · Accepted Answer · 2015-06-16T07:19:00

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!