Create a MySQL table dynamically in C#
Sometimes you might need to create database tables dynamically, based on new users, products etc. The snippet below allows you to do exactly that, create a new table in your database dynamically, straight from your code.
This example is heavily based on How to connect to a MySQL database and retrieve data in C# as the same concept of connecting to the database is required.
The first thing you need to do, as mentioned in the article linked above, is that you will need to add a reference to MySQL.Data in your project. After you have done that, you can modify the snippet below to create the table with the columns you want based on your requirements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | string createTableQuery = string.Format(@"CREATE TABLE `{0}` ( `sid` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(120) NOT NULL DEFAULT '', `title` varchar(120) NOT NULL DEFAULT '', `description` text NOT NULL, `optionscode` text NOT NULL, `value` text NOT NULL, `disporder` smallint(5) unsigned NOT NULL DEFAULT '0', `gid` smallint(5) unsigned NOT NULL DEFAULT '0', `isdefault` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`sid`), KEY `gid` (`gid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;", "YourTableName"); string serverIp = "<Server IP or Hostname>"; string username = "<DB username>"; string password = "<DB password>"; string databaseName = "<DB name>"; string dbConnectionString = string.Format("server={0};uid={1};pwd={2};database={3};", serverIp, username, password, databaseName); var conn = new MySql.Data.MySqlClient.MySqlConnection(dbConnectionString); conn.Open(); var cmd = new MySql.Data.MySqlClient.MySqlCommand(createTableQuery, conn); cmd.ExecuteNonQuery(); |
If you are unsure as to which database engine to use, please take a look at https://dev.mysql.com/doc/refman/5.0/en/storage-engines.html as every database engine has different type of limitations, so it is better to choose the one that better suits your needs.
How to insert data into table that select by textbox.
How do you prevent SQL Injection if you don’t use parameters?
That depends on where you want to use the code above.
If you are building a desktop application for a single user then you don’t care about SQL injections, although it is always recommended to sanitize the input, mainly in case you want to reuse your code for other projects that might actually be prone to an SQL injection.
Other than that, you should always escape the input or using parameterized statements.