how to write mysql create table query in c#

Question

how to create table query in c# for mysql database..

MySqlConnection con = new MySqlConnection("Server=localhost;Database=demo_test;UID=root;Password= ");

MySqlCommand acmd = new MySqlCommand("CREATE TABLE first (" + a.Columns[0] + " int(20) NOT NULL auto_increment,'" + a.Columns[1].ToString() + "' varchar(100) NOT NULL default,PRIMARY KEY (" + a.Columns[0]+") 1", con); 
con.Open();

acmd.ExecuteNonQuery();

it gives me an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''name' varchar(100) NOT NULL default,PRIMARY KEY (id) 1' at line 1

remove single quote for a.Columns[1]

andy
– andy

2013-02-25 09:19:57 +00:00
Commented Feb 25, 2013 at 9:19 — andy
– andy, Commented Feb 25, 2013 at 9:19

Dejan · Accepted Answer · 2015-12-23 15:10:02Z

2

Instead of fighting with SQL directly, you could use Mig# like this:

        var schema = new DbSchema(ConnectionString, DbPlatform.MySql5);
        schema.Alter(db => db.CreateTable("first")
            .WithPrimaryKeyColumn(a.Colunns[0], DbType.Int32).AsIdentity()
            ...);

answered Dec 23, 2015 at 15:10

Dejan

10.5k8 gold badges84 silver badges129 bronze badges

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

Comments

John Woo · Accepted Answer · 2013-02-25 09:18:09Z

1

It's because you are wrapping the column names with single quote which converts the value into string and not an identifier anymore. Remove the single quotes and it will work.

string query = @"CREATE TABLE first (" + a.Columns[0] + " int(20) NOT NULL auto_increment, " 
                     + a.Columns[1].ToString() + " varchar(100) NOT NULL default,
                     PRIMARY KEY (" + a.Columns[0]+")"

answered Feb 25, 2013 at 9:18

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

user1254919 Over a year ago

string query = @"CREATE TABLE first (" + a.Columns[0] + " int(20) NOT NULL auto_increment, "+ a.Columns[1].ToString() + " varchar(100) NOT NULL default,PRIMARY KEY (" + a.Columns[0]+")"; MySqlCommand acmd=new MySqlCommand(); acmd.CommandText=query; acmd.Connection=con; acmd.ExecuteNonQuery(); con.Close();

Collectives™ on Stack Overflow

how to write mysql create table query in c#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related