Ad:

C# Discussion Thread
Unsolved
Views: 1409

Nov 10th, 2009

How to create mysql db programmably

Expand Post »

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?

Similar Threads

check if mysql table exists in PHP
How to CREATE a mySQL database in C# code in C#
SMS Message With PHP/MYSQL in PHP
create mysql query in C++
Pasing texte file to create mysql database using java in Java

SAINTJAB

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Sep 2009

Permalink

Nov 10th, 2009

Re: How to create mysql db programmably

Try this.

C# Syntax (Toggle Plain Text)
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();
               }		
         }
}
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.

Last edited by Ramesh S; Nov 10th, 2009 at 6:44 am.

Ramesh S

Reputation Points: 165

Solved Threads: 113

Posting Pro

Offline

580 posts
since Jun 2009

Permalink

Nov 10th, 2009

Re: How to create mysql db programmably

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

C# Syntax (Toggle Plain Text)
        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();
                }
            }
        }
        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();
                }
            }
        }

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Nov 10th, 2009

Re: How to create mysql db programmably

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.

Ryshad

Reputation Points: 512

Solved Threads: 246

Nearly a Posting Virtuoso

Offline

1,260 posts
since Aug 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: GIS

Next Thread in C# Forum Timeline: Evenhandler Problem

DaniWeb Message

Cancel Changes

User Name
Password