Transcript 6-Database-Csharp

MySQL Connection using ADO.Net
Connecting to MySQL from .NET Languages
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
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, forwardonly 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 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:
or
Tools -> Add/Remove Toolbox Items...
Tools -> Choose Toolbox Items...
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:
1. Project -> Add Reference -> Browse
2. 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.
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 = String.Format(
"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 );
myconn.Open();
}
catch ( MySqlException e )
{
Console.WriteLine("Error connecting to server: "+e.Message);
}
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: cmd.ExecuteNonQuery() returns int.
 SHOW (QUERY): cmd.ExecuteReader() returns MySqlDataReader
MySqlDataReader reader = cmd.ExecuteReader( )
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();
}
Resources
MySQL
 http://dev.mysql.com/tech-resources/articles/dotnet/
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