C#/MySql 3: Create database

Written by: maffelu , 2009-04-17 07:27:51

Allrighty! To communicate with your database you need to use SQL commands. There will be a separate article about usefull commands so see that for more information about what to do.
This article will deal with how to do it.

To enter a command in your MySql database you need one thing, a MySqlCommand instantiation. MySqlCommand is a class found in the MySql.Data.MySqlClient namespace. It's the "verbal" link between your app and the database.
This class has several methods and properties that are good to know, in our case, three specific ones.

Property: string CommandText
Method: int ExecuteNonQuery()
Method: MySqlDataReader ExecuteReader()

Create a database

Right, the most important thing to start with is to create a database. Without one, this will be quite pointless.

The command to create a database is "create database xxx;" where xxx is the name you want for your database.

To do this, we will have to create a connection to our SQL server first:

MySqlConnection connect;

string connectionLine;



//Create a connection string 

connectionLine = "Data source=localhost;UserId=root;Password=dog;";



//Instantiate the MySqlConnection class, constructor takes one connectionstring

connect = new MySqlConnection(connectionLine);



//Instantiate the MySqlCommand class

cmd = new MySqlCommand();



try

{

   //Create a connection

   cmd.Connection = connect;

   //Open the connection

   cmd.Connection.Open();

   Console.WriteLine("Connection opened.");

}

catch (NullReferenceException ex)

{

   Console.WriteLine("Error: {0}", ex.ToString());

}

Now we have a connection up and running which means we can now interact with the database.
I showed you before what the sql command was to create a database. Now we have to send that command to our mysql server.

The way to do that is to use the MySqlCommand class as mentioned earlier in this article.
First we create a command string:

//Create a query

commandLine = "CREATE DATABASE mydb;";

Then set the MySqlCommand property CommandText to your line:

//Set the command query

cmd.CommandText = commandLine;

To execute the current query (the query in the CommandText property) we use the ExecuteNonQuery method in the MySqlCommand class:

//Execute the command query

cmd.ExecuteNonQuery();

Now we have created a database called mydb.

The full code

using System;

using MySql.Data.MySqlClient;



namespace MorkConsTest

{

    class Program

    {

       



        static void Main(string[] args)

        {

            MySqlConnection connect;

            MySqlCommand cmd;

            string connectionLine;

            string commandLine;



            //Create a connection string

            connectionLine = "Data source=localhost;UserId=root;Password=dog;";



            //Instantiate the MySqlConnection class, constructor takes one connectionstring

            connect = new MySqlConnection(connectionLine);

            //Instantiate the MySqlCommand class

            cmd = new MySqlCommand();



            try

            {

                //Create a connection

                cmd.Connection = connect;

                //Open the connection

                cmd.Connection.Open();

                Console.WriteLine("Connection opened.");

            }

            catch (NullReferenceException ex)

            {

                Console.WriteLine("Error: {0}", ex.ToString());

            }



            //Create a query

            commandLine = "CREATE DATABASE mydb;";

            //Set the command query

            cmd.CommandText = commandLine;

            try

            {

                //Execute the command query

                cmd.ExecuteNonQuery();

                Console.WriteLine("Database created.");

            }

            catch (MySqlException ex)

            {

                Console.WriteLine("Error: {0}", ex.ToString());

            }

            finally

            {

                //Close the connection

                cmd.Connection.Close();

                Console.WriteLine("Connection closed.");

            }



            Console.Read();

        }

    }

}

There are no comments on this article.

Leave a message!

If you have any question or just want to leave a message, just fill out the form below!

Your e-mail will not be visible in your post, it is for validation reasons only

Name:
E-mail:
Comment: