How to create mysql db programmably

[SAINTJAB]

Hi, I want to create a mysql table in a database in C#. this table should have unlimited fields where the user can add more information to it without having to overwrite the already existing information. I want to do this cos I will not know the amount of info the user will add in the table and this will give the user the ability to add as much info as and when it is available without the need to restructure the table. can I do that programmably and how?

[Ramesh S]

Try this.

using System;
using System.Drawing;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySqlDemo
{
	/// <summary>
	/// Description of Form1.
	/// </summary>
           public class Form1 : System.Windows.Forms.Form
            {
		
                void btnCreateDB(object sender, System.EventArgs e)
                {
                  MySqlConnection conn = new MySqlConnection("Data Source=localhost;Persist Security Info=yes;UserId=root; PWD=YourPassword;");
                  MySqlCommand cmd = new MySqlCommand("CREATE DATABASE YourDBName;", conn );
		
                  conn .Open();			
                  cmd .ExecuteNonQuery();
                  conn .Close();
               }		
         }
}

You need to download MySQL Connector/Net to use the MySql.Data.MySqlClient namespace.

[DdoubleD]

Example of create table and ALTER table using the MySqlClient already mentioned:

public static void AlterTableExample()
        {
            string connStr = DbWrapper.TestDbWrapper.BuildConnectionString(DbWrapperType.MySql);
            string createStatement = "CREATE TABLE Test (Field1 VarChar(50), Field2 Integer)";
            string alterStatement = "ALTER TABLE Test ADD Field3 Boolean";

            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();

                // create the table...
                using (MySqlCommand cmd = new MySqlCommand(createStatement, conn))
                {
                    cmd.ExecuteNonQuery();
                }

                // alter table to add another column...
                using (MySqlCommand cmd = new MySqlCommand(alterStatement, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

[Geekitygeek]

Just a thought, but wouldnt it be better to have a seperate table for the additional fields in a zero-to-many relationship?
Something like:

TABLE OF ITEMS
ItemID numeric,
ItemDesc nvarchar(max)

TABLE OF ATTRIBUTES
AttributeID numeric,
ItemID numeric,
AttributeName nvarchar(max),
AttributeType nvarchar(50),
AttributeValue nvarchar(max)

ItemID and AttributeID as seeded identity keys, ItemID becomes a foreign key link to the attributes table. That way you have an item with a description and can add as many additional attributes as you like.

The problem with doing it by adding columns (as above), is that every time you add a column, it will add an empty cell in that column for all existing items. If thats what you intend then you can ignore this post :)

Obviously, if you just want to set the columns from code the first time the database is created and then they are static after that then use the code above. But if you intend for the user to add a varying amount of data to each item in the table then this way will be far more efficient and flexible.