MorkaLork Development

Interesting stuff I've picked up over the years...

C#/MySql 3: Create database

2009-04-17 07:27:51 | 240 views | csharp class method tutorial communicate database sql commands properties

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();
}
}
}



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.