How create columns with C# in SQL

Question

İ want to make an application. I want the code to create as many columns into a table as the number of columns I type into a Textbox. My main purpose in the application is to write a title(like a table) and add as many subtitles (column) as I want to it. but I can only add columns by combining the system code. I can only add columns by making changes in the code below, but what I want is to create a textbox on the form and add as many columns as the number written there.

`private void button1_Click(object sender, EventArgs e)
        {
            string s1, s2;
            s1 = textBox2.Text;
            s2 = "char(20)"; 
            try
            {
                baglanti.Open();
                SqlCommand komut = new SqlCommand("Create Table " + textBox1.Text + " ( " + s1 + " " + s2 + " ) ", baglanti);
                komut.ExecuteNonQuery();
                baglanti.Close();
                MessageBox.Show("Succes");
            }
            catch (Exception)
            {
                MessageBox.Show("Error");
            }
        }
}`

So what is your question here? Though the real problem here is the massive security hole in your code. You are injecting unsanitised values into your RDBMS. — Thom A
– Thom A ♦, Commented Oct 27, 2021 at 14:20
Not trying to be snarky, but are you sure you're not just looking for an Excel workbook instead, or maybe a NoSQL document store? RDBMS systems don't do well with arbitrary table structures and constructing dynamic SQL safely is a pain. — Jeroen Mostert
– Jeroen Mostert, Commented Oct 27, 2021 at 14:22
What do you think would happen if someone entered t(i int); CREATE LOGIN NewSA WITH PASSWORD = 'a', CHECK_POLICY = OFF; ALTER SERVER ROLE sysadmin ADD MEMBER NewSA;-- for their table's "name"? — Thom A
– Thom A ♦, Commented Oct 27, 2021 at 14:22
To pull this off you'll have to write your sqlstatement string inside of a for loop that iterates as many times as the number in the text box. That should be pretty straightforward so it's not clear where exactly you are stuck. I agree with others though that 1) This feels like a solution for a problem you shouldn't be having (there is rarely a good reason to dynamically monkey with database objects) 2) Even written well, this will likely open a sql injection attack hole. — JNevill
– JNevill, Commented Oct 27, 2021 at 14:24

Joel Coehoorn · Accepted Answer · 2021-11-01 14:05:22Z

2

There is a better way. Instead of adding columns and tables dynamically, where you have potentially many tables following this format:

User-created Table 1

Title	SubTitle1	SubTitle2	...	SubTitleN
data for first table ...

User-created Table 2

Title	SubTitle1	SubTitle2	...	SubTitleX
data for second table ...

You should use two tables total, where the schema pretty much never changes, like this:

Single Base Table

ID	TableName	Title
1	...	...
2	...	...

SubTitles

ID	TableID	SubTitle
1	1	...
2	1	...
N	1	...
N+1	2	...
N+2	2	...
N+X	2	...

In the SubTitles table, first ID field is to ensure uniqueness within the table and allow referencing a specific subtitle, and the second TableID field is to relate back to the correct record in the first table. Both are needed.

edited Nov 1, 2021 at 14:05

answered Oct 27, 2021 at 19:06

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmet Faruk Sucu Over a year ago

Thanks. I did as you said, but when adding subTitles in the second table, I cannot add more than one header to the same ID. I combined the ID and Table ID in the database in SQL. Error text= System.Data.SqlClient.SqlException: 'Violation of PRIMARY KEY constraint 'PK_ProjeUnsurları'. Cannot insert duplicate key in object 'dbo.ProjeUnsurları'. The duplicate key value is (11). The statement has been terminated.'

Joel Coehoorn Over a year ago

I combined the ID and Table ID in the database in SQL. That's exactly what you should NOT do. I used two separate fields in this answer for a reason. Each record in the SubTitle table needs it's own unique ID, and also needs a reference to the parent TableID. I updated the answer to explain this.

Collectives™ on Stack Overflow

How create columns with C# in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related