r3dux.org

/** 
 * 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