MySQL Connection using ADO.
Net
Connecting to MySQL from .NET Languages
[Link] Architecture
[Link] 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 Connection Data Adaptor Dataset Application
Source Object (Local)
([Link])
MySQL [Link] 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.
Getting [Link]
You need a MySql "Connector" for .Net applications.
Download from
[Link]
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 -> Add/Remove Toolbox Items...
or Tools -> Choose Toolbox Items...
Undefined MySql Namespace in C#
After installing [Link], in your project you
would add its name space to your C# source code
using [Link];
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:
1. Project -> Add Reference -> Browse
2. Find the .Net2.0 [Link] file, ex:
C:/MySql/[Link]/bin/.Net 2.0/[Link]
This should fix the name space problem.
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 Source=localhost;Database=bank;User
Id=bank;Password=FatChance";
MySqlConnection myconn = new MySqlConnection( connectString );
Better programming:
public DBConnection(string host, string database,
string user, string pass) {
string connectString = [Link](
"Data Source={0};Database={1};User Id={2};Password={3}",
host, database, user, pass);
MySqlConnection myconn = new MySqlConnection( connectString );
}
Opening the Connection
After creating connection, open it.
This may throw a MySqlException
MySqlConnection myconn = null;
try {
myconn = new MySqlConnection( connectString );
[Link]();
}
catch ( MySqlException e )
{
[Link]("Error connecting to server: "+[Link]);
}
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: Semi-colon
UPDATE, INSERT, DELETE: [Link]() returns int.
SHOW (QUERY): [Link]() returns MySqlDataReader
MySqlDataReader reader = [Link]( )
Processing Query Data
MySqlDataReader has many methods for getting data by column number,
column name, or index.
Iterate over results using (boolean) [Link]( )
MySqlDataReader reader = null;
try {
reader = [Link]( );
if ( reader == null ) {
[Link]("ExecuteReader failed");
return;
}
while( [Link]() ) {
[Link]( [Link](0) );
}
} catch ( MySqlException e) {
[Link]("caught exception " + [Link] );
} finally {
if (reader != null) [Link]();
}
Resources
MySQL
[Link]
Learning SQL
[Link]
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