如何在不同的模式中创建表 [英] How to create a table in different schema
问题描述
朋友们好..
我在C＃.net框架中进行多租户项目4.这是一个Web应用程序。当新用户在网站上注册时，我正在创建一个与用户名相同的新模式。然后根据用户的要求，我想在该模式下创建表（我使用用户名创建的模式）。但是它表示您没有使用模式的权限。以下是我创建架构的代码。架构已成功创建。这里，txtCompanyName是我的文本框。用户在此文本框中输入公司名称，并使用此名称创建架构。
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.
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.
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.
推荐答案
首先，我强烈建议你不要为每个用户创建一个模式。这可能会导致很多问题，我很乐意与您讨论替代方案。
随着免责声明，我们需要转到第二个问题。您当前的方法容易受到SQL注入攻击。为了实现您的目标，我建议创建一个存储过程来处理您需要的模式和表。您将需要使用动态SQL来实现此目的。你的程序想要这样的东西：
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:
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:
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!
这篇关于如何在不同的模式中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
