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
DECLARE @sql NVARCHAR(MAX) = N'CREATE SCHEMA [' + @schemaName + '] AUTHORIZATION [dbo];'
PRINT @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)'
PRINT @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");
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();
}
}
catch (Exception 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!