c#+mysql数据库框架,如何使用C＃复制MySql数据库架构？

I'm trying to use C# & MySql in order to copy an empty table (recreate the Schema really). The structure looks like this:

> TableTemplate (schema)

+ Tables

> FirstTable (table)

> second table (table)

> ...

> SomeOtherTable

+ Tables

> ...

What I would like is to copy the TableTemplate into a new Schema with the user name.

The first obvious path to oblivion was trying CREATE TABLE @UserName LIKE TableTemplate, swiftly learning that sql parameters are supposed to be used for values and not table names (all hail jon skeet, again: How to pass a table as parameter to MySqlCommand?).

So that leaves us with manual validation of the user names in order to build the table names (robert's a prime example).

Next, it seems that even CREATE TABLE UserID LIKE TableTemplate; won't work (even from MySQL Workbench), since TableTemplate isn't a table.

So It's down to writing a loop that will create a table LIKE each table in TableTemplate, after creating a UserID Schema (after manual validation of that string), or trying other options like dumping the database and creating a new one, as seen in these questions:

But I would prefer avoid running a process, dumping the database, and creating it from there every time I add a user.

Any suggestions would be highly appreciated.

解决方案

Ended up using something like this, in a method where aName is passed for the table name:

using (MySqlCommand cmd = new MySqlCommand(string.Format("CREATE DATABASE {0} ;", aName), connection))

{

cmd.ExecuteNonQuery(); // Create the database with the given user name

// Building the sql query that will return a "create table" per table in some_db template DB.

cmd.CommandText = (string.Format("SELECT CONCAT (\"CREATE TABLE {0}.\", TABLE_NAME ,\" "

+ "LIKE some_other_db.\", TABLE_NAME ) as creation_sql "

+ "FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'some_db';"

, aName));

try // Building the inner tables "create table" sql strings

{

using (MySqlDataReader reader = cmd.ExecuteReader())

{

while (reader.Read())

createInnerTablesList.Add(reader.GetString(0));

}

}

catch (MySqlException mysql_ex) { ... } // handle errors

foreach (var sql_insert_query in createInnerTablesList)

{

try // Insert the tables into the user database

{

cmd.CommandText = sql_insert_query;

cmd.ExecuteNonQuery();

}

catch (Exception e) { ... } // handle errors

}

}

The reasons for using LIKE vs AS like Jungsu suggested is that even though the AS will create the tables, it will not keep any of the constraints and keys defined (primary key, etc).

Using the LIKE will replicate them with the constraints.

I'm still not too happy about this, since I feel I'm missing something though ...