Create a Table using SQLHelper with a variable name in C#

Question

I want to create table with name of username.The username is written in uname.Text textbox in xaml. I am using SQLHelper for this.I think I am having a problem with syntax.Can anyone correct my syntax in the code given below.

SqlHelper.ExecuteDataset("Data Source=ADMIN-PC\\MSSQL;Initial Catalog=RecordsDB;Integrated Security=True", CommandType.Text, "CREATE TABLE '" + uname.Text + "'(StudentID int,LastName varchar(255),RollNum varchar(255),Address varchar(255),City varchar(255));   ");

Error I am getting: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect syntax near 'abc'.

abc is what i have given as username in textbox

StuartLC · Accepted Answer · 2015-03-02T09:47:59.040

Remove the apostrophe's from the table name, viz:

CREATE TABLE " + uname.Text + " ( ...

Instead, you should also use [...] around the table name, as User names like O'Connor will be problematic.

Also, when creating dynamic Sql like this, be sure to validate the input (user name) to protect against injection attacks. (Since Table names can't be parameterized).

However, as an alternative to your approach (creating a new table for each user), why not create a single table for all users, and add an additional surrogate key like UserId to it? This way you encapsulate yourself from a lot of issues, including issues such as users changing their names.

Edit
IMO better alternative to creating a table per user named UserName

CREATE TABLE UserData
(
    UserDataId INT IDENTITY(1,1) NOT NULL,  -- Surrogate PK
    UserName varchar(255), -- Becomes a column, not a table name
    StudentID int, -- ? Is this a surrogate key for the same user
    LastName nvarchar(255),
    RollNum nvarchar(255),
    Address nvarchar(255),
    City nvarchar(255),

    CONSTRAINT PK_UserData PRIMARY KEY(UserDataId)
)

After inserting a new user into this table, you can retrieve the new Surrogate UserDataId key via SCOPE_IDENTITY().

See here http://en.wikipedia.org/wiki/Surrogate_key. Please remove your MySql tag, as you are sending answerers down the wrong track. — StuartLC, Mar 02 '15 at 06:20

score 0 · Answer 2 · answered Mar 02 '15 at 06:02

0

Replace the single quotes ' with `

So something like

CREATE TABLE `" + uname.Text + "`(StudentID int,LastName varchar(255),RollNum varchar(255),Address varchar(255),City varchar(255));

Have a look at Schema Object Names

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

So in most cases you can leave out the ` but it is required if the name has special characters in it.

answered Mar 02 '15 at 06:02

Adriaan Stander

162,879
31
289
284

Sir its giving me this error :An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Incorrect syntax near '`'. – Amaid Niazi Mar 02 '15 at 06:10

Create a Table using SQLHelper with a variable name in C#

2 Answers2