Click here to Skip to main content
15,892,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi.
I am working on 'SQL EDITOR' Project and I am stuck at one point,that how user create a table.I solve the database problem,but i don't know how can i resolve he create table problem.Here is my logic:

1: User login
2: Create Database
3: Click create table then select db
4: after selecting db,I've created a textbox(for col name),dropdownlist(for data type), and two checkboxes(one for primary other for allow null)
5: User insert and select values from control and click "insert" button then values shows in gridview,user can also edit and delete column.After inserting values,USER CLICK ON "Create Table" button then values successfully save in database( tbl_field) table but issue with table,Only "First row" of gridview insert in query and table created but only contain one column,because i used for loop and condition gridview.rows.count ,when first time loop start execution then table created just first row inserted inside the query,when second iteration come then table is already created error etc.Can anyone help me about this,how can i do this in better way or how can resolve this issue?Here is my gridview image after user insert values in gridview image

What I have tried:

Here is my create table button code ,I'm receiving values form gridview and i want create table in sql according to gridview column-name,data-type etc.
C#
StringBuilder query = new StringBuilder();
       query.Append("CREATE TABLE ");
       query.Append("ab");
       query.Append(" ( ");

       for (int i = 0; i <GridView2.Rows.Count; i++)
       {
           query.Append(GridView2.Rows[i].Cells[1].Text.ToString());
           query.Append(" ");
           query.Append( GridView2.Rows[i].Cells[2].Text.ToString());
           query.Append(", ");
       }

       if (GridView2.Rows.Count > 1) { query.Length -= 2; }  //Remove trailing ", "
       query.Append(")");
       SqlCommand sqlQuery = new SqlCommand(query.ToString(), cnn);
       SqlDataReader reader = sqlQuery.ExecuteReader();
       cnn.Open();
       sqlQuery.ExecuteNonQuery();
       cnn.Close();
Posted
Updated 16-Apr-16 22:56pm
v2

1 solution

When you "talk" to SQL, you talk in text messages: and a CREATE TABLE statement is a single such message - what you do in your loop is try to create a whole table for each element in it!
First you issue a command:
SQL
If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(COLUMN1 INT)
Then you do it again for the next row:
SQL
If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(COLUMN2 NVARCHAR(MAX))
And so on.
Regardless of if the table exists before the loop, it will definitely exist for the second row!
What you need to do is something along the lines of:
1) Preset string to "CREATE TABLE Customer ("
2) Preset Separator to blank
3) For each row, add to string the Separator and "[<ColumnName>] <ColumnType>"
4) Set Separator to comma
5) Repeat 3 for all rows
6) Add to string ")"
7) Execute command.

I'd suggest using a StringBuilder for this, or building a List of the name and type pair strings and using string.Join to combine them afterwards

But...You are going to need to be damn careful.
By the very nature of this code, it is wide open to SQL Injection - and particularly for a web based product that is very, very dangerous.
You need to care extreme care to check your inputs to make sure they cannot contain any odd SQL which can sabotage other tables or databases.

And the whole idea of a fixed name table on a web based table creator is going to give you real problems in production!
 
Share this answer
 
v2
Comments
Hameed Khan 17-Apr-16 4:10am    
Thanks for replying my post.but soory,i can't understand your answer.please,can you provide me code..My values coming from gridview.how can i do this?I'm beginner,please help me
Hameed Khan 17-Apr-16 4:15am    
Please read my 5th point in my question,you can easily understand why I'm using loop and what i m doing,please help me...
OriginalGriff 17-Apr-16 4:41am    
Read what I said.
And the pseudo code should give you what you need.
Hameed Khan 17-Apr-16 4:56am    
Kindly check my updated code,according to you i do it with string builder,but it's not working
OriginalGriff 17-Apr-16 5:09am    
"It's not working" is not a helpful error report - it tells us nothing about what happened.
What did it do that you didn't expect, or not do that you did?
What did the string look like in the debugger when you sent it to SQL?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900