MySQL Connection using ADO.Net

1
MySQL Connection using ADO.Net

• Connecting to MySQL from .NET Languages

2
ADO.Net Architecture

• ADO.Net provides an architecture for
  communicating between an application and a data
  source.
• The data source can be anything that has the
  required API, but usually it is a database server.

Data Source
Connection Object(ADO.Net)
Data Adaptor
Dataset (Local)
Application
3
MySQL ADO.Net Provider

• MySqlConnection is main connection to the MySQL
  database
• MySqlCommand enables the execution of any command
  against the database.
• MySqlDataReader provides fast, forward-only read
  access to the database.
• MySqlDataAdapter serves as an interface between
  the MySQL data classes and the Microsoft DataSet.
  
• MySqlParameter used to store dynamic parameters
  for a command.
• MySqlTransaction used to represent a MySQL
  transaction.

4
Getting MySqlConnector.Net

• You need a MySql "Connector" for .Net
  applications.
• Download from
• http//dev.mysql.com/downloads/
• Run the installer.
• The connector registers itself with the "Global
  Assembly Cache" so that the DLL can be found.
• Difference from Java Java uses a CLASSPATH to
  find code Visual Studio uses Windows Registry to
  find resources.
• (Optional for visual programming) Add the
  components to the Toolbox in Visual
  Studio Tools -gt Add/Remove Toolbox
  Items...or Tools -gt Choose Toolbox Items...

5
Undefined MySql Namespace in C

• After installing MySqlConnector.Net, in your
  project you would add its name space to your C
  source code
• using MySql.Data.MySqlClient
• but, you may get a compiler error that the
  "MySql" name space is not found.
• in this case, add a reference to the Connector's
  DLL file
• Project -gt Add Reference -gt Browse
• Find the .Net2.0 MySqlData.dll file, ex
• C/MySql/MySqlConnector.Net/bin/.Net
  2.0/MySqlData.dll
• This should fix the name space problem.

6
Creating a Connection Object

• Connection Object manages the connection to
  database server.
• You must specify server name, username, password
• Can omit unnecessary attributes or leave blank.

string connectString "Data SourcelocalhostData
basebankUser IdbankPasswordFatChance" MySqlC
onnection myconn new MySqlConnection(
connectString )
Better programming
public DBConnection(string host, string
database, string user, string pass)
string connectString String.Format( "Data
Source0Database1User Id2Password3",
host, database, user, pass) MySqlConnection
myconn new MySqlConnection( connectString )
7
Opening the Connection

• After creating connection, open it.
• This may throw a MySqlException

MySqlConnection myconn null try myconn
new MySqlConnection( connectString
) myconn.Open() catch ( MySqlException e )
Console.WriteLine("Error connecting to
server "e.Message)
8
Creating a Command Object

• Use a MySqlCommand object to issue database cmds
• A Command object is like a Java Statement object.
• You can reuse a Command object.
• Requires a Connection object (myconn) as param.

MySqlCommand cmd new MySqlCommand("SHOW
TABLES", myconn)

• Method of executing command depends on the SQL
  statement
• UPDATE, INSERT, DELETE cmd.ExecuteNonQuery()
  returns int.
• SHOW (QUERY) cmd.ExecuteReader() returns
  MySqlDataReader

Semi-colon
MySqlDataReader reader cmd.ExecuteReader( )
9
Processing Query Data

• MySqlDataReader has many methods for getting data
  by column number, column name, or index.
• Iterate over results using (boolean) reader.Read(
  )

MySqlDataReader reader null try reader
cmd.ExecuteReader( ) if ( reader null )
Console.WriteLine("ExecuteReader
failed") return while( reader.Read() )
Console.WriteLine( reader.GetString(0) )
catch ( MySqlException e)
Console.WriteLine("caught exception "
e.Message ) finally if (reader ! null)
reader.Close()
10
Resources

• MySQL
• http//dev.mysql.com/tech-resources/articles/dotne
  t/
• Learning SQL
• http//www.w3schools.com/sql/ nice tutorial and
  command reference
• Learning JDBC
• JDBC Trail in Sun's Java Tutorial.
• Dietel, Java How To Program, Chapter 25.
• ... and zillions of resources on the web