How To: Connect to a MySQL database using Connector/.NET in C#

Try this:

/** 
 * Note: For this project to work the mysql.data.dll reference has to be added to the project by Right-clicking
 * on References then choosing Add Reference (in the Solution Explorer pane), then clicking on the Browse tab
 * and selecting the file mysql.data.dll - which you should first copy into the project folder along with the
 * other .dll files extracted from the mysql-connector-net-6.5.4-noinstall.zip file from: 
 * http://dev.mysql.com/downloads/connector/net/#downloads
 * 
 * When getting the Connector/.NET be sure to pick the ".NET/Mono" platform and not just "Microsoft Windows", also
 * you likely want to target .Net 4.0 onwards (not .NET 2.0 onwards) so once you've extracted the connector archive
 * be sure to copy the dlls from the "v4" folder and not the "v2" folder!
 **/
 
using System;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
 
namespace SimpleMySQLConnector
{
	class Program
	{
		static void Main(string[] args)
		{             
			string host     = "127.0.0.1"; // The IP address 127.0.0.1 is the same as "localhost" - it just means "this machine"
			string database = "testdb";    // If this database doesn't already exist we'll create it
			string user     = "root";      // Default WampServer MySQL username
			string password = "";          // Default WampServer MySQL password (no password!)
 
			// Create a provider string from our details
			string connectionString = "Data Source=" + host
 						+ ";Database="   + database
						+ ";User ID="    + user
						+ ";Password="   + password;
 
			// Create a new connection to the database (this doesn't actually connect yet - we have to call .Open for that!)
			MySqlConnection dbConn = new MySqlConnection(connectionString);
 
			// Try to actually open the connection to the DBMS and database
			try
			{
				Console.WriteLine("Attempting to connect to: " + database + "@" + host + "...");
				dbConn.Open();               
			}
			// Can't connect to the database? Then let's try creating it...
			catch (MySqlException e)
			{
				Console.WriteLine(e.Message);                
				Console.WriteLine("Could not connect to: " + database + " - attempting to create it...");
 
				try
				{
					// Specify a connection string which doesn't mention the database we're connecting to and try to open
					// the connection. If this fails there's a problem with the DBMS and we're going to be forced to quit.
					string noDBConnString = "Data Source=" + host + ";User ID=" + user + ";Password=" + password;
					dbConn.ConnectionString = noDBConnString;
					dbConn.Open();
 
					// Assuming we can connect to the DBMS at all, attempt to create the database
					string sql = "CREATE DATABASE " + database + "; USE " + database; 
					MySqlCommand cmd = new MySqlCommand(sql, dbConn);
					cmd.ExecuteNonQuery();
 
					// Create a "users" table which contains two fields: ID which is an Int, and name which is a VARCHAR(20)
					cmd.CommandText = "CREATE TABLE users(ID INT, name VARCHAR(20))";
					cmd.ExecuteNonQuery();
 
					Console.WriteLine("Database and tables created successfully!");
				}
				// Can't create the database and/or table? Not much else for it but to quit out...
				catch (MySqlException e2)
				{
					Console.WriteLine(e2.Message);
					Console.ReadLine();
					Environment.Exit(-1);
				}
			}
 
			// Get a valid integer from the user
			bool gotValidInt = false;
			int idNumber = 0;
			while (!gotValidInt)
			{
				// Get some input from the user            
				Console.WriteLine("Please enter a ID number:");
				string idNumberString = Console.ReadLine();
				int idNumberInt;
				if (int.TryParse(idNumberString, out idNumberInt)) // Try to parse the string as an integer
				{
					idNumber = idNumberInt; // Assign the successfully-converted-to-int value to our idNumber
					gotValidInt = true;     // Set our flag to say we got a valid int so can leave the while loop!
				}
				else
				{
					Console.WriteLine("Not an integer!");
				}
			}
 
			// Get a user name from the user
			Console.WriteLine("Please enter a user name:");
			string name = Console.ReadLine();
 
			// Use a prepared statement to insert the values we just got from the user into our database
			try
			{
				string sql = "INSERT INTO users(ID, name) VALUES (@idValue, @nameValue)";
				MySqlCommand cmd = new MySqlCommand(sql, dbConn);
				cmd.Prepare();
				cmd.Parameters.AddWithValue("@idValue", idNumber); // Substitute in the idNumber value for @idValue
				cmd.Parameters.AddWithValue("@nameValue", name);   // Substitute in the name     value for @nameValue
				cmd.ExecuteNonQuery();
 
				Console.WriteLine();
				Console.WriteLine("Data insertion successful!");
				Console.WriteLine();
			}
			catch (MySqlException e)
			{
				Console.WriteLine(e.Message);
			}
 
 
			// Display all the entries in the "users" table
			Console.WriteLine("---- Table Contents ----");
			try
			{
				// Create a SQL query which can be executed on our database connection
				string sqlQuery = "SELECT * FROM users";
				MySqlCommand cmd = new MySqlCommand(sqlQuery, dbConn);
 
				// Create an object to hold the results of our query and run the query
				// Note: The MySqlDataReader object is a read-only onject which allows you to quickly
				// get records out of a database table. It cannot be used to update a database table. 
				MySqlDataReader mysqlReader = cmd.ExecuteReader();
 
				// Display column headings
				Console.WriteLine("ID\tName");
 
				// Loop through our results printing them out to the console
				while (mysqlReader.Read())
				{
					// The first field (field 0) of the "users" table is the ID field (which is an int)
					int idFieldValue = mysqlReader.GetInt32(0);
 
					// The second field (field 1) of the "users" table is the Name field (which is a string)
					string nameFieldValue = mysqlReader.GetString(1); 
 
					// Output the record to the console
					Console.WriteLine(idFieldValue + "\t" + nameFieldValue);
				}
 
				// Close our reader when we're done
				mysqlReader.Close();
			}
			catch (MySqlException e)
			{
				Console.WriteLine(e.Message);
			}
 
			// Close our database connection when we're done
			dbConn.Close();
 
			// Wait for the user to press a key before exiting
			Console.ReadKey();
 
		} // End of Main method
 
	} // End of class
 
} // End of namespace

How To: Insert data into a MySQL DB using Connector/C++

I created a guide to using MySQL Connector/C++ for a class a while back which showed a not-too-pretty way to put data into a DB, and I created a second part using prepared statements, locking, rollbacks and all that other good stuff – then I never posted it! Well, no more ;)

MySQL Connector/C++ Guide - Part 2

Details: MySQL & Connector/C++ Introductory Guide – Part 2 of 2
Format: Powerpoint 2003 (Best readability in Powerpoint 2003 onwards, LibreOffice and OpenOffice)
Slide Count: 19
Link: MySQL-Connector-Guide-Part-2.zip

If you find anything that needs correcting feel free to let me know! Cheers!

How To: Work with MySQL and Connector/C++

I threw together a set of slides on the topic for a class which was presented today… Thought maybe they’ll be of use to someone else.

MySQL & Connector Guide

Details: MySQL & Connector/C++ Introductory Guide
Format: Powerpoint 2007 and OpenOffice Impress
Slide Count: 26
Link: MySQL-Connector-Guide-1.zip

If you find anything that needs correcting feel free to let me know and I’ll make any changes as required.

Also, there’ll be a second set of slides posted soon on using prepared statements, rollbacks and enforcing transactional integrity – isn’t life fun? =P

Update: Second part now available here.

How To: Use MySQL Connector/C++ to Connect to a MySQL Database in Windows

I got this working in Linux in about ten minutes, while the same thing in Windows took me closer to three hours… The difficulty I had was that I need to get this working on multiple machines which all get wiped and reset on each boot, so it had to be entirely stand-alone with everything necessary to build in the same project. And the difficulty with that is that Connector/C++ needs the boost library to compile, which I wasn’t too keen on involving. In the end I couldn’t find a way around it and just threw the entire boost library into the same directory as the project as well as the libmysql.lib/dll and mysqlcppconn.lib libraries.

So now it works. It’s a little ugly, and the project folder comes in at 62MB, but it works. Really it’s not all that bad, if you just wanted to build and deploy something you’d end up with a couple of hundred KB executable plus a few MB of dlls. I guess I could go in and strip out some parts of the boost library which aren’t being used. But to be honest it’s been a long day and I’m sick of fighting with it, so I’ll leave that as an exercise for the reader.

Reminder / Info

  • This code is tested and working on Windows XP 32-Bit.
  • The project (i.e. download, at bottom of post) is for Microsoft Visual Studio 2008 Express
  • The version of the libs/headers included are:
    • libmysql.lib from MySQL server v1.5.36 32-Bit
    • boost library v1.45.0
    • mysqlcppconn.lib/dll from Connector/C++ v1.1.0 32-Bit
  • I really, really hate having to work with Windows.

Anyhoo – here’s the junk…

// Standard C++ includes
#include <iostream>
#include <cstdlib>
#include <string>
using namespace std;
 
// Include the Connector/C++ headers
#include "cppconn/driver.h"
#include "cppconn/exception.h"
#include "cppconn/resultset.h"
#include "cppconn/statement.h"
 
// Link to the Connector/C++ library
#pragma comment(lib, "mysqlcppconn.lib")
 
// Specify our connection target and credentials
const string server   = "tcp://127.0.0.1:3306";
const string username = "root";
const string password = ""; // No password - thanks, WAMP Server!
 
int main()
{
	sql::Driver     *driver; // Create a pointer to a MySQL driver object
        sql::Connection *dbConn; // Create a pointer to a database connection object
        sql::Statement  *stmt;   // Create a pointer to a Statement object to hold our SQL commands
        sql::ResultSet  *res;    // Create a pointer to a ResultSet object to hold the results of any queries we run
 
	// Try to get a driver to use to connect to our DBMS
	try
	{
		driver = get_driver_instance();
	}
	catch (sql::SQLException e)
	{
		cout << "Could not get a database driver. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	// Try to connect to the DBMS server
	try
	{
		dbConn = driver->connect(server, username, password);
	}
	catch (sql::SQLException e)
	{
		cout << "Could not connect to database. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	stmt = dbConn->createStatement(); // Specify which connection our SQL statement should be executed on
 
	// Try to query the database
	try
	{
		stmt->execute("USE mysql");              // Select which database to use. Notice that we use "execute" to perform a command.
 
		res = stmt->executeQuery("show tables"); // Perform a query and get the results. Notice that we use "executeQuery" to get results back
	}
	catch (sql::SQLException e)
	{
		cout << "SQL error. Error message: " << e.what() << endl;
		system("pause");
		exit(1);
	}
 
	// While there are still results (i.e. rows/records) in our result set...
	while (res->next())
	{
		// ...get each field we want and output it to the screen
		// Note: The first field/column in our result-set is field 1 (one) and -NOT- field 0 (zero)
		// Also, if we know the name of the field then we can also get it directly by name by using:
		// res->getString("TheNameOfTheField");
		cout << res->getString(1) << endl;                
	}
 
	// Clean up after ourselves
	delete res;
	delete stmt;
	delete dbConn;
 
	system("pause");
	return 0;
}

*** REALLY IMPORTANT: Build the following project in Release mode if you want it to work!! No, really. The libs and dll’s that the project uses have been built in Release mode and you can’t mix n’ match. ***

Download of Visual Studio 2008 Express project including all necessary headers/libs/dlls (i.e. it’s ready to go – and 12MB): MySQL-Connector-Test.zip

Credits: Thanks to TidyTutorials for the guide that got me started.