Reading data from MySQL using CSharp in 40 lines or less

Sometimes I just need to get some data out of MySQL using CSharp.  I don’t need LINQ, SSIS or some other large scale solution, I don’t need abstraction, just a quick and dirty “get the data and get out” function.

This post will document how to get data out of MySQL in 40 lines or less.  Granted there is more than 40 lines here total but most of that is config and setup.

Prerequisites: A MySQL server install and a working knowledge of SQL and the MySQL ODBC Connector .

NOTES: You may need to add a reference to System.Configuration for these examples to work. I started with a Windows CSharp Command Line Project.  I also had to reboot to get the ODBC Connector to work after I installed it.

Program.cs
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data;
  6. using System.Data.Common;
  7. using System.Data.Odbc;
  8. using System.Configuration;
  9. namespace MySQLTest1
  10. {
  11.      class Program
  12.     {
  13.          static void Main( string [] args)
  14.         {
  15.              Int32 ClientID=0;
  16.              string ClientName= “” ;
  17.              string connetionString = ConfigurationManager .AppSettings[ “MySQLConnectionString” ];
  18.             System.Data.Odbc. OdbcConnection cnn = new System.Data.Odbc. OdbcConnection (connetionString);
  19.             cnn.Open();
  20.              OdbcCommand ocTableCommand = new OdbcCommand ( “SELECT ClientID, ClientName FROM Clients WHERE Active = 1” , cnn);
  21.              OdbcDataReader odrTableReader = ocTableCommand.ExecuteReader();
  22.              while (odrTableReader.Read())
  23.             {
  24.                 ClientID = odrTableReader.GetInt32(0);
  25.                 ClientName = odrTableReader.GetString(1);
  26.                  Console .WriteLine( “{0} {1}” , ClientID, ClientName);
  27.             }
  28.             cnn.Close();
  29.              Console .WriteLine( “Press any key to continue” );
  30.              Console .ReadKey();
  31.         }
  32.     }
  33. }

 

In the App.config file you will need to setup your MySQL connection.  Be sure to set the user id, server and catalog (aka database).

App.config
  1. <? xml  version = 1.0 ”  encoding = utf-8 ?>
  2. < configuration >
  3.    < appSettings >
  4.      < add key = MySQLConnectionString ”  value = DRIVER={MySQL ODBC 5.1 Driver};UID=reporting;SERVER=192.168.168.200;DATABASE=test;PORT=3306; />
  5.    </ appSettings >
  6. </ configuration >

 

Using the following to populate the “test” MySQL database.  You will need to create the test database and grant “reporting” privileges to the test database.  Note, reporting was setup without a password.

test.sql
  1. — Table structure for table Clients
  2. CREATE TABLE IF NOT EXISTS Clients (
  3.    ClientID int ( 11 ) NOT NULL AUTO_INCREMENT COMMENT ‘Customer ID’ ,
  4.    Active tinyint ( 4 ) DEFAULT ‘1’ ,
  5.    ClientName varchar ( 50 ) NOT NULL,
  6.    PRIMARY KEY ( ClientID )
  7.    ) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1 ;
  8. — Data for table Clients
  9. INSERT INTO Clients ( ClientName ) VALUES
  10. ( ‘Acme Coffee’ ),
  11. ( ‘Acme Networks’ ),
  12. ( ‘Acme Training’ ),
  13. ( ‘Acme Performance Marketing’ );

Download:  MySQLTest1 Visual Studio 2010 file

Leave a Reply