Section 1: Overview
ADO.NET is a set of computer software components that programmers can use to access data and data services.
ADO.NET relies on the .NET Framework's various classes to process requests and perform the transition between a database system and the user.
An ADO connection acts as a "pipe" from a relational database to a C# program.
ADO.NET provides connection services for a variety of databases, including Oracle, MySQL, SQL Server, DBase, DB2, Informix, Postgres, and Sybase.
The MySQL Connector/Net is a fully-managed ADO.NET driver for MySQL.
MySQL connectors, including Connector/Net, are available here.
Section 2: Using Connector/Net
First make sure you have downloaded and installed the MySQL Connector/Net from the MySQL website.
Next, in your C# project add a reference to: MySql.Data (Project | Add Reference | .NET tab)
Finally, add "using MySql.Data.MySqlClient;"
Section 3: Connecting to the Database
Declare connection object
The connection object must be declared as a New MySqlConnection. This connection string uses the Connector/Net provider for MySQL.
MySqlConnection conObject = null;
Connect to the database
There are various ways of connecting to a database. The details below pertain to a MySQL database.
A connection string can be defined as consisting of several factors, including...
-
the server on which the database resides
- server = "localhost"; can be used if the database resides on the same machine as the application
-
the port, if different than the default.
- MySQL defaults to port 3306.
- Port 3306 is blocked by ISU, so it was reset to a different port for this demo.
- the database name
- the user id
- the user password
Here is an example of a connection string:
string connectionString = "Server=134.50.5.24;" +
"Port=444;" +
"Database=cis430sp09;" +
"Uid=SQLCIS430sp09;" +
"Pwd=opensourceisbad;";
conObject = new MySqlConnection(connectionString);
conObject.Open();
Optionally, a function can be written to connect to a database:
public bool connectNet_Connect(ref MySqlConnection conObject)
{
bool functionReturnValue = false;
try
{
string connectionString = "Server=134.50.5.24;" +
"Port=444;" +
"Database=cis430sp09;" +
"Uid=SQLCIS430sp09;" +
"Pwd=opensourceisbad;";
conObject = new MySqlConnection(connectionString);
conObject.Open();
functionReturnValue = conObject.State == ConnectionState.Open;
}
catch (InvalidOperationException ex)
{
MessageBox.Show("Connection already open. " + ex.Message);
functionReturnValue = false;
}
catch (Exception ex)
{
MessageBox.Show("Connection Exception. " + ex.Message);
functionReturnValue = false;
}
MessageBox.Show("Connection " + ((conObject.State == ConnectionState.Open) ?
"established!" : "unsuccessful."));
return functionReturnValue;
}
Section 4: Querying the Database
The MySqlCommand object sends a SQL query to the database for processing. The method has the syntax
MySqlCommand ( [SQL query string], [connection object] )
The following code segment deletes the customer with customer #1 from the customer table
if (conObject.State == ConnectionState.Open)
{
string sqlString1 = "select * from phpassn where userID between 585 and 590;";
MySqlCommand objCmd = new MySqlCommand(sqlString1, conObject);
MySqlDataReader rdr = objCmd.ExecuteReader();
}
More on ExecuteReader coming up.
Executing delete queries is a bit different; they require ExecuteNonQuery.
string sqlString3 = "delete from phpassn where userID = " + objCmd.LastInsertedId;
objCmd = new MySqlCommand(sqlString3, conObject);
objCmd.ExecuteNonQuery();
Section 5: DataReader class
ADO.NET replaces the concept of data rows (recordsets) with the DataSet object.
- This essentially provides us with full access to a given database, including all rows, tables and relationships in an object-oriented and type-safe manner.
- It is, however, total overkill for the simple query and traversals that are most often performed on databases.
For simple queries .NET provides the DataReader class (OleDbDataReader, OdbcDataReader, SqlDataReader, and MySqlDataReader) that is essentially a type-safe read-only, forward-only rowset.
- The differences between the various versions of DataReaders is in which data access library they use.
- MySqlDataReader works best with MySQL, SqlDataReader works best with SQL Server, and the others work best with ODBC and OleDB data sources.
A dataReader is defined as follows (assume that the previous queries have been run to produce a DataSet.
MySqlDataReader rdr = objCmd.ExecuteReader();
Here is an example code snippet:
string sqlString1 = "select * from phpassn where userID between 585 and 590;";
MySqlCommand objCmd = new MySqlCommand(sqlString1, conObject);
MySqlDataReader rdr = objCmd.ExecuteReader();
int fldNum = 0;
string strFieldItem = null;
while (rdr.Read())
{
for (fldNum = 0; fldNum <= rdr.FieldCount - 1; fldNum++)
{
if (!rdr.IsDBNull(fldNum))
{
strFieldItem = rdr.GetString(fldNum);
output += strFieldItem + "\r\n ";
}
}
output += "\r\n";
//output = output + "\r\n" + rdr.GetString(0) + "\r\n" + rdr.GetString(1);
}
Section 6: Complete Test Program
public partial class Form1 : Form
{
private void cmdGo_Click(object sender, EventArgs e)
{
string output = null;
string connectionType = String.Empty;
MySqlConnection conObject = null;
bool status = connectNet_Connect(ref conObject);
if (conObject.State == ConnectionState.Open)
{
string sqlString1 = "select * from phpassn where userID between 585 and 590;";
MySqlCommand objCmd = new MySqlCommand(sqlString1, conObject);
MySqlDataReader rdr = objCmd.ExecuteReader();
int fldNum = 0;
string strFieldItem = null;
while (rdr.Read())
{
for (fldNum = 0; fldNum <= rdr.FieldCount - 1; fldNum++)
{
if (!rdr.IsDBNull(fldNum))
{
strFieldItem = rdr.GetString(fldNum);
output += strFieldItem + "\r\n ";
}
}
output += "\r\n";
//output = output + "\r\n" + rdr.GetString(0) + "\r\n" + rdr.GetString(1);
}
rdr.Close();
conObject.Close();
}
txtOutput.Text = output;
}
public bool connectNet_Connect(ref MySqlConnection conObject)
{
bool functionReturnValue = false;
try
{
string connectionString = "Server=134.50.5.24;" +
"Port=444;" +
"Database=cis430sp09;" +
"Uid=SQLCIS430sp09;" +
"Pwd=opensourceisbad;";
conObject = new MySqlConnection(connectionString);
conObject.Open();
functionReturnValue = conObject.State == ConnectionState.Open;
}
catch (InvalidOperationException ex)
{
MessageBox.Show("Connection already open. " + ex.Message);
functionReturnValue = false;
}
catch (Exception ex)
{
MessageBox.Show("Connection Exception. " + ex.Message);
functionReturnValue = false;
}
MessageBox.Show("Connection " + ((conObject.State == ConnectionState.Open) ?
"established!" : "unsuccessful."));
return functionReturnValue;
}
}