How can i create sql table in database through button click with user interface

Question

0.00/5 (No votes)

See more:

, +

I want to create Sql table through the click of button......
I use this command:-

C#

con.Open();

SqlCommand cmd = new SqlCommand("create table @table (@col1 @datatype1, @col2 @datatype2, @col3 @datatype3, @col4 @datatype4, @col5 @datatype5 )", con);

cmd.Parameters.Add(new SqlParameter("@table",textBox6.Text)); 
cmd.Parameters.Add(new SqlParameter("@col1", textBox1.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col2", textBox2.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col3", textBox3.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col4", textBox4.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col5", textBox5.Text.ToUpper()));

cmd.Parameters.Add(new SqlParameter("@dataType1", comboBox1.SelectedItem.ToString()));

cmd.Parameters.Add(new SqlParameter("@dataType2", comboBox3.SelectedItem.ToString()));

cmd.Parameters.Add(new SqlParameter("@dataType3", comboBox5.SelectedItem.ToString()));

cmd.Parameters.Add(new SqlParameter("@dataType4", comboBox7.SelectedItem.ToString()));

cmd.Parameters.Add(new SqlParameter("@dataType5", comboBox9.SelectedItem.ToString()));

cmd.ExecuteNonQuery();
con.Close();

Please Check this coding.................

Posted 18-Dec-13 1:31am

Member 10447111

Updated 18-Dec-13 2:12am

Maciej Los

v3

Add a Solution

Comments

[no name] 18-Dec-13 7:34am

What is the problem you are facing..?

Member 10447111 18-Dec-13 12:44pm

Incorrect syntax near '@table'.

Ganesh Raja 18-Dec-13 7:56am

What is the error?

Member 10447111 18-Dec-13 12:44pm

Incorrect syntax near '@table'.

Maciej Los 18-Dec-13 8:16am

What about permissions?

Do you want to allow users to create unknown number of tables?

Member 10447111 18-Dec-13 13:35pm

please sir help me out asap

Member 10447111 18-Dec-13 12:43pm

Error is :- Incorrect syntax near '@table'.

Member 10447111 18-Dec-13 12:49pm

complete code:-

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Create_Table_from_Button_Click
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=practise;Integrated Security=True");

private void create_button1_Click(object sender, EventArgs e)
{
con.Open();

SqlCommand cmd = new SqlCommand("create table @table (@col1 @datatype1, @col2 @datatype2, @col3 @datatype3, @col4 @datatype4, @col5 @datatype5 )", con);

cmd.Parameters.Add(new SqlParameter("@table",textBox6.Text));

cmd.Parameters.Add(new SqlParameter("@col1", textBox1.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col2", textBox2.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col3", textBox3.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col4", textBox4.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@col5", textBox5.Text.ToUpper()));
cmd.Parameters.Add(new SqlParameter("@dataType1", comboBox1.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@dataType2", comboBox3.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@dataType3", comboBox5.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@dataType4", comboBox7.SelectedItem.ToString()));
cmd.Parameters.Add(new SqlParameter("@dataType5", comboBox9.SelectedItem.ToString()));

cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Done");
}

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2013-12-18T02:23:00

Please, read my comment to the question first.

If you want to execute SQL command to create table, you need to grant PERMISSIONS[^] to the current user.

Personally, i would not recommend to allow users to create unknown number of tables. Who will be able to manage them?

[EDIT]
You can't create query string in that way:
SQL

create table @table (@col1 @datatype1, @col2 @datatype2, @col3 @datatype3, @col4 @datatype4, @col5 @datatype5

Above code will never compiled!

You can create dynamic SQL[^] code, which is passed into EXEC or EXECUTE[^] method.

SQL

DECLARE @sql VARCHAR(255) = 'SELECT * FROM TableName'
EXEC(@sql)

For further information, please see:
Writing Secure Dynamic SQL in SQL Server[^]
Building Dynamic SQL In a Stored Procedure[^]
Two Handy Techniques for Creating Dynamic SQL in Stored Procedures[^]
Understanding how SQL Server executes a query[^]
How to Generate T-SQL Code by Using Dynamic SQL?[^]

Remember, you've been warned!

[EDIT]

Jashobanta · Answer 2 · 2013-12-18T09:59:00

DO NOT DO IT IN THIS WAY.

Try creating a stored proc that can do this. Create the below stored procedure in your sql and do all the validations from your end on the same in the proc.

SQL

create proc createTable
(
@tableName varchar(30),
@column1 varchar(30),
@column1DataType varchar(30),
@column2 varchar(30),
@column2DataType varchar(30)
)
AS
DECLARE @CreateTableString NVARCHAR(MAX)
BEGIN
	IF EXISTS (SELECT NAME FROM sys.tables WHERE name=@tableName)
		BEGIN	
			PRINT ('TABLE ALREADY EXISTS');
                        RETURN -1;
		END
	ELSE
		BEGIN
			SET @CreateTableString='CREATE TABLE '+@tableName +'('+@column1+' ' +@column1DataType+','+@column2+' ' +@column2DataType+')'
			EXEC (@CreateTableString);
                        PRINT ('TABLE CREATED SUCCESSFULLY');
		END
END

Now from your front end, you have to create sql parameters with all values as strings and then execute it. They way it should be passed is like :-

SQL

EXEC createTable 'test1','test1c1','varchar(30)','test1c2','varchar(30)'

I guess this solves the issue well. I have tried running it in my system and it is fine.

Vote it and mark it as solution if it solves your problem..
Regards
Jashobanta

Jashobanta · Answer 3 · 2013-12-18T09:39:00

If you want to create a table in such a manner, you might be getting errors as creating table needs some specific permissions on the database. But there is a way to do this. You can use Entity Framework Code first approach for this. You can create classes in your program and entity framework will be creating all the tables and relational mappings in your database at runtime.

Go through the below links and you can do it easily I guess.. not a tough thing to do..

http://www.hanselman.com/blog/SimpleCodeFirstWithEntityFramework4MagicUnicornFeatureCTP4.aspx[^]

http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx[^]

Vote it and mark it as solution if it solves your problem..
Regards
Jashobanta

An@nd Rajan10 · Answer 4 · 2013-12-18T02:33:00

Solution 2

C#

SqlCommand cmd = new SqlCommand("create table @table (@col1,@datatype1, @col2, @datatype2, @col3 ,@datatype3, @col4 ,@datatype4, @col5 ,@datatype5 )", con);

Posted 18-Dec-13 2:33am

An@nd Rajan10

Comments

Member 10447111 18-Dec-13 12:54pm

Incorrect syntax near '@table'.

Maciej Los 18-Dec-13 15:23pm

You can't build query in that way ;(
[EDIT]
Please, see my updated answer!
[/EDIT]

Jashobanta 18-Dec-13 16:08pm

I don't think Grant or Deny can be used to allow an user for executing DML commands, not for DDL commands..
you can deny an user to do a select on a table or insert on a table but you cannot allow an user to create a table if the user is not listed in either sysadmin or dbcreator group..

How can i create sql table in database through button click with user interface

4 solutions

Solution 1

Solution 4

Solution 3

Solution 2

Add your solution here

Preview 0